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 /* Party / Site Identifiers */
hps.party_site_id AS party_site_id,
hps.party_site_number AS party_site_number,
/* Party & Account */
hp.party_name AS party_name,
hca.account_number AS account_number,
hca.account_name AS account_name,
/* Address */
hl.address1 AS address1,
hl.address2 AS address2,
hl.address3 AS address3,
hl.address4 AS address4,
hl.city AS city,
hl.state AS state,
hl.province AS province,
hl.postal_code AS postal_code,
ftl.territory_short_name AS country,
/* Ship-to Contact Name */ (
SELECT TRIM (hp2.party_name)
FROM apps.hz_cust_account_roles hcar,
apps.hz_relationships hr,
apps.hz_parties hp2
WHERE hr.party_id = hcar.party_id
AND hr.object_id = hp2.party_id
AND hr.object_type = 'PERSON'
AND hcar.cust_account_role_id = ooha.ship_to_contact_id
) AS ship_to_contact_name,
/* Ship-to Contact Email */ (
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
) AS ship_to_contact_email,
/* Survey Preference */ (
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
) AS survey_opt_out,
/* Ship-to Contact Phone */ (
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
) AS ship_to_contact_phone,
/* Account Site Name */
hcsu.location AS account_site_name
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 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;