Long time back , I got a complementary copy of EBS books from author Roel Hogendoorn.

These six books, roughly have 175-200 pages each, covering some of key areas of EBS.

• System Administration
• Workflow
• Flexfields
• Alert
• Reporting in E-Business Suite
• BI Publisher

I am so delighted with the content in these BOOK that I thought to share to rest of community people.

"...Extremely well written book, enjoyable and informative reading!"

Truly an excellent book and definitely a must for EBS beginners. In a world of ERP , this book actually delivers a lot more than its cost. Roel, has provided an incredibly honest approach to some of key attributes essential for EBS Implementer.

The webshop for the books is: http://stores.lulu.com/learnworks

This is a very basic question for ERP consultants , what is basic difference into two manufacturing operation : discrete manufacturing and process manufacturing . Lets redefined and understand in simple words:

As we know, in ERP prospective manufacturing operation can be broadly classified into two major category:

• Flow manufacturing
• Discrete manufacturing
• Process manufacturing

Discrete Manufacturing

Discrete manufacturing is distinguished by the production of distinct items that use bills of material and routings to determine costs and lead times.

Examples: Automobile manufacturing, computer manufacturing, dishwasher and washing machine manufacturing, etc .

Typically they follows either a Product, Process or a Combination Layout these layouts can be understood like:

• Product Layout - Processes come to the product . typical example are Ship Building, Car Assembly Line, PC's, etc.
• Process Layout - Products go to Process areas Typical Example are Cabinets and Casings, Sub-assemblies, Rubber Mixing, etc.

Is normally a Product that is "Built Up" from components or sub-assemblies.

A note with Oracle Product
Discrete manufacturing is Oracle Applications method to handle the unique problems in manufacturing equipment, like electronics, medical devices or a complicated assembly like the space shuttle as indicated above.

Discrete manufacturing is based on piece parts that are assembled or machined to make larger assemblies.

Oracle Discrete Manufacturing has a great deal of functionality to manage the Bills of Materials, Routings and Engineering changes that are required to adequately track assembly and cost the finished product.

Flow Manufacturing
Flow manufacturing is also called Lean Manufacturing or JIT(Just-In-Time) and uses the principles of demand flow manufacturing to produce product is required, for customer orders, quickly and to meet the demand exactly on-time.

Usually, in order to use Flow a company needs to have production facilities that subscribe to the principals of Lean Manufacturing. It is necessary to design production facilities so that production lines, resources, machines, and labor are making only what is needed for immediate customer orders.

Process Manufacturing

Process manufacturing is distinguished by a production approach that has minimal interruptions in actual processing in any one production run, or between production runs of similar products. This approach produces multiple unique products in relatively small batches flowing through different production operations throughout the factory.

They typically follows a Process Layout.

They are normally producing a Product that is "Homogeneous" and equally divided for the convenience of packaging.

Typical examples are Food, pharmaceutical and other batched-based manufacturers such as refineries, wineries, etc .

A Note with Oracle Process Manufacturing
The OPM capabilities allow for multiple units of measure because the flexibility in batch production is required in process industries.

Process manufacturing is used at companies that make products that use formulas, receipts and/or have co-products or by products. Typical users of OPM are manufactures of food products or chemicals that have complex internal processes and need a high level of control.

The OPM capabilities allow for multiple units of measure because the flexibility in batch production is required in process industries.

What makes process manufacturing different from discrete?

Process manufacturing uses formulations or recipes. A discrete manufacturer uses Bills of Materials (BOMs). A discrete manufacturer assembles along a routing, whereas a process manufacturer blends in a batch.Apart from these other difference with other factors are summurized as:

Which come under which category ?

In Oracle supplier can be entered from three different place.These are the Setup required to perform for a suppliers.

• Supplier Accounts
• Taxes
• Payments
• Term Date Basis
• Due Date Basis
• Invoice Currency
• Holds

In Oracle these are the important features through the screen:

• Enter Suppliers
• Enter employees as Suppliers
• Merge duplicate Suppliers

Supplier Data Entry

Other Details

• Classification Information (Significant for type and employee name)
• Parent subsidiary relationship
• Supplier bank information
• Appropriate GL accounts information
• Record tax information
• Hold conditions to supplier site (rather than on individual invoices)

and these are the optional field

• Tax payer ID
• VAT registration number
• Inactive date (to prevent invoice/PO entry after a certain date)

Important Table that holds supplier

• PO_VENDORS : this hold a suppliers information

Some important columns:

• • SEGMENT1 – SYSTEM GENERATED OR MANUALLY ENTERED NUMBER FOR SUPPLIER (PO_UNIQUE_IDENTIFIER_CONTROL)
  • VENDOR_ID – SYSTEM GENERATED UNIQUE NUMBER
  • CREDIT STATUS –GOOD/POOR
  • ORGANIZATION_TYPE_LOOKUP_CODE
  • VENDOR_TYPE_LOOKUP_CODE – VENDOR/EMPLOYEE (EMPLOYEE_ID)
  • ONE_TIME_FLAG
• PO_VENDOR_SITES_ALL : This hold corresponding site information Contains Details of the site like Full Address/Work of site
  and its link with vendor_id.Important columns includes:
  • PURCHASING_SITE_FLAG, VENDOR_SITE_CODE, RFQ_ONLY_SITE_FLAG, PAY_SITE_FLAG,HOLD_ALL_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG, HOLD_UNMATCHED_INVOICES_FLAG
• PO_VENDOR_CONTACTS : This hold supplier contact information for respective sites.

How they connected

These are typically connected as:

Take a note Segment1 is holding suppliers Number.

Quick query for new babies

select * from po_vendors
where segment1 = '1001‘;

select *
from po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = '1001'
and pov.vendor_id = pvs.vendor_id;

select *
from po_vendor_contacts pvc,
po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = '1001'
and pov.vendor_id = pvs.vendor_id
and pvs.VENDOR_SITE_ID = pvc.vendor_site_id;

Supplier Interface

Yes, that was one of the enhancement in oracle 11.5.10 release. Prior to that version, there was no interface or Public exist for supplliers, thus it was a tricky to handle supplier. As we have already seen AP availability of AP some time back.

If you are having 11.5.10+ version,and if you have requirement to create supplier from your legacy data than use Supplier Interface, life will be easier. Is n't.

How we start

This consist in 2 steps process:

Step 1. Populate the new interface tables with data. This can be done via SQL*Loader or typically used to load data in a table.

Step 2. Run the Supplier Open Interface Request Set (FNDRSSUB1703)

Alternatively, run the individual import programs to load one table at a time

Some important notes for suppliers Interface

• When you load the data it should be 'NEW' initially. After the Supplier Sites Open Interface is run the status gets changed to PROCESSED'/'REJECTED'.

The entire flow can be shown as:

A Note with R12 for Suppliers

As we know in R12 Supplier is part of TCA , thus the link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id. The link between
PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.

When a Supplier is created Record will be Inserted in HZ_PARTIES. When the Supplier Site is created Record will be Inserted in HZ_PARTY_SITES. When Address is created it will be stored in HZ_LOCATIONS.

Implementing DFF in Form consist of two steps Process. The first steps consist of designing the table structure and registration and step 2 consist of enabling in the form.

Steps 1:

• Define DFF fields in the custom tables

ATTRIBUTE1…ATTRIBUTE15 and ATTRIBUTE_CATEGORY are the normal fields used for DFF

• Register the table with Oracle AOL
• Register the DFF with Oracle AOL

For this you have to login into application with :

Application Developer -> Flex Field -> Descriptive -> Register

Then you should specify the name of the DFF (Say BTL_AR_DFF) and specify the associated table.

Step 2:

• Create DFF fields in the custom form

1. Create a form based on the custom table

• Copy TEMPLATE form has example descriptive flexfield which we can use
• Use the TEXT_ITEM_DESC_FLEX property class
• Attach ENABLE_LIST_LAMP_LOV for the field

2. Create a non-base table text item in the block with property class TEXT_ITEM_DESC_FLEX
3. Make sure that DFF is mapped to fields (ATTRIBUTE) of the table and is enabled.

• Next is to call Flexfield Routines to add DFF built-ins in the form to invoke DFF.
  • This consist of these steps:

1. Write, a form level trigger WHEN-NEW-FORM-INSTANCE to invoke the DFF as

FND_DESCR_FLEX.DEFINE(
Block => 'Name of the DFF block',
Field => 'BTL_DFF',
Appl_short_name => 'AR',
Desc_flex_name => 'BTL_AR_DFF',
Title => 'BPL AR More Info' );

2. Write, block level triggers PRE-QUERY and POST-QUERY as

• FND_FLEX.EVENT('PRE-QUERY');
• FND_FLEX.EVENT('POST-QUERY');
• FND_FLEX.EVENT('Pre-Query');
• FND_FLEX.EVENT('When-Validate-Record');
• FND_FLEX.EVENT('Post-Query');
• FND_FLEX.EVENT('When-New-Item-Instance');
• FND_FLEX.EVENT('Pre-Insert');
• FND_FLEX.EVENT('When-Validate-Item');
• FND_FLEX.EVENT('Pre-Update ');
  

These are the steps required to implement KFF in customization.

• Define KFF fields in your database tables.

The custom table should contain a field named as XXX_ID (where XXX means the entity. For ex. PART_ID, CODE_COMBINATION_ID etc) to capture the selected code combination ID.

• Register the table with Oracle AOL.
• Register the KFF with Oracle AOL

Logon as

Application Developer -> Flex fields -> Key -> Register
Existing KFF can also be used. Ex. Accounting FF

• Create KFF in the custom form

1. Create a form based on the custom table.

2. In the block, create two non-base table text items of data type CHAR (2000). One text item is to store the code combinations (Say BTL_KFF ) and other one is to store description of the code (Say BTL_KFF_DESC. Make this item as non-updateable).

• Add KFF standard built-ins in the form to invoke KFF

  The custom table should contain a field named as XXX_ID (where XXX means the entity. For ex. PART_ID, CODE_COMBINATION_ID etc) to capture the selected code combination ID.

this is typical registration of KFF. For Customization in custom development here are the steps:

1. Write a form level trigger WHEN-NEW-FORM-INSTANCE to invoke the KFF as

Fnd_Key_Flex.Define(
Block => ‘block_name’,
Field => ‘concatenated_segments_field_name’,
[Description => ‘description_field_name’,]
[ID => ‘Unique_ID_field’
Appl_Short_Name => ‘application_short_name’,
Code => ‘key_flexfield_code’,
Num => ‘structure_number’,

In above defination take a note,

Usage

FND_KEY_FLEX.DEFINE(
block => 'Custom block',
Field => 'BTL_KFF',
Description => 'BTL_KFF_DESC',
ID => 'XXX_ID',
Appl_short_name => 'SQLGL',
Code => 'GL#',
Num => '101',
Vrule => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED \nE\nAPPL=''SQLGL'';
name=Parent Values are not allowed\nN'
);

2. XXX_ID will store the code combination ID for each selection.

3.Invoke Flexfield functionality by calling FND_Flex.Event(event) from:

• PRE-QUERY
• POST-QUERY
• PRE-INSERT
• PRE-UPDATE
• WHEN-VALIDATE-RECORD
• WHEN-NEW-ITEM-INSTANCE
• WHEN-VALIDATE-ITEM

3. Write an item level trigger WHEN-NEW-ITEM-INSTANCE on BTL_KFF as

FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE')

4. Write an item level trigger KEY-EDIT on BTL_KFF as

FND_FLEX.EVENT('KEY-EDIT');

5. Write an item level trigger WHEN-VALIDATE-ITEM on BTL_KFF as

FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');

IF :CUSTOM_BLOCK.XXX_ID = -1 THEN
FND_MESSAGE.SET_STRING('You Have Selected An Undefined Code Combination !');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;

These steps makes you flexfield enable in your form.

A NOTE FOR FLEX FIELD DEFINATION

Lets take a close scan to understand some of the paramater used in Flex Field defination.

1. [Validate=> ‘{FOR_INSERT|FULL|PARTIAL|NONE|PARTIAL_IF_POSSIBLE}’,]

• This is used only for Key FlexFields only
• “For_Insert” used in a block where the code_combinations table is the base table
• “Full” used where the code_combination_id is a foreign key in the base table
• “None” when any value is allowed, such a range flexfield for query purposes
• “Partial” validates segment values but not the full combination
• “Partial_If_Possible” only used when the flexfield has dependent segments

2.[Vdate => ‘date’,]

• Validation date compared to segment value start/end dates

3.[Required=>’[Y|N’,]

• This is applicable only in the case for key flexfields only
• ‘Y’ requires user to enter a valid value for every required segment, even if the flexfield is never navigated to
• ‘N’ only requires required segments to be entered when the flexfield has been updated

4.[Displayable => ‘{ALL | flexfield_qualifier | segment_number} [\0{ALL|flexfield_qualifier|segment_number}]’,]

• Key Flexfields only. Default is “ALL”
• Naming flexfield qualifier or segment number causes only those segments to appear.
• Toggle this parameter by specifying more than one value separated with \0
• Display all but the first segment by specifying:
• DISPLAYABLE=>’ALL\01’
• Same parameters for Updateable and Insertable

5.[COPY=>’block.field\n{ALL|flexfield qualifier|segment number} [\0block.field\n{ALL|flexfield qualifier|segment number}]’,]

• Again, key flexfields only.
• Copy a form field value into the segment indicated by flexfield qualifier or segment number
• Value must be valid for segment or it will be erased
• Copy works in reverse when flexfield pop-up window closes
• Example copies a form global variable into the natural account segment of the Accounting Flexfield:
• COPY=>’GLOBAL.default_expense_obj\nGL_ACCOUNT’

6.[VRULE => ‘flexfield qualifier\n
segment qualifier\n
{I[nclude] | E[xclude]}\n
APPL=application_short_name;
NAME=Message Dictionary message name\n
Validation value1\n
Validation value2…
[\0flexfield qualifier\n
…

• Key or range flexfields
• Impose additional validation
• ‘\n’ is delimiter between parameters
• ‘\0’ is delimiter between groups of parameters

As we have seen DFF provide customizable expansion space in the form to capture additional information, which are not provided at present. A DFF describes an application entity, providing form and database expansion space that can be customized.

Why Descriptive Flex fields

• Customers require more data to be stored than what Oracle provides
• Oracle gave us additional “space” in most tables to store this data
• Descriptive Flexfields will survive an upgrade

Global vs Context Sensitive

• Global Data Elements mean the question will be asked for every occurrence of the Descriptive Flexfield.
• Context Sensitive Elements are questions asked “depending” on the answer to some other question. This “other” question can use a Reference field from above for its answer.

How Segments Use Underlying Columns

• A DFF uses columns that are added onto a database table. The table contains any columns that its entity requires.
  The DFF columns provide ‘blank’ columns that you can use to store information which is not already stored in another column of that table.
• A DFF requires one column for each possible segment and one additional column in which to store structure information(i.e. The context value).
• You can define as many segments in a single structures as you have DFF columns in your table.
  The DFF columns are usually named as ATTRIBUTEn, where n is a number.
  

Implementing Descriptive Flexfileds
For implementing DFF it requires five steps:

• Define DFF columns in your Database table
• Register your table with AOL/ Pl/SQL scripts
• Register Your DFF with Oracle AOL
• Create Your DFF in Your Form
• Add DFF routines to Your Form

Drawbacks of DFF

1. Disk space is wasted.
2. DFF has limited segment for global as well as context-sensitive attribute.
3. No query possible on DFF segment values.

In last post he have seen most businesses use codes made up of meaningful segments (intelligent keys) to identify accounts, part numbers and other business entities. For example a company might have a part number 'PAD-NR-GRN' indicating a Notepad, Narrow Ruled and Green. Another might have a different code structure for the same Notepad, Narrow Ruled and Green as 'GRN-NR-PAD'. Hence KFF provides the flexibility to define the code structure of any business entity in the customer-desired format without reprogram the application.

We all know , oracle GL uses a KFF called Accounting flex field to uniquely identity a GL account. Oracle have customized this KFF to include 6 segments like Company Code, Cost Center, Product, Product Line and sub account. Valid values are available to each segment and cross validation rules are provided for segment combinations. This KFF can be customized depends upon the business needs.

When to use a KFF

• Used to uniquely identify an application entity with an intelligent key, where the key can be multipart and each part can have some meaning.
• Use KFF that Oracle Applications provides to integrate your applications seamlessly with Oracle Applications.

What End User’s want there business Perspective

These are the things normally any business user want if implementation team and management avoiding any customization.

• Customize the Flex fields Appearance
  • Flexfield title
  • Number and order of segments
  • Prompts
  • Value sets, values, and value description.
• Use Flexfield Functionality for Validation
  • Individual segment validation
  • Cross-segment validation
• Customize to the Situation
  • Flexfields allow the application to adjust to fit specific business needs.
  • Use multiple structures to match different needs in different situations.

The good things is that the above can be easily control and managed from screen, without any extra(Ordinary) skill

Types of Key Flexfield Forms

In oracle application key Flexfield appears on three different types of application form :

1. Combinations Form
2. Foreign Key Form
3. Range Form

These form types correspond to the types of tables that contains key flexfield data.

Create Key Flexfield Combinations

A combination is a particular complete code or combination of segment values that makes up the code that uniquely identifies an object. For example, each part number would be a single combination, and if you had ten parts you would define ten combinations. A valid combination is simply a combination that may currently be used.

Combinations Table
Each key Flexfield has one corresponding combinations table, known as the Combinations table, where the flexfield stores a list of the complete codes, with one column for each segment of the code, together with the corresponding unique ID number(a code combination id number or CCID) for the code. Then the other tables in the application have a column that stores just the unique ID for the code

Foreign Key Form

• Use the flex field code combinations from a form with a foreign key reference to the combinations table.
• Internally the underlying Table Uses a Foreign Key
• The unique ID primary key column of the combinations table serves as a foreign key column here.
• Often the foreign key table contains a structure ID column as well. It can be best represented as figure below,

Range From

• Here table Contains Two Columns For Each Possible Segment
• Often the table contains a structure ID column as well.
• This form does not require actual combinations, and so does not need a foreign key link to the combinations table.
• Perform operation on ranges of combinations using this form.
  

Create New Combinations Dynamically

Allow the defination of new flexfield combinations from forms with a foreign key references, as mention in the following diagram.

Describe The Values In A Segment

• A segment qualifier is like the segment asking each value a question “What type of value are you?”
  • For example, assign an account type of “Expense” to the Account segment value 3003.
• Uses Segment Qualifiers with the Accounting Flexfield
  • Detail Budgeting Allowed.
  • Detail Posting Allowed.
  • Account Type: Asset, Expense, Liability, Ownership/Stockholde’s Equity, or Revenue.

Dynamically Insert Allowed

• Set and change this property while defining the flexfield structure on the Define
• Key Flexfield Segments form.
  • Sometimes this property is not technically feasible
  • If the combinations table includes mandatory columns that can not be filled by the flexfield.
  • Who columns, CCID columns, and enabled/activation date information can be filled by the flexfield.

This is all about the key flex field. Hopefully it would be good to start with , If you want to learn more about the concept, please do refer Flexfield user guide. Next will take another deep drive for DFF.

You can enter a value directly to a date field or use the calendar facility in APPS. Calendar lets you the following needs.

• Represent date and time graphically so that you can make a quick selection without typing.
• Select only those dates or date ranges that are suitable for the current field.

How to invoke a calendar in a form

To invoke a calendar in a custom form you need to do the following.

• Create a text item with data type DATE in the form.
• Set the LOV of the text item to ENABLE_LIST_LAMP in the property sheet.
• Set the LOV for Validation property of the text item to FALSE.
• Create an item level trigger KEY-LISTVAL as CALENDAR.SHOW

For those who are very new to oracle application , you should know the help menu has some great functionality for diagnostics purpose.

• How to see application version, Form shortname and version of form
  (Help) About Oracle application
• (Help)Diagnostics ->Examine
  • Pass the apps password and investigate the value for some variable.
• (Help)Diagnostics ->Examine ....Chose $DESCRIPTIVE_FLEXFIELD$block
• (Help)Diagnostics ->Examine ....Chose $ENVIRONMENT$block
  • This has all primary environment variables.
• (Help)Diagnostics ->Examine ....Chose $PROFILE$block
  • this shows all profile options given to user who is accessing the application.
• (Help)Diagnostics ->Examine ....Chose system block...choose LAST_QUERY field
  • This will shows last query used by the form.
• (Help) Diagnostics -> Display Database error
  • This shows any database error message.
• (Help) Diagnostics -> Trace(checkbox)
  • The options are on and off for forms tracing. Tracing file (.trc) is put into user dump folder.

In order to allow use of the system by multiple business units, accommodating differing requirements for functional currencies, accounting structures and security, whilst at the same time ensuring a central core design Oracle provides functionality known as "Multiple Organizations" in short is called " multi-Org".This allows different business units to have their own view of the applications, in particular for the financials AP ( Payables), AR ( Receivables), FA (Fixed Assets), CE (Case Management) and INV ( Inventory).

Multi-Org is an important feature of Oracle Applications for several reasons:

• Allows multiple sets of books and multiple legal entities to be configured and to operate in the same instance
• Provides support for data security between business units within a single applications installation
• Permits users to sell and ship products from different legal entities (in different sets of books) with automatic intercompany accounting
• Supports internal requisitions and purchasing/receiving products from different inventory organizations (within the same set of books)
• Enables an enterprise to be housed in one database instance of Oracle, spanning multiple countries, currencies, and legal entities without a reduction in response times (architecture-related)
• Multiple Organizations Reporting enhances the reporting capabilities of Oracle Applications products by allowing you to report at the:Set of Books level, Legal entity level or Operating unit level

Multiple Organization in Oracle Applications depends primarily on defining your organizational structure in the multi-level hierarchy used by Oracle Applications. The levels are:

• Business groups
• Accounting sets of books
• Legal entities
• Operating units]
• Inventory organizations

Business Groups: Oracle Applications secures human resources information, including organization definition, by business group. At least one Business Group will be required for every country since the employee legislation is specific for each country and employee profile is set up at the Business Group level. Security access to the sensitive Human Resources data is secured at the highest level at the Business Group. In short Business Group partitions Human Resources information in a multi-organization structure. Organization Structures and Organization Hierarchy is defined within the Business Group.

Multiple sets of books can share the same business group if they share the same business group attributes, including HR flex-field structures (Grade, positions flex-fields etc).

Accounting sets of books: A General Ledger concept for having separate financial reporting entities for which chart of accounts, calendar, or functional currency differs. In addition for scalability and ensuring independent numbering system for all the accounting transactions as well as the ability to open and close each of the legal entities period independently a separate set of books will be configured for each legal entity.

Legal entities: An organization that represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other relevant legal company information to this entity.

Operating units: An organization that partitions and uses data for Payables, Purchasing, Order Management, Cash Management, Fixed Assets and Receivables. Operating Units allow for configuration of the Oracle Applications across Multiple Business Groups using a single installation of the software.

Inventory organizations: An organization that tracks inventory transactions and balances, and/or that manufactures or distributes products or components. Segregate Item data objects for Inventory, Purchasing, Order Entry, and the Manufacturing Applications (Organization_Id).

Examples could be manufacturing plants, warehouses, distribution centers, and sales offices

The following applications secure information by inventory organization: Oracle Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and Purchasing receiving functions. To run any of these applications, you must choose an organization that has been classified as an inventory organization.

HR Organization
Internal Departments to which Employees are Assigned.

Asset Organizations
An asset organization is an organization that allows you to perform asset–related activities for a specific Oracle Assets corporate depreciation book. Oracle Assets uses only organizations designated as asset organizations