Oracle SQL
Index
1. Installation of Oracle 11g and SQL Developer
i) Create and Insert data into emp and dept tables
2. SQL Statements
3. Built-in Functions
i) Single Row Functions
ii) Aggregate Functions
iii) Analytic Functions
iv) Object Reference Functions
v) Model Functions
4. NVL2
5. INSTRING
6. SUBSTRING
7. MODULUS
8. TRANSLATE
9. REPLACE
10. DECODE
11. TRIM
12. PADDING
13. ROUND
14. DUMP
15. REGEXP_REPLACE
16. REGEXP_SUBSTR
17. NULLIF
18. Sort varchar2 field as a numeric field
19. LISTAGG
20. NEXT_DAY
21. Query to fetch hold details in Order Management
22. Query to Fetch Order#, Invoice#, Receipt# for given Customer profile
23. Query to fetch Request Group and Responsibility for given Concurrent Program Name
24. Query to confirm whether the bug has been applied to instance
25. Query for BPA (Bill Presentment Architecture) to get the fields which are assigned to particular template
26. Query for for mapping between SC (Service Contracts) and AR
27. Query to get user details
28. Query to identify the login URL of an instance from the Database
29. Query to fetch Menus & Functions for a given Responsibility
30. Fetch data having CLOB datatype
31. Query to fetch Order Header Sold to details
32. Query to fetch Order Header Bill to details
33. Query to fetch Order Header Ship to details
34. Query to fetch Order Header End to details
35. Query to fetch Order Header Deliver to details
36. Query to fetch Order Line Ship to details
37. Query to fetch Order Line End to details
38. Query to fetch Order Line Deliver to details
39. Query to fetch Order Line Bill to details
40. PSEUDO Columns
41. Sequence
42. Release (Version) Name
43. INSERT
44. Characters in string
45. DELETE
46. UPDATE
47. ALTER
48. Rename column name in Table
49. Add column to Table
50. Modify column/ column datatype in a Table
51. Query to fetch Customer BILL_TO details
52. Query to fetch Customer SHIP_TO details
53. P2P query
54. Query to find list of personalizations on OAF page
1. Installation of Oracle 11g and SQL Developer
Step 1: In Google search for ‘download oracle express edition 11g’ and click on link ‘Oracle Database Express Edition (XE) Downloads’. Click on prior release in case 18c version comes. (https://www.oracle.com/database/technologies/xe-prior-releases.html)
Step 2: Download the version compatible with your machine (Windows 64-bit with JDK 8 included)
Step 3: Accept Oracle Licence Agreement and download
Step 4: Provide your Oracle credentials. In case you don’t have please create Oracle account at free cost.
Step 5: Extract All
Step 6: Open sqldeveloper with type as Application
Step 7: Open command prompt (cmd)
Step 8: Type ‘SQLPLUS / AS SYSDBA’
Step 9: Type ‘ALTER USER SYS ACCOUNT UNLOCK IDENTIFIED BY password’;
Step 10: In SQL Developer click on new (+) connection and provide values as below:
| Connection Name | SYS |
| Username | SYS |
| Password | Give same password as in Step 9 |
| Connection Type | TNS |
| Role | SYSDBA |
| Network Alias | ORCLPDB |
| Click on Test | Status should be Success |
| Click on Connect |
--https://www.youtube.com/watch?v=cci3PifT54U
--SYSTEM user password is oracle
DROP USER oradb;
CREATE USER oradb IDENTIFIED BY oracle123;
grant CREATE SESSION,
ALTER SESSION,
ALTER USER,
CREATE DATABASE LINK,
CREATE MATERIALIZED VIEW,
CREATE PROCEDURE,
CREATE PUBLIC SYNONYM,
CREATE ROLE,
CREATE SEQUENCE,
CREATE SYNONYM, CREATE table,
CREATE TRIGGER,
CREATE TYPE,
CREATE VIEW,
CREATE ANY INDEX,
UNLIMITED TABLESPACE
TO oradb;
i) Create and Insert data into emp and dept tables
CREATE TABLE dept (
deptno NUMBER(2,0)
,dname VARCHAR2(14)
,loc VARCHAR2(13)
,CONSTRAINT pk_dept PRIMARY KEY ( deptno )
);
CREATE TABLE emp (
empno NUMBER(4,0)
,ename VARCHAR2(10)
,job VARCHAR2(9)
,mgr NUMBER(4,0)
,hiredate DATE
,sal NUMBER(7,2)
,comm NUMBER(7,2)
,deptno NUMBER(2,0)
,CONSTRAINT pk_emp PRIMARY KEY ( empno )
,CONSTRAINT fk_deptno FOREIGN KEY ( deptno ) REFERENCES dept ( deptno )
);
INSERT INTO dept (deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('13-JUL-87','dd-mm-rr') - 85,3000,NULL,20);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('13-JUL-87','dd-mm-rr') - 51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
2. SQL Statements
| Data Definition Language (DDL) Statements | Data Manipulation Language (DML) Statements | Transaction Control Statements | Session Control Statements | System Control Statements | Embedded SQL Statements |
| CREATE | INSERT | COMMIT | ALTER SESSION | ALTER SYSTEM | |
| ALTER | UPDATE | ROLLBACK | SET ROLE | ||
| DROP | DELETE | SAVEPOINT | |||
| TRUNCATE | MERGE | SET TRANSACTION | |||
| GRANT | SELECT | SET CONSTRAINT | |||
| REVOKE | EXPLAIN PLAN | ||||
| RENAME | CALL | ||||
| PURGE | LOCK TABLE | ||||
| AUDIT | |||||
| NOAUDIT | |||||
| COMMENT | |||||
| ANALYZE | |||||
| FLASHBACK | |||||
| ASSOCIATE STATISTICS | |||||
| DISASSOCIATE STATISTICS |
3. Built-in Functions
| Single Row Functions |
| Aggregate Functions |
| Analytic Functions |
| Object Reference Functions |
| Model Functions |
i) Single Row Functions
SELECT
--CEIL returns smallest integer greater than or equal to n
ceil(123.0) ceil_a --123
,ceil(123.01) ceil_b --124
,ceil(123.1) ceil_c --124
,ceil(123.5) ceil_d --124
,ceil(123.9) ceil_e --124
,ceil(-123.0) ceil_f -- -123
,ceil(-123.01) ceil_g -- -123
,ceil(-123.1) ceil_h -- -123
,ceil(-123.5) ceil_i -- -123
,ceil(-123.9) ceil_j -- -123
--FLOOR returns largest integer less than or equal to n
,floor(123.0) floor_a --123
,floor(123.01) floor_b --123
,floor(123.1) floor_c --123
,floor(123.5) floor_d --123
,floor(123.9) floor_e --123
,floor(-123.0) floor_f -- -123
,floor(-123.01) floor_g -- -124
,floor(-123.1) floor_h -- -124
,floor(-123.5) floor_i -- -124
,floor(-123.9) floor_j -- -124
--MOD returns remainder
,mod(11,4) mod_a --3
,mod(11,-4) mod_b --3
,mod(-11,4) mod_c -- -3
,mod(-11,-4) mod_d -- -3
--ROUND returns n integer places to the right of decimal point. >4 same and <=5 next 10th number
,round(123.0) round_a --123
,round(123.12345,1) round_b --123.1
,round(123.45678,1) round_c --123.5
,round(123.56789,1) round_d --123.6
,round(123.90123,1) round_e --123.9
,round(123.12345,2) round_f --123.12
,round(123.45678,2) round_g --123.46
,round(123.56789,2) round_h --123.57
,round(123.90123,2) round_i --123.9
--CHR returns ASCII characters
,CHR(67) || CHR(65) || CHR(84) chr_a --CAT
--CONCAT concatenates char1 and char2
,concat('A',' and B') concat_a --A and B
--INITCAP only first letter in uppercase
,initcap('ORACLE') initcap_a --Oracle
,initcap('oracle') initcap_b --Oracle
--LOWER returns value in lower case
,lower('ORACLE') lower_a --oracle
,lower('oracle') lower_b --oracle
--UPPER returns value in upper case
,upper('ORACLE') upper_a --ORACLE
,upper('oracle') upper_b --ORACLE
--LPAD left side values will be padded
,lpad('Oracle',10,'*') lpad_a --****Oracle
,lpad('OracleOracle',10,'*') lpad_b --OracleOrac
--RPAD right side values will be padded
,rpad('Oracle',10,'*') rpad_a --Oracle****
,rpad('OracleOracle',10,'*') rpad_b --OracleOrac
--SUBSTR returns n characters till nth position
,substr('ABCDEFG',3,4) substr_a --CDEF
--REGEXP_SUBSTR
,regexp_substr('This is to identify,string, in between ,first, comma',',[^,]+,') regexpr_substr_a --,string,
--REGEXP_REPLACE
,regexp_replace('This is to identify,string, in between ,first,comma',',[^,]+,') regexpr_replace_a --This is to identify in between comma
--REPLACE String substitute
,replace('JACK and JUE','J','BL') replace_a --BLACK and BLUE
--TRANSLATE Character by character substitute
,translate('JACK and JUE','J','BL') translate_a --BACK and BUE
--ASCII returns decimal representation
,ascii('C') || ascii('A') || ascii('T') ascii_a --676584
--INSTR returns number of nth position to nth occurrence
,instr('Oracle Corporation','a',4,1) instr_a--14
--LENGTH returns length of string
,length('Oracle Corporation') length_a--18
--REGEXP_INSTR
,regexp_instr('This is to identify,string, in between ,first, comma',',[^,]+,') regexpr_instr_a--20
--datetime functions
,current_date --11-NOV-2019 11:22:52
,SYSDATE --11-NOV-2019 11:22:52
,trunc(SYSDATE) --11-NOV-2019 00:00:00
,last_day(SYSDATE) --30-NOV-2019 11:22:52
,next_day(SYSDATE,'MONDAY') --18-NOV-2019 11:22:52
,sessiontimezone --Europe/London
,current_timestamp --11-NOV-2019 11:22:52
--TO_CHAR converts to character datatype
,TO_CHAR('01110') to_char_a --01110
,TO_CHAR(01110) to_char_b --1110
,TO_CHAR(SYSDATE,'DD-MM-YY') to_char_c --11-11-19
--TO_NUMBER converts to number datatype
,to_number('01110') to_number_a --1110
,to_number(01110) to_number_b --1110
--TO_DATE converts to date datatype
,TO_DATE('01-JAN-2020','DD-MM-RRRR HH24:MI:SS') to_date_a --01-JAN-2020 00:00:00
--NVL2(String, NOT NULL, NULL)
,nvl2('String','Not Null','Null') nvl2 --Not Null
FROM
dual;
--TRIM Trims leading or trailing characters from string
SELECT
empno --7788
,TRIM(7 FROM empno) trim_empno --88
,hiredate --02-APR-1981 00:00:00
,TRIM(0 FROM hiredate) trim_hiredate --2-APR-1981 00:00:
--LTRIM Trims leading or left side characters from string
,empno --7788
,ltrim(empno,7) ltrim_empno --88
--RTRIM Trims trailing or right side characters from string
,empno --7566
,rtrim(empno,6) rtrim_empno --75
--ADD_MONTHS add months to date
,hiredate add_months_a
,add_months(hiredate,1) add_months_b
,add_months(hiredate,12) add_months_c
--MONTHS_BETWEEN number of months between two dates
,months_between(TO_DATE('02-02-1995','MM-DD-YYYY'),TO_DATE('01-01-1995','MM-DD-YYYY') ) months_between_a --1.03225806451612903225806451612903225806
FROM
emp;
--EXTRACT Extracts year, month, day, hour…
SELECT EXTRACT(YEAR FROM hiredate) year
,COUNT(empno) emps
FROM emp
GROUP BY EXTRACT(YEAR FROM hiredate)
ORDER BY emps DESC;
iv) Object Reference Functions
| deref |
| make_ref |
| ref |
| reftohex |
| value |
v) Model Functions
| cv |
| iteration_number |
| presentnnv |
| presentv |
| previous |
4. NVL2
NVL2(String, NOT NULL, NULL)
SELECT nvl2('String','Not Null','Null') nvl2
FROM dual;
Result: Not Null
SELECT nvl2(NULL,'Not Null','Null') FROM dual; Result: Null
5. INSTRING
INSTRING: Returns position (number)
INSTR(Column/ Expression, Character, m, n) — Returns position of character from mth position and nth occurance
SELECT INSTR ('Stringtt', 't', 1, 1) FROM DUAL
Result: 2
SELECT INSTR (‘Stringtt’, ‘t’, 1, 3) FROM DUAL
Result: 8
Query: SELECT INSTR (‘Stringtt’, ‘t’, 1, 3) FROM DUAL
Result: 8
Query: SELECT INSTR (‘Stringtt’, ‘g’, 1, 2) FROM DUAL
Result: 0
Query: SELECT INSTR (‘Stringtt’, ‘g’, 4, 1) FROM DUAL
Result: 6
6. SUBSTRING
SUBSTRING: Returns characters
SUBSTR(Column/ Expression, m, n) — Displays characters which starts from m position till n character
Query: SELECT SUBSTR (‘String’, 1, 3) FROM DUAL
Result: Str
Query: SELECT SUBSTR (‘String’, 2, 4) FROM DUAL
Result: trin
Query: SELECT SUBSTR (‘String’, -3, 2) FROM DUAL
Result: in
7. MODULUS
MODULUS: Returns denominator (Either 0 or 1)
MOD(m, n) — Returns m/n value and is used to confirm whether the given number is even or odd.
IF MOD(m, 2) = 0 THEN its even number else odd number
Query: SELECT MOD (12345, 2) FROM DUAL
Result: 1
Query: SELECT MOD (12346, 2) FROM DUAL
Result: 0
8. TRANSLATE
TRANSLATE: Replace one-to-one character in a string
Query: SELECT TRANSLATE (‘123SRI’, ‘S3’, ‘4T’) FROM DUAL
Result: 12T4RI
Query: SELECT TRANSLATE (‘123SRI’, ‘3S’, ‘4T’) FROM DUAL
Result: 124TRI
Query: SELECT TRANSLATE (‘123SRI’, ‘S2’, ‘4’) FROM DUAL
Result: 134RI
Query: SELECT TRANSLATE (‘123SRI0’, ‘.3S’, ‘.’) FROM DUAL
Result: 12RI0 — After . characters wont display
Query: SELECT TRANSLATE (‘ABCDE01230450’, ‘.123456789’, ‘.’) FROM DUAL
Result: ABCDE000
Query: SELECT TRANSLATE (‘ABCDE01230450’, ‘0’||’ABCDE’, ‘0’) FROM DUAL
Result: 01230450
9. REPLACE
REPLACE: Replace characters in a string which are in sequence
Query: SELECT REPLACE (‘123SRI’, ‘S3’, ‘4T’) FROM DUAL
Result: 123SRI
Query: SELECT REPLACE (‘123SRI’, ‘3S’, ‘4T’) FROM DUAL
Result: 124TRI
Query: SELECT REPLACE (‘123SRI0’, ‘3S’, ‘.’) FROM DUAL
Result: 12.RI0
Also used to remove ascii characters. When user gives space bar or presses enter button in data field then ascii characters like CHR(10) or CHR(13) inserts into DB. To remove those acsii character we use REPLACE.
Query: SELECT REPLACE (hcp.email_address, CHR (13), ”)
FROM hz_contact_points hcp
10. DECODE
DECODE(Column, Value1, Substitute1, Value2, Substitute2, Default_Substitute)
Query: SELECT DECODE (‘String1’, ‘String1’, ‘Substitute1’, ‘String2’, ‘Substitute2’, ‘Default’) FROM DUAL
Result: Substitute1
Query: SELECT DECODE (‘String2’, ‘String1’, ‘Substitute1’, ‘String2’, ‘Substitute2’, ‘Default’) FROM DUAL
Result: Substitute2
Query: SELECT DECODE (‘String3’, ‘String1’, ‘Substitute1’, ‘String2’, ‘Substitute2’, ‘Default’) FROM DUAL
Result: Default
By now you will be able to find the difference between TRANSLATE, REPLACE and DECODE
TRANSLATE: One-to-one character
REPLACE: In sequence characters
DECODE: Complete string
11. TRIM
Trimming: Removes unwanted data/ blank spaces and trimming is case sensitive. Trims each character wise.
We can use TRIM or LTRIM or RTRIM
Query: SELECT (‘xyzZxyLASTWORD’) STRING, –xyzZxyLASTWORD
LTRIM (‘xyzZxyLASTWORD’, ‘L’) LTRIM1, –xyzZxyLASTWORD
LTRIM (‘xyzZxyLASTWORD’, ‘xyzX’) LTRIM2, –ZxyLASTWORD
LTRIM (‘xyzZxyLASTWORD’, ‘xyzL’) LTRIM3, –ZxyLASTWORD
LTRIM (‘xyzZxyLASTWORD’, ”) LTRIM4, –NULL
RTRIM (‘xyzZxyLASTWORD’, ‘L’) RTRIM1, –xyzZxyLASTWORD
RTRIM (‘xyzZxyLASTWORD’, ‘D’) RTRIM2, –xyzZxyLASTWOR
RTRIM (‘xyzZxyLASTWORD’, ‘WORD’) RTRIM3, –xyzZxyLAST
RTRIM (‘xyzZxyLASTWORD’, ”) RTRIM4 –NULL
FROM DUAL
12. PADDING
Padding: Adding extra characters to adjust the width
Query: SELECT LPAD (‘Sample’, 4, ‘*’) FROM DUAL
Result: Samp
Query: SELECT LPAD (‘Sample’, 7, ‘*’) FROM DUAL
Result: *Sample
Query: SELECT LPAD (‘Sample’, 10, ‘*’) FROM DUAL
Result: ****Sample
Query: SELECT RPAD (‘Sample’, 10, ‘*’) FROM DUAL
Result: Sample****
13. ROUND
First digit after decimal. Between 5-9 next number else same
SELECT ROUND (45.953) FROM DUAL — 46
SELECT ROUND (44.553) FROM DUAL — 45
SELECT ROUND (48.653) FROM DUAL — 49
SELECT ROUND (45.453) FROM DUAL — 45
SELECT ROUND (44.353) FROM DUAL — 44
SELECT ROUND (48.253) FROM DUAL — 48
SELECT ROUND (45.953, 1) FROM DUAL — 46
SELECT ROUND (44.553, 1) FROM DUAL — 44.6
SELECT ROUND (48.653, 1) FROM DUAL — 48.7
SELECT ROUND (45.453, 1) FROM DUAL –45.5
SELECT ROUND (44.353, 1) FROM DUAL — 44.4
SELECT ROUND (48.253, 1) FROM DUAL — 48.3
SELECT ROUND (45.953, -1) FROM DUAL — 50
SELECT ROUND (44.553, -1) FROM DUAL — 40
SELECT ROUND (48.653, -1) FROM DUAL — 50
SELECT ROUND (45.453, -1) FROM DUAL –50
SELECT ROUND (44.353, -1) FROM DUAL — 40
SELECT ROUND (48.253, -1) FROM DUAL — 50
14. DUMP
DUMP: Use DUMP to get ASCII characters. ASCII stands for American Standard Code for Information Interchange.
Query: SELECT DUMP(‘A B’) FROM DUAL
Result: Typ=96 Len=3: 65,32,66
In ASCII table we have 65 for A, 32 for SPACE and 66 for B
Query: SELECT DUMP(‘A
B’) FROM DUAL
Result: Typ=96 Len=3: 65,10,66
Here 10 denotes new line and 96 denotes data type code and without using LENGTH function we can get length from Len
This function plays crucial role in to determine blank value in Reports. Though we dont have any data due to special characters like new line CHR(10) or carriage return CHR(13) we still find element name to be displayed.
15. REGEXP_REPLACE
The below syntax replace extra spaces between words with single space
Query: SELECT REGEXP_REPLACE (‘This is to demonstrate REGEXP_REPLACE, Function’,
‘( ){1,}’,
‘ ‘)
“REGEXP_REPLACE”
FROM DUAL;
Result: This is to demonstrate REGEXP_REPLACE, Function
16. REGEXP_SUBSTR
Checks whether any character exists in given string or not
Query: SELECT REGEXP_SUBSTR (:v_exp, ‘[abc]’) FROM DUAL
Result: If :v_exp = sdfcjhk then we have value as c
If :v_exp = ioijhsdfg then we have NULL value
If :v_exp = kjbac then we have value as b
Query: SELECT NVL(REGEXP_SUBSTR (:v_exp, ‘[.]’), ‘NULL’) FROM DUAL
Result: If :v_exp = abc123 then we have NULL string
If :v_exp = abc.123 then we have ‘.’ value
17. NULLIF
NULLIF(column1, column2)
If column1 = column2 then NULL returns else column1
Query: SELECT NULLIF (‘A’, ‘B’) FROM DUAL
Result: A
Query: SELECT NULLIF (‘A’, ‘A’) FROM DUAL
Result: NULL
–> GROUP Separator: TO_CHAR(column_name, ‘999G999D99999’)
18. Sort varchar2 field as a numeric field
1) SELECT *
FROM po_headers_all
ORDER BY CAST (segment1 AS INTEGER);
2) SELECT *
FROM hz_cust_accounts_all
ORDER BY LPAD (account_number, 30);
3) SELECT * FROM hz_cust_accounts_all
ORDER BY TO_NUMBER (REGEXP_SUBSTR (account_number, ‘[0-9]+’,1,1));
19. LISTAGG
LISTAGG – Returns single column data in one row
• SELECT LISTAGG(last_name, ‘; ‘) WITHIN GROUP (ORDER BY hire_date) “Employees” FROM EMP; — All last_names will be displayed in single row with ; separator
• SELECT LISTAGG(last_name, ‘; ‘) WITHIN GROUP (ORDER BY hire_date) “Employees” FROM EMP GROUP BY DEPTNO;
• SELECT DEPTNO, LISTAGG(last_name, ‘; ‘) WITHIN GROUP (ORDER BY hire_date) “Employees” FROM EMP GROUP BY DEPTNO;
20. NEXT_DAY
NEXT_DAY: Returns the date of next week of first Sunday.
SELECT NEXT_DAY (SYSDATE, ‘Sunday’) FROM DUAL;
21. Query to fetch hold details in Order Management
/*Holds applied at header level*/
SELECT hca.account_number customer,
ooha.order_number order#,
ooha.ordered_date,
hp.party_name customer_name,
hr.NAME operating_unit,
(SELECT SUM (ordered_quantity * ool.unit_selling_price + tax_value)
FROM oe_order_lines_all ool
WHERE ool.header_id = ooha.header_id)
order_amount,
ooha.transactional_curr_code,
ohd.NAME hold_type,
ohsa.released_flag hold_released_flag,
ohsa.last_update_date hold_released_date,
fu.description hold_applied_by,
fu.user_name
FROM oe_order_headers_all ooha,
hr_operating_units hr,
hz_parties hp,
hz_cust_accounts hca,
oe_order_holds_all oha,
oe_hold_sources_all ohsa,
oe_hold_definitions ohd,
hz_cust_profile_classes hcpc,
hz_customer_profiles hcp,
fnd_user fu
WHERE hr.organization_id(+) = ooha.org_id
AND hca.cust_account_id = ooha.sold_to_org_id
AND hca.party_id = hp.party_id(+)
AND ooha.header_id = oha.header_id(+)
AND oha.hold_source_id = ohsa.hold_source_id(+)
AND ohsa.hold_id = ohd.hold_id(+)
AND hcpc.profile_class_id = hcp.profile_class_id(+)
AND hcp.cust_account_id = hca.cust_account_id(+)
AND ohsa.released_flag = 'Y'
AND ohsa.last_updated_by = fu.user_id(+)
AND TRUNC (ohsa.last_update_date) BETWEEN ’01-Jan-2014′ AND SYSDATE
AND fu.user_name IN (‘Enter User Name’)
AND oha.line_id IS NULL
UNION
/*Holds applied at line level*/
SELECT hca.account_number sold_to_acc#,
ooha.order_number,
ooha.ordered_date,
hp.party_name sold_to_part_name,
hr.NAME ou_name,
(SELECT SUM (ordered_quantity * ool.unit_selling_price + tax_value)
FROM oe_order_lines_all ool
WHERE ool.header_id = ooha.header_id)
order_amount,
ooha.transactional_curr_code,
ohd.NAME hold_name,
ohsa.released_flag,
ohsa.last_update_date released_date,
fu.description user_name,
fu.user_name
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hr_operating_units hr,
hz_parties hp,
hz_cust_accounts hca,
oe_order_holds_all oha,
oe_hold_sources_all ohsa,
oe_hold_definitions ohd,
hz_cust_profile_classes hcpc,
hz_customer_profiles hcp,
fnd_user fu
WHERE hr.organization_id(+) = ooha.org_id
AND hca.cust_account_id = ooha.sold_to_org_id
AND hca.party_id = hp.party_id(+)
AND ooha.header_id = oola.header_id(+)
AND oola.line_id = oha.line_id(+)
AND oha.hold_source_id = ohsa.hold_source_id(+)
AND ohsa.hold_id = ohd.hold_id(+)
AND hcpc.profile_class_id = hcp.profile_class_id(+)
AND hcp.cust_account_id = hca.cust_account_id(+)
AND ohsa.released_flag = 'Y'
AND ohsa.last_updated_by = fu.user_id(+)
AND TRUNC (ohsa.last_update_date) BETWEEN '01-Jan-2014' AND SYSDATE
AND fu.user_name IN (‘Enter User Name’)22. Query to Fetch Order#, Invoice#, Receipt# for given Customer profile
SELECT hcpc.profile_class_id
,hcpc.name
,hcpc.description
,hcpa.currency_code profile_currency
,hcaa.account_number
,hu.name ou
,ooh.order_number
,ppa.segment1 project_number
,rct.trx_number invoice_number
,acra.receipt_number
FROM apps.hz_cust_profile_classes hcpc
,apps.hz_customer_profiles hc
,apps.hz_cust_profile_amts hcpa
,apps.hz_cust_accounts_all hcaa
,apps.oe_order_headers_all ooh
,apps.pa_projects_all ppa
,apps.hr_operating_units hu
,apps.ra_customer_trx_all rct
,apps.ar_cash_receipts_all acra
,apps.ar_receivable_applications_all ara
WHERE hcpc.profile_class_id = hc.profile_class_id
AND hc.cust_account_id = hcaa.cust_account_id
AND hc.cust_account_profile_id = hcpa.cust_account_profile_id
AND hcpa.currency_code = ooh.transactional_curr_code
AND ooh.sold_to_org_id = hcaa.cust_account_id
AND TO_CHAR(
ooh.order_number
) = TO_CHAR(
ppa.name
)
AND ooh.org_id = hu.organization_id
AND TO_CHAR(
ooh.order_number
) = ( rct.ct_reference (+) )
AND rct.customer_trx_id = ara.applied_customer_trx_id (+)
AND ara.cash_receipt_id = acra.cash_receipt_id (+)
AND hcpc.name =:name
ORDER BY order_number DESC;23. Query to fetch Request Group and Responsibility for given Concurrent Program Name
SELECT distinct a.user_concurrent_program_name ,d.responsibility_name ,c.request_group_name FROM fnd_concurrent_programs_tl a ,fnd_request_group_units b ,fnd_request_groups c ,fnd_responsibility_vl d WHERE a.concurrent_program_id = b.request_unit_id AND b.request_group_id = c.request_group_id AND c.request_group_id = d.request_group_id AND a.user_concurrent_program_name LIKE '%Enter Program Name%';
24. Query to confirm whether the bug has been applied to instance
SELECT * FROM ad_bugs WHERE bug_number = '';25. Query for BPA (Bill Presentment Architecture) to get the fields which are assigned to particular template
SELECT b.field_name "Field Name"
,c.item_description "View Name"
,c.item_code "Item Code"
,c.item_name "Item Name"
,c.column_name "View Column Name"
FROM ar_bpa_templates_vl a
,ar_bpa_template_fields b
,ar_bpa_items_vl c
WHERE 1 = 1
AND a.template_id = b.template_id
AND b.item_id = c.item_id
AND a.template_name = 'Template Name'
ORDER BY 1;26. Query for for mapping between SC (Service Contracts) and AR
SELECT jtl.notes
FROM jtf_notes_tl jtl
,jtf_notes_b jnb
,okc_k_lines_b okl
,okc_k_headers_all_b okh
,ra_customer_trx_all rcta
WHERE 1 = 1
AND jtl.jtf_note_id = jnb.jtf_note_id
AND jtl.language = 'US'
AND TO_CHAR(
jnb.source_object_id
) = TO_CHAR(
okl.id
)
AND okh.id = okl.dnz_chr_id
AND rcta.ct_reference = okh.contract_number
AND okh.sts_code = 'ACTIVE'
AND rcta.trx_number =:trx_number;27. Query to get user details
SELECT fu.user_name
,fu.email_address
,hzp.person_first_name
,hzp.person_last_name
,hzp.person_middle_name
,hzp.person_name_suffix
,hzp.person_pre_name_adjunct
FROM fnd_user fu
,hz_parties hzp
WHERE fu.user_id = p_user_id
AND hzp.party_id = fu.person_party_id;28. Query to identify the login URL of an instance from the Database
SELECT home_url FROM icx_parameters;
29. Query to fetch Menus & Functions for a given Responsibility
SELECT DISTINCT b.entry_sequence
,b.prompt
,b.description
,b.grant_flag
, ( SELECT a.menu_name
FROM fnd_menus a
WHERE a.menu_id = b.menu_id
) menu_name
, ( SELECT a.menu_name
FROM fnd_menus a
WHERE a.menu_id = b.sub_menu_id
) sub_menu_name
, ( SELECT a.user_function_name
FROM fnd_form_functions_vl a
WHERE a.function_id = b.function_id
) function_name
,b.last_update_date
, ( SELECT g.user_name
FROM fnd_user g
WHERE g.user_id = b.last_updated_by
) last_updated_by
, ( SELECT g.user_name
FROM fnd_user g
WHERE g.user_id = b.created_by
) created_by
,b.creation_date
, ( SELECT g.user_name
FROM fnd_user g
WHERE g.user_id = b.last_update_login
) last_update_login
FROM fnd_menu_entries_vl b START WITH
b.menu_id IN ( SELECT k.menu_id
FROM fnd_menus k
WHERE k.menu_id = ( SELECT DISTINCT menu_id
FROM fnd_responsibility_vl
WHERE upper(responsibility_name) = upper('&resp_name')
)
)
CONNECT BY
PRIOR b.sub_menu_id = b.menu_id
ORDER BY menu_name DESC
,entry_sequence ASC
,sub_menu_name ASC;30. Fetch data having CLOB datatype
SELECT dbms_lob.substr(
notes_detail
,7000
,1
)
FROM jtf_notes_tl jtl
,jtf_notes_b jnb
,okc_k_lines_b okl
,okc_k_headers_all_b okh
,ra_customer_trx_all rcta
,ra_customer_trx_lines_all rctal
,okc_k_lines_b okld
WHERE 1 = 1
AND jtl.jtf_note_id = jnb.jtf_note_id
AND jtl.language = 'US'
AND TO_CHAR(
jnb.source_object_id
) = TO_CHAR(
okld.id
)
AND okh.id = okl.dnz_chr_id
AND okh.id = okld.dnz_chr_id
AND rcta.ct_reference = okh.contract_number
AND okh.sts_code = ‘active’
AND rcta.customer_trx_id = rctal.customer_trx_id
AND okld.id = okl.cle_id
AND rctal.contract_line_id = TO_CHAR(
okl.id
)
AND EXISTS ( SELECT MAX(a.last_update_date)
FROM jtf_notes_tl a
WHERE a.jtf_note_id = jtl.jtf_note_id
)
AND rcta.trx_number = in_n_trx_number;31. Query to fetch Order Header Sold to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hp.party_name
|| '|'
|| hl.address1
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| hl.city
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = ooha.sold_to_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp, apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = ooha.sold_to_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No')
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE)
AND NVL (hcp.preference_end_date, SYSDATE)
AND hcar.cust_account_role_id = ooha.sold_to_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = ooha.sold_to_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.hz_cust_accounts hca
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_parties hp
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND hca.cust_account_id = ooha.sold_to_org_id
AND hca.party_id = hp.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND ooha.org_id = hcsu.org_id
AND hcsu.site_use_code = 'BILL_TO'
AND hcsu.primary_flag = 'Y'
AND hcsu.status = 'A'
AND ftl.territory_code = hl.country
AND ftl.language (+) = 'US';32. Query to fetch Order Header Bill to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hp.party_name
|| '|'
|| hl.address1
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| hl.city
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = ooha.invoice_to_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id =
ooha.invoice_to_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id =
ooha.invoice_to_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = ooha.invoice_to_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.hz_cust_accounts hca
,apps.hz_parties hp
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND ooha.invoice_to_org_id = hcsu.site_use_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = ftl.territory_code
AND ftl.language = 'US'
AND hps.status = 'A';33. Query to fetch Order Header Ship to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hp.party_name
|| '|'
|| hl.address1
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| hl.city
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = ooha.ship_to_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = ooha.ship_to_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id = ooha.ship_to_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = ooha.ship_to_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.hz_cust_accounts hca
,apps.hz_parties hp
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number = p_order
AND hcsu.site_use_id = ooha.ship_to_org_id
AND hca.party_id = hp.party_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = ftl.territory_code
AND ftl.language = 'US'
AND hps.status = 'A';34. Query to fetch Order Header End to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hl.address1
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| hl.city
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = ooha.end_customer_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id =
ooha.end_customer_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id =
ooha.end_customer_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = ooha.end_customer_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.hz_cust_accounts hca
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND ooha.end_customer_id = hca.cust_account_id (+)
AND ooha.end_customer_site_use_id = hcsu.site_use_id (+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id (+)
AND hcas.party_site_id = hps.party_site_id (+)
AND hps.location_id = hl.location_id (+)
AND ftl.territory_code (+) = hl.country
AND ftl.language (+) = 'US';35. Query to fetch Order Header Deliver to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hp.party_name
|| '|'
|| hl.address1
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| hl.city
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = ooha.deliver_to_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id =
ooha.deliver_to_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id =
ooha.deliver_to_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = ooha.deliver_to_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.hz_cust_accounts hca
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_parties hp
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND ooha.deliver_to_org_id = hcsu.site_use_id (+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = ftl.territory_code
AND ftl.language (+) = 'US';36. Query to fetch Order Line Ship to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hp.party_name
|| '|'
|| replace(
hl.address1
,CHR(124)
,")
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| REPLACE (hl.city, CHR (13), "
)
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = oola.ship_to_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = oola.ship_to_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id = oola.ship_to_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = oola.ship_to_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.oe_order_lines_all oola
,apps.hz_cust_accounts hca
,apps.hz_parties hp
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND oola.line_id = p_lineid
AND ooha.header_id = oola.header_id
AND hcsu.site_use_id = oola.ship_to_org_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = ftl.territory_code
AND ftl.language = 'US'
AND hps.status = 'A';37. Query to fetch Order Line End to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hl.address1
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| hl.city
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = oola.end_customer_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id =
oola.end_customer_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id =
oola.end_customer_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = oola.end_customer_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.oe_order_lines_all oola
,apps.hz_cust_accounts hca
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND oola.line_id = p_lineid
AND ooha.header_id = oola.header_id
AND oola.end_customer_id = hca.cust_account_id (+)
AND oola.end_customer_site_use_id = hcsu.site_use_id (+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id (+)
AND hcas.party_site_id = hps.party_site_id (+)
AND hps.location_id = hl.location_id (+)
AND ftl.territory_code (+) = hl.country
AND ftl.language = 'US';38. Query to fetch Order Line Deliver to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hp.party_name
|| '|'
|| hl.address1
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| hl.city
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = oola.deliver_to_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id =
oola.deliver_to_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id =
oola.deliver_to_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = oola.deliver_to_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.oe_order_lines_all oola
,apps.hz_cust_accounts hca
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_parties hp
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND oola.line_id = p_lineid
AND ooha.header_id = oola.header_id
AND oola.deliver_to_org_id = hcsu.site_use_id (+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = ftl.territory_code
AND ftl.language (+) = 'US';39. Query to fetch Order Line Bill to details
SELECT hca.account_number
|| '|'
|| hca.account_name
|| '|'
|| hp.party_name
|| '|'
|| replace(
hl.address1
,CHR(124)
,")
|| '|'
|| hl.address2
|| '|'
|| hl.address3
|| '|'
|| hl.address4
|| '|'
|| REPLACE (hl.city, CHR (13), "
)
|| '|'
|| hl.state
|| '|'
|| hl.province
|| '|'
|| hl.postal_code
|| '|'
|| ftl.territory_short_name
|| '|'
|| ( SELECT TRIM(hp.party_name)
FROM apps.hz_cust_account_roles hcar
,apps.hz_relationships hr
,apps.hz_parties hp
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = oola.invoice_to_contact_id
)
|| '|'
|| ( SELECT replace(
hcp.email_address
,CHR(13)
,")
FROM apps.hz_contact_points hcp,
apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id =
oola.invoice_to_contact_id)
|| '|'
|| (SELECT DECODE (hcp.preference_code,
'DO_NOT', 'Yes',
'DO', 'No',
'No'
)
FROM apps.hz_contact_preferences hcp,
apps.hz_cust_account_roles hcar,
apps.hz_relationships hr
WHERE hcar.party_id = hr.party_id(+)
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hcp.contact_type(+) = 'SURVEY'
AND hr.party_id = hcp.contact_level_table_id(+)
AND SYSDATE BETWEEN NVL (hcp.preference_start_date,
SYSDATE
)
AND NVL (hcp.preference_end_date,
SYSDATE
)
AND hcar.cust_account_role_id =
oola.invoice_to_contact_id)
|| '|'
|| (SELECT REPLACE (hcp.raw_phone_number, CHR (13), "
)
FROM apps.hz_contact_points hcp
,apps.hz_cust_account_roles hcar
WHERE hcar.party_id = hcp.owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcar.cust_account_role_id = oola.invoice_to_contact_id
)
|| '|'
|| hcsu.location
FROM apps.oe_order_headers_all ooha
,apps.oe_order_lines_all oola
,apps.hz_cust_accounts hca
,apps.hz_parties hp
,apps.hz_cust_site_uses_all hcsu
,apps.hz_cust_acct_sites_all hcas
,apps.hz_locations hl
,apps.hz_party_sites hps
,apps.fnd_territories_tl ftl
WHERE 1 = 1
AND ooha.order_number =:p_order_number
AND oola.line_id = p_lineid
AND ooha.header_id = oola.header_id
AND oola.invoice_to_org_id = hcsu.site_use_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = ftl.territory_code
AND ftl.language = 'US'
AND hps.status = 'A';40. PSEUDO Columns
Pseudo columns behave like a normal table columns but the values are not actually stored in table. Only SELECT operation can performed but not INSERT, UPDATE, DELETE.
The available pseudo columns are CURRVAL, NEXTVAL, LEVEL, ROWID and ROWNUM
41. Sequence
Sequence is a schema object that generates unique sequential values
CURRVAL – Returns current value of a Sequence — sequencename.currval
NEXTVAL – Returns next value of a Sequence — sequencename.nextval
CURRVAL and NEXTVAL can be used only in….
1. SELECT list of SELECT statement
2. Values clause in INSERT statement
3. SET clause in UPDATE statement
42. Release (Version) Name
SELECT release_name FROM fnd_product_groups;43. INSERT
INSERTS’s are of two types
1. Value Method — INSERT INTO table_name VALUES (value1, value2…valuen);
2. Address Method — INSERT INTO table_name VALUES (&col1, &col2…&coln); — Here for every INSERT into complete row, we have to use forward slash
INSERT data into specified columns
INSERT INTO table_name(col1, col2…coln) VALUES (value1, value2…valuen);
Oracle inserts data into DB in two ways:
1. Conventional Insert: Oracle reuses free space in the table and insert new data
2. Direct-Path Insert: Free space in the existing data is not used and new data inserts after the existing data in table. This method increases the performance of DB.
INSERT /*+ APPEND */ INTO table_name (SELECT 1 FROM DUAL);
44. Characters in string
--Second letter starts with A
SELECT * FROM emp WHERE ename LIKE '_A%';
--Third letter starts with A
SELECT * FROM emp WHERE ename LIKE '__A%';
--Second letter which ends with E
SELECT * FROM emp WHERE ename LIKE '%E_%';
--Third letter which ends with E
SELECT * FROM emp WHERE ename LIKE '%E__%';
45. DELETE
Deletes the data temporarily from table
DELETE table_name
DELETE table_name WHERE <condition>
46. UPDATE
--Update/ modify table data on two columns
UPDATE emp
SET
sal = 1000
,deptno = 20
WHERE empno = 7839;
UPDATE table_name
SET
column_name1 = ( SELECT column_name1 FROM table_name WHERE cust_id = 123
)
,column_name2 = ( SELECT column_name2 FROM table_name WHERE cust_id = 456
)
WHERE cust_id = 789;47. ALTER
–Add or remove table columns and to modify data type
ALTER TABLE sampalt01 ADD/ MODIFY (
sampname VARCHAR2(10) DEFAULT 'NOT GIVEN'
CONSTRAINT sampalt01_sampname_nn NOT NULL
CONSTRAINT sampalt01_sampname_unq UNIQUE
CONSTRAINT sampalt01_sampname_chk CHECK ( sampname = upper(sampname) )
,sampdate DATE DEFAULT SYSDATE
CONSTRAINT sampalt01_sampdate_nn NOT NULL
);48. Rename column name in Table
ALTER TABLE table_name RENAME COLUMN old_column_name
TO new_column_name;49. Add column to Table
Single column – ALTER TABLE table_name ADD column_name data_type;
Multiple columns – ALTER TABLE table_name ADD (column1 data_type, column2 datatype…columnn datatype);
50. Modify column/ column datatype in a Table
ALTER TABLE table_name MODIFY
column_name datatype;51. Query to fetch Customer BILL_TO details
SELECT rcta.trx_number ar_invoice_number
,hp.party_name customer_name
,hcaa.account_number
,hcsua.location
,hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.state
,hl.country
,hl.postal_code
,acra.receipt_number
FROM hz_parties hp
,hz_party_sites hps
,hz_locations hl
,hz_cust_accounts_all hcaa
,hz_cust_acct_sites_all hcasa
,hz_cust_site_uses_all hcsua
,ra_customer_trx_all rcta
,ar_cash_receipts_all acra
,ar_receivable_applications_all araa
WHERE 1 = 1
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hcaa.party_id
AND hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.site_use_id = rcta.bill_to_site_use_id
AND hcasa.party_site_id = hps.party_site_id
AND hcsua.site_use_code = 'BILL_TO'
AND hcaa.cust_account_id = rcta.bill_to_customer_id
AND rcta.customer_trx_id = araa.applied_customer_trx_id(+)
AND araa.cash_receipt_id = acra.cash_receipt_id(+)
AND rcta.trx_number = :trx_number;
52. Query to fetch Customer SHIP_TO details
SELECT rcta.trx_number ar_invoice_number
,hp.party_name customer_name
,hcaa.account_number
,hcsua.location
,hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.state
,hl.country
,hl.postal_code
,acra.receipt_number
FROM hz_parties hp
,hz_party_sites hps
,hz_locations hl
,hz_cust_accounts_all hcaa
,hz_cust_acct_sites_all hcasa
,hz_cust_site_uses_all hcsua
,ra_customer_trx_all rcta
,ar_cash_receipts_all acra
,ar_receivable_applications_all araa
WHERE 1 = 1
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hcaa.party_id
AND hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.site_use_id = rcta.ship_to_site_use_id
AND hcasa.party_site_id = hps.party_site_id
AND hcsua.site_use_code = 'SHIP_TO'
AND hcaa.cust_account_id = rcta.ship_to_customer_id
AND rcta.customer_trx_id = araa.applied_customer_trx_id(+)
AND araa.cash_receipt_id = acra.cash_receipt_id(+)
AND rcta.trx_number = :trx_number;53. P2P query
SELECT DISTINCT ( SELECT a.segment1
FROM po_headers_all a
WHERE 1 = 1
AND a.type_lookup_code = 'CONTRACT'
AND a.po_header_id = pla.contract_id) cpa
,prha.segment1 requisition_number
,pha.segment1 po_number
,pla.line_num
,plla.shipment_num
,plla.quantity quantity_ordered
,plla.quantity_received
,pda.quantity_delivered
,plla.quantity_billed
,pla.unit_price po_unit_price
,msib.segment1 ordered_item
,msib.description ordered_item_description
,rt.transaction_type
,rt.transaction_date
,rt.subinventory
,rsh.receipt_num
,aia.invoice_num
,ail.line_number inv_line_number
,aid.distribution_line_number inv_dist_number
,ail.line_type_lookup_code line_type
,aid.line_type_lookup_code dist_line_type
,aid.amount
,aid.quantity_invoiced
,ac.check_number
,aipa.payment_num
,aipa.accounting_event_id
,gjb.name journal_batch
,gjh.name journal_name
,gjh.creation_date journal_creation_date
FROM apps.po_requisition_headers_all prha
,apps.po_requisition_lines_all prla
,apps.po_req_distributions_all prda
,apps.po_headers_all pha
,apps.po_lines_all pla
,apps.po_distributions_all pda
,apps.po_line_locations_all plla
,apps.mtl_system_items_b msib
,apps.rcv_transactions rt
,apps.rcv_shipment_headers rsh
,apps.ap_invoices_all aia
,apps.ap_invoice_lines_all ail
,apps.ap_invoice_distributions_all aid
,apps.ap_invoice_payments_all aipa
,apps.ap_checks_all ac
,xla.xla_ae_headers xah
,xla.xla_ae_lines xal
,xla.xla_transaction_entities xte
,gl_import_references gir
,gl_je_headers gjh
,gl_je_lines gjl
,gl_je_batches gjb
WHERE 1 = 1
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = prda.requisition_line_id
AND prda.distribution_id = pda.req_distribution_id
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = pda.line_location_id
AND pla.item_id = msib.inventory_item_id (+)
AND rt.po_header_id = pha.po_header_id
AND rt.po_line_location_id = plla.line_location_id
AND rt.po_line_id = pla.po_line_id
AND rt.transaction_type = 'DELIVER'
--AND rt.organization_id = msib.organization_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND aipa.check_id = ac.check_id
AND aia.invoice_id = aipa.invoice_id
AND aia.invoice_id = ail.invoice_id
AND aia.invoice_id = aid.invoice_id
AND pda.po_distribution_id (+) = aid.po_distribution_id
AND ail.line_type_lookup_code = 'ITEM'
AND aipa.accounting_event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.entity_id = xte.entity_id
AND nvl(xte.source_id_int_1, (-99)) = TO_CHAR(ac.check_id)
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gjh.je_batch_id
--AND aid.line_type_lookup_code = 'ACCRUAL'
AND pha.segment1 = '80000090';
54. Query to find list of personalizations on OAF page
SELECT
frv.responsibility_name,
frv.responsibility_id,
substr(perz_doc_path,instr(perz_doc_path,'/',1,3) + 1, (instr(perz_doc_path,'/',1,4) - instr(perz_doc_path,'/',1,3) ) - 1),
perz_doc_path
FROM
fnd_responsibility_vl frv,
(
SELECT
path.path_docid perz_doc_id,
jdr_mds_internal.getdocumentname(path.path_docid) perz_doc_path
FROM
jdr_paths path
WHERE
path.path_docid IN (
SELECT DISTINCT
comp_docid
FROM
jdr_components
WHERE
comp_seq = 0
AND comp_element = 'customization'
AND comp_id IS NULL
)
ORDER BY
perz_doc_path
) pers_tab
WHERE
perz_doc_path LIKE '%'
|| frv.responsibility_id
|| '%'
AND frv.responsibility_name IN (
'Pass Responsibility Name'
);
Remove column from Table
Single column – ALTER TABLE table_name DROP column_name;
Multiple columns – ALTER TABLE table_name DROP (column1, column2….columnn);
Similarly we can increase/ decrease column precision (length) [NUMBER data type] and to decrease the precision the column should have NULL values.
Making column unused
ALTER TABLE table_name SET UNUSED COLUMN column_name;
Even though the column is unused still it will occupy memory
Dropping unused columns
ALTER TABLE table_name DROP UNUSED COLUMNS;
TRUNCATE – Deletes table data permanently
TRUNCATE TABLE table_name;
DROP – Deletes table data as well as structure of table
DROP TABLE table_name;
RENAME – Renames the table name
RENAME old_table_name to new_table_name;
COMMIT
Two types
1. Implicit – Occurs whenever DDL operation performs.
2. Explicit – Issued by User
ROLLBACK
This will undo the operation
This will be applied in two methods:
1. Upto previous COMMIT
2. Upto previous ROLLBACK
SAVEPOINT
We can use SAVEPOINT to ROLLBACK portions of current set of transactions
SAVEPOINT savepoint_name;
SAVEPOINT S1;
INSERT INTO table_name VALUES (‘A’, ‘B’);
SAVEPOINT S2;
INSERT INTO table_name VALUES (‘C’, ‘D’);
SAVEPOINT S3;
INSERT INTO table_name VALUES (‘E’, ‘F’);
SAVEPOINT S4;
INSERT INTO table_name VALUES (‘G’, ‘H’);
Before ROLLBACK if we check SELECT * FROM table_name we get all four records
ROLLBACK TO SAVPOINT S3;
or
ROLLBACK TO S3;
Then we get only first two records. (A/B and C/D)
Joins
A join is a query that combines rows from 2 or more tables. (i.e not having a WHERE clause)
SELECT EMPNO, ENAME, DNAME, LOC FROM EMP, DEPT;
Join Condition
Query that contains a WHERE clause which compares two columns from different table.
Types
1. Equi Join/ Simple Join/ Inner Join — A join condition containing equality (=) operator
2. Self Join — Join to a table itself. SELECT columns FROM table1 T1, table1 T2 WHERE T1.column1 = T2.column2
3. Non Equi Join — No direct relation between two tables.
SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal
4. Outer Join — Returns all rows from one table though other table contains NULL values. (+) operator should be placed where you want all values from that specific table i.e including NULLS
To join ‘n’ tables we must have ‘n-1’ join conditions
Mapping between OM and INV
SELECT *
FROM apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.mtl_system_items_b msib
WHERE msib.inventory_item_id = oola.inventory_item_id
AND msib.organization_id = ooha.ship_from_org_id;
Query to fetch Legal Entity for Operating Unit
SELECT ood.organization_id,
ood.organization_code,
ood.organization_name,
ood.operating_unit,
hou.name ou,
gl.name ledger,
ood.legal_entity,
xep.name le_name
FROM apps.org_organization_definitions ood,
apps.hr_operating_units hou,
apps.xle_entity_profiles xep,
apps.gl_ledgers gl
WHERE ood.operating_unit = hou.organization_id
AND xep.legal_entity_id = ood.legal_entity
AND gl.ledger_id = ood.set_of_books_id;
Query to fetch ROW_NUMBER’s
SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) ROW_NUMBER, e.* FROM emp e;
Query to fetch rows which have Even ROW_NUMBER’s
SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) ROW_NUMBER, e.* FROM emp e) a WHERE MOD (a.ROW_NUMBER, 2) = 0;
Query to fetch rows which have Odd ROW_NUMBER’s
SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) ROW_NUMBER, e.* FROM emp e) a WHERE MOD (a.ROW_NUMBER, 2) = 1;
Query to fetch rows BETWEEN 5 AND 10 ROW_NUMBER’s
SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) ROW_NUMBER, e.* FROM emp e) a WHERE 1 = 1 AND a.ROW_NUMBER BETWEEN 5 AND 10;
Difference between Subquery and Inline View
* Max number of sub queries in a sql statement is 255
* Multiple columns or expressions can be compared between the main query and sub query
* Main query and sub query can get data from different tables
* Sub queries can contain GROUP BY and ORDER BY clauses
WITH Clause
WITH clause will be used as an alternate to INLINE view (FROM clause)
Example 1: SELECT e.ename, d.deptno, d.dep_count dep_count FROM emp e, ( SELECT deptno, COUNT (*) dep_count FROM emp GROUP BY deptno) d WHERE e.deptno = d.deptno ORDER BY d.deptno;
Example 2: WITH dep_count AS ( SELECT deptno, COUNT (*) dep_count FROM emp GROUP BY deptno) SELECT e.ename, dc.deptno, dc.dep_count FROM emp e, dep_count dc WHERE e.deptno = dc.deptno ORDER BY dc.deptno;
Query to get current date with 24hr format
SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) FROM DUAL
Query to get nth salary
Here we calculated 3rd highest salary.
Inline View: SELECT e.* FROM emp e, (SELECT empno, ROW_NUMBER () OVER (ORDER BY sal DESC) row_num FROM emp) b WHERE b.row_num = 3 AND b.empno = e.empno;
WITH Clause: WITH nth_sal AS (SELECT empno, ROW_NUMBER () OVER (ORDER BY sal DESC) rn_sal FROM emp) SELECT * FROM emp e, nth_sal sal WHERE sal.rn_sal = 3 AND sal.empno = e.empno;
Query to check locks on DB objects
SELECT * FROM dba_dml_locks WHERE name = ‘object_name’;
Mapping between lookup values and flex values
SELECT *
FROM fnd_lookup_values flv,
fnd_flex_value_sets ffvs,
fnd_flex_values ffv,
fnd_flex_values_tl ffvt
WHERE 1 = 1
AND flv.lookup_type = ‘Lookup_Type’
AND flv.enabled_flag = ‘Y’
AND flv.security_group_id = 0
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffv.flex_value = paaf.ass_attribute1
AND ffvt.language = USERENV (‘LANG’)
AND ffvs.flex_value_set_name = ‘Value Set Name’;
Query to display salary value in words
SELECT sal, INITCAP (TO_CHAR (TO_DATE (sal, ‘jsp’), ‘jsp’)) inwords FROM emp;
Query to get employee details by passing payroll_number
SELECT *
FROM pay_people_groups ppg,
per_all_assignments_f paaf,
per_all_people_f papf
WHERE papf.person_id = paaf.person_id
AND paaf.people_group_id = ppg.people_group_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND ppg.segment4 = ‘pass_payroll_number’;
Query to get concurrent program executable details
SELECT fcpt.user_concurrent_program_name cp_name,
fcp.concurrent_program_name cp_short_name,
fat.application_name cp_application_name,
fetl.user_executable_name,
fet.executable_name executable_short_name,
fat1.application_name executable_application_name,
flv.meaning execution_method,
fet.execution_file_name,
fcp.enable_trace
FROM fnd_concurrent_programs_tl fcpt,
fnd_concurrent_programs fcp,
fnd_application_tl fat,
fnd_executables fet,
fnd_application_tl fat1,
fnd_lookup_values flv,
fnd_executables_tl fetl
WHERE 1 = 1
AND fcpt.user_concurrent_program_name LIKE ‘Program Name%’
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcp.application_id = fat.application_id
AND fcp.executable_id = fet.executable_id
AND fcp.executable_application_id = fet.application_id
AND fet.application_id = fat1.application_id
AND flv.lookup_code = fet.execution_method_code
AND flv.lookup_type = ‘CP_EXECUTION_METHOD_CODE’
AND fet.executable_id = fetl.executable_id
AND fet.application_id = fetl.application_id;
Query to fetch executable and request group for a given concurrent program
SELECT fcpvl.user_concurrent_program_name,
fevl.executable_name,
fevl.execution_file_name,
fevl.execution_method_code,
fevl.user_executable_name,
fevl.description executable_description,
fatl.application_name,
frg.request_group_name,
frg.description request_group_description
FROM fnd_concurrent_programs_vl fcpvl,
fnd_application_tl fatl,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_executables_vl fevl
WHERE 1 = 1
AND fcpvl.user_concurrent_program_name LIKE ‘Program Name%’
AND fcpvl.application_id = fatl.application_id
AND fcpvl.concurrent_program_id = frgu.request_unit_id
AND frgu.request_group_id = frg.request_group_id
AND fcpvl.executable_id = fevl.executable_id;
Query to extract duplicate records
SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name HAVING COUNT(column_name)>1
Interview Topics
1) We can use COMMIT inside a trigger only if it is an Autonomous transaction.
2) Maximum characters allowed in dbms_output.put_line() – 32767 bytes
3) Maximum number of columns in a table or view in Oracle 9i – 1000
4) Datatype of NULL in Oracle – CHAR(0)
5) Maximum length of Oracle Objects in 9i – 30
6) To check the version of Oracle – select * from v$version;
8) UTL_FILE package is used to Read/ Write from and to external files
9) Query to fetch a string having pattern like ‘A_B’
SELECT * FROM table WHERE name like ‘%A\_B%’ ESCAPE ‘\’;
10) If we use ALTER TABLESPACE statement and specify TEMPORARY clause
– The offline files may require media recovery before you bring the table space online.
– Oracle performs a checkpoint for all online data files in the table space.
– Oracle ensures that all files are written
– Oracle performs checkpoint for the online data files in the tablespace
11) iSQL*Plus command – DESCRIBE
12) UNION or INTERSECT – The number of columns and data types must be identical for all SELECT statements in the query.
13) Query to fetch a string containing character ‘A’ at any place and second last character should be ‘B’
SELECT ‘*’ FROM DUAL
WHERE INSTR (‘StrAingBt’, ‘A’) <> 0
AND SUBSTR (‘StrAingBt’, -2, 1) = ‘B’
14) SELECT TRUNC(ROUND(156.00, -1), -1) FROM DUAL — 160
15) SELECT * FROM emp WHERE deptno = &&value;
The && in SQL*Plus does not prompt to enter value again and again. It prompts only for the first time during execution. However in Toad, it prompts again and again.
Queries
SELECT * FROM dba_objects where UPPER(object_name) = ”;
SELECT * FROM dba_source WHERE UPPER(name) = ”;
SELECT * FROM dba_dependencies WHERE referenced_name = ”;
Query to generate XML tags
Here lv_clob_datatype is CLOB data type
lv_clob_datatype OUT NOCOPY CLOB;
SELECT XMLAGG (
XMLELEMENT (
“G_EMPLOYEE”,
XMLELEMENT (“EMPLOYEE_NUMBER”, emp.EMPLOYEE_NUMBER)
, XMLELEMENT (“NI_NUMBER”, emp.NI_NUMBER)
, (SELECT XMLAGG (
XMLELEMENT (
“G_NAMES”,
XMLELEMENT (
“G_NAME”
, XMLELEMENT (“TITLE”, e1.TITLE)
, XMLELEMENT (“FIRST_NAME”, e1.FIRST_NAME)
, XMLELEMENT (“LAST_NAME”, e1.LAST_NAME))))
AS “G_NAMES”
FROM per_all_people_f e1
WHERE e1.record_id = emp.record_id)
, XMLELEMENT (“PAYROLL_NUMBER”, emp.PAYROLL_NUMBER)
, XMLELEMENT (“JOB”, emp.JOB)
, XMLELEMENT (“NORMAL_HOURS”, emp.NORMAL_HOURS)
, XMLELEMENT (“DATE_START”, emp.DATE_START)
, XMLELEMENT (“LENGTH_OF_SERVICE”, emp.LENGTH_OF_SERVICE)
, (SELECT XMLAGG (
XMLELEMENT (
“G_ADDRESSES”,
XMLELEMENT (
“G_ADDRESS”
, XMLELEMENT (“ADDR_LINE_1”, e1.ADDR_LINE_1)
, XMLELEMENT (“ADDR_LINE_2”, e1.ADDR_LINE_2)
, XMLELEMENT (“ADDR_LINE_3”, e1.ADDR_LINE_3)
, XMLELEMENT (“CITY”, e1.CITY)
, XMLELEMENT (“COUNTY”, e1.COUNTY)
, XMLELEMENT (“POST_CODE”, e1.POSTAL_CODE)
, XMLELEMENT (“COUNTRY”, e1.COUNTRY))))
AS “G_ADDRESSES”
FROM per_all_people_f e1
WHERE e1.record_id = emp.record_id)
, XMLELEMENT (“HOME_PHONE_NUMBER”, emp.HOME_PHONE_NUMBER)
, XMLELEMENT (“MOBILE_PHONE_NUMBER”,emp.MOBILE_PHONE_NUMBER)
, XMLELEMENT (“EMAIL_ADDRESS”, emp.EMAIL_ADDRESS)
, XMLELEMENT (“STORE_NUMBER”, emp.STORE_NUMBER)
, XMLELEMENT (“EMPLOYMENT_STATUS”, emp.EMPLOYMENT_STATUS)
, XMLELEMENT (“EMPL_STATUS_EFFECTIVE_DATE”, emp.EMPL_STATUS_EFFECTIVE_DATE)
, XMLELEMENT (“BIOMETRIC_EMPLOYEE_FLAG”, emp.BIOMETRIC_EMPLOYEE_FLAG)
, XMLELEMENT (“PRIMARY_LABOUR_ACCOUNT”, emp.PRIMARY_LABOUR_ACCOUNT)
, XMLELEMENT (“PRI_LAB_ACCOUNT_EFFECTIVE_DATE”, emp.PRI_LAB_ACCOUNT_EFFECTIVE_DATE)
, XMLELEMENT (“DEVICE_GROUP”, emp.DEVICE_GROUP)
, XMLELEMENT (“BADGE_NUMBER”, emp.BADGE_NUMBER)
, XMLELEMENT (“BADGE_NUMBER_EFFECTIVE_DATE”, emp.BADGE_NUMBER_EFFECTIVE_DATE)
, XMLELEMENT (“PAY_RULE”, emp.PAY_RULE)
, XMLELEMENT (“ROLE_HIRE_DATE”, emp.ROLE_HIRE_DATE)
, XMLELEMENT (“STORE_TYPE”, emp.STORE_TYPE)
, XMLELEMENT (“CLOCK_INDICATOR”, emp.CLOCK_INDICATOR)
, XMLELEMENT (“MIDDLE_INITIAL”, emp.MIDDLE_INITIAL)
, XMLELEMENT (“EMPLOYMENT_EXPIRATION_DATE”, emp.EMPLOYMENT_EXPIRATION_DATE)
, XMLELEMENT (“BADGE_EXPIRATION_DATE”, emp.BADGE_EXPIRATION_DATE)
, XMLELEMENT (“LABOUR_ACCOUNT_EXPIRATION_DATE”, emp.LABOUR_ACCOUNT_EXPIRATION_DATE)
, XMLELEMENT (“PAY_RULE_EFFECTIVE_DATE”, emp.PAY_RULE_EFFECTIVE_DATE)
, XMLELEMENT (“WORKFORCE_TIMEKEEPER_LICENSE”, emp.WORKFORCE_TIMEKEEPER_LICENSE)
, XMLELEMENT (“DATE_OF_BIRTH”, emp.DATE_OF_BIRTH)
, XMLELEMENT (“WORK_PHONE_NUMBER”, emp.WORK_PHONE_NUMBER)
, XMLELEMENT (“PRIMARY_JOB_ACCOUNT”, emp.PRIMARY_JOB_ACCOUNT)
, XMLELEMENT (“PRI_JOB_ACCOUNT_EFFECTIVE_DATE”, emp.PRI_JOB_ACCOUNT_EFFECTIVE_DATE)
, XMLELEMENT (“PRI_JOB_ACCOUNT_EXP_DATE”, emp.PRI_JOB_ACCOUNT_EXP_DATE)
, XMLELEMENT (“WORKER_TYPE”, emp.WORKER_TYPE)
, XMLELEMENT (“DEPARTMENT”, emp.DEPARTMENT)
, XMLELEMENT (“RECORD_MODE”, emp.RECORD_MODE)
–, XMLELEMENT (“CHG_COL_LIST”, emp.CHG_COL_LIST)
, XMLELEMENT (“CHG_COL_LIST”,
(SELECT
REPLACE (
(SELECT regexp_replace (LISTAGG (CHG_COL_LIST, ‘,’) WITHIN GROUP (ORDER BY RECORD_ID), ‘([^,]+)(,\1)*(,|$)’, ‘\1\3’)
FROM per_all_people_f e
WHERE 1 = 1
AND STATUS=’New’
AND CHG_COL_LIST<>’ALL’
AND e.person_id =emp.person_id
), ‘,’, ‘|’) CHG_COL_LIST
FROM DUAL))
, XMLELEMENT (“GRADE”, emp.GRADE)
, XMLELEMENT (“RIGHT_TO_WORK_TYPE”, emp.RIGHT_TO_WORK_TYPE)
, XMLELEMENT (“LINE_MANAGER”, emp.LINE_MANAGER)
, XMLELEMENT (“INITIAL_DOC_DATE”, emp.INITIAL_DOC_DATE)
, XMLELEMENT (“AGE”, emp.AGE)
, XMLELEMENT (“REGION”, emp.REGION)
, XMLELEMENT (“CREATION_DATE”, emp.CREATION_DATE)
, XMLELEMENT (“CURR_STORE_CPS_CONFIG”, emp.CURR_STORE_CPS_CONFIG)
, XMLELEMENT (“OLD_STORE_CPS_CONFIG”, emp.OLD_STORE_CPS_CONFIG)
)).EXTRACT (‘/’).getClobVal ()
AS “result”
–INTO lv_clob_datatype
FROM per_all_people_f emp
WHERE emp.status = ‘New’
AND emp.record_id IN
(SELECT MAX (emp1.record_id)
FROM per_all_people_f emp1
WHERE emp1.person_id = emp.person_id
AND emp1.status = ‘New’
GROUP BY emp1.employment_status);
Query to find the sql query which is taking long time in Concurrent Program
SELECT
f.user_name
,a.request_id “Req Id”
,a.concurrent_program_id “Prg Id”
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id “OS”
,vs.sid
,vs.serial# “Serial#”
,vp.spid
,TO_CHAR(request_date,’DD-MON-YY hh24:mi:ss’) request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 “Time”
,c.concurrent_program_name||’ – ‘||c2.user_concurrent_program_name “Program”
FROM APPLSYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN (‘I’,’P’,’R’,’T’)
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = ‘US’
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9;
SELECT sql_text FROM v$sqltext t, v$session s
WHERE
t.ADDRESS =s.SQL_ADDRESS
AND t.HASH_VALUE=s.SQL_HASH_VALUE
AND s.sid =:pass_sid_from_above_query
ORDER BY PIECE;
Convert rows into Column with comma separator
SELECT RTRIM(XMLAGG(XMLELEMENT(E,column_name,’,’).EXTRACT(‘//text()’) ORDER BY column_name).GetClobVal(),’,’) AS LIST
FROM table_name
WHERE 1 = 1;
⇒ The query which is executing during concurrent program run
select * from v$sql WHERE SQL_ID IN (SELECT SQL_ID FROM gv$session
where CLIENT_IDENTIFIER = ‘USER_NAME’
AND MODULE = ‘MODULE_NAME’);
⇒ Clear Cache
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
COMMIT;
Post executing above statements, check in DB using below queries.
select * from v$db_object_cache where name like ‘%SELECT%’;
select * from v$sql where sql_text like ‘%SELECT%’;
select * from v$sqlarea where sql_text like ‘%SELECT%’;
In case we find still find Cache issue, then execute below command with the help of DBA since developers usually wont have access to execute.
exec sys.dbms_shared_pool.purge(‘000000020AD932F8,879763644′,’C’);
commit;
‘000000020AD932F8—get address of a query from v$sqlarea
879763644’—get hash_value from v$sqlarea
Query to fetch Responsibility, Menu and Request Group and Menu Exclusions
SELECT DISTINCT ( SELECT application_short_name
FROM fnd_application
WHERE application_id = b.application_id
) module
,a.responsibility_name
, ( SELECT application_name
FROM fnd_application_tl
WHERE application_id = b.application_id
) application
,b.responsibility_key
,a.description
,DECODE(b.version,’4′,’Oracle Applications’,’M’,’Oracle Mobile Applications’,’W’,’Oracle Self Service Web Applications’) available_from
, ( SELECT data_group_name
FROM fnd_data_groups
WHERE data_group_id = b.data_group_id
) data_group
, ( SELECT application_name FROM fnd_application_tl WHERE application_id = data_group_application_id
) data_group_application
, ( SELECT user_menu_name
FROM fnd_menus_tl
WHERE menu_id = b.menu_id
) menu
, ( SELECT request_group_name
FROM fnd_request_groups
WHERE request_group_id = b.request_group_id
) request_group
, ( SELECT application_name FROM fnd_application_tl WHERE application_id = group_application_id
) request_group_application
,DECODE(frf.rule_type,’M’,’Menu’,’F’,’Function’) type
,CASE
WHEN frf.rule_type = ‘M’ THEN ( SELECT fmv.user_menu_name
FROM apps.fnd_menus_tl fmv
WHERE frf.action_id = fmv.menu_id
AND frf.rule_type = ‘M’
)
WHEN frf.rule_type = ‘F’ THEN ( SELECT ffvl.user_function_name
FROM apps.fnd_form_functions_tl ffvl
WHERE frf.action_id = ffvl.function_id
AND frf.rule_type = ‘F’
)
END
name
FROM fnd_responsibility_tl a
,fnd_responsibility b
,fnd_resp_functions frf
WHERE 1 = 1
AND a.responsibility_id = b.responsibility_id
AND a.application_id = b.application_id
AND b.end_date IS NULL
AND frf.responsibility_id(+) = a.responsibility_id
AND a.responsibility_name = :responsibility_name
ORDER BY 1;
SQL Query to fetch Category codes via Purchasing Super User >> Setup >> Items >> Categories >> Category Codes
SELECT t.id_flex_structure_name structure_name
, ( mc.segment1 || ‘-‘ || mc.segment2 || ‘-‘ || mc.segment3 ) category
,mct.description
,mc.enabled_flag enabled
,mc.disable_date inactive_on
FROM mtl_categories_tl mct
,mtl_categories_b mc
,fnd_id_flex_structures_tl t
,fnd_id_flex_structures b
WHERE mc.category_id = mct.category_id
AND mc.structure_id = b.id_flex_num
AND b.application_id = t.application_id
AND b.id_flex_code = t.id_flex_code
AND b.id_flex_num = t.id_flex_num
AND b.application_id = 401
AND t.id_flex_code = ‘MCAT’
AND t.id_flex_structure_name = :structure_name;
SQL Query to fetch Supplier, bank, bank branch and bank account details
SELECT
aps.vendor_name “VENDOR NAME”,
aps.segment1 “VENDOR NUMBER”,
aps.creation_date “VENDOR CREATION DATE”,
apss.vendor_site_code “VENDOR SITE CODE”,
(
SELECT
name
FROM
apps.hr_all_organization_units hou
WHERE
1 = 1
AND hou.organization_id = apss.org_id
) “OPERATING UNIT”,
ieb.bank_name “BANK NAME”,
ieb.bank_number “BANK NUMBER”,
iebb.bank_branch_name “BRANCH NAME”,
iebb.branch_number “BRANCH NUMBER”,
ieba.bank_account_num “BANK ACCOUNT NUMBER”,
ieba.bank_account_name “BANK ACCOUNT NAME”
FROM
ap.ap_suppliers aps,
ap.ap_supplier_sites_all apss,
apps.iby_ext_bank_accounts ieba,
apps.iby_account_owners iao,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_branches_v iebb
WHERE
aps.vendor_id = apss.vendor_id
AND iao.account_owner_party_id = aps.party_id
AND ieba.ext_bank_account_id = iao.ext_bank_account_id
AND ieb.bank_party_id = iebb.bank_party_id
AND ieba.branch_id = iebb.branch_party_id
AND ieba.bank_id = ieb.bank_party_id;
SQL Query to fetch Concurrent program last run date
SELECT actual_completion_date
INTO l_last_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id =
(SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1,
fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id =
fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name = ”
AND fcr1.status_code = ‘C’);
SQL Query to fetch Concurrent program current run date
SELECT actual_start_date
INTO l_curr_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id =
(SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1,
fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id =
fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name =”
AND fcr1.status_code = ‘R’
AND fcr1.phase_code = ‘R’);
SQL query to fetch server time zone and current time
• SELECT sessiontimezone, current_date FROM dual; — session = connected to which session; current_date = clients date; SYSDATE= servers date
• We can set date format in a particular session using: ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;
• SELECT current_timestamp, SYSTIMESTAMP FROM dual; — both gives timestamp upto milli seconds
• SELECT dbtimezone FROM dual; — Oracle DB timezone. Timezone running for database.
• SELECT SYSDATE BST, NEW_TIME(SYSDATE, ‘EST’, ‘BST’) EST FROM dual; — Convert time zone from BST to EST
• SELECT EXTRACT(DAY FROM SYSTIMESTAMP) FROM dual; Provides current day date which is similar to SELECT TO_CHAR(SYSDATE, ‘DD’) FROM DUAL;
• SELECT CAST(213.65 AS NUMBER(6)) CAST FROM dual; Output: 214
• SELECT CAST(‘213.65’ AS NUMBER(6, 1)) CAST FROM dual; Output: 213.7
• SELECT CAST(‘213.65’ AS NUMBER(6, 2)) CAST FROM dual; Output: 213.65
• SELECT CAST(‘ABC ‘ AS CHAR(10)) CAST FROM dual; Output: ABC — Removes extra NULL spaces = Replacement of PAD
• SELECT CAST(’18-JUL-2018′ AS TIMESTAMP WITH LOCAL TIME ZONE) CAST FROM dual; — Convert string based value into data format
• SELECT CAST(TO_DATE(’18-JUL-2018′, ‘DD-MON-YYYY’) AS TIMESTAMP WITH LOCAL TIME ZONE) CAST FROM dual; — Alternate function to TO_CHAR, TO_NUMBER, TO_DATE is CAST
• SELECT CAST(1234 AS NUMBER DEFAULT 0 ON CONVERSION ERROR) CAST FROM dual;
Receivables Activities
SELECT
hou.name operating_unit
,arta.name
,arta.description
,al.meaning type
,arta.status active
,al1.meaning gl_account_source
,al2.meaning tax_rate_code_source
, ( DECODE(chart_of_accounts_id,101,segment1
|| ‘-‘
|| segment2
|| ‘-‘
|| segment3
|| ‘-‘
|| segment4
|| ‘-‘
|| segment5
|| ‘-‘
|| segment6
|| ‘-‘
|| segment7
|| ‘-‘
|| segment8,50274,segment1
|| ‘-‘
|| segment2
|| ‘-‘
|| segment3
|| ‘-‘
|| segment4
|| ‘-‘
|| segment5
|| ‘-‘
|| segment6
|| ‘-‘
|| segment7
|| ‘-‘
|| segment8,NULL) ) activity_gl_account–gcck.concatenated_segments activity_gl_account
,arta.default_acctg_distribution_set distribution_set
FROM
ar_receivables_trx_all arta
,hr_all_organization_units hou –hr_operating_units hou
,fnd_lookup_values al –ar_lookups al
,fnd_lookup_values al1 –ar_lookups al1
,fnd_lookup_values al2 –ar_lookups al2
,gl_code_combinations gcck –gl_code_combinations_kfv gcck
WHERE
1 = 1
AND arta.org_id = hou.organization_id
AND al.view_application_id = 222
AND al.security_group_id = 0
AND al.lookup_code = arta.type
AND al.lookup_type = ‘RECEIVABLES_TRX’
AND al1.view_application_id = 222
AND al1.security_group_id = 0
AND al1.lookup_code = arta.gl_account_source
AND al1.lookup_type = ‘GL_ACCOUNT_SOURCE’
AND al2.view_application_id = 222
AND al2.security_group_id = 0
AND al2.lookup_code = arta.tax_code_source
AND al2.lookup_type = ‘TAX_CODE_SOURCE’
AND gcck.code_combination_id (+) = arta.code_combination_id
ORDER BY
2;
Approval Limit
SELECT
fu.user_name
,aaul.document_type
,aaul.reason_code
,aaul.currency_code
,aaul.amount_from
,aaul.amount_to
,aaul.primary_flag
FROM
ar_approval_user_limits aaul
,fnd_user fu
WHERE
1 = 1
AND aaul.user_id = fu.user_id;
AR Profile Options
SELECT
substr(pro1.user_profile_option_name,1,35) profile
,DECODE(pov.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) option_level
,DECODE(pov.level_id,10001,’Site’,10002,appl.application_short_name,10003,resp.responsibility_name,10004,u.user_name) level_value
,nvl(pov.profile_option_value,’Is Null’) profile_option_value
FROM
fnd_profile_option_values pov
,fnd_responsibility_tl resp
,fnd_application appl
,fnd_user u
,fnd_profile_options pro
,fnd_profile_options_tl pro1
WHERE
1 = 1
AND pro.profile_option_name = pro1.profile_option_name
AND pro.profile_option_id = pov.profile_option_id
AND resp.responsibility_name LIKE ‘%Rec%Man%’
AND pov.level_value = resp.responsibility_id (+)
AND pov.level_value = appl.application_id (+)
AND pov.level_value = u.user_id (+)
ORDER BY
1
,2
AutoCash Rule Set
SELECT
aah.hierarchy_name name
,aah.description
,aah.status
,aah.include_discounts
,aah.include_dispute_items
,aah.include_finance_charges
,flv2.meaning remaining_remittance_amount
,aah.apply_partial_payments
,aar.rule_sequence
,flv1.meaning autocash_rule
FROM
ar_autocash_hierarchies aah
,ar_autocash_rules aar
,fnd_lookup_values flv1
,fnd_lookup_values flv2
WHERE
1 = 1
AND aah.autocash_hierarchy_id = aar.autocash_hierarchy_id
AND aar.autocash_rule = flv1.lookup_code
AND flv1.lookup_type = ‘AUTOCASH_RULE’
AND aah.remaining_amount = flv2.lookup_code
AND flv2.lookup_type = ‘AUTOCASH_REMAINING_AMOUNT’;
Distribution Sets
SELECT
haou.name operating_unit
,ads.distribution_set_name
,ads.description
,ads.status
,adsl.percent_distribution
, ( DECODE(chart_of_accounts_id,101,segment1
|| ‘-‘
|| segment2
|| ‘-‘
|| segment3
|| ‘-‘
|| segment4
|| ‘-‘
|| segment5
|| ‘-‘
|| segment6
|| ‘-‘
|| segment7
|| ‘-‘
|| segment8,50274,segment1
|| ‘-‘
|| segment2
|| ‘-‘
|| segment3
|| ‘-‘
|| segment4
|| ‘-‘
|| segment5
|| ‘-‘
|| segment6
|| ‘-‘
|| segment7
|| ‘-‘
|| segment8,NULL) ) account
,adsl.description lines_description
FROM
ar_distribution_sets_all ads
,hr_all_organization_units haou
,ar_distribution_set_lines_all adsl
,gl_code_combinations gcc
WHERE
1 = 1
AND ads.org_id = haou.organization_id
AND ads.distribution_set_id = adsl.distribution_set_id
AND gcc.code_combination_id = adsl.dist_code_combination_id;
DFF
SELECT *
FROM
fnd_flex_values_tl t,
fnd_flex_values b
WHERE
b.flex_value_id = t.flex_value_id;
Lookups
SELECT
*
FROM
fnd_lookup_values;
Document Sequence
SELECT
fds.name
,fat.application_name
,fds.start_date
,fds.end_date
,DECODE(fds.type,’A’,’Automatic’,’M’,’Manual’,’G’,’Gapless’) type
,fds.initial_value
FROM
fnd_document_sequences fds
,fnd_application_tl fat
WHERE
1 = 1
AND fds.application_id = fat.application_id;
AutoInvoice Line Ordering Rules
SELECT
rlor.name,
rlor.description,
rlor.start_date,
rlor.end_date,
rlob.order_by_sequence,
rlobc.column_name,
DECODE(rlob.order_by_type,’A’,’Ascending’,’Descending’) type
FROM
ra_line_ordering_rules rlor,
ra_line_order_bys rlob,
ra_line_order_by_columns rlobc
WHERE
1 = 1
AND rlor.ordering_rule_id = rlob.ordering_rule_id
AND rlob.column_id = rlobc.column_id;
Billing Cycle
SELECT
t.cycle_name
,t.description
,DECODE(b.bill_cycle_type,’EVENT’,’Event Based’,’RECUR’,’Recurring’,b.bill_cycle_type) billing_cycle_type
,b.cycle_frequency
,b.start_date
,b.last_day
,b.day_1
,b.day_2
,b.day_3
,b.day_4
,b.day_5
,b.day_6
,b.day_7
,b.day_8
,b.day_9
,b.day_10
,b.day_11
,b.day_12
,b.day_13
,b.day_14
,b.day_15
,b.day_16
,b.day_17
,b.day_18
,b.day_19
,b.day_20
,b.day_21
,b.day_22
,b.day_23
,b.day_24
,b.day_25
,b.day_26
,b.day_27
,b.day_28
,b.day_29
,b.day_30
,b.day_31
,b.day_monday
,b.day_tuesday
,b.day_wednesday
,b.day_thursday
,b.day_friday
,b.day_saturday
,b.day_sunday
,b.skip_weekends
,b.skip_holidays
,b.repeat_daily
,b.repeat_weekly
,b.repeat_monthly
,b.day_type
FROM
ar_cons_bill_cycles_b b
,ar_cons_bill_cycles_tl t
WHERE
t.billing_cycle_id = b.billing_cycle_id
Payment Terms
SELECT
rtt.name “Name”,
rtt.description “Description”,
rt.partial_discount_flag “Allow Discounts on Part. Pay.”,
rt.prepayment_flag “Prepayment”,
rt.credit_check_flag “Credit Check”,
acbct.cycle_name “Billing Cycle”,
rt.base_amount “Base Amount”,
DECODE(rt.calc_discount_on_lines_flag,’N’,’Invoice Amount’,flv_disc.meaning) “Discount Basis”,
TO_CHAR(rt.start_date_active,’DD-MON-RRRR’)
|| ‘ – ‘
|| TO_CHAR(rt.end_date_active,’DD-MON-RRRR’) “Effective Dates”,
rt.printing_lead_days “Print Lead Days”,
flv_inst.meaning “Installment Options”,
rtl.sequence_num “Seq”,
rtl.relative_amount “Relative Amount”,
rtl.due_days “Days”,
rtl.due_date “Date”,
rtl.due_day_of_month “Day of Month”,
rtl.due_months_forward “Months Ahead”
FROM
apps.ra_terms_tl rtt,
apps.ra_terms_b rt,
apps.ar_cons_bill_cycles_tl acbct,
apps.ra_terms_lines rtl,
apps.fnd_lookup_values flv_disc,
apps.fnd_lookup_values flv_inst
WHERE
1 = 1
AND rt.term_id = rtt.term_id (+)
AND rtt.language = rtt.source_lang
AND rt.billing_cycle_id = acbct.billing_cycle_id (+)
AND rt.term_id = rtl.term_id
AND rt.calc_discount_on_lines_flag = flv_disc.lookup_code (+)
AND flv_disc.lookup_type (+) = ‘DISCOUNT_BASIS’
AND rt.first_installment_code = flv_inst.lookup_code (+)
AND flv_inst.lookup_type (+) = ‘INSTALLMENT_OPTION’
ORDER BY
rtt.name,
rtl.sequence_num
AutoAccounting Rules
SELECT
haou.name “Operating Unit”
,flv1.meaning “Type”
,DECODE(rads.segment,’SEGMENT1′,’Company’,’SEGMENT2′,’Account’,’SEGMENT3′,’Cost Centre’,’SEGMENT4′,’Business Unit’,’SEGMENT5′,’Category Code’
,’SEGMENT6′,’Project’,’SEGMENT7′,’Activity’,’SEGMENT8′,’Spare’) “Segment”
,flv2.meaning “Table Name”
,rads.constant “Constant”
FROM
ra_account_defaults_all rada
,hr_all_organization_units haou
,fnd_lookup_values flv1
,ra_account_default_segments rads
,fnd_lookup_values flv2
WHERE
1 = 1
AND rada.org_id = haou.organization_id
AND rada.type = flv1.lookup_code
AND flv1.lookup_type = ‘AUTOGL_TYPE’
AND rads.table_name = flv2.lookup_code (+)
AND flv2.lookup_type (+) = ‘AUTOGL_TABLES’
AND rada.gl_default_id = rads.gl_default_id
ORDER BY
2
,1
,rads.segment;
Transaction Types
SELECT
hou.name operating_unit
,xep.name legal_entity
,rctt.name
,rctt.description
,al.meaning class
,al2.meaning creation_sign
,al3.meaning transaction_status
,al4.meaning printing_option
,NULL invoice_type
,rctt2.name credit_memo_type
,aars.rule_set_name application_rule_set
,aat.payment_term_name terms
,rctt.start_date
,rctt.end_date
,rctt.accounting_affect_flag open_receivable
,rctt.adj_post_to_gl allow_adjustment_posting
,rctt.post_to_gl post_to_gl
,rctt.allow_freight_flag allow_freight
,rctt.natural_application_only_flag natural_application_only
,rctt.tax_calculation_flag default_tax_classification
,rctt.exclude_from_late_charges exclude_from_late_charges_cal
,rctt.allow_overapplication_flag allow_over_application
, ( DECODE(gcck.chart_of_accounts_id,101,gcck.segment1
|| ‘-‘
|| gcck.segment2
|| ‘-‘
|| gcck.segment3
|| ‘-‘
|| gcck.segment4
|| ‘-‘
|| gcck.segment5
|| ‘-‘
|| gcck.segment6
|| ‘-‘
|| gcck.segment7
|| ‘-‘
|| gcck.segment8,50274,gcck.segment1
|| ‘-‘
|| gcck.segment2
|| ‘-‘
|| gcck.segment3
|| ‘-‘
|| gcck.segment4
|| ‘-‘
|| gcck.segment5
|| ‘-‘
|| gcck.segment6
|| ‘-‘
|| gcck.segment7
|| ‘-‘
|| gcck.segment8,NULL) ) receivable_account
, ( DECODE(gcck3.chart_of_accounts_id,101,gcck3.segment1
|| ‘-‘
|| gcck3.segment2
|| ‘-‘
|| gcck3.segment3
|| ‘-‘
|| gcck3.segment4
|| ‘-‘
|| gcck3.segment5
|| ‘-‘
|| gcck3.segment6
|| ‘-‘
|| gcck3.segment7
|| ‘-‘
|| gcck3.segment8,50274,gcck3.segment1
|| ‘-‘
|| gcck3.segment2
|| ‘-‘
|| gcck3.segment3
|| ‘-‘
|| gcck3.segment4
|| ‘-‘
|| gcck3.segment5
|| ‘-‘
|| gcck3.segment6
|| ‘-‘
|| gcck3.segment7
|| ‘-‘
|| gcck3.segment8,NULL) ) freight_account
, ( DECODE(gcck2.chart_of_accounts_id,101,gcck2.segment1
|| ‘-‘
|| gcck2.segment2
|| ‘-‘
|| gcck2.segment3
|| ‘-‘
|| gcck2.segment4
|| ‘-‘
|| gcck2.segment5
|| ‘-‘
|| gcck2.segment6
|| ‘-‘
|| gcck2.segment7
|| ‘-‘
|| gcck2.segment8,50274,gcck2.segment1
|| ‘-‘
|| gcck2.segment2
|| ‘-‘
|| gcck2.segment3
|| ‘-‘
|| gcck2.segment4
|| ‘-‘
|| gcck2.segment5
|| ‘-‘
|| gcck2.segment6
|| ‘-‘
|| gcck2.segment7
|| ‘-‘
|| gcck2.segment8,NULL) ) revenue_account
, ( DECODE(gcck4.chart_of_accounts_id,101,gcck4.segment1
|| ‘-‘
|| gcck4.segment2
|| ‘-‘
|| gcck4.segment3
|| ‘-‘
|| gcck4.segment4
|| ‘-‘
|| gcck4.segment5
|| ‘-‘
|| gcck4.segment6
|| ‘-‘
|| gcck4.segment7
|| ‘-‘
|| gcck4.segment8,50274,gcck4.segment1
|| ‘-‘
|| gcck4.segment2
|| ‘-‘
|| gcck4.segment3
|| ‘-‘
|| gcck4.segment4
|| ‘-‘
|| gcck4.segment5
|| ‘-‘
|| gcck4.segment6
|| ‘-‘
|| gcck4.segment7
|| ‘-‘
|| gcck4.segment8,NULL) ) clearing_account
, ( DECODE(gcck5.chart_of_accounts_id,101,gcck5.segment1
|| ‘-‘
|| gcck5.segment2
|| ‘-‘
|| gcck5.segment3
|| ‘-‘
|| gcck5.segment4
|| ‘-‘
|| gcck5.segment5
|| ‘-‘
|| gcck5.segment6
|| ‘-‘
|| gcck5.segment7
|| ‘-‘
|| gcck5.segment8,50274,gcck5.segment1
|| ‘-‘
|| gcck5.segment2
|| ‘-‘
|| gcck5.segment3
|| ‘-‘
|| gcck5.segment4
|| ‘-‘
|| gcck5.segment5
|| ‘-‘
|| gcck5.segment6
|| ‘-‘
|| gcck5.segment7
|| ‘-‘
|| gcck5.segment8,NULL) ) unbilled_receivable_account
, ( DECODE(gcck6.chart_of_accounts_id,101,gcck6.segment1
|| ‘-‘
|| gcck6.segment2
|| ‘-‘
|| gcck6.segment3
|| ‘-‘
|| gcck6.segment4
|| ‘-‘
|| gcck6.segment5
|| ‘-‘
|| gcck6.segment6
|| ‘-‘
|| gcck6.segment7
|| ‘-‘
|| gcck6.segment8,50274,gcck6.segment1
|| ‘-‘
|| gcck6.segment2
|| ‘-‘
|| gcck6.segment3
|| ‘-‘
|| gcck6.segment4
|| ‘-‘
|| gcck6.segment5
|| ‘-‘
|| gcck6.segment6
|| ‘-‘
|| gcck6.segment7
|| ‘-‘
|| gcck6.segment8,NULL) ) unearned_revenue_account
, ( DECODE(gcck7.chart_of_accounts_id,101,gcck7.segment1
|| ‘-‘
|| gcck7.segment2
|| ‘-‘
|| gcck7.segment3
|| ‘-‘
|| gcck7.segment4
|| ‘-‘
|| gcck7.segment5
|| ‘-‘
|| gcck7.segment6
|| ‘-‘
|| gcck7.segment7
|| ‘-‘
|| gcck7.segment8,50274,gcck7.segment1
|| ‘-‘
|| gcck7.segment2
|| ‘-‘
|| gcck7.segment3
|| ‘-‘
|| gcck7.segment4
|| ‘-‘
|| gcck7.segment5
|| ‘-‘
|| gcck7.segment6
|| ‘-‘
|| gcck7.segment7
|| ‘-‘
|| gcck7.segment8,NULL) ) tax_account
FROM
ra_cust_trx_types_all rctt
,hr_all_organization_units hou
,xle_entity_profiles xep
,fnd_lookup_values al
,fnd_lookup_values al2
,fnd_lookup_values al3
,fnd_lookup_values al4
,ra_cust_trx_types_all rctt2
,ar_app_rule_sets aars
,arfv_ar_terms aat
,gl_code_combinations gcck
,gl_code_combinations gcck2
,gl_code_combinations gcck3
,gl_code_combinations gcck4
,gl_code_combinations gcck5
,gl_code_combinations gcck6
,gl_code_combinations gcck7
WHERE
1 = 1
AND rctt.org_id = hou.organization_id
AND xep.legal_entity_id (+) = rctt.legal_entity_id
AND al.lookup_type = ‘INV/CM’
AND al.lookup_code = rctt.type
AND al2.lookup_type = ‘SIGN’
AND al2.lookup_code = rctt.creation_sign
AND al3.lookup_type = ‘INVOICE_TRX_STATUS’
AND al3.lookup_code = rctt.default_status
AND al4.lookup_type = ‘INVOICE_PRINT_OPTIONS’
AND al4.lookup_code = rctt.default_printing_option
AND rctt.credit_memo_type_id = rctt2.cust_trx_type_id (+)
AND rctt.org_id = rctt2.org_id (+)
AND rctt.rule_set_id = aars.rule_set_id (+)
AND rctt.default_term = aat.term_id (+)
AND gcck.code_combination_id (+) = rctt.gl_id_rec
AND gcck2.code_combination_id (+) = rctt.gl_id_rev
AND gcck3.code_combination_id (+) = rctt.gl_id_freight
AND gcck4.code_combination_id (+) = rctt.gl_id_clearing
AND gcck5.code_combination_id (+) = rctt.gl_id_unbilled
AND gcck6.code_combination_id (+) = rctt.gl_id_unearned
AND gcck7.code_combination_id (+) = rctt.gl_id_tax
ORDER BY
1;
Transaction Sources
SELECT
haou.name operating_unit,
xep.name legal_entity,
rbsa.name,
rbsa.description,
DECODE(rbsa.batch_source_type,’INV’,’Manual’,’Imported’) type,
DECODE(rbsa.status,’A’,’Checked’,’Unchecked’) active,
rbsa.start_date from_date,
rbsa.end_date TO_DATE,
DECODE(rbsa.auto_trx_numbering_flag,’Y’,’Checked’,’Unchecked’) automatic_trans_numbering,
rbsa.last_batch_num last_batch_number,
DECODE(rbsa.receipt_handling_option,’ON-ACCOUNT’,’On Account’,’REFUND’,’Credit Card Refund’,rbsa.receipt_handling_option) receipt_handling_for_credits
,
rbsa.allow_duplicate_trx_num_flag allow_dup_transaction_num,
rbsa.copy_inv_tidff_to_cm_flag copy_tran_info_flexfield_cm,
flv.meaning reference_field_default_value,
rctty.name standard_transaction_type,
rbsa1.name credit_transaction_source,
NULL context_value,
NULL regional_information,
rbsa.copy_doc_number_flag copy_document_no_tran_no
FROM
ra_batch_sources_all rbsa,
ra_batch_sources_all rbsa1,
ra_cust_trx_types_all rctty,
hr_all_organization_units haou,
xle_entity_profiles xep,
fnd_lookup_values flv
WHERE
1 = 1
AND rbsa.credit_memo_batch_source_id = rbsa1.batch_source_id (+)
AND rctty.cust_trx_type_id (+) = rbsa.default_inv_trx_type
AND rbsa.org_id = rctty.org_id (+)
AND rbsa1.org_id (+) = rbsa.org_id
AND rbsa.org_id = haou.organization_id
AND xep.legal_entity_id (+) = rbsa.legal_entity_id
AND flv.lookup_code = rbsa.default_reference
AND flv.lookup_type = ‘PA_CODE’
ORDER BY
1;
Salespersons
SELECT
b.resource_number
,t.category
,t.resource_name
,b.start_date_active
,b.end_date_active
,b.transaction_number
FROM
jtf_rs_resource_extns b
,jtf_rs_resource_extns_tl t
WHERE
1 = 1
AND b.resource_id = t.resource_id
AND b.category = t.category
Remit To Address
SELECT
hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.county
,hl.state
,hl.province
,hl.postal_code
FROM
hz_locations hl
,hz_party_sites hps
WHERE
hl.location_id = hps.location_id
AND hps.party_site_number = 1000;
Memo Lines
SELECT
haou.name operating_unit
,amlat.name
,amlat.description description
,DECODE(amlab.line_type,’CB’,’Chargeback Line’,’FREIGHT’,’Freight’,’LINE’,’Line’,’REV’,’Debit Memo Receipt Reversal’,’CHARGES’,’Charges’
,’TAX’,’Tax’,amlab.line_type) type
,amlab.tax_code tax_classification
,amlab.tax_product_category tax_product_category
,amlab.unit_std_price unit_list_price
,amlab.uom_code unit_of_measure
, ( DECODE(gcck.chart_of_accounts_id,101,gcck.segment1
|| ‘-‘
|| gcck.segment2
|| ‘-‘
|| gcck.segment3
|| ‘-‘
|| gcck.segment4
|| ‘-‘
|| gcck.segment5
|| ‘-‘
|| gcck.segment6
|| ‘-‘
|| gcck.segment7
|| ‘-‘
|| gcck.segment8,50274,gcck.segment1
|| ‘-‘
|| gcck.segment2
|| ‘-‘
|| gcck.segment3
|| ‘-‘
|| gcck.segment4
|| ‘-‘
|| gcck.segment5
|| ‘-‘
|| gcck.segment6
|| ‘-‘
|| gcck.segment7
|| ‘-‘
|| gcck.segment8,NULL) ) revenue_account
,NULL account_description
,rr1.name invoicing_rule
,rr2.name accounting_rule
,amlab.start_date from_date
,amlab.end_date TO_DATE
FROM
ar_memo_lines_all_tl amlat
,ar_memo_lines_all_b amlab
,hr_all_organization_units haou
,gl_code_combinations gcck
,ra_rules rr1
,ra_rules rr2
WHERE
1 = 1
AND haou.organization_id = amlat.org_id
AND amlat.org_id = amlab.org_id
AND amlat.memo_line_id = amlab.memo_line_id
AND amlab.gl_id_rev = gcck.code_combination_id (+)
AND amlab.invoicing_rule_id = rr1.rule_id (+)
AND amlab.accounting_rule_id = rr2.rule_id (+)
ORDER BY
1;
Application Rule Sets
SELECT
aars.rule_set_name
,aars.description
,aars.freeze_flag
,aar.rule_sequence
,aar.rule_name
,aard.line_type
,aard.tax_treatment
,aard.rounding_correction_flag
FROM
ar_app_rule_sets aars
,ar_app_rules aar
,ar_app_rule_details aard
WHERE
aars.rule_set_id = aar.rule_set_id
AND aar.rule_id = aard.rule_id
ORDER BY
1
,4;
Receipt Classes
SELECT
arc.name name
,arc.creation_method_code creation_method
,nvl( arc.remit_method_code,’No Remittance’ ) remittance_method
,DECODE( arc.clear_flag,’N’,’Directly’,’S’,’By Automatic Clearing’,’Y’,’By Matching’,arc.clear_flag ) clearance_method
,arm.name receipt_method_name
,arm.printed_name printed_name
,arm.start_date from_date
,arm.end_date TO_DATE
FROM
ar_receipt_classes arc
,ar_receipt_methods arm
WHERE
arc.receipt_class_id = arm.receipt_class_id;
Receipt Sources
SELECT
hou.name operating_unit
,absa.name
,absa.description
,al.meaning receipt_source_type
,arc.name receipt_class
,arm.name payment_method
,cba.bank_account_name bank_account
,al1.meaning batch_numbering
,absa.last_batch_num last_number
,absa.start_date_active effective_start_date
,absa.end_date_active effective_end_date
FROM
ar_batch_sources_all absa
,hr_all_organization_units hou
,fnd_lookup_values al
,ar_receipt_classes arc
,ar_receipt_methods arm
,fnd_lookup_values al1
,ce_bank_accounts cba
WHERE
1 = 1
AND absa.org_id = hou.organization_id
AND hou.organization_id = nvl(:p_org_id,hou.organization_id)
AND al.lookup_code (+) = absa.type
AND al.lookup_type (+) = ‘BATCH_TYPE’
AND arc.receipt_class_id (+) = absa.default_receipt_class_id
AND arm.receipt_method_id (+) = absa.default_receipt_method_id
AND al1.lookup_code (+) = absa.auto_batch_numbering
AND al1.lookup_type (+) = ‘BATCH_NUMBERING’
AND cba.bank_account_id (+) = absa.remit_bank_acct_use_id;
Transmission Format
SELECT
atf.format_name
,atf.description format_description
,DECODE(atf.zengin_char_set,’EBCDIC’,’Ebcdic’,’SJIS’,’Sjis’) zengin_character_set
,flv.meaning record_type
,flv.description record_type_description
FROM
ar_transmission_formats atf
,ar_trans_record_formats atrf
,fnd_lookup_values flv
WHERE
atf.transmission_format_id = atrf.transmission_format_id
AND atrf.record_type_lookup_code = flv.lookup_code
AND flv.lookup_type = ‘TRANS_RECORD_TYPES’
ORDER BY
1;
Statement Cycles
SELECT
arsc.name
,arsc.description
,arsc.interval
,arsc.status
,haou.name
,ascda.statement_date
,DECODE(ascda.printed,’S’,’Checked’,’Y’,’Checked’,’N’,’Unchecked’,ascda.printed) skip
,ascda.print_date
FROM
ar_statement_cycles arsc
,ar_statement_cycle_dates_all ascda
,hr_all_organization_units haou
WHERE
arsc.statement_cycle_id = ascda.statement_cycle_id (+)
AND ascda.org_id = haou.organization_id (+);
Standard Messages
SELECT
b.name
,flv.meaning type
,b.start_date
,b.end_date
,t.text message
FROM
ar_standard_text_tl t
,ar_standard_text_b b
,fnd_lookup_values flv
WHERE
b.standard_text_id = t.standard_text_id
AND b.text_use_type = flv.lookup_code
AND flv.lookup_type = ‘STANDARD_MSG_TYPES’;
Lockbox
SELECT
haou.name operating_unit
,ala.lockbox_number lockbox_number
,ala.status
,absa.name batch_source
,cba.bank_account_num bank_account
,cbb.bank_name bank_name
,cbb.address_line1
,cbb.city
,cbb.zip_code
,ala.contact_name
,ala.telephone
,ala.bank_origination_number
, ( DECODE(chart_of_accounts_id,101,segment1
|| ‘-‘
|| segment2
|| ‘-‘
|| segment3
|| ‘-‘
|| segment4
|| ‘-‘
|| segment5
|| ‘-‘
|| segment6
|| ‘-‘
|| segment7
|| ‘-‘
|| segment8,50274,segment1
|| ‘-‘
|| segment2
|| ‘-‘
|| segment3
|| ‘-‘
|| segment4
|| ‘-‘
|| segment5
|| ‘-‘
|| segment6
|| ‘-‘
|| segment7
|| ‘-‘
|| segment8,NULL) ) accounting_flexfield
,ala.batch_size
,ala.gl_date_source
,ala.exchange_rate_type
,ala.receipt_method_id
FROM
ar_lockboxes_all ala
,hr_all_organization_units haou
,ar_batch_sources_all absa
,ce_bank_accounts cba
,cefv_bank_branches cbb
,gl_code_combinations gcc
WHERE
ala.org_id = haou.organization_id
AND ala.batch_source_id = absa.batch_source_id
AND absa.remit_bank_acct_use_id = cba.bank_account_id
AND cba.bank_branch_id = cbb.bank_branch_id
AND cba.asset_code_combination_id = gcc.code_combination_id;
SQL Query to find form function assigned to which Menu in Responsibility
SELECT
frtl.responsibility_name,
fr.responsibility_key,
fm.menu_id,
fm.menu_name,
menu.function_id,
menu.prompt,
fffv.user_function_name,
fffv.function_name,
fffv.type
FROM
(
SELECT
CONNECT_BY_ROOT fmet.menu_id top_menu_id,
fmet.menu_id menu_id,
fmet.sub_menu_id,
fmet.function_id,
fmet.prompt
FROM
fnd_menu_entries_vl fmet
CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id
AND PRIOR fmet.prompt IS NOT NULL
) menu,
fnd_responsibility fr,
fnd_responsibility_tl frtl,
fnd_menus fm,
fnd_form_functions_vl fffv
WHERE
fr.menu_id = menu.top_menu_id
AND fffv.function_id = menu.function_id
AND fffv.type <> 'SUBFUNCTION'
AND menu.function_id IS NOT NULL
AND menu.prompt IS NOT NULL
AND fm.menu_id = menu.menu_id
AND frtl.responsibility_id = fr.responsibility_id
AND frtl.responsibility_name =:responsibility_name
AND menu.function_id NOT IN (
SELECT
ffvl.function_id
FROM
apps.fnd_resp_functions frf,
applsys.fnd_responsibility_tl frt,
apps.fnd_form_functions_vl ffvl
WHERE
frf.responsibility_id = frt.responsibility_id
AND frf.action_id = ffvl.function_id
AND frf.rule_type = 'F'
AND frt.responsibility_name = frtl.responsibility_name
)
AND menu.menu_id NOT IN (
SELECT
fmv.menu_id
FROM
apps.fnd_resp_functions frf,
applsys.fnd_responsibility_tl frt,
apps.fnd_menus_vl fmv
WHERE
frf.responsibility_id = frt.responsibility_id
AND frf.action_id = fmv.menu_id
AND frf.rule_type = 'M'
AND frt.responsibility_name = frtl.responsibility_name
)
ORDER BY
fffv.user_function_name;