Query to Find Concurrent Programs Performance Query in Oracle Apps R12

SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,' R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
FROM  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
       AND f.program_application_id = p.application_id
       AND f.concurrent_program_id = pt.concurrent_program_id
       AND f.program_application_id = pt.application_id
       AND pt.user_concurrent_program_name='Order Import'
ORDER BY
      f.actual_start_date DESC;

Query for Supplier Details with Bank in Oracle Apps R12

SELECT       
                aps.vendor_name "vendor_name",
                ass.vendor_site_code "Payee_Identifier"
                , accts.ext_bank_account_id "Payee_Bank_Account_Identifier"
                , bank.party_name "Bank_Name"
                , branch.bank_branch_name "Branch_Name"
                , accts.country_code "Account_Country_Code"
                , accts.bank_account_name "Account_Name"
                , accts.bank_account_num "Account_Number"
                , accts.currency_code "Account_Currency_Code"
                , NULL "Allow_International_Payments"
                , uses.start_date "Account_start_date"
                , uses.end_date "Account_end_date"
                , accts.iban "IBAN"
                ,accts.check_digits "check_digits"
                , accts.bank_account_name_alt "Account_Alternate_Name"
                , accts.bank_account_type "Bank_account_type"
                , accts.account_suffix "Account_Suffix"
                , accts. description "DESCRIPTION"
                , accts.agency_location_code "Agency_Location_Code"
                , accts.exchange_rate_agreement_num   "Exchange_Rate_Agreement_Number"
                , accts.exchange_rate_agreement_type "Exchange_Rate_Agreement_Type"
                , accts.exchange_rate "EXCHANGE_RATE"
                , accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE"
           FROM  ap_suppliers aps
                ,ap_supplier_sites_all ass
                ,iby_pmt_instr_uses_all uses
                ,iby_external_payees_all payee
                ,iby_ext_bank_accounts accts
                ,hz_parties bank
                ,ce_bank_branches_v branch
          WHERE uses.instrument_type   = 'BANKACCOUNT'
            AND aps.vendor_id          = ass.vendor_id
            AND aps.party_id           = payee.payee_party_id
            AND payee.ext_payee_id     = uses.ext_pmt_party_id
            AND payee.payment_function = 'PAYABLES_DISB'
            AND uses.instrument_id     = accts.ext_bank_account_id
            AND accts.bank_id          = bank.party_id
            --AND accts.bank_id          = bankprofile.party_id
            AND accts.branch_id         = branch.branch_party_id
            AND aps.end_date_active  IS NULL
            AND ass.inactive_date    IS NULL;

Query to Find Customer Query in Oracle Apps R12

SELECT hca.account_number customer_number, hp.party_name customer_name,
       (SELECT hcpa.currency_code
          FROM hz_cust_profile_amts hcpa
         WHERE hcpa.cust_account_id = hca.cust_account_id
           AND hcpa.site_use_id IS NULL) " CURRENCY",
       hp.attribute1 " Customer Credit Limit",
       hp.attribute1 " Customer Order Limit",
       (SELECT meaning
          FROM ar_lookups
         WHERE lookup_type = 'CUSTOMER CLASS'
           AND lookup_code = hca.customer_class_code)
                                                    "Customer Classification",
       (SELECT hcpc.NAME
          FROM hz_cust_profile_classes hcpc,
               hz_customer_profiles hcp
         WHERE hcpc.profile_class_id = hcp.profile_class_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Customer Profile ",
       (SELECT NAME
          FROM ra_terms
         WHERE term_id = hca.payment_term_id) "Customer Payment Terms",
       (SELECT meaning
          FROM ar_lookups
         WHERE lookup_type = 'CUSTOMER_CATEGORY'
           AND lookup_code = hcasa.customer_category_code)
                                                          "Customer Category",
       hps.party_site_number site_number,
       (SELECT hcpa.trx_credit_limit
          FROM hz_cust_profile_amts hcpa
         WHERE hcpa.cust_account_id =
                                     hca.cust_account_id
           AND hcpa.site_use_id IS NULL) " site Credit Limit",
       (SELECT hcpa.overall_credit_limit
          FROM hz_cust_profile_amts hcpa
         WHERE hcpa.cust_account_id = hca.cust_account_id
           AND hcpa.site_use_id IS NULL) " Site Order Limit",
       (SELECT hcpc.NAME
          FROM hz_cust_profile_classes hcpc,
               hz_customer_profiles hcp,
               hz_cust_site_uses_all hcsua
         WHERE hcpc.profile_class_id = hcp.profile_class_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Customer Site Profile ",
       (SELECT rt.NAME
          FROM hz_customer_profiles hcp,
               ra_terms rt,
               hz_cust_site_uses_all hcsua
         WHERE hcp.standard_terms = rt.term_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Site Payemnt Terms",
       (SELECT DISTINCT hcasa.attribute1
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id =
                               hcasa.cust_acct_site_id)
                                                       " Customer Site Class",
       (SELECT address1
          FROM hz_locations
         WHERE location_id = hps.location_id) address1,
       (SELECT address2
          FROM hz_locations
         WHERE location_id = hps.location_id) address2,
       (SELECT address3
          FROM hz_locations
         WHERE location_id = hps.location_id) address3,
       (SELECT address4
          FROM hz_locations
         WHERE location_id = hps.location_id) address4,
       (SELECT city
          FROM hz_locations
         WHERE location_id = hps.location_id) city,
       (SELECT postal_code
          FROM hz_locations
         WHERE location_id = hps.location_id) postal_code,
       (SELECT state
          FROM hz_locations
         WHERE location_id = hps.location_id) state,
       (SELECT ftt.territory_short_name
          FROM fnd_territories_tl ftt, hz_locations hl
         WHERE hl.country = ftt.territory_code
           AND hl.location_id = hps.location_id) country,
       (SELECT DISTINCT hcsua.LOCATION
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id =
                                     hcasa.cust_acct_site_id
                    AND hcsua.site_use_code = 'BILL_TO') bill_to_location,
       (SELECT DISTINCT hcsua.LOCATION
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id =
                                     hcasa.cust_acct_site_id
                    AND hcsua.site_use_code = 'SHIP_TO'
  and hcsua.status='A') ship_to_location,
       (SELECT resource_name
          FROM jtf_rs_defresources_v jrd,
               hz_cust_site_uses_all hcsua
         WHERE jrd.resource_id = hcsua.primary_salesrep_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') "Sales Person",
       (SELECT NAME
          FROM oe_transaction_types_v ott,
               hz_cust_site_uses_all hcsua
         WHERE ott.transaction_type_id = hcsua.order_type_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') "Order type",
       (SELECT NAME
          FROM qp_price_lists_v qpl,
               hz_cust_site_uses_all hcsua
         WHERE qpl.price_list_id = hcsua.price_list_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') " Price List Name ",
       (SELECT organization_name
          FROM org_organization_definitions ood,
               hz_cust_site_uses_all hcsua
         WHERE ood.organization_id = hcsua.warehouse_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') "Ware House ",
       (SELECT segment1 || '.' || segment2 || '.' || segment3
          FROM ra_territories rt, hz_cust_site_uses_all hcsua
         WHERE rt.territory_id = hcsua.territory_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') territory,
       (SELECT    segment1
               || '.'
               || segment2
               || '.'
               || segment3
               || '.'
               || segment4
               || '.'
               || segment5
               || '.'
               || segment6
               || '.'
               || segment7
               || '.'
               || segment8
          FROM gl_code_combinations gcc, hz_cust_site_uses_all hcsua
         WHERE code_combination_id = hcsua.gl_id_rec
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') " Receivable Account ",
       (SELECT    segment1
               || '.'
               || segment2
               || '.'
               || segment3
               || '.'
               || segment4
               || '.'
               || segment5
               || '.'
               || segment6
               || '.'
               || segment7
               || '.'
               || segment8
          FROM gl_code_combinations gcc, hz_cust_site_uses_all hcsua
         WHERE gcc.code_combination_id = hcsua.gl_id_rev
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') " Revenue Account ",
       (SELECT ac.NAME
          FROM hz_customer_profiles hcp,
               ar_collectors ac
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id IS NULL
           AND hcp.cust_account_id = hca.cust_account_id)
                                                   "Customer Collector Code ",
       (SELECT ac.description
          FROM hz_customer_profiles hcp,
               ar_collectors ac
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id IS NULL
           AND hcp.cust_account_id = hca.cust_account_id)
                                                   "Customer Collector Name ",
       (SELECT ac.NAME
          FROM hz_customer_profiles hcp,
               ar_collectors ac,
               hz_cust_site_uses_all hcsua
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO')
                                              "Customer Site Collector Code ",
       (SELECT ac.description
          FROM hz_customer_profiles hcp,
               ar_collectors ac,
               hz_cust_site_uses_all hcsua
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO')
                                              "Customer Site Collector Name ",
       (SELECT ar.meaning
          FROM hz_customer_profiles hcp,
               ar_lookups ar
         WHERE ar.lookup_type = 'CREDIT_CLASSIFICATION'
           AND hcp.credit_classification = ar.lookup_code
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Customer Credit Class",
       (SELECT ar.meaning
          FROM hz_customer_profiles hcp,
               ar_lookups ar,
               hz_cust_site_uses_all hcsua
         WHERE ar.lookup_type = 'CREDIT_CLASSIFICATION'
           AND hcp.credit_classification = ar.lookup_code
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Customer Site Credit Class",
       (SELECT hcsua.primary_flag
          FROM hz_cust_site_uses_all hcsua
         WHERE hcsua.cust_acct_site_id =
                                    hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Primary Bill To",
       (SELECT NAME
          FROM ar_statement_cycles astc,
               hz_customer_profiles hcp
         WHERE astc.statement_cycle_id = hcp.statement_cycle_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Statement Cycle",
       (SELECT hcp.cons_bill_level
          FROM hz_customer_profiles hcp
         WHERE hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) " Bill Level",
       (SELECT hcp.cons_inv_type
          FROM hz_customer_profiles hcp
         WHERE hcp.cust_account_id =
                               hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Type ( Detail / Summary)",
       hca.attribute1 sec_type, hca.attribute2 bank, hca.attribute3 branch,
       hca.attribute4 doc_no, hca.attribute5 amount,
       hca.attribute6 issue_date, hca.attribute7 exp_date,
       hca.attribute8 " Customer Information", hca.attribute9 company_type,
       hca.attribute10 "CREDIT LMT CHECK", hca.attribute11 years,
       hca.attribute12 "FROM DATE", hca.attribute13 "TO DATE",
       hca.attribute14 " CREDIT_AUTOHOLD(Y/N)",
       hca.attribute15 "CEILING_LIMIT(OMR)", hca.attribute16 cash_cust,
       hca.attribute17 comm_regn_no_date, hca.attribute18 id_card_no,
       hca.attribute19 grace_period, hca.attribute20 dormant_rationalisation
  FROM hz_parties hp,
       hz_party_sites hps,
       hz_cust_accounts_all hca,
       hz_cust_acct_sites_all hcasa
 WHERE hp.party_id = hps.party_id
   AND hp.party_id = hca.party_id
   AND hcasa.party_site_id = hps.party_site_id
   AND hca.cust_account_id = hcasa.cust_account_id
   AND hca.account_number = '415091';

Oracle Application Short Names

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa. basepath                 "Basepath"
  FROM fnd_application     fa,
             fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
 AND fat.language      = USERENV('LANG')
 ORDER BY fat.application_name;

APPLICATION SHORT NAME                APPLICATION NAME
1. FND                                                       Application Object Library
2. OFA                                                       Assets
3. BOM                                                     Bills of Material
4. IBC                                                        Content Manager
5. ODQ                                                       Data Query
6. ZX                                                          E-Business Tax
7. EDR                                                        E-Records
8. POM                                                       Exchange
9. GL                                                           General Ledger
10. INV                                                        Inventory
11. MFG                                                      Manufacturing
12. WPS                                                     Manufacturing Scheduling
13. MRP                                                     Master Scheduling/MRP
14. OE                                                         Order Entry
15. ONT                                                       Order Management
16. AP                                                           Payables
17. IBY                                                         Payments
18. PAY                                                         Payroll
19. PJM                                                        Project Manufacturing
20. PA                                                           Projects
21. PN                                                           Property Manager
22. PO                                                           Purchasing
23. QA                                                           Quality
24. AR                                                           Receivables
25. WSH                                                       Shipping Execution
26. XLA                                                        Subledger Accounting

Oracle Form Personalization Query

SELECT fcr.id,
  fff.user_function_name,
  fcr.form_name,
  fcr.sequence,
  fcr.description,
  fcr.enabled,
  fu.user_name,
  fcr.trigger_event,
  fcr.trigger_object,
  fcr.last_update_date,
  fcr.condition,
  fff.function_name,
  ffca.sequence,
  ffca.property_value,
  ffca.target_object,
  ffca.message_text,
  fffs.irep_assoc_function_name,
  fffs.irep_description,
  fffs.irep_method_name
FROM fnd_form_custom_rules fcr,
  fnd_form_functions_vl fff,
  fnd_user fu,
  fnd_form_custom_actions ffca,
  fnd_form_functions fffs,
  fnd_form_functions_tl ffft
WHERE fcr.function_name = fff.function_name
AND ffca.created_by     = fu.user_id
AND fcr.last_updated_by = fu.user_id
AND ffca.rule_id        = fcr.id
AND fffs.function_id    = ffft.function_id
AND fcr.form_name       ='POXPOEPO'
ORDER BY fcr.last_update_date DESC;

How to Copy a Table With or Without Data Using CREATE TABLE AS Statement

1. How to copy both the structure and data of a table

      create table emp_copy

      as

      select * from emp;

2. How to copy specific columns of a table along with their data.

     create table emp_copy

     as

     select empno, sal from emp;

3. How to copy only the structure of the table without the data.

    create table emp_copy

    as

    select * from emp

     where 1=0;

Query to Find Order Management QUERY in Oracle Apps R12

SELECT   oh.order_number, ol.ordered_item, org.organization_name,
         ol.ordered_quantity, ol.shipped_quantity,         
         rsv.reservation_quantity,
         lkup.meaning pick_status, oh.org_id
FROM     oe_order_headers_all oh,
         oe_order_lines_all ol,
         oe_transaction_types_tl typ,
         wsh_delivery_details wdd,
         mtl_reservations rsv,
         org_organization_definitions org,
         fnd_lookup_values lkup
WHERE oh.header_id = ol.header_id
     AND oh.order_type_id = typ.transaction_type_id
     AND typ.NAME NOT LIKE '%Internal%Order%'
     AND wdd.source_header_id = oh.header_id
     AND wdd.source_line_id = ol.line_id
     AND wdd.released_status = lkup.lookup_code
     AND lkup.lookup_type = 'PICK_STATUS'
     AND org.organization_id = ol.ship_from_org_id
     AND rsv.demand_source_header_id(+) = ol.header_id
     AND rsv.demand_source_line_id(+) = ol.line_id
     AND ol.ordered_item LIKE 'X%'
     AND ol.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
ORDER BY order_number;



SELECT   rh.segment1 requisition_number, hrl.location_code requesting_org,
         oh.order_number, org.organization_name fulfillment_org,
         it.segment1 item_number, rl.quantity
 FROM po_requisition_headers_all rh,
         po_requisition_lines_all rl,
         hr_locations hrl,
         mtl_system_items_b it,
         oe_order_headers_all oh,
         oe_order_lines_all ol,
         org_organization_definitions org
WHERE rh.requisition_header_id = rl.requisition_header_id
     AND rl.deliver_to_location_id = hrl.location_id
     AND it.inventory_item_id = rl.item_id
     AND it.organization_id = rl.source_organization_id
     AND rl.requisition_header_id = oh.source_document_id
     AND ol.ship_from_org_id = org.organization_id
     AND oh.header_id = ol.header_id
     AND ol.inventory_item_id = rl.item_id
     AND it.segment1 LIKE 'X%'
     AND rh.authorization_status = 'APPROVED'
     AND ol.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
ORDER BY rh.segment1;

Query to Join Order Management Tables with Oracle Quoting tables

SELECT ooh.header_id
         , ooh.order_number
         , ool.line_Id
         , ool.ship_from_org_id organization_id
         , ool.inventory_item_id
         , ool.ordered_quantity
         , ool.order_quantity_uom
         , ool.line_type_id  xx_line_type_id
         , qtl.order_line_type_id
         , ool.request_date
         , ool.creation_date
         , ool.ship_to_org_id
         , ool.sold_to_org_id
         , ool.subinventory

    FROM   aso.aso_quote_headers_all qte
         , aso.aso_quote_lines_all qtl
         , aso.aso_shipments shp
         , ont.oe_order_headers_all ooh
         , ont.oe_order_lines_all ool
    WHERE  qte.quote_header_Id = 382
    AND    qtl.quote_header_id = qte.quote_header_id
    AND    shp.quote_header_id = qtl.quote_header_id
    AND    shp.quote_line_id = qtl.quote_line_id
    AND    ooh.orig_sys_document_ref like qte.quote_number||':%'
    AND    ooh.source_document_id = qte.quote_header_id
    AND    ool.header_id = ooh.header_id
    AND    ool.source_document_line_Id = shp.shipment_id ;

Find Table and Column Name with table and column description

SELECT fa.application_id,
  fa.application_short_name ,
  fat.application_name ,
  table_name ,
  column_name ,
  ft.description table_description ,
  fc.description column_description
FROM fnd_tables ft ,
  fnd_columns fc ,
  fnd_application_tl fat ,
  fnd_application fa
WHERE ft.table_id      = fc.table_id
AND fat.application_id = ft.application_id
AND fa.application_id  = fat.application_id
AND fc.column_name =:column_name;

Complex Sql Queries - Part 2

1. Display the names of the employees who are working in the company for The past 5 years.
      SELECT ename
      FROM emp
      WHERE TO_CHAR (sysdate,'YYYY')-TO_CHAR(hiredate,'YYYY')>=5;

2. Display your age in days.
      SELECT to_date (sysdate)-to_date ('01-apr-96')
      FROM dual;

3. Display your age in months.
      SELECT months_between (sysdate,'01-apr-96')
      FROM dual;

4. Display the 10th record of emp table without using group by and rowid?
      SELECT *
      FROM EMP
      WHERE ROWNUM<11
      MINUS
      SELECT * FROM EMP WHERE ROWNUM <10;

5. Display the various jobs and total number of employees within each job Group.
      SELECT job,
      COUNT (job)
     FROM EMP
     GROUP BY job;

6. Display the depart numbers and max salary for each department.
      SELECT deptno,
      MAX (sal)
     FROM EMP
     GROUP BY deptno;

7. Display the depart numbers with more than three employees in each dept.
      SELECT deptno,
     COUNT (deptno)
     FROM EMP
     GROUP BY deptno
     HAVING COUNT (*)>3;

8. Display the name of the employee who earns the highest salary.
      SELECT ename
     FROM emp
     WHERE sal=
     (SELECT MAX (sal) FROM emp);

9. Display the names of the employees who earn the highest salary in their respective departments.
     SELECT ename,
                    sal,
                    deptno
     FROM EMP
     WHERE sal IN
     (SELECT MAX (sal) FROM EMP GROUP BY deptno);

10. Find out the top 5 earners of the company?
     SELECT DISTINCT SAL
     FROM EMP E
    WHERE 5>=
    (SELECT COUNT (DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)
    ORDER BY SAL DESC;

     

Complex SQL Queries

1. Display Salary in Words.
    SELECT ename,
                   sal,
               TO_CHAR(to_date(sal,'jsp'),'jsp')"in words"
    FROM emp;

2. Find the 3rd MAX salary in the emp table.
    SELECT DISTINCT sal
    FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e1.sal <= e2.sal);

3. Find the 3rd MIN salary in the emp table.
     SELECT DISTINCT sal
     FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 where e1.sal >= e2.sal);

4. Select FIRST n records from a table.
     SELECT * FROM emp WHERE rownum <= &n;

5. Select LAST n records from a table.
     SELECT * FROM emp
     MINUS
    SELECT * FROM emp WHERE rownum <=
   (SELECT COUNT(*) - &n FROM emp);

6. How to get 3 Max salaries?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
   (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal <= b.sal  )
   ORDER BY a.sal DESC;

7. How to get 3 Min salaries ?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal);

8. How to get nth max salaries?
   SELECT DISTINCT hire_date
   FROM emp a
   WHERE
   &n =
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal );