Wednesday, October 24, 2018

HOW AND WHEN COGS ACCOUNT HIT FOR TRANSACTIONS


Inventory Interface generates the cost of goods sold account for transactions when passing the transactions to Oracle Inventory. Receivables Interface generates a receivable account, a revenue account, a tax account, a freight account, and others, but not the cost of goods sold account. Ship confirm and pick release do not generate any accounts. 
___________________________________________________________________ 
Oracle Application R12 COGS 
In Oracle Application R12 COGS process has been changed. Reason for that are aggressive revenue
recognition practices as well as the guidelines from various governing bodies.
Till R11 Cost of goods sold has been recognized as soon as the Order line has shipped, as shown in below
steps
After ship confirm, user run the interface trip stop (ITS).
ITS in turns run the OM Interface and Inventory Interface.
Inventory Interface calls Inventory transaction manager which in turns call COGS WF.
But as per new practices COGS should be recognized along with the revenue.
In R12 used need to define deferred cogs account. These deferred cogs account can be defined at each
inventory org level.
During shipping process Inventory tables will hold the deferred COGS accounts. Only after invoicing has
done in AR, AR will notify the Costing and Costing in turns call the COGS account generator to get the
cogs account .In that way COGS and revenue will be recognized in the same period.
There are few exceptions like how to get the COGS for 
1. Ship only line (No Invoice will be created).  
To handle above cases Close-line activity of the order line workflow has modified to call the costing API
to get the cogs value  
What is the Deferred COGS account in R12 
The deferred COGS of goods account is the new feature introduced in Release 12. The basic
fundamental behind the enhancement is that the COGS is now directly matched to the Revenue. The
same was not possible till now.
Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS upon
ship confirm, despite the fact that revenue may not yet have been earned on that shipment. With this
enhancement, the value of goods shipped from inventory will be put in a Deferred COGS account. As
percentages of Revenue are recognized, a matching percentage of the value of goods shipped from
inventory will be moved from the Deferred COGS account to the COGS account, thus synchronizing 
the recognition of revenue and COGS in accordance with the recommendations of generally accepted
accounting principles.
The Matching Principle is a fundamental accounting directive that mandates that revenue and its
associated cost of goods sold must be recognized in the same accounting period. This enhancement will
automate the matching of Cost of Goods Sold (COGS) for a sales order line to the revenue that is billed
for that sales order line.
The deferral of COGS applies to sales orders of both non-configurable and configurable items (Pick-To Order and Assemble-To-Order). It applies to sales orders from the customer facing operating units in the 
case of drop shipments when the new accounting flow introduced in 11.5.10 is used. And finally, it also
applies to RMAs that references a sales order whose COGS was deferred. Such RMAs will be accounted
using the original sales order cost in such a way that it will maintain the latest known COGS recognition percentage.
To set the deferrred COGS account.
 Inventory –Setup–Organization–Parameters–Other Accounts
A new account is added which is referred as the Deffered COGS accounts.
NEW ACCOUNTING:
Release 12 :
  
When a Sales order is shipped the following accounting takes place:
Inventory Valuation Account : Credit.  
Deferred COGS account : Debit
Once the revenue is recognised, you would need to decide the percentage you wish to recognize the
Revenue. A COGS recognition transaction will be created to reflect a change in the revenue recognition
percentage for a sales order line.
The steps to generate such transactions are as follows: 
1. Run the Collect Revenue Recognition Information program. This program will collect the change in
revenue recognition percentage based on AR events within the user specified date range. 
2. Run the Generate COGS Recognition Events. This program will create the COGS recognition
transaction for each sales order line where there is a mismatch between the latest revenue recognition
percentage and the current COGS recognition percentage.
Note that users can choose how often they want to create the COGS Recognition Events. 
Navigation to run the COGS recognition request : 
– Cost > COGS Recognition > Collect Revenue Recognition Information 
– Cost > COGS Recognition > Generate COGS Recognition Events 
– Cost > View Transactions > Material Transactions
The distribution for the COGS Recognition transaction associated with the Sales Order transaction now
would be as follows:
Deffered COGS : Debit y revenue percentage 
COGS : Credit (Actual revenue percentage )
Thus, essentially the recognized COGS balance is to move the value from Deferred COGS to COGS.
This particular COGS recognition transaction actually correspond to a revenue recognition percentage
change.
You can view the transactions as : 
Navigation: 
– Cost > View Transactions > Material Transactions > Distributions
A new COGS Revenue Matching Report shows the revenue and COGS information of sales order that fall
within the user specified date range by sales order line
SIMPLER TERMS ( Table level details ) :
Once the whole cycle is complete we will have 2 transactions lines in mtl_material_transactions.
1. Sales Order 
2. COGS Recognition transaction
Accounting will be in mtl_transaction_accounts and the Subledger accounting tables as follows: 
Transaction 1: 
Inventory Valuation Account : Credit. (item_cost) 
Deferred COGS account : Debit (item_cost)
Transaction 2: 
Deffered COGS : Credit (Actual revenue percentage) 
COGS : Debit (Actual revenue percentage )
COGS (Cost of Goods Sold) in Oracle E-Business Suite Release 12 
Deferred COGS is a new feature introduced in Oracle E-Business Suite Release 12. The basic
fundamentals behind the enhancement are that the COGS are now directly matched to the
Revenue. 
  
Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS
upon ship confirm, despite the fact that revenue may not yet have been earned on that shipment.
With this enhancement, the value of goods shipped from inventory will be put in a Deferred
COGS account. As percentages of Revenue are recognized, a matching percentage of the value
of goods shipped from inventory will be moved from the Deferred COGS account to the COGS
account, thus synchronizing the recognition of revenue and COGS in accordance with the
recommendations of generally accepted accounting principles. 
  
While this helps solve some key accounting issues, there are some key issues one needs to be
aware of: 
    
• Currently Deferred COGS accounting cannot be turned off in Oracle EBS Release 12. 
• The activity of recording COGS recognition is now a multi-step process 
• Run AR Revenue Recognition, and Submit Accounting Processes 
• Run a set of concurrent processes in Cost Manager to record Sales Order and revenue
recognition transactions and to create and cost COGS recognition transactions. These
COGS recognition transactions adjust deferred and earned COGS in an amount that
synchronizes the % of earned COGS to earned revenue on Sales Order shipment lines. 
• Record Order Management Transactions: records new sales order transaction activity
such as shipments and RMA returns in Oracle Order Management. 
• Collect Revenue Recognition Information: determines the percentage of recognized or 
earned revenue related to invoiced sales order shipment lines in Oracle Receivables. 
• Generate COGS Recognition Events: creates and costs COGS recognition events for
new sales order shipments/returns and changes in revenue recognition and credits for
invoiced sales order shipment lines. 
The end result of these activities is a series of COGS Recognition Material Distributions.
However these distributions will not be visible on the Material Transaction screen, unless the
‘Include Logical Transaction’ checkbox is checked.
R12 Order Management: Revenue-COGS Matching Part I 
It is a relief to see this much awaited functionality. We heard our accounting departments
complaining about the period mismatches in for our COGS and Revenue accounting for one
order. We ship an order on the last day of the month and COGS gets posted to this month, but if
the invoice is created with next period’s GL date as the current AR period is closed by the time
the order is invoiced. Now all that is changed. Revenue-COGS matching is a standard
functionality now. In simple terms, this means, COGS for an order line will be recognized only if
the revenue is recognized for that line making sure that the revenue and COGS are posted in the
same month. 
All of us have spent a lot of time working on COGS accounting workflow to achieve what we
want for our clients/companies. In some cases we even customized Revenue accounting
generation (avoiding auto accounting logic) by using ra_interface, distributions_all table. We had
a handle on accounts generation in this process but not on the actual events of accounting
recognition. 
We all know this.
When we ship the order and run the Interface Trip Stops program, inventory gets reduced and
orders get updated to move forward in the workflow to the next activity. Interface Trip Stops
program calls the OE_FLEX_COGS_PUB to generate the COGS account as per design. This
gets passed on to the mtl_material_transactions table in the distribution_account column. When
Cost Manager runs, distribution_account from mtl_material_transactions is picked up to generate
accounting as shown. 
                                Cr Inventory Material account $100
                                            Dr COGS Account $100 
The role of COGS
workflow is not changed. It is still the same which generates the account of our choice per
workflow design. It still passes the generated account to the mtl_material_transactions table into
the distribution_account column. But what changed in R12 is accounting. In order to match
Revenue with COGS accounting in terms of timing, COGS account cannot be used at the time
shipping. Instead revenue recognition process of the invoice for that order line should generate
COGS accounting. 
To achieve this, a new account called Deferred COGS account is introduced at the inventory
organization parameters level. So when the order shipped instead of the above entries the entry
will be 
                       Cr Inventory Material account             $100
                                    Dr Deferred COGS Account    $100 
When you invoice is this order line, if you have no revenue recognition policies or specialized
accounting rules, revenue should be instantly recognized (upon running revenue recognition
program). 
After revenue is recognized, the following programs need to be run to relieve deferred COGS
value and debit actual COGS account. 
Record Order Management Transactions: This program collects all the transactions that
belong to transaction types Sales order issue and Logical Sales Order Issue which are not costed
and the order line is invoiceable. The source table is mtl_material_transactions. This program 
inserts rows into two tables: cst_cogs_events and cst_revenue_cogs_match_lines. This program
is not necessary to run. When not run, Cost Manager will insert rows into these tables. So from
implementation considerations, this program is not required to be run. 
Collect Revenue Recognition Information: This program collects invoice line information of
the order line after the revenue is recognized. The source tables are ra_customer_trx_lines_all
and ra_cust_trx_line_gl_dist_all. It will check the percentage of the revenue recognized (we can
recognize revenue partially for a specific order line based on accounting rule or contingency
rules) and inserts that information into this table: cst_revenue_recognition_lines. Also the table
cst_revenue_cogs_control table is updated with the latest run date with high date of this
parameter, which is used in the next run of the same program. 
Generate COGS
Recognition Events: The role of this program is to record a logical material transaction, which
is used to create final COGS entry. This program takes information from the above tables and
creates one logical inventory transaction in mtl_material_transactions with a new transaction
type called COGS Recognition. In the same program these transactions will be costed (not by the
cost manager) creating the following accounting entries. The COGS account in this entry is taken
from the distribution_account in mtl_material_transactions table (which was generated earlier by
COGS workflow). 
                                Cr Deferred account                     $100
                                              Dr COGS Account             $100 
This is the concept in simple terms. There are different cases (well documented in the Cost
Management User Guide) in this same flow which, I will take one at a time to discuss in the
coming posts. 
SQL statements that help understand the data model are below, 
SELECT header_id
  FROM oe_order_headers_all
 WHERE order_number = &your_order_number;
SELECT line_id
  FROM oe_order_lines_all 
 WHERE header_id = (SELECT header_id
                      FROM oe_order_headers_all
                     WHERE order_number = &your_order_number);
SELECT *
  FROM mtl_material_transactions
 WHERE trx_source_line_id IN (SELECT line_id
                                FROM oe_order_lines_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number =
&your_order_number))
       AND transaction_type_id IN (33, 10008);
SELECT *
  FROM mtl_transaction_accounts
 WHERE transaction_id IN (
          SELECT transaction_id
            FROM mtl_material_transactions
           WHERE trx_source_line_id IN (SELECT line_id
                                          FROM oe_order_lines_all
                                         WHERE header_id = (SELECT header_id
                                                              FROM
oe_order_headers_all
                                                             WHERE
order_number = &your_order_number))
             AND transaction_type_id IN (33, 10008));
SELECT *
  FROM cst_revenue_cogs_match_lines
 WHERE cogs_om_line_id IN (SELECT line_id
                             FROM oe_order_lines_all
                            WHERE header_id = (SELECT header_id
                                                 FROM oe_order_headers_all
                                                WHERE order_number =
&your_order_number));
SELECT *
  FROM cst_cogs_events
 WHERE cogs_om_line_id IN (SELECT line_id
                             FROM oe_order_lines_all 
                            WHERE header_id = (SELECT header_id
                                                 FROM oe_order_headers_all
                                                WHERE order_number =
&your_order_number));
SELECT *
  FROM cst_revenue_cogs_control;
SELECT *
  FROM ra_customer_trx_lines_all
 WHERE interface_line_context = ‘ORDER ENTRY’
   AND interface_line_attribute6 IN (SELECT line_id
                                       FROM oe_order_lines_all
                                      WHERE header_id = (SELECT header_id
                                                           FROM
oe_order_headers_all 
                                                          WHERE order_number
= &your_order_number));
SELECT *
  FROM ra_cust_trx_line_gl_dist_all
 WHERE customer_trx_line_id IN (
          SELECT customer_trx_line_id
            FROM ra_customer_trx_lines_all
           WHERE interface_line_context = ‘ORDER ENTRY’
             AND interface_line_attribute6 IN (SELECT line_id
                                                 FROM oe_order_lines_all
                                                WHERE header_id = (SELECT
header_id
                                                                     FROM
oe_order_headers_all
                                                                    WHERE
order_number = &your_order_number))
             AND account_set_flag = ‘N’
             AND account_class = ‘REV’);
SELECT *
  FROM cst_revenue_recognition_lines
 WHERE revenue_om_line_id IN (SELECT line_id
                                FROM oe_order_lines_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number =
&your_order_number));
SELECT *
  FROM mtl_transaction_accounts
 WHERE transaction_id IN (
          SELECT transaction_id
            FROM mtl_material_transactions
           WHERE trx_source_line_id IN (SELECT line_id
                                          FROM oe_order_lines_all
                                         WHERE header_id = (SELECT header_id
                                                              FROM
oe_order_headers_all
                                                             WHERE 
order_number = &your_order_number))
             AND transaction_type_id IN (33, 10008));

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...