SQL Developer Keyboard Shortcuts

1. SQL Worksheet :
Execute Query  -  Ctrl+Enter or F9
Execute Script  -   F5
Explain Plan     -   F10
Autotrace          -   F6
SQL Recall       -   Ctrl + Up/Down
SQL Recall Append  - Ctrl + Shift +Up/Down
SQL History     -   F8
Completion Insight  -  Ctrl + Spacebar
Format             -          Ctrl + F7
Search Database   -     Alt+G
Toggle Comment   -     Ctrl + /
Toggle Case          -        Ctrl + Shift + '
Increm­ental Search  -  Ctrl + e
Switch to Results     -    Alt + PgDn
Switch to Editor       -     Alt + PgUp
Open Unshared Worksheet       -       Ctrl + Shift + N
DESC             -         Shift + F4

2. Data Grids :
Find Data              -     Ctrl + F
Copy with Column Headers       -      Ctrl + Shift + C
Commit        -              F11
Rollback       -             F12

Query to Find Customer Contact in Oracle Apps R12

SELECT hcasa.org_id,
  role_acct.account_number,
  hcasa.orig_system_reference,
  rel.subject_id,
  rel.object_id,
  party.party_id party_id,
  rel_party.party_id rel_party_id,
  acct_role.cust_account_id ,
  acct_role.cust_acct_site_id ,
  party.person_pre_name_adjunct contact_prefix,
  SUBSTR(party.person_first_name, 1, 40) contact_first_name,
  SUBSTR(party.person_middle_name, 1, 40) contact_middle_name,
  SUBSTR(party.person_last_name, 1, 50) contact_last_name,
  party.person_name_suffix contact_suffix,
  acct_role.status,
  org_cont.job_title contact_job_title,
  org_cont.job_title_code contact_job_title_code,
  rel_party.address1 contact_address1,
  rel_party.address2 contact_address2,
  rel_party.address3 contact_address3,
  rel_party.address4 contact_address4,
  rel_party.country contact_country,
  rel_party.state contact_state,
  rel_party.city contact_city,
  rel_party.county contact_county,
  rel_party.postal_code contact_postal_code
FROM hz_contact_points cont_point,
  hz_cust_account_roles acct_role,
  hz_parties party,
  hz_parties rel_party,
  hz_relationships rel,
  hz_org_contacts org_cont,
  hz_cust_accounts role_acct,
  hz_contact_restrictions cont_res,
  hz_person_language per_lang,
  hz_cust_acct_sites_all hcasa
WHERE acct_role.party_id             = rel.party_id
AND acct_role.role_type              = 'CONTACT'
AND org_cont.party_relationship_id   = rel.relationship_id
AND rel.subject_id                   = party.party_id
AND rel_party.party_id               = rel.party_id
AND cont_point.owner_table_id(+)     = rel_party.party_id
AND cont_point.contact_point_type(+) = 'EMAIL'
AND cont_point.primary_flag(+)       = 'Y'
AND acct_role.cust_account_id        = role_acct.cust_account_id
AND role_acct.party_id               = rel.object_id
AND party.party_id                   = per_lang.party_id(+)
AND per_lang.native_language(+)      = 'Y'
AND party.party_id                   = cont_res.subject_id(+)
AND cont_res.subject_table(+)        = 'HZ_PARTIES'
AND role_acct.cust_account_id        = hcasa.cust_account_id
AND hcasa.cust_acct_site_id          = acct_role.cust_acct_site_id
AND party.party_id                   ='525338';

SUPPLIER API TO UPDATE PAYMENT PRIORITY

PROCEDURE mutl_supplier_updation
   IS
      l_vendor_rec         ap_vendor_pub_pkg.r_vendor_rec_type;
      l_return_status      VARCHAR2 (10);
      l_msg_count          NUMBER;
      l_msg_data           VARCHAR2 (1000);
      l_vendor_id          NUMBER;
      l_party_id           NUMBER;
      l_payment_priority   NUMBER;
      l_supplier_name      VARCHAR2 (255);
      l_error_message      VARCHAR2 (100);
      l_msg_index_out      NUMBER;
      err_code             NUMBER;
      err_msg              VARCHAR2 (1000);

      CURSOR cur_payment
      IS
         SELECT supplier_name, vendor_id, payment_priority
           FROM xx_supplier1 where status is null;
   BEGIN
      FOR supplier_rec IN cur_payment
      LOOP
         l_supplier_name := supplier_rec.supplier_name;
         l_vendor_id := supplier_rec.vendor_id;
         l_payment_priority := supplier_rec.payment_priority;
         DBMS_OUTPUT.put_line ('supplier_name' || l_supplier_name);
         DBMS_OUTPUT.put_line ('vendor_id' || l_vendor_id);
         DBMS_OUTPUT.put_line ('payment_priority' || l_payment_priority);
         --update vendor payment priority value
         l_vendor_rec.vendor_id := l_vendor_id;
         l_vendor_rec.payment_priority := l_payment_priority;
         ap_vendor_pub_pkg.update_vendor_public
                                       (p_api_version        => 1,
                                        x_return_status      => l_return_status,
                                        x_msg_count          => l_msg_count,
                                        x_msg_data           => l_msg_data,
                                        p_vendor_rec         => l_vendor_rec,
                                        p_vendor_id          => l_vendor_rec.vendor_id
                                       );
         DBMS_OUTPUT.put_line ('return_status: ' || l_return_status);
         DBMS_OUTPUT.put_line ('msg_data: ' || l_msg_data);
     --program successful then update the status 'S'
         IF l_return_status = fnd_api.g_ret_sts_success
         THEN
            UPDATE xx_supplier1
               SET status = 'S'
             WHERE vendor_id = l_vendor_id;

            DBMS_OUTPUT.put_line ('success');
         ELSE
            IF l_msg_count > 0
            THEN
               FOR i IN 1 .. l_msg_count
               LOOP
                  apps.fnd_msg_pub.get (p_msg_index          => i,
                                        p_encoded            => fnd_api.g_false,
                                        p_data               => l_msg_data,
                                        p_msg_index_out      => l_msg_index_out
                                       );

                  ---Find Error Message
                  IF l_error_message IS NULL
                  THEN
                     l_error_message := SUBSTR (l_msg_data, 1, 250);
                  ELSE
                     l_error_message :=
                        l_error_message || ' /'
                        || SUBSTR (l_msg_data, 1, 250);
                  END IF;

                  DBMS_OUTPUT.put_line
                                  ('*****************************************');
                  DBMS_OUTPUT.put_line ('API Error: ' || l_error_message);
                  DBMS_OUTPUT.put_line
                                  ('*****************************************');
               END LOOP;

               ---update status and error message
           
                  UPDATE xx_supplier1
                     SET error_message = l_error_message,
                         status = l_return_status
                   WHERE vendor_id = l_vendor_id;
           
            END IF;
         END IF;
      END LOOP;
      ---Find the error message and error code
   EXCEPTION
      WHEN OTHERS
      THEN
         err_code := SQLCODE;
         err_msg := SUBSTR (SQLERRM, 1, 200);
         DBMS_OUTPUT.put_line (   'Error code'
                               || err_code
                               || ':'
                               || 'Error message'
                               || err_msg
                              );
   END mutl_supplier_updation;

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;