1. finance
2. accounting and auditing

How to Create Shipping Burdens for Oracle Cost
Management, in Spite of Subledger Accounting!
Session ID#: 13944
Prepared by:
Doug Volz
Douglas Volz Consulting, Inc.
REMINDER
Check in on the
COLLABORATE mobile app
Learning Points
■ Learn about the business reasons for earning material
overheads at time of shipment and why your COGS and other
shipping expenses may be at a different from your inventory
costs or value
■ Understand the different approaches to solving this for
Release 12 and why this was much easier in Release 11i
■ Learn how to use SLA and other techniques for solving this
business requirement
■ Find out which alternative a recent client choose and contrast
this with other clients
Agenda
■ Shipping Burdens
▪ Definitions and Accounting Principles
▪ Why needed? Why Shipping Burdens?
▪ How does SLA work with Discrete Oracle Cost Management?
▪ Why was Release 11i so Much Easier?
▪ Business Solutions for Shipping Burdens
▪ Recent Client Experiences with Release 12
▪ Appendix:
—
Create Material Shipping Burden & SLA Entries
—
Discrete Cost Management SLA Model
—
Inventory Transactions Architecture
—
Create DFFs for Sub-Elements and Material Transactions
Doug Volz
Helping people use Oracle since 1990
■ Professional Summary
▪
30+ years industry, design and consulting experience, specializing in design,
implementation and project delivery for Cost Management business solutions
▪
Specific areas of expertise:
•
•
•
•
▪
▪
Profit in inventory
Intercompany
A/P accruals
WIP analysis
•
•
•
•
Multi‐org inventory reporting
Inventory reconciliation
Product Line & Margin analysis
Cost Rollup and Update
Presenter at Collaborate (OAUG) and UKOUG since 1996
Multi-national experience in twelve countries
■ Qualification Summary
▪
Former co-designer for Oracle Cost Management
▪
Lead the OAUG Cost Management Special Interest Group
▪
Cost Management industry experience
Helping people using Oracle Applications since 1990
Copyright ©2014 Douglas Volz Consulting, Inc.
+1 510 755 7050
doug@volzconsulting.com
www.volzconsulting.com
Douglas Volz Consulting, Inc.
„ Douglas Volz Consulting started in 2005 to provide:
ƒ
ƒ
ƒ
ƒ
ƒ
Cost Accounting Business & System Improvements
Procure to Pay Business Improvements
Project Management and Advisory Services
Multi-Org, Global Cost Reporting Solutions
Lots of free advice and conference papers:
• www.volzconsulting.com/resources.html
• www.volzconsulting.com/oaugcostsig.html
Sample Project Experience:
Helping people using Oracle Applications since 1990
Copyright ©2014 Douglas Volz Consulting, Inc.
Solutions You Can Use
─ http://oaug.org/education-events/cpd
2008 OAUG Collaborate & 2007 UKOUG: A/P Accruals
How to Setup, Use and Balance Your A/P Accrual Accounts (or How to Manage the Accounts from Hades)
2009, 2013 & 2014: OAUG Collaborate & UKOUG: Cost Management & SLA
2009: Cost Accoun ng As You Want It ─ EBS R12 Cost Accoun ng with SLA
2013: Subledger Accounting for Discrete Cost Accounting: Product Line Accounting Made Easy Through SLA 2014: How to Create Shipping Burdens for Oracle Cost Management, in Spite of Subledger
Accounting
2010: OAUG Collaborate: Profit in Inventory Solutions & PJM Solutions
Does Rel. 12 Solve Global Inter‐Company Issues for Multiple Ledgers, Profit in Inventory and COGS?
Can We Actually Reconcile Project MFG to Inventory, WIP, Projects & G/L? What Was I Thinking?
2011 & 2014: OAUG Collaborate & UKOUG: Inventory Reconciliation
2011: 60 Inventory Orgs? 6 Ledgers? No Worries, Reconcile Your Inventory With Ease!
2014: Reconcile Your Inventory to G/L Balances With Ease, From 1 to 1,000 Inventory Organizations!
2012: OAUG Collaborate: Transaction Interfaces for Period Close
Egads! How in the Dickens Do I Handle Those Month‐End Interfaces? (And Why Can’t I Close My Books)
Definitions and Accounting
Principles
Definitions
■ Burdens:
Another word for Overheads or Indirect Costs; as a general
term Overheads may include expenses related to product
procurement, production, warehousing or distribution as well as
non-product related expenses such as G&A and selling
expenses
■ Cost of Goods Sold (adapted from Wikipedia):
▪ The cost of products or raw materials, including freight or shipping
charges;
▪ The cost of storing products the business sells;
▪ Direct labor costs for workers who produce the products;
▪ Factory overhead expenses
▪ Discounts that must be deducted from the costs of purchased
inventory including trade discounts and manufacturer’s rebates
Definitions
■ Direct Product Costs:
Directly traceable product expenses such as direct material
(purchase cost), direct labor and other direct production costs
■ General & Administrative Expenses (G&A):
General operating expenses and taxes that are related to the
overall operation of the company
■ Handling Costs:
US GAAP – FAS605: “Costs incurred to store, move, and
prepare the products for shipment. Generally, handling costs
are incurred from the point the product is removed from
finished goods inventory to the point the product is provided to
the shipper and often include an allocation of internal
overhead.”
Definitions
■ Inventoriable Costs:
Acquisition and production costs included in the inventory
value, such as direct materials, direct labor, indirect
manufacturing overheads and indirect material handling costs
US GAAP – FAS 151: “Inventories are presumed to be stated at cost. The definition of
cost as applied to inventories is understood to mean acquisition and production cost
…”
IFRS – IAS2: “The cost of inventories shall comprise all costs of purchase, costs of
conversion and other costs incurred in bringing the inventories to their present
location and condition.”
Definitions
■ Non-Inventoriable Costs:
General and administrative (G&A) and selling expenses or any cost
which is not related to the acquisition or production product cost
US GAAP – FAS 151: “general and administrative expenses should be included as
period charges, except for the portion of such expenses that may be clearly related to
production and thus constitute a part of inventory costs (product charges). Selling
expenses constitute no part of inventory costs.”
IFRS – IAS2: “Certain costs are not included in the valuation of inventories. These
costs are recognized as expense when they occur. Following are some of the
common examples of these costs:
▪
Abnormal wastage of materials, labor and other production costs
▪
Storage costs if they are not essential for the production process
▪
Selling and distribution costs
▪
Administrative costs that are not involved in bringing the inventory to its present
condition and location”
Definitions
■ Selling Expenses:
Costs incurred to market products such as advertising, sales
commissions, sales salaries, sales offices and other selling
expenses
■ Shipping Costs:
US GAAP – FAS605: “Costs incurred to physically move the product
from the seller’s place of business to the buyer’s designated
location.”
Accounting Principles
■ Accrual Matching Principle & Revenue Recognition:
Must recognize appropriate costs when revenue is booked or
recognized. IFRS – IAS2: “When inventories are sold and revenue
is recognised, the carrying amount of those inventories is
recognised as an expense (often called cost-of-goods-sold).”
Why Needed? Why Shipping
Burdens?
Why Want Shipping Burdens?
■ Costs of shipping are significant but not part of inventory value
■ May want to recognize some MFG G&A costs at time of shipping
■ Suppliers may offer discounts based on shipping volumes or based
on the customer for that shipment
■ Trying to avoid “sales staff giving away the product”
Release 11i COGS Example:
Original Entries
Shipping Burdens
R11i COGS Entries
Sales Order Issue
DR COGS Account
CR Inventory
Debit / Credit Acct Line Type
100
100
Account
Inventory Valuation
Shipping Burden
10
DR COGS Account
CR Matl Overhead Absorption
10
Account
Ovhd Absorption
Release 12 COGS Example:
Original Entries
Shipping Burdens
R12 COGS Entries
Debit / Credit Acct Line Type
Sales Order Issue
DR Deferred COGS
CR Inventory
100
100
Deferred COGS
Inventory Valuation
100
COGS
Deferred COGS
COGS Recognition
DR COGS Account
CR Deferred COGS
100
Shipping Burden
10
DR COGS Account
CR Matl Overhead Absorption
COGS
10
Ovhd Absorption
Why Was Release 11i So Much
Easier for Adding Shipping
Burdens?
R10 – R11i Transaction Flow for Cost Mgmt
Enter Transaction(s)
Accounting
Processor
Receiving
Material
WIP
Transaction
Accounting Tables
Inventory
Period Close
Transfer to G/L
GL_
INTERFACE
G/L Tables
GL_SETS_OF_BOOKS
Journal
Import
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
Release 11i Solution
■ Enter Material Overhead Sub-Element(s) for Shipping Burdens
■ Enter and store the Shipping Burden rates in an unimplemented
cost type, a MFG Lookup Code or in a custom table
■ Create a custom program to insert additional rows into the
material accounting distributions:
Shipping Burden Example
DR COGS Account
(from existing transaction)
Debit / Credit Acct Line Type
10
CR Material Overhead Absorption
10
(from shipping burden sub-element)
COGS
Ovhd Absorption
■ All other processing is unchanged (except tweaks to Margin Reporting)
How Does SLA Work with
Oracle Cost Management?
Supply Chain Transaction Processing
■ Release 12 has two transaction models
▪ RCV, INV, WIP Transactions
▪ SLA transactions – “Mirror image” of the original transactions
SLA
PO Receipts
SO Shipmts
Inventory
LSPs, Others
RCV
INV
WIP
RCV
COST
Txn Sources
Supply Chain
INV
WIP
WIP
G/L
Key Concepts for SLA Transaction Types
Event Model:
A set of subledger transaction types with common characteristics
▪ Entity : Denotes the transaction source
▪ Event Class: Classifies transaction types by accounting rule
▪ Event Type: for each transaction type, defines possible actions
with accounting significance
EVENT
MODEL
ENTITY
Material, Receiving or WIP or Write Off
Transaction
EVENT
CLASS
Grouping of transaction events which
have similar kind of accounting
EVENT
TYPE
The most granular level of business
event which has accounting impact
R12 Transaction Flow for Cost Management
Enter Transaction(s)
Accounting
Processor
Transaction
Accounting Tables
Receiving
Material
WIP
Create
Accounting
Module
Specific
Transaction
Accounting
Tables
One Common
Accounting
Subledger
Table
SLA Accounting Tables
G/L Tables
XLA_EVENTS
GL_LEDGERS
XLA_AE_HEADERS
GL_
INTERFACE
GL_JE_BATCHES
XLA_AE_LINES
GL_JE_HEADERS
XLA_DISTRIBUTION
_LINKS
GL_JE_LINES
Setup and Process
ADR SETUPS
Define or Identify ADR
Sources
PROCESS
Enter Transaction(s)
JOURNAL ENTRY SETUPS
Define/copy and modify
Define/copy
and
modify
journal line
types
account derivation rules
Define/copy and modify
Define/copy
and modify
descriptions
journal line types
Define/copy and modify
descriptions
ASSIGNMENT
Assign to SLAM and Ledger
Cost Manager
Create Accounting*
Transfer Journal Entries to GL
*Run ‘Create Accounting – Cost Management’ concurrent request for
accounting all transactions from the Cost Management – SLA
responsibility.
Receiving Accounting can also be generated in the Purchasing
responsibilities using the ‘Create Accounting – Receiving’ concurrent
request.
These requests have an option to transfer the entries created to
General Ledger as well as post at the same time.
Cost Management and SLA Processing Steps
■ The Cost Manager creates the accounting entries for material
accounting distributions and calls a special program
(CST_XLA_PVT(CSTVXLAB.pls) to create the initial
information for Create Accounting (SLA)
MTL
MTL_MATERIAL_TRANSACTIONS
SLA
COST MANAGER
MTL_TRANSACTION_ACCOUNTS
XLA_EVENTS
XLA_TRANSACTION_ENTITIES
Cost Management and SLA Processing Steps
■ Create Accounting picks up the entries found in
XLA_EVENTS and XLA_TRANSACTION_ENTITIES and
completes the creation of the SLA accounting entries
XLA_EVENTS
CREATE ACCOUNTING
XLA_DISTRIBUTION_LINKS
XLA_AE_HEADERS
XLA_AE_LINES
Issues with SLA
■ Create Accounting never writes a processed status to either
MTL_TRANSACTION_ACCOUNTS or
XLA_DISTRIBUTION_LINKS
■ Create Accounting only knows the processing status for the
entire material transaction (XLA_EVENTS), but not for each
transaction accounting entry
▪ Process status by TRANSACTION_ID or EVENT_ID
▪ But not by each DR or CR or INV_SUB_LEDGER_ID
■ See next slide for desired transaction flow diagram
Desired Transaction Processing
– Create Accounting and SLA
Material Transactions
Cost Processor
Material Accounting Entries
1.
Cost Processor creates the material accounting entries
2.
Shipping Burdens are then created with a custom
program
3.
The custom program also calls the CSTVXLAB.pls
package to create the initial SLA information
4.
There are now two SLA events for the same material
transaction, one created by the Cost Processor and
one by the custom program
5.
Create Accounting processes the old and new entries
and creates the SLA journal entries with no duplication
Standard Program
Custom Program
Create New Matl Acct’g Entries
Create Accounting
SLA Journal Entries
Actual Transaction Processing
– Create Accounting and SLA
Material Transactions
Cost Processor
Material Accounting Entries
1.
Cost Processor creates the material accounting entries
2.
Create Accounting runs before the Shipping Burdens
are created and processes the existing SLA event and
material accounting entries
3.
Shipping Burdens are created with the custom program
4.
The custom program also calls the CSTVXLAB.pls
package to create the initial SLA information and a
second SLA event for the same material transaction
5.
Create Accounting processes the new entries, reprocesses the old entries and by doing so, doubles up
the SLA journal entries
Standard Program
Custom Program
Create Accounting
Create New Matl Entries
Create Accounting
SLA Journal Entries
Business Solutions for
Shipping Burdens
Potential Solutions
1. Do this adjustment in a management reporting system only
2. Include the Shipping Burdens in the Frozen Costs (inventory
value) and back it out at month-end
3. Create the Shipping Burdens in the original distribution tables
(mtl_transaction_accounts) and as SLA accounting entries
4. Only create the Shipping Burdens in the SLA accounting entries
(using the same SLA events for material transactions)
5. Interface manual SLA entries for Shipping Burden entries
6. Only create the entries into the G/L
Desired Solution Requirements
1. Want good visibility for Cost Accountants using standard
material distribution inquiries and reports
2. Follow the Oracle architecture for material distributions & SLA
Custom material distribution reporting (which combines the
original material distributions and SLA information) must work
the same way for Oracle and custom accounting distributions
3. As needed use SLA setups and processes
4. Minimize or avoid creation of custom tables
5. Limit the amount of custom code
Pros and Cons
1. Do this adjustment in a management reporting system only
▪ Not as easily accessed by all concerned parties
▪ Custom solution, all inputs, outputs, inquiries and reports
▪ Creates a condition where “source of truth” is in multiple places
▪ Not included in Margin Analysis Load and Report programs
2. Include the Shipping Burdens in the Frozen Costs (inventory
value) and back it out at month-end
▪ Source of truth in one location
▪ But requires month-end manual adjustment for US GAAP and IFRS
▪ Requires custom reporting (to back it out)
▪ May be best choice depending on complexity of number of
interfaces and customizations
▪ Margin Analysis Load and Report programs work with no changes
Pros and Cons
3. Create the Shipping Burdens in the original distribution tables
(mtl_transaction_accounts) and as SLA accounting entries
▪ Able to see these new entries in the original reports and inquiries
▪ But have to prevent Create Accounting from doubling-up entries
▪ SLA setups can be quite extensive and requires SLA expertise
▪ May need to modify Margin Analysis Load and Report programs
4. Only create the Shipping Burdens in SLA accounting entries
(using the same SLA events/material transactions)
▪ Will not show up in any original report or inquiry
▪ Needs lots of custom programming, SLA setups and development
▪ Needs custom reporting; different from normal Cost Mgmt entries
▪ Need to modify Margin Analysis Load and Report programs
Pros and Cons
5. Interface manual SLA entries for Shipping Burden entries
▪ One of the easiest solutions (open interface)
▪ Lack of visibility, needs custom reporting, not easily accessed
▪ Not directly tied to the actual COGS entries
▪ Cannot easily use this information for margin and performance
reporting
6. Only create the entries into the G/L
▪ Not in a subledger
▪ Lack of visibility, needs custom reporting, not easily accessed
▪ Not directly tied to the actual COGS entries
▪ Cannot easily use this information for margin and performance
reporting
Recent Client Experiences with
Release 12
Release 12 Client Experiences
■ Tried third solution, new material distributions and SLA entries
▪ Client had too many customizations, both with the material
transactions and as post-processing customizations
▪ Even needed to change stored transaction cost information (in
mtl_material_transactions) in addition to changing three major
custom interfaces
▪ At the time could not prevent SLA from doubling-up entries
■ Second solution ended up being easier, put into Frozen costs
▪ As new sub-element, put burdens into the Frozen costs
▪ Designed month-end inventory value reports to back out these
costs at month-end
▪ Designed monthly material account summary reports for visibility
Summary
Summary
■ Life with SLA can be very useful:
▪ Configure transactions to use new accounts
▪ Multiple representations for the same transactions (by chart of
accounts)
▪ Better mapping capabilities, can do outside of consolidations
▪ Can even create new sources, even integrate entirely new nonOracle modules and transactions (with FIN Hub)
■ But SLA can be devilishly complex, you have to weigh the
pros and cons
■ Make the best choice for your situation, there is not one
“right” answer
Appendix
A) Create Material Shipping Burden & SLA Entries
B) Discrete Cost Management SLA Model
C) Inventory Transactions Architecture
D) Creating DFFs for Material Transactions and Sub-Elements
Appendix
A) Create Material Shipping Burden & SLA Entries
B) Discrete Cost Management SLA Model
C) Inventory Transactions Architecture
D) Creating DFFs for Material Transactions and Sub-Elements
A) Create Material Shipping Burden & SLA Entries
■ Required Steps
1. Enter Material Overhead Sub-Elements for Shipping Burdens
2. Identify which transactions to apply shipping burdens
3. Enter and store the Shipping Burdens
4. Create custom program to insert additional rows into the material
accounting distributions, using the same material transaction
5. Configure SLA to process additional material accounting entries
ƒ Add Journal Lines as needed
ƒ
Add logic to prevent double-processing
■ Caveat: This Solution is a Prototype, Requires Testing
1) Enter Material Overhead Sub-Elements for
Shipping Burdens
Added a DFF to avoid hard‐
coding the Sub‐Element name
Choose Material Txn Types for Burdens
Click here
2) Identify Which Transactions to Apply
Shipping Burdens
■ Use the Appendix Section “Discrete Cost Management SLA Model”
▪ Identify which SLA Event Class Names: Sales Order Issue
▪ Identify which Event Type Name
—
COGS Recognition
—
RMA Receipt
—
RMA Issue
▪ Identify which SLA Journal Line Types to change:
—
Cost of Goods Sold, Deferred COGS, Cost Update Adjustment
Identify Which Transactions to Apply
Shipping Burdens (Cont’d)
■ Have to Correlate SLA Events With “Real” Material Transactions
Material
Transaction
Definition
Material Transactions
• COGS Recognition
ƒ RMA Receipt
ƒ RMA Return
SLA Event Class Name
SLA Event
Definition
ƒ Sales Order Issue
• COGS Recognition
ƒ RMA Receipt
ƒ RMA Return
Accounting Line Type
ƒ Cost of Goods Sold
ƒ Deferred COGS
ƒ Overhead Absorption
(New)
Journal Line Type
ƒ Cost of Goods Sold
ƒ Deferred COGS
ƒ Overhead Absorption
(New)
Î The Oracle EBS Supply Chain transactions loosely correlate to
the SLA events and journal lines. Can be confusing.
Release 12 COGS Example:
Don’t Want to Mess With Deferred COGS
Original Entries
Shipping Burdens
R12 COGS Material Transaction Entries
Debit / Credit Acct Line Type
Sales Order Issue Txn
DR Deferred COGS
CR Inventory
100
100
Deferred COGS
Inventory
100
COGS
Deferred COGS
COGS Recognition Txn
DR COGS Account
CR Deferred COGS
100
Shipping Burden (added to COGS Recognition Txn)
10
DR COGS Account
CR Matl Overhead Absorption
COGS
10
Ovhd Absorption
3) Enter and Store the Shipping Burden Rates
■ Enter the Rates in a Non-Implemented Cost Type
■ Store by Item in Only One Inventory Organization
Uncheck Multi‐Org
Enter and Store the Shipping Burden Rates (Cont’d)
■ Enter the rate by cost type by item (for this example)
■ You could also use a MFG Lookup Code, custom table …
Example Values From CST_ITEM_COST_DETAILS
CST_ITEM_COST_DETAILS
INVENTORY_ITEM_ID
EXAMPLE
VALUE
11923
MTL_TRANSACTION
_ACCOUNTS
COMMENTS
INVENTORY_ITEM_ID
MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID
207
ORGANIZATION_ID
MTL_PARAMETERS
COST_TYPE_ID
1207
N/A
CST_COST_TYPES
1
N/A
This Level
RESOURCE_ID
Sub‐Element Identifier
RATE_OR_AMOUNT
USAGE_RATE_OR_AMOUNT X Original COGS BASE_TRANSACTION_VALUE
LEVEL_TYPE
RESOURCE_ID
USAGE_RATE_OR_AMOUNT
78542
0.1
BASIS_TYPE
5
BASIS_TYPE
Percent of Total Value
COST_ELEMENT_ID
2
COST_ELEMENT_ID
Material Overhead
ROLLUP_SOURCE_TYPE
1
N/A
User Defined
4) Create Custom Program to Insert Additional
Accounting Entries
INSERT INTO mtl_transaction_accounts
■ Use existing material transactions
■ Adding new material accounting
entries using the same material
TRANSACTION_ID
■ On the material transaction update
ATTRIBUTE15 to indicate Shipping
Burdens have been recorded
■ BASE_TRANSACTION_AMOUNT =
the item’s RATE_OR_AMOUNT X
original COGS amount
VALUES
(transaction_id,
(v_trx_id,
reference_account,
v_adjust_account,
last_update_date,
SYSDATE,
last_updated_by,
fnd_profile.VALUE('USER_ID'),
creation_date,
SYSDATE,
created_by,
fnd_profile.VALUE('USER_ID'),
last_update_login,
fnd_profile.VALUE('USER_ID'),
inventory_item_id,
v_mtl.inventory_item_id,
organization_id,
v_mtl.organization_id,
transaction_date,
v_mtl.transaction_date,
transaction_source_id,
v_mtl.transaction_source_id,
transaction_source_type_id,
v_mtl.transaction_source_type_id,
transaction_value,
v_trx_val,
primary_quantity,
v_trx_qty,
gl_batch_id,
-1,
accounting_line_type,
3, -- Material Overhead Absorption
base_transaction_value,
v_trx_val,
contra_set_id,
v_mtl.contra_set_id,
rate_or_amount,
v_trx_rate,
basis_type,
v_basis_type,
resource_id,
v_resource_id,
cost_element_id,
2,
activity_id,
NULL,
currency_code,
v_mtl.currency_code,
currency_conversion_date,
v_mtl.currency_conversion_date,
currency_conversion_type,
v_mtl.currency_conversion_type,
currency_conversion_rate,
v_mtl.currency_conversion_rate,
request_id,
v_request_id,
program_application_id,
fnd_profile.VALUE('PROGRAM_APPLICATION_ID'),
program_id,
fnd_profile.VALUE('PROGRAM_ID'),
program_update_date,
SYSDATE,
encumbrance_type_id,
v_mtl.encumbrance_type_id,
repetitive_schedule_id,
v_mtl.repetitive_schedule_id,
gl_sl_link_id)
'');
Values for MTL_TRANSACTION_ACCOUNTS
MTL_TRANSACTION_ACCOUNTS
SOURCE
COMMENTS
TRANSACTION_ID
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
REFERENCE_ACCOUNT
ABSORPTION_ACCOUNT
From BOM_RESOURCES
INVENTORY_ITEM_ID
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
ORGANIZATION_ID
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
TRANSACTION_DATE
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
TRANSACTION_SOURCE_ID
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
TRANSACTION_SOURCE_TYPE_ID
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
TRANSACTION_VALUE
MTL_TRANSACTION_ACCOUNTS
NULL for COGS
PRIMARY_QUANTITY
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
GL_BATCH_ID
MTL_TRANSACTION_ACCOUNTS
NULL
ACCOUNTING_LINE_TYPE
CST_ACCOUNTING_LINE_TYPE
3 – overhead absorption
35 – cost of goods sold
BASE_TRANSACTION_VALUE
USAGE_RATE_OR_AMOUNT X For Basis Type “Percent of Total Value”
COGS BASE_TRANSACTION_VALUE X SIGN(PRIMARY_QUANTITY)
Values for MTL_TRANSACTION_ACCOUNTS
MTL_TRANSACTION_ACCOUNTS SOURCE
COMMENTS
CONTRA_SET_ID
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
RATE_OR_AMOUNT
USAGE_RATE_OR_AMOUNT
From the item cost
BASIS_TYPE
CST_BASIS_TYPES Lookup Code
5 for “% of Total Value”
RESOURCE_ID
BOM_RESOURCES.RESOURCE_ID
Sub‐Element Identifier
COST_ELEMENT_ID
BOM_RESOURCES.
COST_ELEMENT_ID
Usually a value of 2 (Material Overhead)
CURRENCY_CODE
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
CURRENCY_CONVERSION_DATE
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
CURRENCY_CONVERSION_TYPE
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
CURRENCY_CONVERSION_RATE
MTL_TRANSACTION_ACCOUNTS
Same as COGS entry
GL_SL_LINK_ID
Leave NULL
Create Accounting / GL
INV_SUB_LEDGER_ID
Leave NULL
From CSTVXLAB.pls
5) Steps to Configure SLA
Create custom PL/SQL function
Define custom sources
Can skip this step
Create account derivation rules (ADRs)
Create journal line types (JLTs)
Create journal line definitions (JLDs)
Create an application accounting definition (AAD)
Create a subledger accounting method (SLAM)
Assign it to a Ledger
Desired Material Transaction Flow
Material Transactions
Cost Processor
No
Material Accounting Entries
Don’t Process the Journal Line
Shipping Burdens Exist?
A
Yes
Standard Program
Custom Program
New SLA Conditions
Do Process the Journal Line
Create Accounting
A
Create New Material Accounting Entries
Create Accounting
SLA Journal Entries
A
Create Custom PL/SQL Function
Create custom PL/SQL function
Define custom sources
Create account derivation rules (ADRs)
Create journal line types (JLTs)
Create journal line definitions (JLDs)
Create an application accounting definition (AAD)
Create a subledger accounting method (SLAM)
Assign it to a Ledger
Create Custom PL/SQL Function
■ Will determine if SLA can process the journal line
CREATE OR REPLACE FUNCTION XXX_CST_CNTRL_CREATE_ACCTG (p_transaction_id IN NUMBER) RETURN VARCHAR2 is
l_yes_no varchar2(2);
BEGIN
SELECT
FROM
WHERE
;
nvl((select DISTINCT('Y')
from
inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_transaction_accounts mta
where mmt.transaction_id
= p_transaction_id
and
mmt.transaction_type_id = mtt.transaction_type_id
and
mmt.transaction_id
= mta.transaction_id
and
((nvl(mtt.attribute1,'N') = 'Y' -- indicates this transaction is for Shipping Burdens
and
nvl(mmt.attribute15,'N') = 'Y' -- indicates shipping burden exist
)
OR
(nvl(mtt.attribute1, 'N') = 'N' -- indicates not for Shipping Burdens
)
)
), 'N'
)
into l_yes_no
inv.mtl_material_transactions mmt
mmt.transaction_id
= p_transaction_id
RETURN l_yes_no;
END XXX_CST_CNTRL_CREATE_ACCTG;
/
Define Custom Sources
Menu path: Cost Management SLA => Setup => Accounting Methods Builder => Sources =>
Custom Sources
■ Need to use the correct parameters for your PL/SQL inputs
Create Journal Line Types and Definitions
Create custom PL/SQL function
Define custom sources
Create account derivation rules (ADRs)
Create journal line types (JLTs)
Create journal line definitions (JLDs)
Create an application accounting definition (AAD)
Create a subledger accounting method (SLAM)
Assign it to a Ledger
Determine Which Journal Lines to Change
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Journal Lines Definitions
■ Query the Event Class / Sales Order Issue
Need User-Defined Journal Lines
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Journal Lines Definitions
■ Sales Order Issue Event Class - Two Material Txn Types
▪ Sale Order Issue (Shipment)
—
Deferred COGS (DR)
—
Inventory Valuation (CR)
▪ COGS Recognition
—
Cost of Goods Sold (DR)
—
Deferred COGS (CR)
—
Overhead absorption (CR)
▪ RMAs with Cost Changes
—
Cost Update Adjustment
Need New User-Defined Journal Lines
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Journal Lines Definitions
■ Copy and Change These Journal Lines for COGS
Recognition
▪ Cost of Goods Sold
▪ Deferred COGS
▪ Cost Update Adjustment (for RMAs)
■ Create New Journal Line for Material Overhead Absorption as
this is not in the SLA transaction model for Sales Orders
Create User-Defined Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Query Up Each Journal Line and Copy the Oracle Journal
■ Choose the Event Class Sales Order Issue
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Copy the Oracle Journal Line Type – Cost of Goods Sold
2) Enter Line Type Code, Name and
Description
3) Click Done
1) Click Copy
Definition
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Add New Conditions to the Journal Line Type
Click Conditions
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Add New Conditions to the Journal Line Type (Cont’d)
Indicates if the journal line y
p
is ready to be processed
Ensures that COGS is only charged for the COGS Recognition Transaction Type
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Copy the Oracle Journal Line Type – Deferred COGS
2) Enter Line Type Code, Name and
Description
3) Click Done
1) Click Copy
Definition
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Add New Conditions to the Journal Line Type
Click Conditions
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Add New Conditions to the Journal Line Type (Cont’d)
Indicates if the journal line y
p
is ready to be processed
Ensures that Deferred COGS is only charged g
yp
for the COGS Recognition Transaction Type
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Copy the Oracle Journal Line Type – Cost Update Adjustment
2) Enter Line Type Code, Name and
Description
3) Click Done
1) Click Copy
Definition
Copy Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Add New Conditions to the Journal Line Type (Cont’d)
Indicates if the journal line y
p
is ready to be processed
Ensures that RMA Cost Updates are only changed g
yp
for the COGS Recognition Transaction Type
New Journal Line Type for Overhead
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Create the New Journal Line Type
New Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Add Conditions to the Journal Line Type
Click Conditions
New Journal Line Types
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Add Conditions to the Journal Line Type (Cont’d)
Overhead absorption accounting line type
Indicates if the journal line y
p
is ready to be processed
Ensures that Overhead Absorption is only charged g
yp
for the COGS Recognition Transaction Type
Create Journal Line Types and Definitions
Create custom PL/SQL function
Define custom sources
Create account derivation rules (ADRs)
Create journal line types (JLTs)
Create journal line definitions (JLDs)
Create an application accounting definition (AAD)
Create a subledger accounting method (SLAM)
Assign it to a Ledger
Create Journal Line Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Methods and Definitions => Journal Line Definitions
■ Copy the Oracle Journal Line Definition
2) Enter Definition Code, Name and
Description
3) Click Done
1) Click Copy
Definition
Create Journal Line Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Replace the Oracle Journal Line Types with User Defined
Use default ADR for Cost g
Mgmt
Create Journal Line Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Delete Oracle Journal Line Type and Replace with User JL Type
Use default ADR for Cost g
Mgmt
Create Journal Line Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Delete Oracle Journal Line Type and Replace with User JL Type
Use default ADR for Cost g
Mgmt
Create Journal Line Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Delete Oracle Journal Line Type and Replace with User JL Type
Use default ADR for Cost g
Mgmt
Create Journal Line Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Delete Oracle Journal Line Type and Replace with User JL Type
Use default ADR for Cost g
Mgmt
Create Journal Line Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Journal Entry Setups => Journal Line Types
■ Cost Variance and Inventory Valuation Journal Types
Cost Variance and Inventory Valuation is unchanged
Create Application Accounting Definition
Create custom PL/SQL function
Define custom sources
Create account derivation rules (ADRs)
Create journal line types (JLTs)
Create journal line definitions (JLDs)
Create an application accounting definition (AAD)
Create a subledger accounting method (SLAM)
Assign it to a Ledger
Create Application Accounting Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Application Accounting Definitions
■ Copy the standard Oracle Application Accounting Definition
2) Enter Definition
Code, Name and
Description
This limits it to only one COA Structure
3) Click Done
1) Click Copy
Definition
Create Application Accounting Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Application Accounting Definitions
■ Validate the Copied Application Accounting Definition
Validate initial copy
Create Application Accounting Definition
■ Now assign new Journal Lines Definition to your Application
Accounting Definition:
■ COGS Sale Order Event Accounting Examples:
Sales Order Issue => XXX Sales Order Issue
■ Note: there are other COGS-related Event Classes:
▪ Logical Transactions
▪ Sender-side Intransit Interorg Shipment for FOB Receipt
▪ Sender-side Intransit Interorg Receipt for FOB Receipt
Create Application Accounting Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Application Accounting Definitions
Choose the Event Class to change
Then delete the existing row
Create Application Accounting Definition
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Application Accounting Definitions
To catch errors should validate one‐
by‐one
y
Replace with new assignment
Create Application Accounting Definition
Menu path: Cost Management SLA => Requests => Submit a New Request =>
Validate Application Accounting Definitions
■ Another way to Validate Application Accounting Definitions
Create Application Accounting Definition
Menu path: Cost Management SLA => Requests => Submit a New Request =>
Validate Application Accounting Definitions
These should all have a Valid status
Create Subledger Accounting Method (SLAM)
Create custom PL/SQL function
Define custom sources
Create account derivation rules (ADRs)
Create journal line types (JLTs)
Create journal line definitions (JLDs)
Create an application accounting definition (AAD)
Create a subledger accounting method (SLAM)
Assign it to a Ledger
Create Subledger Accounting Method
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Subledger Accounting Methods
2) Enter Definition
Code, Name and
Description
3) Click Done
1) Click Copy
Definition
Assign SLAM to a Ledger
Create custom PL/SQL function
Define custom sources
Create account derivation rules (ADRs)
Create journal line types (JLTs)
Create journal line definitions (JLDs)
Create an application accounting definition (AAD)
Create a subledger accounting method (SLAM)
Assign it to a Ledger
Create User-Defined SLAM
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Subledger Accounting Methods
2) Enter Method
Code, Name and
Description
3) Click Done
1) Click Copy
Definition
Assign Ledger to SLAM
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder =>
Methods and Definitions => Subledger Accounting Methods => Accounting Setups
Assign Ledger to SLAM
Menu path: Cost Management SLA => Accounting Setup => Accounting Methods Builder => Methods and Definitions =>
Subledger Accounting Methods => Accounting Setups
Click on the “pencil” to update the ledger
Assign Ledger to SLAM
Click on the “pencil” to update the ledger
Assign Ledger to SLAM
Select the new SLAM
Select the new SLAM
Testing Your SLA Setups
■ If You AAD Validation Fails
▪ Check the Output File
Check Output
Testing Your SLA Setups – Validation Output
■ Valuable Clues
Review error text
Testing Your SLA Setups
■ Diagnostic Reports to SLA test inputs and outputs
▪ Enable profile option “SLA: Enable Diagnostics”
▪ Run “Create Accounting”
▪ Disable profile option “SLA: Enable Diagnostics”
▪ Run “Transaction Objects Diagnostics” report
▪ Run “Purge Transaction Objects Diagnostics”
Appendix
A) Create Material Shipping Burden & SLA Entries
B) Discrete Cost Management SLA Model
C) Inventory Transactions Architecture
D) Creating DFFs for Material Transactions and Sub-Elements
B) Discrete Cost Management SLA Model
Discrete Cost Management SLA Model
Discrete Cost Management SLA Model
Discrete Cost Management SLA Model
Discrete Cost Management SLA Model
Discrete Cost Management SLA Model
Discrete Cost Management SLA Model
Discrete Cost Management SLA Model
Appendix
A) Create Material Shipping Burden & SLA Entries
B) Discrete Cost Management SLA Model
C) Inventory Transactions Architecture
D) Creating DFFs for Material Transactions and Sub-Elements
C) Inventory Transactions Architecture
■ Inventory allows you to define new transaction types
Sources
+
Actions
=
Transaction Types
▪ As a result, material transactions are more complex
▪ You can query material transactions by Source or Transaction Type
▪ Sources are a group of transactions based on a common origin
▪ Actions indicate the behavior for the transaction
(decrease or increase onhand quantities for example)
▪ Transaction types are the individual kinds of entries for each
transaction source
▪ Material transactions are defined in MTL_TRANSACTION_TYPES
Inventory Transactions Architecture Defined By:
Sources and Actions Define Over 90 Material Transaction Types
ƒ
Sources
Account
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
Account Alias
Cycle Count
Internal Order
Internal Requisition
Inventory
Job or Schedule
Move Order
Physical Inventory
Purchase Order
RMA
Sales Orders
Standard Cost Update
Sample List of Actions
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
Assembly completion
ƒ
Assembly return
ƒ
COGS recognition
ƒ
Cost update
Cycle count adjustment
ƒ
Delivery adjustments
Direct organization xfer ƒ
ƒ
Intransit receipt
ƒ
Intransit shipment
Issue from stores
ƒ
Logical Intransit Receipt ƒ
Logical Intransit Shipment
Logical Delivery
ƒ
Adjustment
ƒ
Logical Expense
ƒ
Requisition Receipt
ƒ
Logical Intercompany
ƒ
Receipt
Logical Intercompany
Receipt Return
Logical Intercompany Sales
Logical Intercompany Sales
Return
Logical Issue
Logical Receipt
Negative Component Issue
Negative Component
Return
Ownership Transfer
Physical Inventory
Adjustment
Receipt into Stores
Retroactive Price Update
Staging Transfer
Subinventory Transfer
WIP Scrap Transaction
So Why Care About Inventory Txn Architecture?
Menu path: Cost Management – SLA => View Transactions => Material Transactions
■ Use Transaction Sources and Transaction Types for Online Queries
So Why Care About Inventory Txn Architecture?
Menu path: Cost Management – SLA => View Transactions => Material Transactions
■ Use Transaction Sources and Transaction Types for Online Queries
List of Sources and Transaction Types
List of Sources and Transaction Types (cont’d)
Accounting Line Types
ƒ
Define the Purpose for the Accounting Entry
ƒ Use these values to understand the accounting entry
ƒ Example: COGS Recognition Transaction
Menu path: Cost Management – SLA => View Transactions => Material Distributions
List of
R12 Accounting
Line Types
Appendix
A) Create Material Shipping Burden & SLA Entries
B) Discrete Cost Management SLA Model
C) Inventory Transactions Architecture
D) Creating DFFs for Material Transactions and Sub-Elements
Sub-Elements:
Enable DFF to Indicate a Shipping Burden
Sub-Elements:
Enable DFF to Indicate a Shipping Burden (Cont’d)
Unclick here
Sub-Elements:
Enable DFF to Indicate a Shipping Burden (Cont’d)
Use an existing value set
Sub-Elements:
Enable DFF to Indicate a Shipping Burden (Cont’d)
Unclick here
Change this Change these values
values
Sub-Elements:
Enable DFF to Indicate a Shipping Burden (Cont’d)
Sub-Elements:
Enable DFF to Indicate a Shipping Burden (Cont’d)
Click here to refreeze
Enable DFF for Material Transaction Types
Unclick
here
Enable DFF for Material Txn Types (Cont’d)
Change this Change these values
values
Enable DFF for Material Txn Types (Cont’d)
Click here to refreeze
Acknowledgements
■ Mohan Iyer and John Peters
– Paper review and support of shared Vision environment
■ Ashish Pathak and Anup Jha – Oracle Cost Development
– Workarounds with SLA and Create Accounting limitations
Any Questions?
Doug Volz
doug@volzconsulting.com
www. volzconsulting.com
+1 510 755‐7050
Please complete the session
evaluation
We appreciate your feedback and insight
You may complete the session evaluation either
on paper or online via the mobile app