Decode
1.It’s a function
2.can compare only discrete vaules
3.Pl/Sql standards
4.cannot process null

Case
1.It’s an Expression
2.can hande range values
3.ANSI Standards
4.Processing time is faster when compared to Decode
5.can process null

Decode
The default operator for comparison is ‘=’,
for e.g select deptno,decode
(deptno,10,’dept10′,20,dept20,default)

so if the deptno=10 then the value is replaced by dept10
and so on,

whereas in Case:

we can use any operator other than ‘=’,
for e.g
select deptno,
case when deptno=10 and sal>1000 then ‘dept10’
else
‘dept20’

Decode: using for reporting purpose. and it is implemented
by if stmt.
Ex: select decode(job,’CLERK’,’executive’,job) from emp;

Case : implemented by if & elsif condition. it is using
pl/sql equality conditionality.
Ex : select case job=’CLERK’ then ‘executive’ else ‘no’ end
a from emp;

it is also used for multipul colms & multi conditions.

in above stmt a is reffered alias name.

There is one more Important difference between CASE and DECODE

DECODE can be used Only inside SQL statement….
But CASE can be used any where even as a paramtre of a function/procedure

Eg:-

Code: [Select all] [Show/ hide]SQL> create or replace procedure pro_01(n number) is
2 begin
3 dbms_output.put_line(‘ The number = ‘||n);
4 End;
5 /

Procedure created.

SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
2 :a := ‘ONE’;
3 End;
4 /

PL/SQL procedure successfully completed.

SQL> Begin
2 pro_01(Decode(:a,’ONE’,1,0));
3 End;
4 /
pro_01(Decode(:a,’ONE’,1,0));
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column ‘DECODE’ may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

SQL> Begin
2 pro_01(case :a when ‘ONE’ then 1 else 0 end);
3 End;
4 /
The number = 1

PL/SQL procedure successfully completed.

  1. What is Concurrent Programming?
Concurrent Processing in Oracle Apps simultaneously executes programs running in the Background with online operations to fully utilize your hardware capacity.
Use Concurrent Programming for
Long Running – Data intensive tasks such as Posting a Journal or generating a report.
  1. What is the Role of Concurrent Managers?
A Concurrent Manager is a component of Concurrent processing that monitors and runs tasks without tying up your computer.
  1. What is AOL?
Oracle Applications are constructed and maintained using the Application Object Library (AOL).
The Three main areas of AOL are
    • Applications Security
    • Operating Profile
    • Concurrent Processing
    •  
  1. Define Interfaces. What are the different types of Interfaces?
Interfaces are used to integrate external systems and data conversion in Oracle Applications.
These can be used to either transfer data from Oracle Applications to a Flat File or Data from Legacy System to Oracle Applications.
There are two types of Interfaces. Inbound and Outbound Interfaces.
Inbound Interfaces are used to transfer data from external system to Oracle Applications.
Outbound Interfaces are used to transfer data from Oracle Applications to External System.
Open Interface is the interface whose interface logic is provided by Oracle.
Custom Interface is an Interface whose logic is developed by implementation team.
  1. What are alerts? What are the different types of alerts? Explain.
Alerts
    • Immediately inform you of the database activity as it happens.
    • Can Periodically trigger off events as and when required
    • Can take predefined actions
    • Allow you to define distribution list
    • Can keep history of the exceptions and actions taken against them.
There are two types of alerts.
    • Event Alerts
    • Periodic Alerts
    •  
  1.  What is the Flex field? What are the types of Flex field?
    • Flex Field is “Flexible Field”
    • A Flexfield is made up of Segments.
    • Each segment has a name that can be assigned and has set of valid values.
    • There are two types of Flex field Key Flex Field and Descriptive Flex Fields
    •  
  2. What are the tables related to flex field?
    • FND_FLEX_VALUES
    • FND_FLEX_VALUE_SETS
    • FND_FLEX_VALUES_TL     
  3. What is AD_DD package?
AD_DD Package is used to register the Table, Columns, and Primary Key in Oracle Applications.
            PROCEDURE REGISTER_TABLE
          Arguments: 
    • P_APPL_SHORT_NAME     
    • P_TAB_NAME            
    • P_TAB_TYPE            
    • P_NEXT_EXTENT         
    • P_PCT_FREE            
    • P_PCT_USED            
         PROCEDURE REGISTER_COLUMN
   Arguments
·         P_APPL_SHORT_NAME     
·         P_TAB_NAME            
·         P_COL_NAME            
·         P_COL_SEQ             
·         P_COL_TYPE            
·         P_COL_WIDTH           
·         P_NULLABLE            
·         P_TRANSLATE           
·         P_PRECISION           
·         P_SCALE                
  1. What are the types of Value sets?
·         None
·         Table
·         Special
·         Pair
·         Dependent
·         Independent
·         Translatable Dependent
·         Translatable Independent
  1. What are the Special and Pair Flex Field?
Special – Value Sets uses FlexField itself
Pair – Two Flex Fields together specifies a range of valid values.
  1. What are the Translatable Dependent and Independent Flex Fields?
   Translatable Independent – Input must exist on previously defined set List of        
   Values. Translated value can be used.
   Translatable Dependent means Input is checked against a subset of values  
   Based on a prior value. Translated value can be used.
  1. What is FND_REQUEST.SUBMIT_REQUEST?
Submits a Concurrent Request for Processing by a Concurrent Manager.
Arguments – Application,program,description,start_time,sub_request,arg1..
  1. What is Client Info?
By calling this Program in SQL*PLUS or reports with correct parameters user can achieve concurrent program environment for testing.
FND_CLIENT_INFO.setup_client_info(application_id Number,
                                                                     Responsibility_id Number,
                                                                    User_id  Number,
                                                                     Security_Group_id Number);

  1. Give the Directory structure in apps?
$APPL_TOP  – Product Directory- Version-

  1. What are the steps in Registering Concurrent Program?
    • Go to Programs and Define Executables.
    • Go to Programs and Define Concurrent Program
    • Go to Responsibility and attach the Request group you want.
  2. What are the different types of executable available in Concurrent Programming?
    • Host
    • Oracle Reports
    • PL/SQL Stored Procedures
    • SQL*LOADER
    • SQL*PLUS
    • Spawned
    • JSP
  3. What are Request Sets?
Request set is a collection of Reports/Programs that you group together and can be submitted to run is a single interaction.

  1. What is Standard Request Submission (SRS Feature)?
SRS provides you with a set of windows for running reports and Programs and a set of windows for creating groups of reports and programs to run together.
Features
    • Specify whether reports or programs in a request set run sequentially or simultaneously
    • Specify whether to continue with a request set if a report or program in a sequential set fails
    • View a log file
    • Specify alternative requests based on completion status of previously run requests in a request set.
  1. What are the different API’s for Concurrent Programming?
    • FND_CONCURRENT
    • FND_FILE
    • FND_PROGRAM
    • FND_SET
    • FND_REQUEST
    • FND_REQUEST_INFO
    • FND_SUBMIT
  2. What are the Different PLL’s Used in Forms?
    • CUSTOM.pll
    • FNDSQF.pll
    • APPCORE.pll
    • APPCORE2.pll
    • appdaypk.pll
    • APPSTAND.pll
  3. What are the Steps in Forms Customization?
    • Define the Form Name in FORM Screen
    • Define Form Functions
    • Attach to Menu/Attach to Request group
  4. What are the triggers that can be modified during Forms Customization?
    • Pre-Forms
    • When-New-Form-Instance
    • Query_Find
    • Post-Form
    • Key-Clrfrm
    • Accept
  5. What are the triggers that cannot be modified during Forms Customization?
    • STANDARD_ATTACHMENT
    • ZOOM
    • FOLDER_ACTION
    • KEY-HELP
    • KEY-EXIT
    • KEY-COMMIT
    • WHEN-WINDOW_CLOSED
    • CLOSE_WINDOW
  6. What are the FlexField Qualifiers?
A Flex field qualifier identifies a particular segment of a key flex field.
  1. What are the Segment Qualifiers?
A Segment Qualifier identifies a particular type of value in a single segment of a key flex field.
  1. What is a Dynamic Insertion?
Dynamic Insertion is the insertion of new valid combination into a Key Flexfields Combinations Table from a form other than the combinations form.
All Validation rules still will apply during insertion.
  1. What are the different Level of Profiles?
User Profiles are used
    • To set options that affect your applications behavior o your preference.
    • A Collection of changeable options that affect the way your applications run
    • Modify Product Specific variables
    • Gives Control over certain Oracle Applications features.
            Profile Levels
·         Site Level
·         Application Level
·         Responsibility Level
·         User Level
Site Level is the lowest level.
  1. Explain Multi-Organization Structure?

Set of book

A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calendar.
Business Group
This is highest level in the Organization Structure. The Business group secures HR Information. Multiple set of books can share same business group.
Legal Entity
A legal company for which you prepare fiscal or tax reports.
Balancing Entity
Represents an accounting entity for which you prepare financial statements.
This is the segment in Accounting Flexfield.
Operating Unit
An Organization that Uses Oracle Cash Management, Order Management and shipping Execution, Oracle Payables, Oracle Purchasing, Oracle receivables.
It may be a Sales office division or a department. An Operating Unit is associated with legal entity.
Inventory Organization
An Organization for which you track Inventory transactions and balances and/or an Organization that manufactures or distributes products.
HR Organization
HR Organization represents the basic work structure of any enterprise.            They usually represent Functional Management or reporting groups that exists within a business group.
  1. How can u see Multi-Organization is enabled or not from SQL Prompt?
SELECT MULTI_ORG_FLAG FROM fnd_product_groups;
  1. What are the two mandatory parameters required for PL/SQL stored Procedure Concurrent Program?
Errbuf and retcode two OUT Parameters are required while defining PL/SQL stored Procedure Concurrent Program.
Errbuf Returns any error messageand retcode returns completion status.
Retcode returns 0 for success, 1 for warnings and 2 for error.
Oracle Alert FAQ’s
  1. What are the event alerts?
Triggered off when a particular event occurs in the Database.
Event can be Insert or Update.
      What are Alert Action Level?
There are two levels of actions that can be performed
            Detail Action and Summary Action.
  1. What is a Summary Threshold?
Oracle Alerts automatically determine whether to perform a detail action or Summary action based on the number of exceptions in the database.
  1. What are the different actions that can be performed in Alerts?
    • Message Actions
    • Concurrent Program Actions
    • Operating Script Actions
    • SQL Statement Script Actions
  2. What are the steps involved in Creating Periodic alerts?
·         Write a select statement
·         Verify and Run the SQL
·         Specify Alert Inputs
·         Define Alert Outputs
·         Create Periodic alert actions
·         Define Periodic alert message actions
·         Create a periodic alert action set
·         Check your periodic alert.
  1. What is the Distribution List in Alerts?
A Pre-defined set of electronic mail ids and printer Instructions that you can use on message actions instead of re-entering all the recipient names each time.
  1. What are the Advance features available in alerts?
·         Distribution List
·         Periodic sets
·         Summary Thresholds
·         Action Set Checks
  1. What are the four implicit Inputs in Alerts?
Mailid, rowid, org_id, date_last_checked
Oracle Applications Standards for New Report Developments…
For developing a Report in Oracle Applications we should follow three standards.
1. Creation of Bind Variable – P_CONC_REQUEST_ID: We must create a Bind Variable called “P_CONC_REQUEST_ID” (We can’t change this name. It is standard name.). If we run Conc. Prgm. from SRS window, it will give a Request ID. It will get store in ‘P_CONC_REQUEST_ID” automatically. This Bind Variable is useful, when we call another Conc. Prgm. with in a Conc. Prgm.

2. FND SRWINIT in Before Report Trigger:
We call the USER_EXIT (‘FND SRWINIT’) form Before Report Trigger. Syntax is
SRW.USER_EXXIT(‘FND SRWINIT’):
This USER_EXIT is initializing the user profiles in the report trigger i.e., before getting the date from the Database.
Note:
While executing the Conc. Prgm. the system allocate memory for the program which contains all details of user. In above syntax,
SRW.USER_EXIT refers to D2K and purpose of this is, when we want to transfer the control from execution of report to other 3rd generation language and again transfer the control to report execution.
FND SRWINIT refers to Oracle Applications. Purpose of this is to get the “User Profile”.
3. FND SRWEXIT in After Report Trigger:
We call the USER_EXIT (‘FND SRWEXIT’) form After Report Trigger. Syntax is
SRW.USER_EXXIT (‘FND SRWEXIT’):
This USER_EXIT is frees the memory which is occupied by user profiles.

Q. AME is possible in PO approval ??
NO. AME for Req approval
Q. Can we create Position heiracchy for Requisition Approval
Yes
Q. vacation rule has to be created by the same user…if being as a first person in the approver  goup the user not able to create vacation rule and goes on leave…then any other way, by  which we can handle that out of scope
Q. Can I get the recording version of yesterda’s session?
No.
Q. Where did we do setups for taxes
Ebiz Tax
Q. Is AME diff in 11i and R12
Conceptually, AME is same or 11i and R12 but the look and feel and navigation is  completely different.
Q. Any profile for confidentiality of Quotation?
No. Anybody with access to quote and quote analysis screen has access to quotations
Q. when we delete approved record, if it is by other user, will he be notified for the same
No.
Q. How to protct cofidentialiy and modification of quot from others
– to prevent access to quote screen
– make the quote screen read only
Q. is there any work flow proces behind this?
no
Q. Can we have PO without RFQ or quotes for that matter.
Yes. We can have PO with RFQ and quotes. But its not a good practice.
Purchase Manager can be questioned why he gave order to some supplier. He will always have 2-3 quotes with him.
Q. but for blanket type of PO do you think quotes are required?
2 ways to determine price for an item
            – Quotations  – document provided by vendor to supply at a price
            – Blanket PO – agreement between vendor and us
Q. If we dont want other buyer can see qout of other buyer even they have access to RFQ screen ??
only related buyer only can see his Quot not other one which is not related to him ??
Personalization. Without personalization, its not possible.
Q. Can we copy Catalog RFQ to Standard quotation?
Yes. Copy Catalog RFQ to Catalaog quote or Standard quotation. Copy Bid RFQ to Bid quotation.
Q. Can you show us Creating PO from Requisition having RFQ as Source Doc.
RFQ cannot be source doc. We can have Quote as Source and we will be creating PO from Req using Quote as Source.
Q. Yes but not for release.
Question not clear
Q. What about negotion process in this ??
Negotiation is possible in Oracle Sourcing. Out of Scope.
Q. Are u going to take Planned Purchase order
Yes. That will be covered later.
Q. Will we cover Schedule release VMIand Consigned.?
Out of scope. Inventory related. Kindly attend Inventory training.
Q. Is there  any analytics available in this release to freeze the minimum quote automatically?
No. Quote analysis and approval is a manual process. No automation is provided.
Q. Can you cover the Advance Pricing intergration in Purchasing?
This will be covered conceptually. There wont be any practicals on the same.
Q. What about Bill of distribution??
Sourcing Rules  – ASL is required. Will cover Sourcing rules later.
Q. Sourcing means for RFQ ??
Selecting the Source of Supply of material.
Sourcing – Sourcing Rules – automatically create PO from requisitions
Q. While using ASL for Commodity theall items listed under that commodity will get imapcted . believe it’s true can u confirm .
Yes. I will show you guys a chart
Q. How the Supplier status behave differently in Global & Local ASL?
Local ASL will override Global
Local (M1) – debarred Global – Approved
PO for M1 – will not allow
PO for M2 – should allow
But local is specific to that org not for others. so for M2 it should allowed.
Q. what is category level supplier?
The supplier will be ASL for all items which fall under that category.
Q. then where do we define that?
That is defined in ASL
Q. Where do we define the range of items under that category?
Linked while defining Items
Q. AND is approved for Item not for category so Item shoud be presidance. and it should allowed. Item shoud have more presidance then category.
NO. Category is having higher precedence. So, if a Supplier is debarred for a category then he is debarred for all items under that category.
Q. Can we create Req to Release Item if is not matching on Requisition and BPA. but ASL is set.
Question is not clear
Q. what if allocation for ASL is <100 %
For manual PR, 100% allocation does not matter. 100% allocation is required only for planned orders. For manual PR the PO will be raised against the supplier with highest allocation.
Q. What does Sourcing Assignment signify?
Sourcing Assignment will determine which supplier to source an item for a given ORG
Q. If we have one org then can we do transaction in Master Org
Yes. No restriction. Best practice that we should not do any transaction in Master Inv Org.
Q. What isTCA??
Trading Community Architecture. Covered in AR. This is party, customer relationship and stored in HZ tables.
Q. How intercompany sourcing happens and how PO will be managed in two companies?
Internal Requisition/Internal Sales Orders
Q. What is the need to add the price into price list…when we have given price for the item in Inventory setup?
Price that we mentioned is the Purchase list price. But for OM, it picks the price from Price list.
Q. While giving the value in Price list…do we add our all the cost and profit in that
For internal items, the transfer price is calculated based on the cost + the transfer options.
Q. What does create internal order conc request do?
Create internal order request will transfer the IR info to OM interface tables.
Q. Why is the cost zero for Internal Orders?
We did not enter any cost for the item, so the cost was zero.
Q. could you please show this cost thing by raising external sales order?
Out of Scope. OM class is required.
Q. Cost is zero for internal sales order, however you told that it will be 10% addition of the cost of that item then why it is not 10 % addition in the cost?
Q. Why is the cost of the Item zero?
The cost of the Item was zero because we did not enter the cost of the item.
Q. Where do we enter the cost?
We enter the cost while defining the Item.
Q. How does the price default while creating IR?
The price of the item while creating IR, is the cost of the Item.
Q. Can you clarify where we set up zero cost for an Item for IR?
We did not specify the zero for IR. If we do not enter the cost, it will be considered as zero.
Q. Can we change PO Currency?
Yes. We can change PO currency. It defaults from supplier setup but we can change it.
Q. Can you explain what is confirming order ?
Sometimes, the Purchase manager requests for certain items over phone. Then he sends the PO just to confirm the verbal order. If the vendor thinks that this is a duplicate order and delivers double, it could cause problem, so he sends a PO with a message that it is confirming order.
What is Difference Between Blanket release and Planned Release?
Blanket release is against blanket purchase agreement and planned release is against planned orders.
Under what Circumstance we’ll use Blanket and Planned Agreement
In blanket, the price of the agreement is fixed but the quantity is not fixed. We get price breaks in Blanket for volume discounts.
In planned, the shipment is tentative. It does not have any price breaks so no volume discounts.
So, blanket is for high volume transactions.
Q. For planned orders, Schedule is available but blanket is for as and when required?
The schedule in planned Orders is just tentative. The actual schedule is determined later.
Blanket can have an effective date range. Planned does not have any effective date range.
Q. Can you cover Advance Pricing?
Not in scope.
Q. What is the way for adding items or modify BPA from supplier point of view..are they need have oracle apps installed with Oracle iSupplier portal?
They will have access to Oracle iSupplier portal which is a web based application. No installation is required.
These are the tips that needs to be followed to performance tune your SQL scripts;

·    Never do a calculation on an indexed column (e.g., WHERE salary*5 > :myvalue)

·    Whenever possible, use the UNION statement instead of OR conditions

·    Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause

·    Always specify numeric values in numeric form and character values in character form (e.g., WHERE emp_number = 565, WHERE emp_name = ?Jones?)

·    Avoid specifying NULL in an indexed column

·    Avoid the LIKE parameter if = will suffice. Using any Oracle function will invalidate the index, causing a full-table scan

·    Never mix data types in Oracle queries, as it will invalidate the index. If the column is numeric, remember not to use quotes (e.g., salary = 50000). For char index columns, always use single quotes (e.g., name = ?BURLESON?)

·    Remember that Oracle’s rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. Always make sure that the last table specified in the FROM clause is the table that will return the smallest number of rows. In other words, specify multiple tables with the largest result set table specified first in the FROM clause

·    Avoid using sub-queries when a JOIN will do the job

·    Use the Oracle decode function to minimize the number of times a table has to be selected

·    To turn off an index you do not want to use (only with a cost-based optimizer), concatenate a null string to the index column name (e.g., name||’) or add zero to a numeric column name (e.g., salary+0). With the rule-based optimizer, this allows you to manually choose the most selective index to service your query

·    If your query will return more than 20 percent of the rows in the table, use a full-table scan rather than an index scan

·    Always use table aliases when referencing columns
·    Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
·    If a view joins 3 extra tables to retrieve data that you do not need, don’t use the view!
·    When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
·    Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you loose control and understanding of exactly how much task loading your query will generate for the system.
·    Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.
·    WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
·    WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
·    In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
·    Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
·    Avoid writing where project_category is not null. Nulls can prevent the optimizer from using an index.
·    Consider using IN or UNION in place of OR on indexed columns. OR’s on indexed columns causes the optimizer to perform a full table scan.
·    Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt/3 > 26000.
·    Avoid this: SUBSTR(haou.attribute1,1,LENGTH(‘:p_otc’)) = :p_otc). Consider this: WHERE  haou.attribute1 like :p_otc||’%’
·    Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don’t assume that an index was defined. Check and talk to your DBA if you don’t find any.
·    Consider replacing outer joins on indexed columns with UNION. A nested loop outer takes more time than a nested loop un-joined with another table access by index.
·    Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
·    Consider NOT EXISTS instead of NOT IN.
·    If a query is going to read most of the records in a table (more than 60%), use a full table scan.
·    Try to group multiple sub queries into one.
·    If you want to actually understand what you are doing, here are a few things that you need to start playing with:
·    Get into EXPLAIN_PLAN. There are multiple way of doing this. The less user friendly is to simply issue this in SQL*Plus: explain plan set statement_id = ‘HDD1’ for ;
·    Look at the trace from Oracle Reports. It tells you how much time it spends on each query.
·    Use the SQL Trace by issuing an alter session set sql_trace=true; then look at it with TKPROF .trc
·    Do not use functions on indexed columns in WHERE clauses Unless specifically using a function based index.

·    Beware of implicit datatype conversions occuring on indexed columns (e.g. comparing a character column with a number expression will invoke an implicit
TO_NUMBER operation on the indexed column, preventing use of the index – in this case it would be better to us an explicit TO_CHAR operation around thenumber expression being compared against

·    Make sure that you have the correct indexes on the tables:-
·    Ensure that the first column of the index is selective ( e.g. more than around 15 distinct values with a near uniform distribution
·    Avoid using insufficiently selective indexes (i.e. each index value applies to at least 15% of the rows).  In this case, a full table scan will be more efficient.
·    Aim to have no more than 4 indexes per table. ( each insert into the table has to also modify data in the indexes )

·    To use compound indexes ensure that the WHERE clause includes conditions on  the leading column(s) of the index

·    NEVER use SELECT * FROM. in application code

·    Try to avoid use of the DISTINCT clause as it always requires a sort operation.  Excessive use of DISTINCT clause may point to an underlying data model problem (e.g a missing table).