Difference between “Internal” and “External” Drop-Ship

In Oracle Context External Drop-Shipping means your Oracle Order Management uses purchase orders to outside suppliers that are automatically generated from sales orders for goods supplied directly from the supplier. The “external ” supplier ships the goods directly to the 3rd Party customer and confirms the shipment through the use of an Advanced Shipment Notice(ASN).

You should take a note,Oracle uses this ASN to record a receiving transaction into inventory followed by an immediate logical shipping transaction. From these transactions, conveyance of title takes place and the customer can be invoiced and the supplier’s invoice can be processed.

where as “Internal” context Drop-Shipping functions in a similar fashion. The key difference is that no inventory transactions take place on the books of the selling operating unit; transfer of ownership of the goods from shipper to seller to customer with the only physical movement of the goods being out of the shipping organization.

Read the rest of this entry »

A Drop Shipment occurs when a customer order is sourced from and delivered by a supplier.

Order Management sends information to the Purchasing Application to create that PO, and then when that PO is received (to indicate shipment from the supplier to your customer), the order line is automatically updated to indicate that it was fulfilled.

In this process, the company running Order Management is modeled as the company to whom the end customer places the original order.

Drop Shipment - Setup

You need to make sure these are attribute setup Correctly:

• Item Attributes

Purchased : Enabled
Purchasable : Enabled
Transactable : Enabled
Stockable : Optional
Reservable : Optional
Inventory Item : Optional
Customer Ordered : Enabled
Customer Order Enabled : Enabled
Internal Ordered : Disabled
OE Transactable : Enabled
Shippable : Optional

• And ,your do set up for Order Source Type as External

Drop Shipment – Your Setup checklist

• Ensure you have created your Order Management Transaction Types and linked your Transaction Types to order and line workflows that support drop shipments.
• Ensure the Oracle Workflow Background Engine is running.
• Ensure all Drop ship locations you will use to perform drop shipments have the Ship To Site and Receiving Site defined.
• Ensure you have defined the Internal Ship To Locations for your drop shipment customers (Oracle Receivables Standard Customer window, Business Purpose Details Tab).
• Ensure your standard items have an associated List Price defined within your PO Inventory organization (Oracle Payables Financial Options window, Supplier-Purchasing Tab).

Drop Shipment - Process Steps

• Create a Sales Order with line where the line source is External
• Book and Schedule the Sales Order
• Run Requisition Import Process
• Now the line status will be in Awaiting Receipt
• Login to the Receiving Organization (Purchasing) who has been setup as an Approver
• Run Requisition Import
• Create Purchase Order from the Requisition
• Approve the PO
• Receive the full quantity
• Run Auto Invoice
• Verify Invoice in Sales Order

Check the details here .

Things not to forget in a DropShipment

• Release 11i/12 does not support Drop Shipment across operating units.
• Blanket PO’s will not used with Drop Shipment , the reason the PO must be created when OM notifies PO that a Drop Ship order has been created.
• You can’t cancelled Drop Shipments once Oracle Purchasing obtains the receipt.
• Standard Items can be used for Drop Shipment.
• In 11i, PTO’s and ATO’s cannot be drop shipped

Similar Post on Drop Shipment

• Understand “Drop Shipment” in Order Management?
• Understand “Drop Shipment” in Order Management? -Part II
• ‘Drop Shipment’, ‘BackOrders’ and ‘Back to Back Order’ …
• Drop Shipment - Functional Setup and flow

This was yet another query asked by one of the reader..so thought to combine in a post so that others can also understand.

Lets try to understand these key terms from Oracle Apps Product side:

Back Orders

• The Oracle “term” backorder is a “status” on the order line or delivery line indicating that you have tried to release an order for picking in your warehouse, but that the pick release was UNSUCCESSFUL because there was no available inventory.(Backorder can be partial or complete). The Oracle term backorder does NOT mean that you have open purchase orders for the out-of-stock item from your vendors.
• The term backorder is also used in business a little differently than in Oracle. The term “An item is on backorder” usually means that the item is not in stock, but the shipping company has already placed purchase orders from their suppliers to restock the item.
• Back Order is when you do not fulfill the Sales Order, or if the inventory is out of stock for delivery to customer.

Drop Shipment

• Drop shipment on the other hand is a method of order fulfillment where the organization taking the order does NOT maintain their own inventory for the drop-shipped product, but fulfill their orders through 3rd party vendors who directly ship to the end customer ordering the product.

For example,

• A orders item x from B
• B orders item x from C
• C ships Item x to A.
• B bills A for the order, C bills B for the order

Back to Back Orders

In Back to Back order the shipment process is also completed through OM as a standard order after the item is received against a PO.

What is the major difference between drop shipment and back to back order ?

More on Drop Shipment & Back to Back Orders(Functional)

High Points for EBS Base Functionality for Drop Shipment

• Drop shipment order processing in EBS is managed using workflow
• Drop ship functionality is based on source type of an order line (Internal/External)
• You can automate your PO/BPA release creation based on ASL/sourcing rule information
• Drop ship process uses standard OM workflows.
• You can use Sales order Purchase order discrepancy report to identify discrepancies between the OM and the PO
• There is a links OM and PO to provide visibility through entire supply chain Flow.

The entire Drop shipment process flow can be best understood as figure below:

Points for Implementation Considerations

• Order line attribute: Source Type (need to be external). You can be defaulted from item or order type
  • Menu -> Responsibility Order Entry Super User/setup/Orders/Types
  • Setup an order type using the cycle defined in the previous step (a),an order number source, a valid standard value rule set.
• Item attributes (OM and Purchasing)

You need to do the setup for these attributes:

-Purchased (PO) Enabled
-Purchasable (PO) Enabled
-Transactable (INV) Enabled
-Stockable (INV) Optional
-Reservable (INV) Optional
-Inventory Item (INV) Optional
-Customer Ordered (OE) Enabled
-Customer Orders Enabled (OE) Enabled
-Internal Ordered (OE) Disabled
-Internal Orders Enabled (OE) Disabled
-Shippable (OE) Optional
-OE Transactable (OE) Enabled

• Setup requisition import parameters
• You can also use grouping by Vendor
• You can also set the multiple distributions set to No
• ASL and/or Sourcing rule relationship need to be setup for Automatic PO creation
• Oracle recommends using a logical organization for drop shipment. Exclude this organization for planning purposes. It can be worked around using non nettable Dropship subinventory

Back to Back Order

As mention above this is ability to create specific supply orders for customer sales orders.
These are main features avaible in EBS

• Designate specific items as B2B orderable
• Enter sales order lines for these items, and have the supply automatically created via a requisition
• Have the requisition converted into a Purchase Order or a release of a blanket Purchase Order
• View the requisition number or PO number and its status from the Sales Order (using reservation details window)
• Reserve the supply from the Requisition to the PO and finally to the Sales Order once the PO is received
• Pick, ship and finally invoice the customer for the product.
• Note to Buyer’s field in the requisition captures the Sales order information
• Line status information shows the progress of the order
  PO Req Created
  PO Req Requested
  PO Created
  PO Received
• If line is manually reserved it progresses to “Awaiting Shipping”
• Changes and Cancellations on sales orders:
  • Reservation is changed
  • Notification sent to Buyer

Hope this makes a clarity on topics.

Most of us heard about e-commerce site like ebay,ubid.com,amazon,com which is very common place in advance countries like USA, UK & Singapore where people can sell or buy there product. Have you ever think ,what is similar situation in real world , when the the word ‘drop shipment’ comes to your mind. ……a business situation when the retailer or trader has no stock himself, instead giving customer details directly to the seller, and then saler than fills the order and send it to customer directly.How its sounds…

Drop shipping still remains a hot topic among SCM practitioner and apps Implementer , but even though it has many benefits, it isn’t the best option for every business. I am not going to walk through the pros and cons of the process , rather would like to walk through some of the key steps and processes which exist in real world which is important to understand the concept which helps during configuration with the product.

As in one of the last post , we have seen drop shipments is a method of fulfilling sales orders by selling products without the order taker handling, stocking, or delivering the products. The seller buys a product and the supplier ships the product directly to the seller’s customer .

In other word ,drop Shipment is a process where the customer places a purchase order on a company and this company instructs its supplier to directly ship the items to the customer.

Drop shipments are done because of the following reasons:

• Customer requires an item that is not normally stocked
• Customer requires a large quantity of the item which is not available with you
• It is more economical when the supplier ships directly to the customer

Types of drop shipment

If you see the real world scenarios this can be best categorize as mainly under three heads as:

• Type 1 : Full Drop Shipment

Where the seller sends the purchase order to the supplier for the full quantity that the customer had ordered

• Type 2 : Normal Shipments and Partial Drop Shipment

Under this scenario, If the seller has only part of the quantity available for shipping to the customer, then that quantity is shipped; a purchase order is created for the remaining quantity which the seller was not able to fulfill. This is typically true for single based item.

• Type 3 : Normal Shipments and Full Drop Shipment

In this scenario, the seller ships some goods from inventory to the customer, and the other goods are always shipped from an external source (supplier).

Process Flow for Drop Shipment (Adopted from User Guide)

Drop Shipments for Standard Items

1. Enter an order for drop ship item
2. Book the order
3. Run Requisition Import
4. Create a purchase order from the requisition
5. Approve the purchase order
6. Receive against the purchase order

Forward drop ship flow for ATO model

1. Enter a sales order for your drop shipped ATO model.
2. Select your options.
3. Schedule and book order (schedule date should default to request date for all lines).
4. Create you configured item by progressing your order ATO Model line or running the Autocreate Configuration batch process.
5. Verify order and line status.
6. Create a supply order (dropship requisition) by progressing your configuration item line or running the Autocreate Dropship Requisition batch process
7. Run the Oracle Purchasing Requisition Import to create a purchase requisition.
8. Create a purchase order for the requisition.
9. Approve the purchase order.
10. Receive the purchase order.

Forward drop ship flow for ATO Item

1. Enter a sales order for your drop shipped ATO item
2. Schedule and book order (schedule date should default to request date for all lines).
3. Create a supply order (dropship requisition) by progressing your configuration item line or running the Autocreate Dropship Requisition batch process.
4. Run the Oracle Purchasing Requisition Import to create a purchase requisition.
5. Create a purchase order for the requisition.
6. Approve the purchase order.
7. Receive the purchase order.

Non-SMC PTO model with drop shipped standard options

1. Enter a sales order for your PTO model.
2. Select options; source type on the components will default.
3. Schedule and book the order.
4. Run requisition import to create a purchase requisition.
5. Create a purchase order for the requisition.
6. Approve the purchase order.
7. Receive the purchase order.

Drop Shipment (Technical)

This section is going to help those who asked to provide some more granular details for drop shipment some time back.

As discussed above and in last post drop shipment is the process by which an organization takes orders from their customer and gets it fulfilled by a 3rd party. The selling organizatoin places a purchase order to teh 3rd party (supplier) who ships the ordered products to the end customer directly. There are a large number of tables that contain data related to drop ship orders. For example:

• oe_order_headers_all and lines_all (order info)
• po_requisition_lines_all, headers_all etc.( req. info)
• po_headers_all, lines_all (po info)
• oe_drop_ship_sources_all (this table contains the link between the req, po and the original order)

Moreover, there are many more tables involved in this therefore its better to have a understanding by corresponding ER diagram.

There is always a need to develop some of the custom report, therefore understanding of understanding table is very very important. Here are the same of underline tables details.

• PO_LINE_LOCATIONS_ALL
  • This table stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs.
• PO_LINES_ALL
  • This table stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs.
• PO_HEADERS_ALL
  • This table stores document headers for purchase orders, purchase agreements, quotations, and RFQs.
• PO_DISTRIBUTIONS_ALL
  • This table stores purchase order distributions.
• PO_REQUISITION_HEADERS_ALL
  • This table stores requisition headers.
• PO_REQUISITION_LINES_ALL
  • This table stores requisition lines.
• PO_REQ_DISTRIBUTIONS_ALL
  • This table stores requisition distributions.
• PO_REQUISITIONS_INTERFACE_ALL
  • This is the Requisition Import interface table.
• OE_ORDER_LINES_ALL
  • This table stores information for all order lines in Oracle Order Management.
• OE_DROP_SHIP_SOURCES
  • This table stores relationships between order lines in oe_order_lines_all table and associated oracle purchasing requisitions in PO_REQUISITIONS_ALL and Oracle purchasing purchase orders in PO_LINES_ALL.
• RCV_SHIPMENT_LINES
  • This table stores receiving shipment line information.

Drop Ship @Release 12
There are no Changes in R12 for Drop Ship Flow same as R11 only

Date flow for Drop Shipment

• Understand “Drop Shipment” in Order Management?

Reference Notes

• Note 225434.1 : Drop Ship FAQ document
• Note 130702.1 :Drop Shipments process

OM is one of the most complex modules in EBS, and in typical complex business model, the integration of other product or third party can’t be denied. Processing an Order requires integration with many other business areas. Most integration points with other Oracle products are implemented via PL/SQL-based APIs.Here is brief discussion for Order Management EBS Integration points.

How Order Management integrated with iStore?

OM records customer orders placed via iStore, Order Capture and other CRM applications. It validates setup for shipping and payment options along with providing order status and the shipping information to customers. Once the quote is converted into an order, you can only make changes to the order through Order Management, prior to booking.

How Order Management integrated with Telesales?

Telesales’ eBusiness Center has several integrations with Order Management. There is an Order tab to view order history and create new orders.

How Order Management integrated with Cost Management?

OM call the the Cost Management CST_COST_API to obtain cost from cst_item_costs or cst_quantity_layers when the Gross Margin feature of OM is enabled.

How Order Management integrated with Field Service?

Field Service Report requires specifically that you setup Price Lists, Units of Measure (UOM), and two Inventory Item Attributes in Order Management. Price Lists contain the list price for an item. Items could be material, but also labor and expenses like units of driving distance. Once material, expense and labor transactions for a task have been taken down on the Field Service Debrief, this information is updated to Charges. In Charges the list price for the item is received from Order Management and is used to generate an invoice for a customer.

How Order Management integrated with Depot Repair?

This is used by Depot Repair to create RMA and Sales Orders, validate customer accounts, and invoice customers for repairs.

How Order Management integrated with Install Base?

Information about Install Base trackable items is interfaced to Install Base in the following ways:

• Shippable Items: For both orders and returns, information is interfaced to Install Base via Inventory Interface.
• Non-Shippable Items: For both order and returns, information is interfaced to Install Base via the Order Management Fulfillment workflow activity Install Base also supports Internal Sales Order transactions by appropriately creating /updating item instances as a result of transactions between internal organizations such as pick transactions, shipments, and receipts.

How Order Management integrated with Service Contracts?

Service Contracts need to pulls information from the Install Base newly created customer records and creates an ownership record.

• Warranty: A Warranty contract is created when a Serviceable product is shipped.
• Extended Warranty: An Extended Warranty contract is created when an Extended Warranty is sold on a sales order. Oracle EAM & OM integration
• Subscription: Fulfillment starts after the contract approval process.
• RMA: Service Contracts sends Order Management RMA information

How Order Management integrated with Advanced Planning System/Global Order Promising/ ASCP?

Order Management uses Advanced Supply Chain Planning’s Global Order Promising functionality to check the availability of ordered items and to schedule order lines.Scheduled Order Lines are viewed as demand by the Advanced Planning System.

How Order Management integrated with Purchasing?

Order Management integrates with Oracle Purchasing in the following functional areas:

• Return Receipts: Order Management uses the Oracle Purchasing Receipt functionality to handle Return receipts. When an item is received, Purchasing calls Order Management to indicate delivery and to get COGS information.
• Internal Orders: Oracle Purchasing uses Order Import to create internal orders.
• Drop-Ship Orders: Order Management integrates with Purchasing to fulfill drop-ship orders. It populates the PO requisitions interface table with information for order lines that need to be fulfilled via an external source.

How Order Management integrated with Bills of Material ?

This is integrated with BOM when models and kits are entered on sales orders.Normally OM uses the Bill of Material defined for the model or kit or explode the model into its components, for the purpose of the user selecting options and for shipping purposes.

How Order Management integrated with Workflow ?

Oracle Workflow is heavily used in EBS and specially to manage Order and Line processing. These PL/SQL based Workflow is a natural replacement for Order Cycles functionality. It provides a Graphical User Interfaces for defining activities, notifications, flows and viewing flow status.

How Order Management integrated with Advanced Pricing ?

OM is tightly integrated with Advance Pricing which includes price lists, modifiers, and agreements. Order Management, through its Sales Agreement functionality, creates price lists and pushes them into the Advanced Pricing tables. Order Management calls the Pricing Engine to make pricing requests during the processing of orders, and receives pricing information back from Advanced Pricing.

How Order Management integrated with Configurator ?

Order Management integrates with Oracle Configurator to support ordering and validation of configurations. The Configurator window is a Java Applet that can be launched from the Sales Order form.

How Order Management integrated with Oracle Payment ?

As we know Oracle payment is new product and OM accepts Credit Card information when entered on orders. It integrates with Oracle Payment to validate this information and get Credit Card authorizations. This information is then interfaced to Receivables.

How Order Management integrated with Receivables ?

Order Management integrates with Oracle Receivables in the following function areas:

• Invoice Interface: Order Management sends invoices and credit memos to Receivables via the Invoice Interface workflow activity. The seeded Invoice Interface - Line workflow sub-process populates the Receivables interface table.
• Receipts: Order Management calls Receivables’ Receipt API to create receipts for prepaid credit card orders. Order Management receives a payment-set id from AR when the receipt is created, and then passes that id back to AR in the autoinvoice tables at invoicing time so that the invoice can be matched to the receipt.
• Tax: Order Management calls the Global Tax Engine APIs to default the Tax Code (ARP_TAX.GET_DEFAULT_TAX_CODE) and to calculate estimated tax (ARP_PROCESS_TAX.SUMMARY) for the order Line. The estimated tax value is now stored on the line and re-calculated only when any of the attributes affecting tax change. Information about the tax value is also stored as Line Price Adjustments.
• Credit Management: If the Credit Management product is installed, notifications are sent to it by Order Management when an order or line goes on credit hold, to initiate a credit review. If the credit review results in a decision to approve the order, a business event is posted which OM subscribes to and then releases the credit hold.

How Order Management integrated with Payables ?

OM accesses the AP Bank Accounts table to populate the Credit Card LOV when an order is being entered with a payment type of Credit Card. Additionally, if a new credit card number is entered and the authorization of that card through iPayment is successful, Order Management calls an AR API to create a new bank account record for the customer in the Bank Accounts table.

How Order Management integrated with Inventory Management ?

• Order Management integrates with Oracle Inventory Management through Managing Reservations.
• You can create reservations to on-hand quantities from the Sales Orders form.

How Order Management integrated with Trade Management ?

Order Management can receive RMA orders and lines from Oracle Trade Management as part of its dispute handling functionality.

Things you should Know about Order to Cash Cycle.

Order Management

• You enter sales and return orders within an operating unit.
• You define price list and discount names for the entire instance. You can also define price list lines, discount lines, and quantity breaks for the entire instance. If you have more than one validation organization in the instance, you may experience error messages when querying a price list if the inventory item is not valid in your operating unit’s OM validation organization. Thus it is best to define price lists by operating unit.
• You can check credit within an operating unit.
• You assign order lines and shipments to a specific warehouse (inventory organization).
• You pick release orders and print pick slips by operating unit.
• You ship confirm orders and print shipping documents by inventory organization. You can ship from another operating unit, even if in a different set of books.
• You receive returned goods in the inventory organization specified on the return.
• Inventory and Manufacturing integration functions, such as reservations, available to promise checking, sales order demand, and assemble to order job creation are processed in the appropriate inventory organization.

Order Management /Account Receivable

• You setup customers for the entire instance but addresses for each operating unit.
• You merge customers and addresses within an operating unit.
• You report supplier/customer netting within an operating unit.

Account Receivable

• You setup receivable account defaults within an operating unit.
• You produce invoices, statements, and dunning letters within an operating unit.
• You enter and track customer commitments within an operating unit.
• You setup bank accounts and associated cash accounts within an operating unit till Release 11i, as subsequent changes has been made in R12.
• Till Release 11i you can enter deposits and individual receipts by bank account within an operating unit.

Similar Post

• What is “Order To Cash” in Oracle?
• Understanding Multi-Organization Structure in EBS
• Understanding Multi-Organization Structure in EBS(Technical) - Part II

The process flow for Internal Orders

Internal orders normally start with creation of requisitions in Oracle Purchasing (which is created as Internal Sales Orders) .

Once the requisitions created from the MRP and Inventory modules, the Requisition Import program must be run in order to move records from the requisition interface tables to the historical PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL.

Now next is to create internal requisitions from Purchasing module.When each requisition is approved, data will be inserted into the MTL_SUPPLY table. When the items on the requisition are stocked in inventory, the supply data can be viewed from the inventory form called Item Supply/Demand.

Once the Internal Requisition is approved, a user should run Create Internal Sales Orders (from a Purchasing responsibility) to load the internal sales orders interface tables called in Order Entry.

After the Creation of Internal Sales Orders has completed, a Import Orders concurrent program need to run from an Order Management.

at this stage, Oracle order Workflow will process the internal sales order using the workflow definition listed for the Transaction Type definition in Order Management. When the workflow completes the Shipping Network is checked to see if the Transfer Type between the source and destination organization is In transit or Direct.

You can use Oracle Order Entry/Shipping to define order cycles, approvals and holds.

Internal Requisitions use the Account Generator, which automatically builds account distributions. You can specify only one accounting distribution per inventory sourced line.

This internal requisition process provides the features needed to define your inter- organization shipping network. For transfers between two organizations, you can specify whether to use in transit or direct shipments. You can also require internal requisitions for transfers between specific organization.

The Data Flow for Internal orders

Last week, got a mail from one of reader who requested for providing data flow of standard orders and Return Orders. So,here are the information for data flow. Normally standard sales order can be split into nine sub steps , which jointly carried out by some module like INV,OM, Pricing, Shipping and AR. Lets take each sub steps with data flow conditions. These are based out of the flow which is available in 11.5.10.2.

1. Order Entry

This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.

2.Order Booking

This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.

Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

3. Reservation

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.

4. Pick Release

Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.

Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

5.Pick Confirm

Items are transferred from saleble to staging Subinventory.

• mtl_material_transactions
• mtl_transaction_accounts
• wsh_delivery_details (released_status ‘Y’‘Released’ )
• wsh_delivery_assignments

6.Ship Confirm

Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries

7.Enter Invoice

This is also called Receivables interface, that mean information moved to accounting area for invoicing details.

8.Complete Line

In this stage order line leval table get updated with Flow status and open flag.

9.Close Order

This is last step of Order Processing . In this stage only oe_order_lines_all table get updated.

These are the table get affected in this step.

These are the typically data flow of a order to cash model for a standard order.

Irrespective of installation , whether it’s full or shared, it is important to understand the “date tracking” concept that oracle utilizing in HRMS.

Effective Dates
Date Tracking is a means of maintaining a history of changes to personnel records. It applies to the following parts of a person’s record in Oracle HR: People, Assignments. Each of the areas that are date tracked within a record are date tracked independently.

We can ‘date track’ by setting an Effective Date. An effective date is the date at a particular point in time when a person’s record is effective. When you set an effective date for your work, DateTrack ensures that only information effective on that day is used for any processing,
validation, enquiries and reporting you carry out.

Lets understand this in this simple diagram , which is consider as timeline of a personal record.

It shows how the person’s Status has changed over time to reflect their changing circumstances. As the changes are made in Oracle, the system keeps a record of each change. These records can be thought of as different slices - or different pages in a file that show each change. Date tracking allows you to visit each slice or page at any time. If you set the effective date to 17 July 2001.. for example, the record would show that this individual is Married. Reset back to today and the marital status would show as divorced.

Take a another example for employee Assignment

In this example the assignment record shows how this person’s Position changes over time.

When this person originally started in company , their position was Programmer 1. Over time their position get changed and Oracle keep maintaining the history of this. In addition, this individual has been told that they have a new position which will take effect as of the 01 Jan 2008. Using date tracking you can record that this position change will take effect in advance of it happening - simply by setting the Effective Date to 01 Jan 2008 and making the change. Until that date actually arrives, the position will still show as the current one.

Insert
If there are future dated entries in the system (as in the assignment example above), and you wish to record a new change that will occur prior to the forthcoming change then Oracle will prompt you to Insert the record as part of the update. In this scenario, Oracle will insert a new ’slice’ of history up to the date of the future change only.

In above diagram example, a future dated change was entered to record that this individual’s position is changing from Programmer Level 3 to Analyst on 1 Jan 2007. However, subsequent to this change being entered and saved in Oracle, this person will have a new supervisor from July 1st - i.e. before taking up the new position. If you now record the new supervisor change, Oracle will prompt you to ‘insert’ it. As such, a further slice of history will exist from 1 July 2007 to 1 Jan 2008.

Which columns in Oracle hold these values

To control these date tracking rows, every DateTracked table must include these columns:

• EFFECTIVE_START_DATE DATE NOT NULL
• EFFECTIVE_END_DATE DATE NOT NULL

The multiple organization support feature uses native database features to build a security layer on top of a single installation of Oracle Applications. This layer of security provides the necessary data partitioning; while at the same time minimizes the number of potentially destabilizing changes to the application code itself. The security layer is provided using database views, which allow access to the partitioned data without any changes to the applications code.

For ERP applications, data partitioning is performed by database views. These views reside in the APPS Oracle schema and derive the appropriate operating unit context from an RDBMS variable.

All applications code is run against the APPS schema. The Applications database architecture is now the same for a Multiple Organizations and non-Multiple Organizations implementation.

Multi Organization Tables

• Multiple Organizations in Oracle Applications is enabled by partitioning some database tables by operating unit. Other tables are shared across operating units (and thus across sets of books).
• In general, the following criteria determine if a table would be partitioned:
  • The table contains a GL Account Code (code combination ID).
  • There is a business reason for the table to be partitioned (for example, the entity should not be shared).
  • The table contains transaction data.
  • The table is an interface table where data being loaded is partitioned.

Applications with Partitioned tables

o Oracle Cash Management
o Oracle Order Management and Shipping Execution
o Oracle Payables
o Oracle Property Manager
o Oracle Projects
o Oracle Purchasing
o Oracle Release Management
o Oracle Receivables
o Oracle Sales Compensation
o Oracle Sales and Marketing
o Oracle Service
o European Localizations
o Latin America Localizations
o Regional Localizations

RDBMS Variable
A global variable exists in the Oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID (or ORG_ID) for the Multiple Organization Support feature. The CLIENT_INFO context is derived from a profile option that the user sets for each responsibility as part of the Multi-Org setup steps.

• Org_id column in the warehouse contains the Operating Unit ID
• Organization_id column contains the warehouse / Inventory Org.

select * from <table name>_ALL
where ORG_ID = SUBSTRB(USERENV(’CLIENT_INFO’),1,10);