Useful Oracle Queries

Date / Time-related queries :
1. Get the first day of the month
     SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
     FROM DUAL;

2. Get the last day of the month
      SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
      FROM DUAL;

3. Get the first day of the Year
     SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day"
     FROM DUAL;

4. Get the last day of the year
     SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day"
     FROM DUAL;

5. Get the number of days in current month
     SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
     FROM DUAL;

6. Get the number of days left in current month
     SELECT SYSDATE,
       LAST_DAY (SYSDATE) "Last",
       LAST_DAY (SYSDATE) - SYSDATE "Days left"
      FROM DUAL;

7. Get the number of seconds passed since today (since 00:00 hr)
      SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
      FROM DUAL;

8. Get the number of seconds left today (till 23:59:59 hr)
      SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
      FROM DUAL;
   
Database administration queries
1. Database version information
     SELECT * FROM v$version;

2. Database default information
     SELECT username,
       profile,
       default_tablespace,
       temporary_tablespace
     FROM dba_users;

3. Get the Oracle version
    SELECT VALUE
    FROM v$system_parameter
    WHERE name = 'compatible';

4. Find the Actual size of a Database
    SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB
    FROM dba_data_files;

5. Find the size of the SCHEMA/USER
    SELECT SUM (bytes / 1024 / 1024) "size"
    FROM dba_segments
    WHERE owner = '&owner';

Performance related queries:
1. CPU usage of the USER
     SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
     FROM v$session ss, v$sesstat se, v$statname sn
     WHERE     se.STATISTIC# = sn.STATISTIC#
     AND NAME LIKE '%CPU used by this session%'
     AND se.SID = ss.SID
     AND ss.status = 'ACTIVE'
     AND ss.username IS NOT NULL
    ORDER BY VALUE DESC;

2. Get the current session id, process id, client process id
      SELECT b.sid,
       b.serial#,
       a.spid processid,
       b.process clientpid
     FROM v$process a, v$session b
     WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');
     

Oracle important query

1. Query to Finding Columns and Tables :

     SELECT  table_name, column_name
     FROM   all_tab_columns
     WHERE column_name like 'PO_HEADER%’;

    select * from all_objects
   where object_name like '%PO_HEADERS_ALL%'
    and object_type = 'TABLE';

2. Query to Find Triggers on a Table:

     SELECT trigger_name
    FROM   all_triggers
    WHERE table_name = ‘<TABLE_NAME>”;

3. GL Batches Query:

   SELECT jb.NAME, gh.period_name,
   gh.je_source, gh.je_category,
   gh.currency_code, gc.concatenated_segments,
  gl.entered_cr,gl.entered_dr,
  gl.accounted_cr, gl.accounted_dr
  FROM gl_je_batches jb,
       gl_je_headers gh,
       gl_je_lines gl,
       gl_code_combinations_kfv gc
 WHERE jb.je_batch_id = gh.je_batch_id
   AND gh.je_header_id = gl.je_header_id
   AND gl.code_combination_id = gc.code_combination_id

4. Query to get Database Links:
      SELECT owner            "Owner",db_link          "Link Name",
      username         "Username",created          "Created",
     host             xonly_host
    FROM   all_db_links
  ORDER BY 1,2;


5. Query to get DBA Directories:
       SELECT owner            "Owner"
      ,directory_name   "Directory Name"
     , directory_path   "Directory Path"
    FROM   all_directories
    ORDER BY 1,2;

Query to Join Order Management Tables with Oracle Quoting tables

SELECT oha.header_id
             , oha.order_number
             , ola.line_Id
            , ola.ship_from_org_id organization_id
             , ola.inventory_item_id
            , ola.ordered_quantity
           , ola.order_quantity_uom
          , ola.line_type_id  xx_line_type_id
           , aql.order_line_type_id
         , ola.request_date
         , ola.creation_date
         , ola.ship_to_org_id
        , ola.sold_to_org_id
          ,ola.subinventory

    FROM   aso_quote_headers_all aqh
         , aso_quote_lines_all aql
         , aso_shipments shp
         , oe_order_headers_all oha
         , ont.oe_order_lines_all ola
    WHERE  aqh.quote_header_Id = '&quote_header_id'
    AND    aql.quote_header_id = aqh.quote_header_id
    AND    shp.quote_header_id = aql.quote_header_id
    AND    shp.quote_line_id = aql.quote_line_id
    AND    oha.source_document_id = aqh.quote_header_id
    AND    ola.header_id = oha.header_id
    AND    ola.source_document_line_Id = shp.shipment_id ;

API Error Handling in Oracle Apps

API Error Handling:

•   Every API has 3 out parameters as x_return_status, x_msg_count and x_msg_data.
• Using these 3 parameters, one can use the below code to log or debug errors in APIs.

dbms_output.put_line (SubStr('x_return_status = '||x_return_status, 1, 255));
dbms_output.put_line ('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line (SubStr('x_msg_data = '||x_msg_data, 1, 255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I ||'.'|| SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

List of API in Oracle Apps TCA

1. Create a customer account – HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT

2. Create a customer account relationship – HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE

3. Update customer account relationship – HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCT_RELATE

4. Update customer account - HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT

5. Create customer profile – HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE

6. Create a customer site – HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE

7. Create a customer site using – HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE

8. Create party site – HZ_PARTY_SITE.V2PUB.CREATE_PARTY_SITE

9. Create party_site_use  - HZ_PARTY_SITE.V2PUB.CREATE_PARTY_SITE_USE

10. Create a person-type party and customer account – HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT

11. Create a phone number – HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

12. Create customer address – HZ_LOCATION_V2PUB.CREATE_LOCATION

13. Create group – HZ_PARTY_V2PUB.CREATE_GROUP

14. Update a customer profile – HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE

15. Update customer address – HZ_LOCATION_V2PUB.UPDATE_LOCATION

16. Create an organization-type party and customer account - HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT

 17. Create person  - HZ_PARTY_V2PUB.CREATE_PERSON

 18. Create organization – HZ_PARTY_V2PUB.CREATE_ORGANIZATION

Create Person API in Oracle Apps

DECLARE
   p_person_rec               hz_party_v2pub.person_rec_type;
   x_cust_account_id      NUMBER;
   x_account_number    VARCHAR2 (2000);
   x_party_id                  NUMBER;
   x_party_number         VARCHAR2 (2000);
   x_profile_id                NUMBER;
   x_return_status           VARCHAR2 (2000);
   x_msg_count                NUMBER;
   x_msg_data                  VARCHAR2 (2000);
   l_msg_index_out          NUMBER;
   l_error_message          VARCHAR2 (100);
BEGIN
    p_person_rec.person_first_name :='&person_first_name';
    p_person_rec.person_last_name :='&person_last_name';
    p_person_rec.created_by_module :='TCA_V2_API';
    hz_party_v2pub.create_person( p_init_msg_list => 'T',
                                  p_person_rec    => p_person_rec,
                                  x_party_id => x_party_id,     
                                  x_party_number => x_party_number,
                                  x_profile_id => x_profile_id,   
                                  x_return_status => x_return_status, 
                                  x_msg_count  => x_msg_count,   
                                  x_msg_data => x_msg_data);
  IF x_return_status = fnd_api.g_ret_sts_success THEN
     dbms_output.put_line('output information');
     dbms_output.put_line('x_party_id:' || x_party_id);
     dbms_output.put_line('x_party_number:' || x_party_number);
     dbms_output.put_line('x_profile_id:' || x_profile_id);
   
      IF x_msg_count > 0
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index          => i,
                                  p_encoded            => fnd_api.g_false,
                                  p_data               => x_msg_data,
                                  p_msg_index_out      => l_msg_index_out
                                 );
         END LOOP;

         IF l_error_message IS NULL
         THEN
            l_error_message := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message :=
                       l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;

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

Create Customer Account Site API in Oracle Apps

DECLARE
   p_cust_acct_site_rec          hz_cust_account_site_v2pub.cust_acct_site_rec_type;
   p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;
   x_return_status        VARCHAR2 (2000);
   x_msg_count            NUMBER;
   x_msg_data             VARCHAR2 (2000);
   x_party_site_id        number;
   x_cust_acct_site_id     number;
   x_account_number         VARCHAR2 (100);
   l_msg_index_out          NUMBER;
   l_error_message          VARCHAR2 (100);
BEGIN
   p_cust_acct_site_rec.cust_account_id :='&cust_account_id';
   p_cust_acct_site_rec.created_by_module :='TCA_V2_API';
   hz_cust_account_site_v2pub.create_cust_acct_site( p_init_msg_list => 'T',
                                  p_cust_acct_site_rec   => p_cust_acct_site_rec,
                                  x_cust_acct_site_id => x_cust_acct_site_id,           
                                  x_return_status => x_return_status, 
                                  x_msg_count  => x_msg_count,   
                                  x_msg_data => x_msg_data);
  IF x_return_status = fnd_api.g_ret_sts_success THEN
     dbms_output.put_line('output information');
     dbms_output.put_line('x_cust_acct_site_id:' || x_cust_acct_site_id);
     DBMS_OUTPUT.put_line ('x_account_number:' || x_account_number);
     DBMS_OUTPUT.put_line ('x_party_site_id:' || x_party_site_id);
   
      IF x_msg_count > 0
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index          => i,
                                  p_encoded            => fnd_api.g_false,
                                  p_data               => x_msg_data,
                                  p_msg_index_out      => l_msg_index_out
                                 );
         END LOOP;

         IF l_error_message IS NULL
         THEN
            l_error_message := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message :=
                       l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;

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

Create Contact Point API in Oracle Apps

DECLARE
       p_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
       p_email_rec   hz_contact_point_v2pub.email_rec_type;
       p_phone_rec   hz_contact_point_v2pub.phone_rec_type;
       p_edi_rec    hz_contact_point_v2pub.edi_rec_type;
       p_telex_rec hz_contact_point_v2pub.telex_rec_type;
       p_web_rec hz_contact_point_v2pub.web_rec_type;
       x_return_status        varchar2(200);
       x_msg_count            number;
       x_msg_data             varchar2(200);
       x_contact_point_id     number;
       l_msg_index_out        number;
       l_error_message           varchar2(100);
       x_owner_table_name     varchar2(50);
       x_email_address           varchar2(200);
       x_phone_area_code       varchar2(20);
       x_phone_number          varchar2(50);
       x_phone_line_type       varchar2(50);
       p_init_msg_list            varchar2(30);
BEGIN
     p_contact_point_rec.contact_point_type:='&contact_point_type';
     p_contact_point_rec.owner_table_id :='&owner_table_id';
     p_contact_point_rec.created_by_module :='TCA_V2_API';
   
     hz_contact_point_v2pub.create_contact_point(p_init_msg_list=> 'T',
                                                 p_contact_point_rec => p_contact_point_rec,
                                                 p_email_rec  => p_email_rec ,
                                                 p_phone_rec => p_phone_rec,
                                                 p_edi_rec => p_edi_rec,
                                                 p_telex_rec => p_telex_rec,
                                                 p_web_rec => p_web_rec,
                                                 x_return_status  => x_return_status ,
                                                 x_msg_count  => x_msg_count ,
                                                 x_msg_data => x_msg_data,
                                                 x_contact_point_id => x_contact_point_id);
     IF x_return_status = fnd_api.g_ret_sts_success THEN
       dbms_output.put_line('output information');
       dbms_output.put_line('x_contact_point_id:' ||  x_contact_point_id);
       dbms_output.put_line ('x_owner_table_name:' || x_owner_table_name);
       dbms_output.put_line  ('x_email_address:' || x_email_address);                                   
       dbms_output.put_line ('x_phone_area_code:' || x_phone_area_code);
       dbms_output.put_line ('x_phone_number:' || x_phone_number);
       dbms_output.put_line ('x_phone_line_type:' || x_phone_line_type);
     IF x_msg_count > 0
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index          => i,
                                  p_encoded            => fnd_api.g_false,
                                  p_data               => x_msg_data,
                                  p_msg_index_out      => l_msg_index_out
                                 );
         END LOOP;

         IF l_error_message IS NULL
         THEN
            l_error_message := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message :=
                       l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;

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

Customer account API in Oracle Apps

DECLARE
   p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;
   p_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
   p_person_rec             hz_party_v2pub.person_rec_type;
   p_organization_rec       hz_party_v2pub.organization_rec_type;
   v_party_type             VARCHAR2 (30);
   x_party_id               NUMBER;
   x_party_number           VARCHAR2 (100);
   x_profile_id             NUMBER;
   x_return_status          VARCHAR2 (100);
   x_msg_count              NUMBER;
   x_msg_data               VARCHAR2 (100);
   x_cust_account_id        NUMBER;
   x_account_number         VARCHAR2 (100);
   p_init_msg_list          VARCHAR2 (30);
   l_msg_index_out          NUMBER;
   l_error_message          VARCHAR2 (100);
BEGIN
   v_party_type := '&party_type';
   p_cust_account_rec.created_by_module := 'TCA_V2_API';

   IF upper(v_party_type) = 'PERSON'
   THEN
      p_person_rec.person_first_name := '&p_person_first_name';
      p_person_rec.person_last_name := '&p_person_last_name';
      hz_cust_account_v2pub.create_cust_account
                           (p_init_msg_list             => 'T',
                            p_cust_account_rec          => p_cust_account_rec,
                            p_person_rec                => p_person_rec,
                            p_customer_profile_rec      => p_customer_profile_rec,
                            p_create_profile_amt        => 'F',
                            x_cust_account_id           => x_cust_account_id,
                            x_account_number            => x_account_number,
                            x_party_id                  => x_party_id,
                            x_profile_id                => x_profile_id,
                            x_party_number              => x_party_number,
                            x_return_status             => x_return_status,
                            x_msg_count                 => x_msg_count,
                            x_msg_data                  => x_msg_data
                           );
   ELSIF upper(v_party_type) = 'ORGANIZATION'
   THEN
      p_organization_rec.organization_name := '&p_org_name';
      hz_cust_account_v2pub.create_cust_account
                           (p_init_msg_list             => 'T',
                            p_cust_account_rec          => p_cust_account_rec,
                            p_organization_rec          => p_organization_rec,
                            p_customer_profile_rec      => p_customer_profile_rec,
                            p_create_profile_amt        => 'F',
                            x_cust_account_id           => x_cust_account_id,
                            x_account_number            => x_account_number,
                            x_return_status             => x_return_status,
                            x_msg_count                 => x_msg_count,
                            x_msg_data                  => x_msg_data,
                            x_party_id                  => x_party_id,
                            x_party_number              => x_party_number,
                            x_profile_id                => x_profile_id
                           );
   END IF;

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('output information');
      DBMS_OUTPUT.put_line ('x_party_id:' || x_party_id);
      DBMS_OUTPUT.put_line ('x_party_number:' || x_party_number);
      DBMS_OUTPUT.put_line ('x_profile_id :' || x_profile_id);
      DBMS_OUTPUT.put_line ('  x_account_number:' || x_account_number);
   ELSE
      IF x_msg_count > 0
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index          => i,
                                  p_encoded            => fnd_api.g_false,
                                  p_data               => x_msg_data,
                                  p_msg_index_out      => l_msg_index_out
                                 );
         END LOOP;

         IF l_error_message IS NULL
         THEN
            l_error_message := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message :=
                       l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;

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

List All Tables in Oracle Apps

List All Tables in Oracle:
   1. Viewing Tables Owned by Current user
     select table_name,owner
     from user_tables
     order by owner,table_name;

   2. Viewing Tables Accessible by Current User
     select table_name,owner
     from all_tables
     order by owner,table_name;
 
   3. Viewing All Tables
     select table_name,owner
     from dba_tables
     where owner='schema_name'
     order by owner,table_name;