Multi - Org Concept in Oracle Apps R12

 Multi-Org in simple term means the implementation of multiple business units 
 (or Organization) under a single installation of Oracle Applications. The concept 
 of Multi-Org will manage the operations of an enterprise which has got subsidiaries
 across globe under a single oracle apps window, taking appropriate care of data
 security and data maintenance. Below are some of the features of multiple 
 organization functionality.

Any number of Business Units in an Enterprise can be supported within a single 
installation of Oracle Application. User can access the data corresponding to 
and limited to the operating unit. Reporting can be managed at different 
organization levels like, Business Group, Ledger, Operating unit etc.
Transactions like Procurement, Receiving, Selling, Shipping Etc. with the same 
Party Can be Performed through Different Organization and can be managed 

internally through inter company postings

A real time organization construct in R12

Here in this example construct, CCS Company has organization structure as follows

1 Business Group - Which controls the organization in America and Australia
2 Legal Entities - one in US and one in AU
2 Primary Ledgers - one in US and one in AU
2 Operating Units - one in US and one in AU
3 Inventory Organizations – two in US and one in AU

How Organization Hierarchy flow in Oracle R12

Multi-Org and Multi-Org Access Control in R12 (MOAC)

Prior to R12, user has to switch between responsibilities to enter transaction and 
for doing other activities for a particular organization. This is very time consuming 
to do activities in an environment like this if you have 100 operating units. 
To overcome this factor, oracle has introduced a new feature in R12 which allow the 
user to switch the organization from the same responsibility which enables the user 
to access different organization and its data from a single responsibility. 

To achieve the new objective, Oracle has introduced new functionality called Multi-Org 
Access Control (MOAC) in release 12. Following are the set up steps needs to follow for 
implementing the MOAC architecture for a particular application.

R12 Table Changes

Suppliers:

New R12 tables  -> Old 11i Tables
AP_SUPPLIERS - replaces PO_VENDORS
AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

Additional supplier related tables in IBY (Payments) and HZ (TCA):
IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.
HZ_PARTIES - Party data for the suppliers.
HZ_PARTY_SITES - Party site data for the supplier sites.

Invoices:

Additional table in R12: AP_INVOICE_LINES_ALL
Allocations - AP_CHRG_ALLOCATIONS_ALL is obsolete in R12

Taxes:

Functionality provided by E-Business Tax
New tables in R12
ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)
ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)
ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)
ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

Payments:

Functionality moved to central Payments (IBY)
New IBY tables in R12:
IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information

Accounting:

Functionality moved to SubLedger Accounting (SLA)
New R12 tables:
XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL 
XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL
XLA_AE_LINES-> replaces AP_AE_LINES_ALL
XLA_DISTRIBUTION_LINKS

Trial Balance:

New R12 Table
XLA_TRIAL_BALANCES
AP_LIABILITY_BALANCE-> not used in new R12 transactions
AP_TRIAL_BALANCE -> not used in new R12 transactions

Bank Accounts:

Functionality moved to Cash Management.
CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCT_USES_ALL  -> replaces AP_BANK_ACCOUNT_USES_ALL
CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

AP Suppliers in R12-Oracle R12

In R12 Accounts Payables, that there is no more supplier form. The Suppliers have gone to self-service now. This is not the only change in the supplier. The suppliers objects have moved from AP product to TCA (Trading Community Architecture) DataModel. Due to this, even the underlying tables have changed. Supplier information is no more stored in PO_VENDORS Table now.

3 new tables have been introduced.

• AP_SUPPLIERS

• AP_SUPPLIER_SITES_ALL

• AP_SUPPLIER_CONTACTS

But don't panic as your customizations can still work as there are views created with names of PO_VENDORS, PO_VENDOR_SITES_ALL and PO_VENDOR_CONTACTS for backward compatibility.

Being a part of the TCA, these tables are closely linked to the hz tables. Here is the list of few imp HZ Tables that are affected when a new supplier is added.

TABLE NAME	DESCRIPTION
HZ_PARTIES	Master table along with AP_SUPPLIERS instead of PO_VENDORS
HZ_PARTY_SITES	Master table for supplier sites along with AP_SUPPLIER_SITES_ALL instead of PO_VENDORS_SITES_ALL
HZ_LOCATIONS	Contains the party sites information
HZ_PARTY_USG_ASSIGNMENTS	Stores party usages
HZ_ORGANIZATION_PROFILES	Captures additional Supplier information, e.g. credit scoring details of Supplier or the Number of Employees working in Supplier Organization
IBY_EXTERNAL_PAYEES_ALL	Captures Payment related details of the Supplier
POS_SUPPLIER_MAPPINGS	It holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID

This is useful in cases whereby two vendors effectively belong the same HZ_Party Record.

See the diagram below for Supplier/Sites Mappings to TCA Objects.

So whenever a supplier is added in R12, an entry is made in all these tables. Functionally also, creating the supplier is different from 11i. Here are the steps to create a new supplier.

1. Hit the CREATE SUPPLIER button

2. Enter a unique supplier name (Organization Name) along with other optional other information like Alias, Tax Registration Number, D-U-N-S number.

3. If the Supplier Number Entry option in the Payables System Setup window is set to Automatic, Payables automatically enters a Supplier Number for you. If this option is set to Manual, you must enter a unique Supplier Number.

4. Click Apply. The system creates the supplier record and accesses the Suppliers: Quick Update page.

5. To create Supplier Sites, you will have to create the locations for that supplier. For that, click on the "Address Book" Button.

6. On the Address Book page, click the Create button.

7. Fill all the address details and address purpose. i.e. Purchasing, Payment or RFQ Only.

8. You can add as many locations as you wish.

9. When created, you can manage the addresses for other information.

<< Click to view the image in large size >>

10. The system only displays sites that are in your MOAC profile

11. The address status indicates whether the supplier has provided any updates for the address. Using iSupplier Portal's Supplier Profile Management tools, suppliers can enter address book information online, creating any number of new addresses, modifying the details for existing addresses, and indicating how each address is used.

12. Suppliers can also inactivate addresses that are obsolete. Buyer administrators need to approve any changes in order to update the master supplier details.

13. Address statuses include:

• New. A supplier has created a new address.

• Change Pending. A supplier has changed the address details. Click the Update icon to review the changes that have been made. The page displays the original address details and the changes, indicated by a blue dot. Buyer administrators can approve or make additional modifications to the changes before approving or rejecting the change. If the supplier has indicated that the address should be removed, there is a status change from Active to Inactive.

• Current. There are no pending updates for the address.

14. Since suppliers are stored in TCA, the address details for the supplier may be used by other Oracle products so be careful if removing supplier addresses. If the address is inactivated, the system no longer associates it to any contacts, and any bank account assignments to the address are inactivated. Methods to inactivate addresses include:

• You can click the Remove button on the Address Book page. This sets the address status to inactive and sets the Inactive Date for every site that is associated with the address in all operating units to today's date.

• You can update the address and set its status to Inactive. This changes the address status and does not inactivate any of the sites that are using the address.

• You can use the Manage Sites page to manually update the Inactive Date for each site.

HZ(TCA) tables in Oracle Receivables

This article describes few important HZ tables in AR and their relationships with each other.

HZ_PARTIES:

The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.

Few Important Columns are

ARTY_ID: Party identifier
PARTY_NUMBER: Unique identification number for this party
PARTY_NAME: Name of the party
PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:

The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.

Few Important Columns are

PARTY_SITE_ID: Party site identifier.
PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
PARTY_SITE_NUMBER: Party site number.
PARTY_SITE_NAME: User-defined name for the site.
ADDRESSEE: Addressee information.

HZ_LOCATIONS:

The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is

LOCATION_ID:

Few Important Columns are

LOCATION_ID: Unique identifier for this location
COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
ADDRESS1: First line for address
ADDRESS2: Second line for address
ADDRESS3: Third line for address
ADDRESS4: Fourth line for address
CITY: City
POSTAL_CODE: Postal Code
STATE: State
ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:

The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.

Few Important Columns are

CUST_ACCOUNT_ID: Customer account identifier
PARTY_ID: A foreign key to the HZ_PARTY table.
ACCOUNT_NUMBER: Account Number
CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:

The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.

Few Important Columns are

CUST_ACCT_SITE_ID: Customer site identifier
CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
BILL_TO_FLAG: Indicates if this is a Bill-To site.
SHIP_TO_FLAG: Indicates if this is a Ship-To site.
MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:

The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.

Few Important Columns are

SITE_USE_ID: Site use identifier
CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:

The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.

Few Important Columns are

CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:

The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.

HZ_PARTY_RELATIONSHIPS:

The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.

Thanks

Sajal Agarwal

Oracle applications – order management tables and its details

Entered
oe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.wsh_delivery_details Released_status Ready to release
Pick Released 
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release. 
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactionsmtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.wsh_new_deliveries if Auto-Create is Yes then data populated.wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from stagedmtl_material_transactions On the ship confirm form, check Ship all boxwsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program forra_customer_trx_lines_all specific batch transaction tables get populated
Price Detailsqp_list_headers_b To Get Item Price Details.
qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phonehz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbersfnd_doc_sequence_categories
fnd_doc_sequence_assignments

Default rules for Price List
oe_def_attr_def_rules Price List Default Rulesoe_def_attr_condns
ak_object_attributes

End User Details 
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)oe_sales_credits

Attaching Documents 
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions

Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Ordersoe_order_lines_all Cancel Order Details.

Workflow TimeOut activity

This is short post about "how Timeout" Parameter work in a workflow activity.


For any workflow activity we have property Timeout "check the Node tab of workflow activity tab". Timeout has 3 values

1. No Timeout
2. Item Attribute
3. Relative Time

No Timeout - means no timeout at all.
Item Attribute - this value will be populated from an attribute

Relative Time - means we can hardcoded time out values in days/hours/minutes based on the requirement, and in this post I will talk about this only. 

Please note that once an activity is time out it will be picked up by the workflow background process (WBP). WBP will pick a time out activity only when it is eligible for Pickup.

Let says relative Timeout is 30 minutes and WBP us scheduled to run after every 10 minutes. Once this activity is time out WBP will not pick it immediately, it will pick it after 30 minutes only (as per out example on 3rd run).


Recently we came across an issue where customer has activity A1.
On execution A1 ,

1. workflow progress to A3  if there is HOLD and once we release HOLD workflow progress to A6.
2. If there is no Hold detected on execution of A1, workflow  progress to A2 , A5 etc 

Customer stated that if A1 detect a Hold it go to A3 and remain there for ever, even after the Hold has been released. some how WPG is not progressing  A3 to A6, thus hundreds of records stuck in A3. Customer has also stated that same thing was working earlier without any issue, he also stated that after removing the Hold If he Manually Progress the records that stuck at A3, workflow Progress to A6.

On Investigation we notice that some how they set the time out to 100 days and because of that WBP was not picking A3 at all ( after hold has released) .
As per this setup of time-out this activity will be eligible to progress by workflow Background process only after 100 days.

To fix the issue ,we set the relative time to a reasonable value and everything works fine.

Be careful when you decide about attribute like this.

Processing Constraints in Oracle Order management

Processing constraints are rules that control who can change what and when they can change it. Processing constraints can prevent certain changes, but can also be set up to perform actions based on those changes. They can define actions that can result from these changes, such as requiring a reason for the change, triggering an action in Audit Trail or Versioning, or raising an Integration Event.

This post describes how to set up your processing constraints based on validation conditions in validation templates (for example, Booked = Yes) which are evaluated for groups of records (record sets).

Processing constraints are rules that control

·         Who can change?

·         What change is allowed?

·         When the change is permissible?

Setup processing constraints for Create, Delete, Update and Cancel operations for order or line based on user responsibility.

Example:

Cancel sales orders, order lines, returns, and return lines.  Order Management automatically adjusts reservations for canceled lines.  The order cancellation feature of Order Management enables you to specify who has the authority to perform a cancellation request.  Cancellations look at constraints.  If you are allowed to cancel sales, the system will perform a cancellation request.  Once a line or order is cancelled, the workflow closes the line.

Processing constraints for orders and returns determine whether you can cancel orders, returns, and lines based on their workflow status.  In addition to your user defined processing constraints, system defined rules exist.  Under these rules you cannot cancel an order if:

·         It has been closed.

·         It has already been cancelled

·         A work order is open for an ATO line.

·         Any part of a line has been shipped or invoiced.

·         Any return line has been received or credited.

Order Management honors processing constraints that you define for the Cancel operation that are stricter than these rules, but if you define any that conflict with these rules, they are ignored.

To prevent a responsibility from cancelling:

Navigate to:        Setup>Rules>Processing Constraints

•        Select the entity to be constrained.

•        Select the operation to be constrained.

•        Enter the constraining conditions.

In the Applicable To Tab:

•        Select the responsibilities authorized to perform this operation.

•        Save the constraint.

To allow a responsibility to cancel when a reason is provided:

•        Select the entity to be constrained.

•        Select the operation to be constrained.

•        Select the action to be taken if this constraint occurs.

•        Enter the constraining conditions.

•        Enter the responsibility constrained from performing this operation.

•        Save the constraint. 

Select an Attribute to constraint, based upon the operation selected.

•    If you select the value UPDATE for the Operation field and you do not select an Attribute value, the constraint allows no update to any field of the entity, by any user.

In User Action, select one of the following:

•        Not Allowed: You cannot perform the constrained operation

•        Require Reason and History: You can perform the operation only if you enter a reason. Use this with Operation CANCEL, Operation UPDATE if the constrained attribute is Ordered Quantity only, and for recording Audit Trail history when requiring a reason for an attribute change

•        Requires History: You can perform the operation and will not be prompted to

·         Enter a Reason. You still have the option to enter both a Reason and Comment, and if you do so, the information is recorded. Use the value for enabling Audit Trail history to be recorded without a reason for an attribute change

Following are the user actions that can be performed:

Check out the blog post for Defining Validation Templates

Processing Constraints Listing Report:

List all processing constraints and the corresponding constrained entities, constrained attributes, constrained operations, validation entities, record sets, validation templates and responsibilities to which constraint is applicable.