Procure to Pay Life Cycle in Oracle Apps R12

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

What is a Responsibility in Oracle Apps?

Responsibility is a collection of different modules that the user is to access.

OR

Responsibility is the collection of menus, request security groups, and data groups.

  Menus: a collection of forms is nothing but menus (Mandatory)

  Request security groups: a collection of programs. (Optional)

  Data groups: a group of modules to be made accessible by the user through (Mandatory)

  Function & Menu: Exclusion (Optional)

          

Define a new Responsibility:

           System admin-> Security: Responsibility -> define

                                        Security: user-> define

What are the common values passed by a procedure in variables ERRBUFF, RETCODE?

ERRBUFF It is used to return the error messages and sent them to the log file.

RETCODE It is used to show the status of the Procedure with 0,1,2.

There are 3 types of RETCODE they are:-

# 0: Success

# 1: Warning

# 2: Error

Application Object Library (AOL) in Oracle Apps R12

AOL mainly contains three things:

• Operating profiles (System Profiles)
• Application security (User, Function, and Responsibility Security)
• Concurrent Processing (Concurrent manager, request, program, process)

AOL is a powerful library having components of code (objects) used in one or more applications. 

AOL contains the following components:

• Users
• Responsibilities
• Request Group
• Request Sets
• Concurrent Programs
• Concurrent Managers
• Concurrent Program Executables
• Value Sets
• Flex Fields
• Form and Functions
• Menus
• Messages
• Table Registration
• Profiles
• Data Group

Responsibility

• It is nothing but a collection of three things – menu, data group, and request group which is attached to a specific user as per his/her level of access.

Request Group

• A request group is a collection of concurrent programs or request sets. It is attached to a responsibility to control the user’s access to concurrent programs/requests.

Request Set

• A request set is a collection of reports and/or programs that you group together. You can submit the reports and/or programs in a request set all at once using a single transaction.

Concurrent Program

• A concurrent program is a program that does not require continued interaction on your part to perform a specific task. In Oracle Applications, for example, the concurrent program may be a program written to create a report or to post a batch of general ledger journal entries.

Concurrent Manager

• The concurrent manager is a component of concurrent processing that monitors and runs tasks without tying up your computer.

Concurrent Process

• The concurrent process is an instance of a running concurrent program. Each time a concurrent manager receives a request and runs a concurrent program, it creates a new concurrent process. A concurrent process can run simultaneously with other concurrent processes

Concurrent Request

• The concurrent request is a request that you submit to run a concurrent program as a concurrent process.

Value Set

• A set of predefined or validated values assigned to a field (parameter) that restricts the user to enter from entering invalidated data.

Menu

• A  menu is a hierarchical arrangement of functions and menus of functions that appears in the Navigator.
• A  menu entry is a menu component that identifies a function or a menu of functions.
• Each responsibility has a menu assigned to it.

Functions

• A function is a set of Oracle Applications that is executed only if the name of the function is present in a list maintained within a responsibility.
• Because all users should not have access to every business function in a form, Oracle Applications provide the ability to identify pieces of applications logic as functions.
• Function security lets you restrict application functionality to authorized users.

Data Group

• The data group is a collection of pairings of Applications with Oracle ID.
• An Oracle ID is a username and password that allows access to application tables in an Oracle database.
• Concurrent managers use a data group to match the application that owns a report or concurrent program with a unique Oracle ID.

What are the WHO columns?

• WHO columns are used to track the changes to your data in the application tables. 
• WHO columns exist in all Oracle Applications standard tables. The following five are considered WHO columns:

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

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.

Types of Purchase Orders in Oracle Apps R12

There are 4 types of Purchase Orders:

1. Standard PO: A Standard PO is created for one–time purchase of various items.
2. Planned PO: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
3. Blanket agreement: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.
4. Contract agreement: Contract purchase agreements are created with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing.

What is Pragma EXECPTION_INIT? Explain the usage?

Pragma exception_init Allows you to handle the Oracle predefined message with your own message. This means you can instruct the compiler to associate the specific message to the oracle's predefined message at compile time. This way you improve the readability of your program and handle it according to your own way.

It should be declared in the DECLARE section.

declare

salary number;

FOUND_NOTHING exception;

Pragma exception_init(FOUND_NOTHING ,100);

begin

select sal into salary from emp where ename =' ANURAG';

dbms_output.put_line(salary);

exception

WHEN FOUND_NOTHING THEN

dbms_output.put_line(SQLERRM);

end;

How many Types of Exceptions in Oracle PL/SQL

There are 2 types of exceptions. They are

a) System Exceptions

E.g. When no_data_found, When too_many_rows

b) User-Defined Exceptions

E.g. My_exception exception

When My_exception then

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.

What is Difference Between UNION and UNION ALL

UNION will return the distinct value

UNION ALL will return the duplicate value.

Oracle Application R12 XML Publisher Base Tables

Affected Base tables for Data Definitions are
xdo_ds_definitions_b
xdo_ds_definitions_tl

Affected Base tables for Templates are
xdo_templates_b
xdo_templates_tl
xdo_lobs

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 ;