Customer tables and their joins in Oracle Apps

Hi! This is post will show you important customer tables in Oracle apps along with their joins.

Important Customer tables:

HZ_PARTIES

HZ_CUST_ACCOUNTS

HZ_PARTY_SITES

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_LOCATIONS

Joins:

HZ_PARTIES, HZ_PARTY_SITES - party_id

HZ_PARTIES, HZ_CUST_ACCOUNTS - party_id

HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL - cust_account_id

HZ_PARTY_SITES, HZ_CUST_ACCT_SITES_ALL - party_site_id

 HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL - cust_acct_site_id

HZ_PARTY_SITES, HZ_LOCATIONS - location_id.

Queries to get ship to and bill to address details:

Ship to address:

SELECT hp.PARTY_NAME customer,

       hl.ADDRESS1,

       hl.ADDRESS2,

       hl.CITY,

       hl.POSTAL_CODE,

       hl.STATE,

       hl.PROVINCE,

       hl.COUNTRY

FROM hz_parties hp,

  hz_cust_accounts hca,

  hz_cust_acct_sites_all hcasa,

  hz_cust_site_uses_all hcsua,

  hz_party_sites hps,

  hz_locations hl

WHERE hp.party_id        = hca.party_id

AND hca.cust_account_id   = hcasa.cust_account_id

AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID

AND hp.party_id          = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hl.location_id       = hps.location_id

AND hp.party_name= <party_name>

AND hcsua.site_use_code = 'SHIP_TO'

Bill to address:

SELECT hp.PARTY_NAME customer,

       hl.ADDRESS1,

       hl.ADDRESS2,

       hl.CITY,

       hl.POSTAL_CODE,

       hl.STATE,

       hl.PROVINCE,

       hl.COUNTRY

FROM hz_parties hp,

  hz_cust_accounts hca,

  hz_cust_acct_sites_all hcasa,

  hz_cust_site_uses_all hcsua,

  hz_party_sites hps,

  hz_locations hl

WHERE hp.party_id        = hca.party_id

AND hca.cust_account_id   = hcasa.cust_account_id

AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID

AND hp.party_id          = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hl.location_id       = hps.location_id

AND hp.party_name = <party_name>

AND hcsua.site_use_code = 'BILL_TO';

Hope this helps you. Great learning ahead!!

Oracle Technical Logical Interview Questions - Part 3

Hi! In this post, I'm going to give you a glimpse of how interviewer would test your logical ability for the requirement given. Having a good logical ability as a technical consultant would not only help you for the interview but also for your career growth. So, lets get started.

1. Consider the Invoices table below:

INVOICE_ID	INVOICE_NUM
101	INV101
102	INV102
103	INV103
103	INV103
102	INV102
101	INV101

Write a query to remove the duplicates from the Invoices table.

Sol: DELETE FROM invoices WHERE rowid NOT IN 

(SELECT MIN(rowid) FROM invoices GROUP BY invoice_id, invoice_num);

OR

DELETE FROM invoices WHERE rowid NOT IN

(SELECT MAX(rowid) FROM invoices GROUP BY invoice_id, invoice_num);

2. Consider below items table:

ITEM_NUM	INV_ORG_ID
Pen	87
Pen	86
Book	87
Laptop	86
Computer	87
Mouse	87
Mouse	86
Keyboard	87
Keyboard	86

Write a query to display items that either exist in inv_org_id 87 or 86 but not both.

Sol: Method 1: SELECT item_num FROM items

                         MINUS

                         (SELECT item_num FROM items WHERE inv_org_id = 87

                         INTERSECT

                         SELECT item_num FROM items WHERE inv_org_id = 86);

Method 2: SELECT item_num FROM items WHERE inv_org_id = 87 AND item_num NOT IN                              (SELECT item_num FROM items WHERE inv_org_id = 86)

                  UNION

                  SELECT item_num FROM items WHERE inv_org_id = 86 AND item_num NOT IN                             (SELECT item_num FROM items WHERE inv_org_id = 87);

Method 3: SELECT item_num FROM items

                  MINUS

                  (SELECT item_num FROM items WHERE inv_org_id = 86 AND item_num IN (SELECT                      item_num FROM items WHERE inv_org_id = 87));

Method 4: SELECT item_num FROM items WHERE item_num NOT IN 

                 (SELECT item_num FROM items WHERE inv_org_id = 87

                 INTERSECT

                 SELECT item_num FROM items WHERE inv_org_id = 86);

Method 5: SELECT item_num FROM items WHERE item_num NOT IN 

                 (SELECT item_num FROM items WHERE inv_org_id = 87 AND item_num IN 

                 (SELECT item_num FROM items WHERE inv_org_id = 86));

3. Write a query to retrieve order numbers that have more than 10 lines.

Sol: SELECT order_number, count(*) 

       FROM oe_order_headers_all ooh, oe_order_lines_all ool

       WHERE ooh.header_id = ool.header_id 

       GROUP BY order_number 

       HAVING count(*) > 10;

4. Write a query to get the hire date of least employee number.

Sol: Method 1: SELECT hire_date FROM emp WHERE empno = (SELECT MIN(empno) FROM                                  emp);

Method 2: SELECT MIN(hire_date) KEEP (dense_rank FIRST ORDER BY empno) FROM emp;

5. Consider below ITEMS table:

ITEM_NUM	INV_ORG_ID
Pen	87
Air Conditioner	86
Book	87
Laptop	86
Computer	87
Ebook	87
Mouse	86
Inkpot	87
Keyboard	86

Write a query to display the items that don't start or end with a vowel.

Sol: SELECT item_num FROM items WHERE SUBSTR(item_num, '1', '1')  NOT IN ('a','e','i','o','u') AND SUBSTR(item_num, '-1', '1') NOT IN (item_num, 'A','E','I','O','U');

6. Consider below table Emp.

Empno	Empname
10	John
11	Jason
12	Joseph
13	Jones
14	Jane
15	Jackmel

Write a query to to display the empname values in the following order: 

Jones, Jason, John, Jackmel, Joseph, Jane.

Sol: SELECT empname FROM emp ORDER BY DECODE(empname, 'Jones',  1, 'Jason', 2, 'John', 3, 'Jackmel', 4, 'Joseph', 5, 'Jane', 6, 7);

7. Display the difference between given two dates in hours: 21-OCT-2019 and 19-AUG-2020

Sol: SELECT (TO_DATE('21-OCT-2019 00:00:00','DD-MON-YYYY')-('19-AUG-2020 00:00:00','DD-MON-YYYY'))*24 FROM dual;

8. Build a hierarchical query on emp table to display the records such that the MGR column equals their EMPNO, start with person(s) who have no manager.

Sol: SELECT * FROM emp

        START WITH mgr IS NULL

        CONNECT BY PRIOR empno = mgr;

9. How to fetch the first record from a table (Take emp as example)?

Sol: SELECT * FROM emp WHERE rownum = 1;

10. How to fetch the last record from a table (Take emp as example)?

Sol: SELECT * FROM emp WHERE rowid = (SELECT MAX(rowid FROM emp);