Thursday, September 29, 2011

How to set org context in Oracle apps R12 and 11i


Set org context in R12


The SQL command to set the ORG_ID prior to running a script is:


SQL> exec mo_global.init('AR');
exec mo_global.set_policy_context('S','&org_id');
Enter the org_id when prompted.


The procedure - mo_global.set_policy_context has two parameters 
p_access_mode & p_org_id


p_access_mode          Description
S In case you want your current session to work against Single ORG_ID
M In case you want your current session to work against multiple ORG_IDs


p_org_id: Only applicable if p_access_mode is passed value of "S"


If using Toad
Begin
mo_global.set_policy_context(‘S’, &org_id);
End;


Set org context in 11i:


The SQL command to set the ORG_ID prior to running a script is:
SQL> execute dbms_application_info.set_client_info(&org_id);
Enter the org_id when prompted.
If using Toad
Begin
fnd_client_info.set_org_context(&org_id);
End;

Wednesday, September 21, 2011

Create XML Publisher Report(With out RDF file)

Required Files:
Template file: HGXPUBTEST.rtf
Data Template: HGXPUBTEST.xml

Output Files:
Output in PDF: HGXPUBTEST_OUTPUT.pdf

Define Concurrent Program
Navigate to System Administrator->Concurrent->Program->Define
Provide folliowing information as shown in the picture.
Program: HG-XML Publisher Test Report
Short Name: HGXPUBTEST
Application: Payables
Executable Name: XDODTEXE
Format: XML

Points To Note:
1.Note down short name. We need to use this XML Publisher defintion to refer this CP.
2.Select executable name as "XDODTEXE" if xml data source is data template(HGXPUBTEST.xml). Method is Java Concurrent Program. If you are generating xml data using Oracle Reports(RDF), then you must define executable(System Administrator->Concurrent->Program->Executables) for this CP and provide it's name in CP define window. Method will be Oracle Reports in this case.
3.XML Publisher engine needs data input in XML format. So always select format is XML.( Final output will be PDF/RTF/HTML.. as selected by you in report submission.)
4.If you want to see final output in XML format then you need set format type as Text instead of XML.

CP definition looks like this after creation.



Add new CP to the Request Group, then add Request Group to your Responsibilty.

Create Data Definition
Navigate to XML Publisher Administrator->Data Definitions
Click Create Data Definition button.

Provide folliowing information as shown in the picture.
Code: HGXPUBTEST
Name: HG-XML Publisher Test Report(your choice)
Application: Payables(your choice)
Click Apply
In the second part of the same page upload file HGXPUBTEST.xml as the Data Template.
Click Save.

Points To Note:
1.Value given in the Code field must be same as the short name of the CP 'HG-XML Publisher Test Report'.
2.HGXPUBTEST.xml is the data template file which holds Paramters, Data Query, Triggers, Data Structure of the report.

Definition looks like this after creation.

Create Template
Navigate to XML Publisher Administrator->Templates
Click Create Template button.

Provide folliowing information as shown in the picture.
Code: HGXPUBTEST(your shoice)
Name: HG-XML Publisher Test Report(your choice)
Application: Payables(your choice)
Type: RTF
Data Definition: HG-XML Publisher Test Report
Upload template HGXPUBTEST.rtf.
Click Apply

Template looks like this after creation.

Submit Request
If you want final output in different format(RTF, HTML, etc), click Options button and change the Format type. Default is PDF.

CP Output(PDF)

Monday, September 19, 2011

Status_Code and Phase_Code meaning in FND_CONCURRENT_REQUEST table


STATUS_CODE Column:

A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
F - Scheduled
G - Warning
H - On Hold
I - Normal
M - No Manager
Q - Standby
R - Normal
S - Suspended
T - Terminating
U - Disabled
W - Paused
X - Terminated
Z - Waiting


PHASE_CODE column

C - Completed
I - Inactive
P - Pending
R - Running

Friday, September 16, 2011

Oracle Flashback Query: Recovering at the Row Level (Recover deleted rows)

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:

SELECT * FROM EMPLOYEE AS OF TIMESTAMP 
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';

Restoring John's information to the table EMPLOYEE requires the following update:
INSERT INTO employee 
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

See Also:

How to Register a Concurrent Program with a Responsibility

1.      Login into System Administrator responsibility
2.      Navigate to Security / Responsibility / Define
3.      Query up the responsibility that is being used
4.      Note the name of the Request Group
5.      Navigate to Security / Responsibility / Request
6.      Query up the Request Group
7.      Add the report under Requests
8.      Save it

Monday, September 12, 2011

Sequence of SQL statement processed -- basic knowledge to increase the performance of your script


Sequence of SQL statement processed

When a query is submitted to the database, it is executed in the following order:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
OR
DER BY clause

The following diagram will show a clear picture:



So why is it important to understand this?
When a query is executed, First all the tables and their join conditions are executed filtering out invalid references between them.

Then the WHERE clause is applied which again filters the records based on the condition given. Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result. As soon as it is completed, the columns mentioned are selected from the corresponding tables. And finally sorted using ORDER BY clause. So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

Example:
Recently I came across the following problem.

The original query was
SELECT DISTINCT so.departure_airport, so.airport_name
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170' AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
ORDER BY price ASC

Table data:
departure_airport    airport_name                       price
'MAN'                      'Manchester Airport'           344
'MAN'                      'Manchester Airport'           288
'MAN'                      'Manchester Airport'            316
'BRS'                       'Bristol'                             289
'BRS'                       'Bristol'                             345
'BRS'                       'Bristol'                             317
'BHX'                      'Birmingham Airport'             343
'BHX'                      'Birmingham Airport'             287
'BHX'                      'Birmingham Airport'             315

Here the query is executed as follows:
All the records from both the tables are taken.

Conditions mentioned in the WHERE clause is applied fetching say 9 records.
Now the distinct of departure_airport and airport_name alone is taken since price is not in SELECT clause. This gives you three rows randomly selected (mostly in the order of insertion to the table!)
And the result is sorted based on price (incorrect price column due to distinct keyword).
So the output is not ordered properly based on price!

So we need to rewrite the query as follows:
All the records from both the tables should be taken.

Conditions mentioned in the WHERE clause needs to applied on the resulting resultset.

Group the resultset based on departure_airport and airport_name to get the minimum price in each group.

Departure_airport               airport_name                                   minprice
'BHX'                                '
Birmingham Airport'                       287
'BRS'                                 '
Bristol'                                       289
'MAN'                                '
Manchester Airport'                      288

Select the columns departure_airport, airport_name and min(so.price) from the resultset. And finally apply the ORDER By clause which orders the resultset based on the column min(so.price).

So the query should be:
SELECT so.departure_airport, so.airport_name, MIN(so.price) AS minprice
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170'
AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
GROUP BY so.departure_airport,so.airport_name
ORDER BY minprice;


Where clause and from clause for better performance: In SQL

Order of tables in the FROM clause should be:
1. Larger table
2. Smaller table
Order of conditions in WHERE clause should be:
2. Less selective
1. More selective
All Boolean conditions without built-in functions or sub queries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.

Saturday, September 10, 2011

How to submit a concurrent program from pl sql

Using FND_REQUEST.SUBMIT_REQUEST function & by passing the required parameters to it we can submit a concurrent program from backend.
But before doing so, we have to set the environment of the user submitting the request.

We have to initialize the following parameters using FND_GLOBAL.APPS_INITIALIZE procedure:
· USER_ID
· RESPONSIBILITY_ID
· RESPONSIBILITY_APPLICATION_ID


Syntax ............

FND_GLOBAL.APPS_INITIALIZE:
procedure APPS_INITIALIZE(user_id in number,
resp_id in number,
resp_appl_id in number);


FND_REQUEST.SUBMIT_REQUEST:
REQ_ID := FND_REQUEST.SUBMIT_REQUEST ( application => 'Application Name', program => 'Program Name', description => NULL, start_time => NULL, sub_request => FALSE, argument1 => 1 argument2 => ....argument n );

Where, REQ_ID is the concurrent request ID upon successful completion.
And concurrent request ID returns 0 for any submission problems.

Example:
First get the USER_ID and RESPONSIBILITY_ID by which we have to submit the program:

SELECT USER_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_APPLICATION_ID,
SECURITY_GROUP_ID
FROM FND_USER_RESP_GROUPS
WHERE USER_ID = (SELECT USER_ID
FROM FND_USER
WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID = (SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_NAME = '&resp_name');


Now create this procedure
CREATE OR REPLACE PROCEDURE APPS.CALL_RACUST (p_return_code OUT NUMBER,
p_org_id NUMBER, -- This is required in R12
p_return_msg OUT VARCHAR2)
IS
v_request_id VARCHAR2(100) ;
p_create_reciprocal_flag varchar2(1) := 'N'; -- This is value of create reciprocal customer
-- Accounts parameter, defaulted to N
BEGIN


-- First set the environment of the user submitting the request by submitting
-- Fnd_global.apps_initialize().
-- The procedure requires three parameters
-- Fnd_Global.apps_initialize(userId, responsibilityId, applicationId)
-- Replace the following code with correct value as get from sql above

Fnd_Global.apps_initialize(10081, 5559, 220);

v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST('AR','RACUST','',
'',FALSE,p_create_reciprocal_flag,p_org_id,
chr(0) -- End of parameters);

p_return_msg := 'Request submitted. ID = ' || v_request_id;
p_return_code := 0; commit ;


EXCEPTION

when others then
p_return_msg := 'Request set submission failed - unknown error: ' || sqlerrm;
p_return_code := 2;
END;


Output:
DECLARE
V_RETURN_CODE NUMBER;
V_RETURN_MSG VARCHAR2(200);
V_ORG_ID NUMBER := 204;
BEGIN
CALL_RACUST(
P_RETURN_CODE => V_RETURN_CODE,
P_ORG_Id => V_ORG_ID,
P_RETURN_MSG => V_RETURN_MSG
);
DBMS_OUTPUT.PUT_LINE('V_RETURN_CODE = ' || V_RETURN_CODE);
DBMS_OUTPUT.PUT_LINE('V_RETURN_MSG = ' || V_RETURN_MSG);
END;


If Return Code is 0(zero) then it has submitted the Customer Interface program successfully and the request id will appear in Return Message as :
V_RETURN_CODE = 0
V_RETURN_MSG = Request submitted. ID = 455949

Oracle Apps DBA Interview Questions


1. Explain the difference between a hot backup and a cold backup and the benefits associated with each?


A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3. How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.         

4. Explain the difference between a data block, an extent and a segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

5. Give two examples of how you might determine the structure of the table DEPT?
Use the describe command or use the dbms_metadata.get_ddl package.

6. Where would you look for errors from the database engine?
In the alert log.

7. Compare and contrast TRUNCATE and DELETE for a table?
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

8. Give the reasoning behind using an index?
Faster access to data blocks in a table.

9. Give the two types of tables involved in producing a star schema and the type of data they hold?
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

10. What type of index should you use on a fact table?
A Bitmap index.

11. Give some examples of the types of database contraints you may find in Oracle and indicate their purpose?
A Primary or Unique Key can be used to enforce uniqueness on one or more columns.
A Referential Integrity Contraint can be used to enforce a Foreign Key relationship between two tables.
A Not Null constraint - to ensure a value is entered in a column
A Value Constraint - to check a column value against a specific set of values.

12. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each?
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any ball in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any ball in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

14. What command would you use to create a backup control file?
Alter database backup control file to trace.

15. Give the stages of instance startup to a usable state where normal users may access it?
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened

16. What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from.

17. How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql

18. How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

19. Explain an ORA-01555?
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE?
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Interview Questions in Oracle Apps Order Management (OM) Drop Ship process

Q1. What is a Drop ship PO?
A: Oracle Order Management and Oracle Purchasing integrate to provide drop shipments. Drop shipments are orders for items that your supplier ships directly to the customer either because you don’t stock or currently don’t have the items in inventory, or because it’s more cost effective for the supplier to ship the item to the customer directly. Drop shipment was introduced in R11.


Q2. How is a Drop Ship PO created?
A:
 Drop shipments are created as sales orders in Order Management. The Purchase Release concurrent program or workflow in Order Management creates rows in the Requisition Import tables in Purchasing. Then Purchasing’s Requisition Import process creates the requisitions. Drop shipments are marked with the Source Type of External in Order Management and Supplier in Purchasing.

Q3. What is the setup required for Drop ship PO?
A: ITEM ATTRIBUTES:

Navigate: Inventory -> Items - > Organization items
Purchased (PO) Enabled
Purchasable (PO) Enabled
Transactable (INV) Enabled
Stockable (INV) Optional
Reservable (INV) Optional
Inventory Item (INV) Optional
Customer Ordered (OM) Enabled
Customer Orders Enabled (OM) Enabled
Internal Ordered (OM) Disabled
Internal Orders Enabled (OM) Disabled
Shippable (OM) Optional
OE Transactable (OM) Enabled
All Drop Ship items must be defined in the organization entered in the profile option OE: Item Validation Organization and in the Receiving Organization.
All drop ship sub-inventory must have Reservable box checked. If the sub-inventory is not Reservable the sales order issue transaction will not be created in MTL_TRANSACTIONS_INTERFACE. After drop ship inventory organization is created, subinventories should be defined. To create the subinventory, go to an inventory responsibility and navigate to Setup -> Organizations -> Subinventories. Asset subinventories must have the reservable and Asset boxes checked. Expense subinventories must have the Reservable box checked and the Asset box unchecked.
Subinventory Attributes for Asset Subinventory
Reservable/Allow Reservations
Asset Subinventory
Subinventory Attributes for Expense Subinventory
Reservable
Asset-must NOT be enabled.

Q4. How can we avoid the miscounting of supply as logical organization is involved?
A: 
You must receive drop-ship items in a logical organization. If you use Oracle master Scheduling/MRP and Oracle Supply Chain Planning, to avoid miscounting supply you may not want to include logical organizations in your planning. If you choose to include logical organizations, ensure that doing so does not cause planning and forecasting complications.

Q5. If you make changes to a sales order after the Purchase Order (
PO) has been generated, will the order changes automatically be updated on the PO?
A: 
Order changes will not be automatically updated on the PO. Pulling up the Discrepancy report will allow you to view the differences between the Sales Order and PO. However, you will have to manually update the POs in the Purchasing application.

Q6. If items on a Drop Ship order are cancelled, does the system automatically generate a 
PO Change to the PO originally sent to the supplier?
A: 
No, Drop Ship functionality in this regard remains the same as in R11. There is a discrepancy report available that will report differences between the PO and the Sales Order.

Q7. Does Order Management 11i have functionality to do serial number management with Drop Shipments?
A: 
You are able to receive serial numbered Drop Ship stock. Order Management will receive the serial number noted on the 
PO.

Q8. Can Configurable Items be drop shipped?
A: 
Currently only Standard Items can be drop shipped. Functionality for Configurable Items will be added in future releases.




Q9. How do I drop ship across operating units?
 A: Release 11i does not currently support this functionality. 


Q10. How are over/under shipments handled in drop shipment?
A: If part of a drop-ship line ships, and you do not wish to fulfill the remaining quantity, cancel the line. Over shipments must also be handled manually. If the supplier ships more than the ordered quantity, you can bill your customer for the additional quantity or request that they return the item. Use the Drop Ship Order Discrepancy Report to view differences between your drop-ship sales orders and their associated purchase requisitions and orders.


Q11. Will Blanket PO's work with Drop Shipment?
A: Blanket PO's will not work with Drop shipment because the PO must be created when OM notifies PO that a drop ship order has been created. This PO is linked to the drop ship order so that when the receipt is done (partial or complete) .OM is updated to receiving interface eligible. Drop ship lines do not use the pick release, ship confirm or inv interface order cycles.


Q12. Can we cancel drop shipment after it is received?
A: Drop shipments cannot be cancelled once Oracle Purchasing obtains the receipt. A user who wants to cancel a drop ship sales order line must ensure no receipts have been created against the line and that the requisition and/or purchase order associated with the line is cancelled. Cancellation of a Partial Drop Ship receipt is allowable. But only the portion that has not been received can be cancelled. If you cancel a drop shipment line for which you have not shipped the entire quantity, the order processing splits the line. The first line contains the quantity shipped and the second line contains the non-shipped quantity in backorder. You can cancel the second line the backorder on the sales order. The PO line quantity should be changed to reflect the new quantity.


Q13. What debugging tools are available for Drop shipments?
A: 1. Diagnostic scripts can be used for troubleshooting problems with sales orders.
2. Debugging receipt transaction or the sales order issue transaction, Set the following profile options:
RCV: Processing Mode to Immediate or Batch
RCV: Debug Mode to Yes
OM: Debug Level to 5
INV: Debug Trace to Yes
INV: Debug level to 10
TP: INV Transaction processing mode to Background
-Then go to Sys Admin: Concurrent: Program: Define; query up the Receiving Transaction Processor and check the Enable Trace box.
-Save the receipt for the deliver transaction (destination type will say Inventory for the deliver transaction).
-View the Receiving Transaction Processor log file, the Inventory Transaction Worker log file, as well as, the trace for the errors.


Q14. What is the Import source and status of PO generated from Drop Shipment?
A: Import source is Order Entry.
Status of PO will always be Approved.

Oracle apps Interview questions - 4


  1. Q: How do you make your own query when you are in forms query mode? A: You can use a placeholder to achieve this. If you enter a single colon ( : ) in one of your query fields during the Enter Query mode, Oracle Forms Run Query will prompt you to enter the text of SQL Where clause.
  1. Q: What is concurrent processing? A: Concurrent processing is a process that simultaneously runs programs in the background (usually on the server rather than your workstation) while working online.
  1. Q: What is a Concurrent Manager? A: A Concurrent Manager is a component of concurrent processing that monitors and runs requests while you work online. Once the user submits a request to run the job, the information is stored in the request table. A concurrent manager gets the information from the request table and executes the specified concurrent job.
  1. Q: What is a request set? A request set is a collection of reports or programs grouped together. Once you submit a request set job, it executes all the programs in a report set sequentially or in a parallel manner as defined in the request set.
  1. Q: What are the four phases of a concurrent request? The four phases are as follows: inactive, pending, running, and completed.
  1. Q: How would you identify the results of the request in the Concurrent View Requests window? Whenever a concurrent job is submitted, Applications creates a Request ID. You can use this Request ID to view the results.
  1. Q: What are the profile options? How many levels of profile options are available? Profile options are set to determine how the applications look and feel. There are four levels of profile options available: site level, application level, responsibility level, and user level. You can have various categories of profile options, such as personal options, system options, auditing profile options, currency options, Flexfield options, online reporting options, personal output viewer options, and user profile options.
  1. Q: What is a document sequence? A document sequence assigns unique numbers to the documents (transactions) generated by Oracle Applications. For example, each invoice has its own unique invoice number and each purchasing document has its own unique purchase order (PO) number.
  1. Q: What are the steps involved in adding a custom program to Oracle Applications?
a)     Develop a concurrent program or report.
b)     Identify the corresponding executable and register it with the application.
c)     Create a concurrent program and its parameters.
d)     Add a concurrent program to a request set.

  1. Q: How do you register a printer? To add a new printer, go to Install Printer Register.
  1. Q: What is a Flexfield? How many types of Flexfields exist? A Flexfield is a field made up of segments. Each segment has an assigned name and a list of valid values. Two types of Flexfields exist: Key Flexfields and Descriptive Flexfields (DFFs).
  1. Q: What is a Key Flexfield? A Key Flexfield is a unique identifier that is made up of meaningful segments to identify GL account numbers and item numbers. Key Flexfields are usually stored in SEGMENT1...SEGMENTn database columns.
Some examples would be Item No 34H-AFR-223-112.G and GL Account No:
100-00-1000-324-11100.

For an example GL Account, segments could be identified as Organization,Cost Center, Account, Product, Product Line.

  1. Q: What are the Key Flexfields in Oracle Applications? The following table lists some of the Key Flexfields available in Oracle Applications.
Key Flexfields
Using Applications
Accounting
General Ledger
Asset Key
Fixed Assets
Location
Fixed Assets
Category
Fixed Assets
Account Aliases
Inventory
Item Catalogs
Inventory
Item Categories
Inventory
System Iitems
Inventory
Stock Locators
Inventory
Sales Orders
Inventory
Sales Tax Location
Receivables
Territory
Receivables
Job
Human Resources
Grade
Human Resources
Position
Human Resources
Soft Coded Key
Human Resources

  1. Q: What is a Descriptive Flex Field? A DFF lets you define the custom fields into Oracle Application forms without customizing the program code. DFFs in forms are represented by a "beer mug" field (a single space field enclosed by brackets) that looks like the following symbol: [ ]. They are usually stored in ATTRIBUTE1...ATTRIBUTEn database columns. DFFs can also be used to accept report parameters.
  1. Q: What types of segments can be set up for DFFs? Global or context-sensitive.
  1. Q: What is a value set? A value set is a list of values validated against segments. You can create a value set and assign it to a Flexfield segment.
  1. Q: How many validation types are there? Six validation types exist:none, dependent, independent, table, special, and pair.
  1. Q: What are the required and optional steps for setting up Flexfields? The required steps are as follows: define the value sets, define the structures, and define the values, if needed. The optional steps are as follows: define the security rules, define the cross-validation rules, and define the shorthand aliases, if necessary.
  1. Q: Can you define cross-validation rules for DFFs? No, you cannot. You can only define them for Key Flexfields.
  1. Q: Can a value set be shared between Flexfields? Yes, value sets can be shared between Flexfields.
  1. Q: Can a value set be shared within a Flexfield structure? No, value sets cannot be shared between segments within a Flexfield as long as they do not carry the same type of information. For example, date information can be shared between segments within a Flexfield.
  1. Q: What are the advanced validation options? Three types of advanced validation options are available. $PROFILES$, which references the current value of a profile option. An example would be $PROFILES$.profile_option_name. Block.field, which references the block field. $FLEX$, which refers to the current value of a previously used value set. An example would be $FLEX$.value_set_name (cascading dependencies).
  1. Q: What is the next step after defining the segments for Flexfields? Freezing and compiling the structure.
  1. Q: What are the steps required to set up value security rules? Make sure security is enabled, define rules for the value set, and assign rules to the user's responsibility.
  1. Q: What is Oracle Alert? Oracle Alert is an exception reporting system. It keeps you informed on an as-needed basis. It also communicates with other users through e-mail regarding exception messages.
  1. Q: How many types of alerts are there? Two types of alerts exist: Periodic Alerts and Event Alerts. Periodic Alerts fire at a time interval, and Event Alerts are fired by database table changes.
  1. Q: What are Quick Codes? Quick Codes, also known as Quickpicks, are standard sets of user-defined values. Lookup is a combination of a code and a description. The lookup tables are generally populated by the scripts located in /install/odf directory.
  1. Q: What is an Open Interface in Oracle Applications? Open Interface, also known as the Application Programmer Interface (API), is a process whereby the Oracle Applications are linked with external or legacy systems. Open Interface works as a temporary staging area to load the external information into Oracle Applications tables. Once the data is validated, it sends the information to the permanent tables. Rejected transactions can be corrected and resubmitted.
  1. Q: Which schema has complete access to the Oracle Applications data model? The APPS schema. AutoInstall automatically sets the FNDNAM environment variable to the name of the APPS schema.
  1. Q: What is the top directory in Oracle Applications? $APPL_TOP.
  1. Q: What is a product top directory? It starts with the product shortname and is suffixed with TOP, such as TOP. For example, General Ledger's top directory is GL_TOP.
  1. Q: What are the log and output directory names for a product group? The product group environment file sets the APPLLOG variable to log and APPLOUT to out. For example, the output directory for General Ledger is $GL_TOP/$APPLOUT. For log, it is $GL_TOP/_$APPLLOG.
  1. Q: What data dictionary tables do you use to obtain detailed information regarding? You can write a query by joining the FND_TABLE and FND__COLUMNS tables. FND_INDEXES and FND_INDEX_COLUMNS tables are part of the data dictionary. All the FND_ table names are self-explanatory.
  1. Q: What are the primary underlying tables for concurrent processing? FND_CONCURRENT_PROGRAMS, FND_CONCURRENT__REQUESTS, FND_CONCURRENT_PROCESSES, and FND_CONCURRENT_QUEUES tables.
  1. Q: What are the primary underlying tables for Flexfields? FND_DESCR_FLEX_CONTEXTS, FND_FLEX_VALIDATION__RULES, FND_FLEX_VALUE_SETS, FND_ID_FLEXS, FND_ID__FLEX_SEGMENTS, and FND_ID_FLEX_STRUCTURES tables.
  1. Q: What is the primary underlying table for AOL QuickCodes? FND_LOOKUPS table.
  1. Q: What is the application dummy table used by a form block? FND_DUAL table.
  1. Q: What is the main underlying table for Profile Options? FND_PROFILE_OPTIONS table.
  1. Q: What are the main prerequisites for creating a custom application or responsibility? Set up a directory structure for a custom application, and define an environment variable that translates to your application base path.
  1. Q: 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:
Column Name
CREATED_BY
CREATION_DATE
LAST_UPDATED_BY
LAST_UPDATE_DATE
LAST_UPDATE_LOGIN

  1. Q: Do I need to have WHO column information in custom forms? Yes. It is strongly recommended to add WHO columns to the custom tables and call standard API, FND_STANDARD.SET_WHO in PRE-INSERT, and PRE-UPDATE triggers in each block of the form. Also, specify these fields as hidden in each block of the form.

  1. Q: What are the additional WHO columns used for concurrent programs? Concurrent programs use all the following WHO inncluding the following four.
Column Name
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE

  1. Q: Can you disable the WHO columns' information in a form block? Yes. You can disable HELP -> ABOUT THIS RECORD information within a block. Call the following procedures in a block level WHEN-NEW-BLOCK-INSTANCE
Trigger:app_standard.event('WHEN-NEW-BLOCK-INSTANCE');
app_standard.enable('ABOUT','PROPERTY_OFF');

  1. Q: How do you register your custom tables in PL/SQL? You can use AD_DD package to register custom tables in PL/SQL.
  1. Q: How do you define the passing arguments in SQL/PLUS and PL/SQL concurrent programs? You must name your passing arguments as &1, &2, &3 and so on.
  1. Q: How do you call your custom reports from a form? You can call your custom Oracle reports in a form using the FND_REQUEST.SUBMIT_REQUEST procedure.
  1. Q: What is a template form? A template form is a starting point for the development of custom forms. Copy the Template.fmb file from $AU_TOP/forms/US directory to your local directory and rename it.

  1. Q: Which libraries are attached to the template form? The following main libraries are directly attached to the template form. APPCORE contains packages and procedures for standard menus, toolbars, and so on. APPDAYPK contains a calendar package. FNDSQF contains packages and procedures for Flexfields, concurrent processing, profiles, and a message dictionary.

  1. Q: What is a calendar? A calendar is an object that lets you select the date and time. It is automatically included in the template form. A Calendar package example would be calendar.show.

  1. Q: Which template form triggers require some modifications? The ACCEPT, FOLDER_RETURN_ACTION, KEY-DUPREC, KEY-MENU, KEYCLRFRM, ON-ERROR, KEY-LISTVAL, POST-FORM, PRE-FORM, QUERY_FIND, WHEN-NEW-FORM-INSTANCE, WHEN-NEW-BLOCK-INSTANCE, WHEN-NEWRECORD-INSTANCE, and WHEN-NEW-ITEM-INSTANCE triggers.

  1. Q: Which template form triggers cannot be modified? The CLOSE_WINDOW, EXPORT, FOLDER_ACTION, KEY-COMMIT, KEY-EDIT, KEY-EXIT, KEY-HELP, LASTRECORD, WHEN-WINDOW-CLOSED, WHENFORM-NAVIGATE, and ZOOMtriggers.

  1. Q: What are the main template files for Pro*C concurrent programs? The main template files are EXMAIN.c and EXPROG.c .

  1. Q: What is the Oracle-recommended application short name for extensions? Oracle recommends an application short name begin with XX. As an example, extensions to Oracle Purchasing would be XXPO.

  1. Q: Where do you maintain the list of your custom programs? All custom programs should be listed in the applcust.txt file. This file is located in the $APPL_TOP/admin directory. When you apply the patches, Oracle Applications uses this file for informational purposes.

  1. Q: What are the steps involved in modifying an existing form? First, you identify the existing file and then you copy the file to a custom application directory, making sure to rename it. You then make the necessary modifications, generate the form, and document it in the custom program list using applcust.txt file.

  1. Q: Where do you maintain database customizations? You can maintain all your table changes by creating a new schema. You can use your custom application short name (such as XXPO) as your Oracle schema name for easy identification. The new schema must be registered in the Oracle AOL.

  1. Q: Can you create extensions to Oracle Applications without modifying the standard form code? Yes. This can be done using the CUSTOM library, which is an Oracle Forms PL/SQL library. You can integrate your custom code directly with Oracle Applications without making changes to your Oracle Applications forms code. The CUSTOM library is located in the $AU_TOP/res/plsql directory. Once you write the code, you compile and generate the CUSTOM procedures to make your changes.

  1. Q: When do you use the CUSTOM library? You can use the CUSTOM library in a variety of cases. You can use it to incorporate Zoom logic, logic for generic events, logic for product-specific events, and to add entries for the special menu.