, , , ,

How to Implement User Profiles?

Implementing User Profiles :

You should define user profile options whenever you want your application to react in different ways for different users, depending on specific user attributes.

You can decide whether your end users can view and update options you define at the User level or you can also define the option such that your end users could not see or change its value.

Predefined User Profile Options :

Database Profile Options :

Oracle Application Object Library provides many user profile options that the Oracle System Administrator or the users can see and update.

Internally Generated Profile Options :

Oracle Application Object Library also provides a set of profile options that you can access via the user profile routines. You can retrieve values for these profile options in your forms and programs.

However, except for the profiles CONC_PRINT_OUTPUT and CONC_PRINT_STYLE, you cannot change their values. System administrators and end users cannot see the values for, nor change the values of, these profile options.

  • FND_PROFILE: User Profile APIs :

You can use the user profile routines to manipulate the option values stored in client and server user profile caches.
On the client, a single user profile cache is shared by multiple form sessions. Thus, when Form A and Form B are both running on a single client, any changes Form A makes to the client’s user profile cache affect Form B’s run–time environment, and vice versa.

On the server, each form session has its own user profile cache. Thus, even if Form A and Form B are running on the same client, they have separate server profile caches. Server profile values changed from Form A’s session do not affect Form B’s session, and vice versa.

Similarly, profile caches on the server side for concurrent programs are separate.

Also, note that the server–side profile cache accessed by these PL/SQL routines is not synchronized with the C–code cache. If you put a value using the PL/SQL routines, it will not be readable with the C–code routines.

Any changes you make to profile option values using these routines affect only the run–time environment.

The effect of these settings ends when the program ends, because the database session (which holds the profile cache) is terminated.

  • FND_PROFILE.PUT :

Summary procedure FND_PROFILE.PUT

(name IN varchar2,

value IN varchar2);

Location FNDSQF library and database (stored procedure)

Description Puts a value to the specified user profile option. If the option does not exist, you can also create it with PUT.

Arguments (input)

name The (developer) name of the profile option you want to set.

Value The value to set in the specified profile option.

  • FND_PROFILE.GET

Summary procedure FND_PROFILE.GET

(name IN varchar2,

value OUT varchar2);

Location FNDSQF library and database (stored procedure)

Description Gets the current value of the specified user profile option, or NULL if the profile does not exist.

Arguments (input)

name The (developer) name of the profile optionwhose value you want to retrieve.

Arguments (output)

Value The current value of the specified user profile option as last set by PUT or as defaulted in the current user’s profile.

Example FND_PROFILE.GET (’USER_ID’, user_id);

  • FND_PROFILE.VALUE

Summary function FND_PROFILE.VALUE

(name IN varchar2) return varchar2;

Location FNDSQF library and database (stored function)

Description VALUE works exactly like GET, except it returns the value of the specified profile option as a function result.

Arguments (input)

Name The (developer) name of the profile option whose value you want to retrieve.

/by
, , , , , ,

What is Subinventory Quantities report?

Form: INVIRSIQ
Use the Subinventory Quantities Report to show inventory item quantities by subinventory.
Report Submission
Use the On-hand Quantity Reports or Submit Requests window and enter Subinventory quantities report in the Name field to submit the report.
Report Parameters
Item Range
Choose one of the following options:
Full listing   :    Report all subinventories.
Partial list by :  Report only those subinventories for a inventory item  partialrange of items.
Specific          :  Report only the subinventory you subinventory  specify.
Items From/To
Enter a beginning and ending item to restrict the report to a range of items. You can enter an item here only if you enter Partial list by inventory item in the Item Range field.
Subinventory
Enter a subinventory for which to report on-hand quantity. You can enter a value here only if you enter Specific subinventory in the Item
Range field.
The original report has the following columns:
1.Item
2.Description
3.Rev
4.Locator
5.UOM
6.Quantity
Customization:
–Add the price of every item.
–Calculate the price*qty=total
–Add the subinventory total
This would give us the value of the sub inventory.
Tables Used:
1.MTL_SYSTEM_ITEMS (msi)
2.MTL_ONHAND_QUANTITIES_DETAIL (moqd)
3.MTL_SECONDARY_INVENTORIES (si)
4.MTL_ITEM_LOCATIONS (mil)
The tables added:
1.CST_ITEMS_COST (cs)
MTL_SYSTEM_ITEMS:
MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item-related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory,
Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing. You can set up the item with multiple segments, since it is implemented as a flexfield.
Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_ FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item attributes and Oracle applications. Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table.
PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character unit that is used throughout Oracle Purchasing. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL.
Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.
MTL_ONHAND_QUANTITIES_DETAIL
MTL_ONHAND_QUANTITIES stores quantity on hand information
by control level and location.
MTL_ONHAND_QUANTITIES is maintained as a stack of receipt
records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria. Note that any transactions which are
committed to the table MTL_MATERIAL_TRANSACTIONS_TEMP are
considered to be played out as far as quantity on hand is concerned in Inventory transaction forms. All our Inquiry forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_IDs to join to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the
transactions that created the row and the transaction that last updated a row.
MTL_SECONDARY_INVENTORIES
MTL_SECONDARY_INVENTORIES is the definition table for the
subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this
item will physically exist in inventory.
Other attributes include general ledger accounts, demand picking order, locator type, availability type, and reservable type. You can also specify whether this subinventory is an expense or asset subinventory (ASSET_INVENTORY), or whether quantities are tracked
(QUANTITY_TRACKED).
MTL_ITEM_LOCATIONS
MTL_ITEM_LOCATIONS is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
The locator is a key flexfield. The Flexfield Code is MTLL.
CST_ITEMS_COST
CST_ITEM_COSTS stores item cost control information by cost type.
For standard costing organizations, the item cost control information for the Frozen cost type is created when you enter a new item. For average cost organizations, item cost control information is created when you transact the item for the first time.
You can use the Item Costs window to enter cost control information.
Where clause of the original report:
1.si.secondary_inventory_name = moqd.subinventory_code(+)
2.si.organization_id = moqd.organization_id (+)
3.moqd.organization_id = msi.organization_id(+)
4.moqd.inventory_item_id = msi.inventory_item_id (+)
5.moqd.locator_id = mil.inventory_location_id(+)
6.moqd.organization_id = mil.organization_id(+)
Additional where clause added for the customized report:
1.cs.inventory_item_id = msi.inventory_item_id
2.cs.organization_id = msi.organization_id
/by
, , ,

What is Item Replenishment ?

Item Replenishment Overview:

One can use the replenishment counting system to plan your inventories. This may be ideal for replenishing free stock items that you store on the production floor, or office supplies kept in departmental cabinets. Oracle Inventory provides an open interface for loading replenishment count data collected from some other system. In some situations, you may want to do your own planning and use Oracle Inventory to create your internal requisitions. As items are issued from a sub-inventory without formal issuing procedures, it is necessary to take inventory of non–tracked sub-inventories to record item usage and determine reorder necessity.
Business Scenario
In order to determine when and how much to replenish, periodical physical count of the items in non–tracked sub-inventories can be entered through the Replenishment Counts window, or via a batch load transaction such as a bar code reader. Oracle Inventory permits on–hand quantity as an additional replenishment information type for non–tracked sub-inventories. If an on–hand quantity is entered, Oracle Inventory uses min–max planning to determine whether an order should be placed. Oracle Inventory provides an external interface for loading replenishment count data collected electronically, such as by a bar code reader. Once you have populated the replenishment interface tables, you can run the Validate Replenishment Interface program to validate the counts and put the information into the appropriate tables.
               
Type of Interface: OPEN Interface
Technical Overview
MTL_REPLENISH_HEADERS, MTL_REPLENISH_LINES are the main tables. MTL_REPLENISH_HEADERS _INT, MTL_REPLENISH_LINES_INT are the Interface tables
The production tables are MTL_REPLENISH_HEADERS and MTL_REPLENISH_LINES. For each record in the MTL_REPLENISH_HEADERS there should be at least one record in MTL_REPLENISH_LINES. The corresponding interface tables are  MTL_REPLENISH_HEADERS_INT and MTL_REPLENISH_LINES_INT.                                
One should be very careful about many things like the Count type (On-Hand Qty, Order Qty, Order Maximum) depend on the item such that On-Hand Qty is applicable to non-tracked items, Order Qty is applicable to Reorder Level planned items while Order Maximum is applicable to items that are Min-Max planned. The replenishment count is mandatory in the first two conditions while it is not needed in Min-Max Planning. With the On-hand Qty, count qty is the on-hand balance while with Order Qty it is the reorder qty.             
Required fields for MTL_REPLENISH_HEADERS_INT are
ORGANIZATION_ID/ORGANIZATION_CODE, PROCESS_MODE, PROCESS_STATUS, REPLENISH_HEADER_ID, REPLENISH_COUNT_NAME, SUBINVENTORY_CODE,
PROCESS_MODE:
 2 – Concurrent
3 – Background
The replenishment validator polling process will pick up interface replenishment counts marked for Background processing. The validator will pick up and process all replenishment counts with a process mode of Background each time it runs.
You use Concurrent processing mode if you want to launch a dedicated replenishment validator process to explicitly process a single  replenishment count, identified as a parameter to the program, from the interface table.
PROCESS_STATUS
1 – Hold
2 – Pending
3 – Processing
4 – Error
5 – Completed
Required fields for MTL_REPLENISH_LINES_INT are INVENTORY_ITEM_ID, ORGANIZATION_ID, COUNT_TYPE_CODE, COUNT_QUANTITY, and COUNT_UOM_CODE
COUNT_TYPE_CODE
Enter the type of the replenishment count entry. The valid count types are:
1 – On-hand Quantity: Use On-hand Quantity to identify counts that are the result of stock-takes of sub-inventories in which you do not track on-hand quantities.
2 – Order Quantity: Use Order Quantity when you want to specify the quantity to be ordered. This count type may be used with either tracked or non-tracked sub-inventories.
3 – Order Maximum: Use Order Maximum when you want to place an order for the min-max maximum quantity specified for item in the sub-inventory specified. This count type may be used with either tracked or non-tracked sub-inventories.
Setting Up the Replenishment Interface
Access the Replenishment Interface through the Oracle Inventory menu (Counting/Replenishment Counts/Process Interface). Select the type of request by choosing Single Request. In the Request Name field, select Validate Replenishment Interface. In the Parameters window, select Concurrent or Background as the Processing Mode and select the Count Name for processing. Select Submit Request to begin processing. You can also use the Schedule button to specify resubmission parameters that will control how frequently the Replenishment Validator polls for records in the interface tables.
Validations Performed
The value of REPLENISH_HEADER_ID must be unique among existing replenishment counts
§         The value of REPLENISH_COUNT_NAME must be unique among existing count headers
§         The value of LAST_UPDATED_BY must be a valid user name
§         ORGANIZATION_ID must be a valid identifier of an organization
§         SUBINVENTORY_CODE must refer to an existing sub-inventory
§         DELIVERY_LOCATION_ID must be a valid identifier of a location associated with the organization generating the replenishment
§         There must be at least one line per header
§         The ORGANIZATION_ID at the header level must be the same as that at the line level
§         COUNT_TYPE_CODE must be either 1, 2, or 3 and must be consistent with whether the sub-inventory is tracked or non-tracked
§         The value of COUNT_QUANTITY must be consistent with COUNT_TYPE_CODE and must be greater than zero INVENTORY_ITEM_ID must refer to a transactable item in the organization specified
§         The item must exist in the sub-inventory and must be min-max planned in that sub-inventory
§         The COUNT_UOM_CODE must be valid and conversions to primary UOM must exist
§         Each line must correspond to a header
Error Messages: (Values for the ERROR_FLAG)
Possible values for the ERROR_FLAG column in the MTL_REPLENISH_HEADERS_INT table are:
1 – Non-unique replenishment header id
2 – Non-unique replenishment count name
3 – Invalid user name
4 – Invalid organization identifier
5 – Invalid subinventory
7 – Header with no corresponding replenishment lines
10 – Header failed because line failed
18 – Delivery location is not valid
Possible values for the ERROR_FLAG column in the MTL_REPLENISH_LINES_INT table are:
1 – No corresponding header id
3 – Invalid user name
8 – Invalid item identifier or item isn’t transactable
9 – Invalid unit of measure or no conversion to primary unit of measure exists
11 – No item specified in either identifier or segments
12 – Invalid count type
13 – On-hand count type used for tracked sub inventory
/by
, , , ,

Oracle WorkFlow Interview Questions

Oracle WorkFlow Interview Questions ?

1. How to find the latest version of a current workflow process, and how to revert to the old version of workflow

2. Name five very important base tables of oracle workflow and their significance.

3. Name five oracle workflow apis and their significance and use

4. How would you clear stuck or deferred workflows and what is meant by a deferred workflow

5. What is the difference between workflow wait activity and block activity, what is the api which is used in this regard to put a wait and to put a block.

6. How would you continue the workflow which is in wait and which is in block mode, name the api which is responsible for that

7. What is access level in oracle workflow and how it is significant.

8. Apart from loading the workflow from workflow builder is there any other method of loading the workflow from database to local machine.

9. How many types of attributes are present in workflow and what is a document type attribute

10. How would you force a user to not re-assign the workflow to other user.

/by
, , , ,

Oracle 11i Apps General Questions

What is Flexfield?
A flexfield is a field made up of segments. A flex field is a flexible data field that your organization can customize to your business needs without programming. Oracle Applications uses two types of flexfields, key flexfields and descriptive flexfields.
What is a Set of Books (SOB)?
Set of Books is a financial entity that shares a common Chart of Accounts, Functional Currency and Accounting Calendar (3Cs).
What is Segment?
A segment is a single sub–field within a flexfield. You define the appearance and meaning of individual segments when customizing a flexfield. A segment is represented in your database as a single table column.
What is a Value Set?
The flexfield validates each segment against a set of valid values that are usually predefined are called Value Sets.
What is Cross Validation?
A cross-validation rule defines whether a value of a particular segment can be combined with specific values of other segments
What are the different types of tables in Oracle 11i Apps?
B – The Main base tables
_ALL – Contains multi-org data
_V – View created on base table
_TL – Table that supports multi language
_VL – View created on multi language tables
_F – Data tracking tables. Used in HRMS
_S – Sequence related table
_DFV / _KFV – The DFF/KFF table created on the base table
What is the difference between Request Group and Request Set?
A request group is a collection of reports or concurrent programs.  A System Administrator defines report groups in order to control user access to reports and concurrent programs.  Only a System Administrator can create a request group.
Request sets define run and print options, and possibly, parameter values, for a collection of reports or concurrent program. End users and System Administrators can define request sets. A System Administrator has request set privileges beyond those of an end user. 
What are different types of validations of Value Sets?
None (not validated at all)
Independent
Dependent
Table
Special (advanced)
Pair (advanced)
Can you change the validation type of the existing Value set?
No.
What are the validation types supported by Accounting Flexfield?
Dependent, Independent, Table

What is the difference between Discrete Manufacturing and Process Manufacturing?
Discrete manufacturing is a manufacturing process where distinct products are built or manufactured in discrete batches on manufacturing floor. The resulting product is what you can count. It uses Bills of Materials (BOMs) and assembles along a routing.
Eg: Computer Manufacturing, Machinery Manufacturing, Automobiles, etc
Process manufacturing is a manufacturing process in which the end product manufactured are undifferentiated. It uses formulations or recipes and blends in a batch.
Eg: Pharmaceutical, Food and beverages, Refineries, etc
What are Shared Entities in Oracle 11i Apps?
Shared entities enable one-time definition of object and are accessed by several products/Modules.
Few shared entities and the owned applications are
Set of Books – General Ledger
Items – Inventory
Unit of Measure – Inventory
Suppliers – Purchasing
Customers – Receivables
Locations – Human Resource
Employee – Human Resource
Organization – Human Resource
How data are secured (partitioned) in Oracle 11i Applications?
General Ledger and Fixed Assets – Set of Books
Human Resource – Business Group
Order Management, Accounts Receivables, Accounts Payables, Purchasing, Cash Management, Projects, Service, Sales Compensation, Sales and Marketing – Operating Unit
Inventory, Manufacturing – Inventory Unit
/by