Interview Question

    1. Pseudo columns

     Automatically filled by Oracle.

     Ex: sysdate, nextval, currval, rowid, rownum, level, sqlcode, sqlerrm, new, old.

2. What are the Parameters for raise_application_errror ()?

    The parameters are Error Code and an Error Message.

    The Syntax: raise_application_errror (Error Code, Error Message);

3. What are the User PARAMETERS in the Reports?

           P_CONC_REQUEST_ID

       P_FLEX_VALUE

Oracle PL/SQL BULK COLLECT

• This keyword asks the SQL engine to return all the rows in one or several collections before returning to the PL/SQL engine so, there is one single roundtrip for all the rows between SQL and PL/SQL engine.
• BULK COLLECT cannot be used on the client side.

(Select)(Fetch)(execute immediate) … BULK COLLECT Into collection_name [,collection_name, …] [LIMIT max].

• LIMIT is used to limit the number of rows returned
• BULK COLLECT can also be used to retrieve the result of a DML statement that uses the RETURNING INTO clause.

SQL> set server output on

SQL> Declare

     TYPE    TYP_TAB_EMP IS A TABLE OF EMP.EMPNO%Type ;

     Temp_no TYP_TAB_EMP; -- a collection of EMP.EMPNO%Type

     Cursor  C_EMP is Select empno From EMP ;

      Pass    Pls_integer := 1 ;

    Begin

      Open C_EMP ;

    Loop

        -- Fetch the table 3 by 3 --

       Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;

       Exit When C_EMP%NOTFOUND ;

       For i In Temp_no.first..Temp_no.last Loop

         dbms_output.put_line( 'Pass ' || to_char(Pass) || ' Empno= ' || Temp_no(i) ) ;

       End loop ;

       Pass := Pass + 1 ;

     End Loop ;

   End ;

 

Pass 1 Empno= 9999

Pass 1 Empno= 7369

Pass 1 Empno= 7499

Pass 2 Empno= 7521

Pass 2 Empno= 7566

Pass 2 Empno= 7654

Pass 3 Empno= 7698

Pass 3 Empno= 7782

Pass 3 Empno= 7788

Pass 4 Empno= 7839

Pass 4 Empno= 7844

Pass 4 Empno= 7876

Pass 5 Empno= 7900

Pass 5 Empno= 7902

Pass 5 Empno= 7934

PL/SQL procedure successfully completed.

You can use the LIMIT keyword to preserve your rollback segment:

Declare

  TYPE    TYP_TAB_EMP IS A TABLE OF EMP.EMPNO%Type ;

  Temp_no TYP_TAB_EMP ;

  Cursor  C_EMP is Select empno From EMP ;

  max_lig Pls_Integer := 3 ;

Begin

  Open C_EMP ;

  Loop

    Fetch C_EMP BULK COLLECT into Temp_no LIMIT max_lig ;

    Forall i In Temp_no.first..Temp_no.last

        Update EMP set SAL = Round(SAL * 1.1) Where empno = Temp_no(i) ;

    Commit ; -- Commit every 3 rows

    Temp_no.DELETE ;

    Exit When C_EMP%NOTFOUND ;

  End Loop ;

End ;

BULK COLLECT can also be used to retrieve the result of a DML statement that uses the RETURNING INTO clause:

SQL> Declare

       TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type;

       TYPE    TYP_TAB_NOM TABLE OF EMP.ENAME%Type; 

       Temp_no TYP_TAB_EMPNO;

       Tnoms   TYP_TAB_NOM ; 

    Begin

       -- Delete rows and return the result into the collection --

       Delete From EMP where sal > 3000

       RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;

      For i in Temp_no.first..Temp_no.last Loop

         dbms_output.put_line( 'Fired employee : ' || To_char( Temp_no(i) ) || ' ' || Tnoms(i) ) ;

      End  loop ;

   End ;

 

Fired employee: 7839 KING

PL/SQL procedure successfully completed.

Difference between NO DATA FOUND and %NOTFOUND?

NO DATA FOUND is an exception raised only for the SELECT...INTO statements when the where clause of the query does not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

If the where clause of an update or delete statement does not match any rows, SQL%NOTFOUND is set to TRUE, rather than raising NO_DATA_FOUND. Because of this, all of the fetch loops are shown so use %NOTFOUND or %FOUND to determine the exits condition for the loop, rather than NO_DATA_FOUND EXCEPTION.

General Ledger Tables in Oracle Apps R12

GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_CODE_COMBINATIONS
GL_BALANCES

GL_PERIODS

GL_JESOURCES

GL_SET_OF_BOOKS
GL_IMPORT_REFERENCES
GL_BUDGETS
GL_BUDGET_ENTITIES
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_VERSIONS
GL_INTERFACE

Account Receivable Tables in Oracle Apps R12

RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL

RA_CUSTOMER_TRX_TYPE_ALL

RA_CUSTOMER_TRX_LINE_GL_DIST_ALL

RA_CUSTOMERS

RA_TERMS

RA_CUSTOMER_TRX_LINE_SALESREPS
AR_PAYMENT_SCHEDULES

AR_CUSTOMER_PROFILES
AR_CUSTOMER_PROFILE_CLASSES
AR_CASH_RECEIPTS
AR_MISC_CASH_DISTRIBUTIONS
AR_CASH_RECEIPT_HISTORY
AR_RECEIVABLE_APPLICATIONS

HZ_PARTIES

HZ_PARTY_SITES

HZ_CUST_ACCOUNTS

HZ_CUST_ACCOUNT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_LOCATIONS

HZ_CONTACT_POINTS

HZ_CUST_CONTACT_POINTS

Inventory Tables in Oracle Apps R12

MTL_SYSTEM_ITEMS_B

MTL_SYSTEM_ITEMS_INTERFACE

MTL_INTERFACE_ERRORS

MTL_ITEM_REVISIONS

MTL_ITEM_REVISIONS_INTERFACE

MTL_CATEGORY_SETS_B

MTL_ITEM_CATEGORIES

MTL_SECONDARY_LOCATORS

MTL_RELATED_ITEMS

MTL_ONHAND_QUANTITIES

CST_ITEM_COST

MTL_PARAMETERS
MTL_ITEM_ATTRIBUTES
MTL_ITEM_TEMPLATES
MTL_ITEM_TEMPL_ATTRIBUTES
MTL_UNITS_OF_MEASURE
MTL_SERIAL_NUMBERS
MTL_LOT_NUMBERS
MTL_ITEM_CATALOG_GROUPS
MTL_MATERIAL_TRANSATIONS
MTL_MATERIAL_TRANSATIONS_TEMP
MTL_DEMAND_INTERTFACE

Difference between DELETE, TRUNCATE and DROP

DELETE Statement: 

• DELETE is a DML command.
• Can rollback in DELETE.
• Triggers get fired.
• Can use conditions (WHERE clause) in DELETE.
• DELETE does not reset the High Water Mark for the table

TRUNCATE statement: 

• TRUNCATE is a DDL command.
• TRUNCATE is much faster than DELETE.

Reason: When you type DELETE all the data get copied into the Rollback Tablespace first. Then delete operation gets performed. That way when you type ROLLBACK after deleting a table, you can get back the data (The system get it for you from the Rollback Tablespace). All this process takes time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster. Once you truncate you can’t get back the data.

• Cannot roll back in TRUNCATE. TRUNCATE removes the record permanently.
• The trigger doesn't get fired
• Cannot use conditions (WHERE clause) in TRUNCATE.
• TRUNCATE command resets the High Water Mark for the table
• This command is used to delete all the rows from the table and free the space containing the table.
• When a table is truncated all the references to the table will be valid.

DROP Statement: 

• DROP is a DDL command.
• No DML triggers will be fired.

The DROP command is used to remove an object from the data dictionary. If you drop a table, all the rows in the table are deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using the DROP command. When a table is dropped all the references to the table will not be valid.

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, and grant or access privileges on the table will also be dropped, if want uses the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. 

We cannot recover the table before Oracle 10g. But Oracle 10g provides the command to recover it by using the command (FLASHBACK)

What is cursor and types of cursor?

The Oracle engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL’s operations and is called a cursor.

Implicit Cursor: Implicit cursor is declared by PL/SQL implicitly for all DML statements & for single rows.

Explicit Cursor: Explicit cursors are declared explicitly by the user, the explicit cursor is for queries only and allows multiple rows to be processed from the query Defined in the declare section of the plsql block.

You have made it like this:

Cursor C1 is select ename from emp;

Ref Cursor: Ref Cursor is the object name of the cursor type. It’s mainly used for dynamic purposes.

TYPE ref_type_name IS REF CURSOR [RETURN return_type]
TYPE empcurtype IS REF CURSOR [RETURN emp%type]

Difference Between Value sets and Lookups in Oracle Apps R12

• Value sets can be attached to parameters of a concurrent program, whereas Lookups can't.
• Certain types of Lookups are maintainable by the users too, for example, HR Users will maintain "Ethnic Minority" lookups. Value Sets are almost never maintained by end-users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators.
• Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make a Value Setlist of values dynamic. On the contrary, Lookup Codes are Static lists of values.

How to differentiate customer and suppliers data in hz_parties table?

SELECT   *
    FROM   (SELECT   hp.party_number,
                     hp.party_name,
                     hp.status,
                     DECODE (NVL (hpu.party_usage_code, hp.party_type),
                             'ORGANIZATION', 'CUSTOMER',
                             NVL (hpu.party_usage_code, hp.party_type))
                        party_type
              FROM   hz_party_usg_assignments hpu, hz_parties hp
             WHERE   hp.party_id = hpu.party_id(+))
ORDER BY   party_type;

Difference between a Stored Procedure and a Trigger?

1. We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined.
2. We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate the execution of another trigger defined on the same table or a different table.
3. Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
4. Stored the procedure can take input parameters, but we can't pass parameters as input to a trigger.
5. Stored procedures can return values but a trigger cannot return a value.
6. We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.
7. We can use transaction statements like begin the transaction, commit the transaction, and rollback inside a stored procedure but we can't use transaction statements inside a trigger.
8. We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can't call a trigger from these files.
9. Stored procedures are used for performing tasks. They can have parameters and return multiple result sets.
10. Triggers normally are used for auditing work. They can be used to trace the activities of table events.

Difference between Procedure and Function?

What is difference between Package and Stored Procedure?

Package:

• A package is a group of PL/SQL types, objects, stored procedures, and functions. it has two parts one is specification other is the body.
• In the specification, we mention procedures, functions & their parameters.
• In the body part, we define the whole operation performed by procedures and functions mentioned in the specification part.
• You can make your procedure private to the package by not declaring it in the package specification.
• packages cannot be called, passed parameters, or nested
• While calling a procedure from a package whole of the package is loaded into the memory. Like if a package consists of 4 procedures & we call 1 procedure then the whole 4 would be loaded to memory.

Procedure:

• The procedure is a standalone pl/sql unit in which all things related to procedure define in one go i.e parameters and whole functionality etc
• A procedure is a stored program in an oracle that is written down when a particular task has to be done.
• A procedure that resides in a package has to be called as <package_name>.<procedure_name>. On the other hand, a standalone procedure can be called by its name alone.
• We can pass IN, OUT parameters in the procedure.

Query to Find the Price list name for Item in Oracle Apps R12

SELECT qph.name

, msi.segment1

, qpl.operand

, qpl.product_precedence

FROM  qp_list_headers qph,

apps.qp_list_lines_v qpl,

inv.mtl_system_items_b msi

WHERE  qph.list_header_id = qpl.list_header_id

and qpl.product_attr_value = to_char(msi.inventory_item_id)

and msi.ORGANIZATION_ID =4

and msi.segment1 ='RED0126MCO';

Query to Find out the Requisition and PO number from Order Number in B2B Order

 SELECT    OOHA.ORDER_NUMBER,
            OOHL.LINE_NUMBER,
            PRHA.SEGMENT1 REQUISITION_NUMBER,
            PRLA.LINE_NUM REQUISITION_LINE_NUMBER,
            PHA.SEGMENT1 PO_NUMBER,
            PLA.LINE_NUM PO_LINE_NUMBER
  FROM   OE_ORDER_HEADERS_ALL OOHA,
         OE_ORDER_LINES_ALL OOHL,
         PO_REQUISITION_HEADERS_ALL PRHA,
         PO_REQUISITION_LINES_ALL PRLA,
         PO_REQ_DISTRIBUTIONS_ALL PRDA,
         PO_DISTRIBUTIONS_ALL PDA,
         PO_HEADERS_ALL PHA,
         PO_LINES_ALL PLA
 WHERE   OOHA.HEADER_ID = OOHL.HEADER_ID
         AND PRHA.INTERFACE_SOURCE_LINE_ID = OOHL.LINE_ID
         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 PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
         AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID;

Query to Find Order Management Join Query in Oracle Apps R12

SELECT   ooh.order_number,
         msib.segment1 item_number,
         ool.line_id,
         mr.reservation_quantity,
         mr.reservation_id
FROM     oe_order_headers_all ooh,
         oe_order_lines_all ool,
         mtl_reservations mr,
         mtl_system_items_b msib
WHERE    ooh.header_id = ool.header_id
         AND mr.demand_source_line_id = ool.line_id
         AND ool.ship_from_org_id = msib.organization_id
         AND mr.inventory_item_id = msib.inventory_item_id
         AND ORDER_NUMBER = 2076061;

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 ;

BLANKET SALES AGREEMENT IN ORACLE APPS API

Create or Replace procedure mutl_blanket_update
is
 
  -- Input variables

   l_hdr_rec             OE_Blanket_PUB.header_rec_type;
   l_hdr_val_rec         OE_Blanket_PUB.Header_Val_Rec_Type;
   l_line_tbl               OE_Blanket_PUB.line_tbl_Type;
   l_line_val_tbl           OE_Blanket_PUB.line_Val_tbl_Type;
   l_line_rec               OE_Blanket_PUB.line_rec_Type;
   l_line_val_rec           OE_Blanket_PUB.line_val_rec_Type;
   l_control_rec            OE_Blanket_PUB.Control_rec_type;
 
 -- Output Variables

   x_header_rec             OE_Blanket_PUB.header_rec_type;
   x_line_tbl                OE_Blanket_PUB.line_tbl_Type;
   x_return_status          VARCHAR2(1000);
   x_msg_count              NUMBER;
   x_msg_data               VARCHAR2(4000);
 
   l_msg_index_out      NUMBER;
   l_error_message      VARCHAR2 (100);

begin
   
    l_line_rec.order_number:='351015';
    l_line_rec.header_id :=5903558;
    l_line_rec.attribute9:='TEST';
 
    OE_Blanket_PUB.Process_Blanket(

p_org_id             => 1

,p_operating_unit     => NULL

,p_api_version_number => 1.0

,x_return_status      => x_return_status

,x_msg_count          => x_msg_count

,x_msg_data           => x_msg_data

,p_header_rec         => l_hdr_rec

,p_header_val_rec     => l_hdr_val_rec

,p_line_tbl           => l_line_tbl

,p_line_val_tbl       => l_line_val_tbl

,p_control_rec        => l_control_rec

,x_header_rec         => x_header_rec

,x_line_tbl           => x_line_tbl
);

IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('success');
      DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
      DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
   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;

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;

     

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 );

Query to Get the Account Flex field Structure

SELECT DISTINCT sob.name Ledger_Name ,
  sob.set_of_books_id,
  sob.chart_of_accounts_id coa_id ,
  fifst.id_flex_structure_name struct_name ,
  ifs.segment_name ,
  ifs.application_column_name column_name ,
  sav1.attribute_value BALANCING ,
  sav2.attribute_value COST_CENTER ,
  sav3.attribute_value NATURAL_ACCOUNT ,
  sav4.attribute_value INTERCOMPANY ,
  sav5.attribute_value SECONDARY_TRACKING ,
  sav6.attribute_value GLOBAL ,
  ffvs.flex_value_set_name ,
  ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs ,
  fnd_id_flex_structures_tl fifst ,
  fnd_segment_attribute_values sav1 ,
  fnd_segment_attribute_values sav2 ,
  fnd_segment_attribute_values sav3 ,
  fnd_segment_attribute_values sav4 ,
  fnd_segment_attribute_values sav5 ,
  fnd_segment_attribute_values sav6 ,
  fnd_id_flex_segments ifs ,
  fnd_flex_value_sets ffvs ,
  gl_sets_of_books sob
WHERE 1                          =1
AND fifs.id_flex_code            = 'GL#'
AND fifs.application_id          = fifst.application_id
AND fifs.id_flex_code            = fifst.id_flex_code
AND fifs.id_flex_num             = fifst.id_flex_num
AND fifs.application_id          = ifs.application_id
AND fifs.id_flex_code            = ifs.id_flex_code
AND fifs.id_flex_num             = ifs.id_flex_num
AND sav1.application_id          = ifs.application_id
AND sav1.id_flex_code            = ifs.id_flex_code
AND sav1.id_flex_num             = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id          = ifs.application_id
AND sav2.id_flex_code            = ifs.id_flex_code
AND sav2.id_flex_num             = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id          = ifs.application_id
AND sav3.id_flex_code            = ifs.id_flex_code
AND sav3.id_flex_num             = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id          = ifs.application_id
AND sav4.id_flex_code            = ifs.id_flex_code
AND sav4.id_flex_num             = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id          = ifs.application_id
AND sav5.id_flex_code            = ifs.id_flex_code
AND sav5.id_flex_num             = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id          = ifs.application_id
AND sav6.id_flex_code            = ifs.id_flex_code
AND sav6.id_flex_num             = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type  = 'GL_BALANCING'
AND sav2.segment_attribute_type  = 'FA_COST_CTR'
AND sav3.segment_attribute_type  = 'GL_ACCOUNT'
AND sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
AND sav5.segment_attribute_type  = 'GL_SECONDARY_TRACKING'
AND sav6.segment_attribute_type  = 'GL_GLOBAL'
AND ifs.id_flex_num              = sob.chart_of_accounts_id
AND ifs.flex_value_set_id        = ffvs.flex_value_set_id;

Query to get details of all Concurrent programs used by a Request Set

SELECT rs.user_request_set_name ,
  rs.request_set_name,
  rs.description ,
  rss.display_sequence Seq,
  cp.user_concurrent_program_name ,
  ap.application_name ,
  e.executable_name ,
  e.execution_file_name ,
  lv. meaning
FROM apps.fnd_request_sets_vl rs,
  apps.fnd_req_set_stages_form_v rss,
  applsys.fnd_request_set_programs rsp,
  apps.fnd_concurrent_programs_vl cp,
  apps.fnd_executables e,
  apps.fnd_lookup_values lv,
  apps.fnd_application_vl ap
WHERE rs.application_id          = rss.set_application_id
AND rs.request_set_id            = rss.request_set_id
AND rss.set_application_id       = rsp.set_application_id
AND rss.request_set_id           = rsp.request_set_id
AND rss.request_set_stage_id     = rsp.request_set_stage_id
AND rsp.program_application_id   = cp.application_id
AND rsp.concurrent_program_id    = cp.concurrent_program_id
AND cp.executable_id             = e.executable_id
AND cp.executable_application_id = e.application_id
AND e.application_id             =ap.application_id
AND lv.lookup_code = e.execution_method_code
AND lv.language         ='US'
AND ap.application_name = 'Order Management';

Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name,
  ffv.application_table_name,
  ffv.title,
  ap.application_name ,
  ffc.descriptive_flex_context_code ,
  ffc.descriptive_flex_context_name,
  ffc.description ,
  ffc.enabled_flag ,
  att.column_seq_num ,
  att.form_left_prompt ,
  att.application_column_name ,
  fvs.flex_value_set_name ,
  att.display_flag ,
  att.enabled_flag ,
  att.required_flag
FROM fnd_descriptive_flexs_vl ffv,
  fnd_descr_flex_contexts_vl ffc,
  fnd_descr_flex_col_usage_vl att,
  fnd_flex_value_sets fvs,
  fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id                =ffv.application_id
AND ffv.descriptive_flexfield_name   = ffc.descriptive_flexfield_name
AND ffv.application_id               = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id            =att.flex_value_set_id
AND ap.application_name              = 'Order Management'
AND ffv.application_table_name       ='OE_ORDER_HEADERS_ALL';

Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name,
  fcp.concurrent_program_name,
  fcp.concurrent_program_id,
  fav.application_short_name,
  fav.application_name,
  fav.application_id,
  fdfcuv.end_user_column_name,
  fdfcuv.form_left_prompt prompt ,
  fdfcuv.enabled_flag,
  fdfcuv.required_flag,
  fdfcuv.display_flag
FROM fnd_concurrent_programs fcp ,
  fnd_concurrent_programs_tl fcpl ,
  fnd_descr_flex_col_usage_vl fdfcuv ,
  fnd_application_vl fav
WHERE fcp.concurrent_program_id       = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id                =fcp.application_id
AND fcpl.language                     = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'|| fcp.concurrent_program_name;

Query To Find Concurrent Program Attached To a Responsibility

SELECT fcpt.user_concurrent_program_name,
  frt.responsibility_name,
  frg.request_group_name
FROM fnd_Responsibility fr,
  fnd_responsibility_tl frt,
  fnd_request_groups frg,
  fnd_request_group_units frgu,
  fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id           = fr.responsibility_id
AND frg.request_group_id              = fr.request_group_id
AND frgu.request_group_id             = frg.request_group_id
AND fcpt.concurrent_program_id        = frgu.request_unit_id
AND fcpt.user_concurrent_program_name = '&conc_program_name';

Query to list all the responsibilities attached to a user

SELECT fu.user_name,
  fr.responsibility_name,
  furg.start_date,
  furg.end_date
FROM fnd_user_resp_groups_direct furg,
  fnd_user fu,
  fnd_responsibility_tl fr
WHERE upper(fu.user_name)  =upper(:user_name)
AND furg.user_id           = fu.user_id
AND furg.responsibility_id = fr.responsibility_id
AND fr.language            = userenv('lang');

Query to find request group and application name for a concurrent program

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
  DECODE(rgu.request_unit_type, 'P', 'Program', 'S', 'Set', rgu.request_unit_type) "Unit Type",
  cp.concurrent_program_name "Concurrent Program Short Name",
  rg.application_id "Application ID",
  rg.request_group_name "Request Group Name",
  fat.application_name "Application Name",
  fa.application_short_name "Application Short Name",
  fa. basepath "Basepath"
FROM fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  fnd_concurrent_programs_tl cpt,
  fnd_application fa,
  fnd_application_tl fat
WHERE rg.request_group_id    = rgu.request_group_id
AND rgu.request_unit_id      = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id        = fat.application_id
AND fa.application_id        = fat.application_id
AND cpt.language             = USERENV('LANG')
AND fat.language             = USERENV('LANG')
AND cpt.user_concurrent_program_name LIKE 'Reservations Interface Manager';