In this Document

Purpose
Troubleshooting Steps
1] No Accounting for COGS Recognition Transaction
2] Subledger Period close Exception report
3] COGS Recognition Transaction with 0 quantity
4] RMA Accounting
5] Duplicate accounting
6] DCOGS account is not zero
7] COGS Revenue Matching Report
References

Applies to:

Oracle Cost Management - Version 12.0.0 and later

Information in this document applies to any platform.

Purpose

The following explains the problems encountered in Cost of Goods Sold (COGS) and
Deferred Cost of Goods Sold ( DCOGS)

Troubleshooting Steps

1] No Accounting for COGS Recognition Transaction

Symptom:-
COGS Recognition Transaction does not have accounting in MTA table. (costed_flag is
NULL)

Explanation:-

If quantity or cost of the item is zero, then COGS Recognition transaction does not have
any accounting.

According to the standard functionality,

When the transaction value is zero no accounting will be created.
COGS recognition is like redistributing expense across DCOGS and COGS. If there is no
expense to be distributed at all (zero cost), creating zero dummy accounting entries
among two expense accounts would be a waste and hurting performance unnecessarily
as well.
So if the item is having zero cost then COGS journals will not be populated.

Use below queries for verification:

1.

Select *
from cst_revenue_cogs_match_lines
Where cogs_om_line_id=&om_line_id;
check: Column = Unit_cost

2.

Select *
from cst_cogs_events
Where cogs_om_line_id=&om_line_id;

Here refer cogs lines (i.e., event_type = 3 ) quantity i.e., event_quantity.

To get sales order line id use below query

Select trx_source_line_id "om_line_id"

from mtl_material_transactions
Where transaction_id=&transaction_id;

/* Use COGS Recognition or SO Issue transaction */

2] Subledger Period close Exception report

Symptom:-
Subledger Period close exception report shows unprocessed COGS recognition events.

Explanation:-
If COGS Recognition transaction has zero quantity or zero cost, MTA does not exists but
XLA_events data exists.

This issue is fixed in COGs code, but if ct. has older version then ct. has to apply
datafix.
Review Note 738529.1 for more detail
For root cause fix, apply latest patch on CSTRCMVB.pls file.

3] COGS Recognition Transaction with 0 quantity

Symptom:-
Sales Order Line has non-zero quantity but COGS is showing zero quantity.

Explanation:-
If RMA exists before COGS Recognition then
COGS Recognition transaction quantity = Sales Order issue quantity - RMA quantity.

Verify the data in CST_COGS_EVENTS table

Select *
from cst_cogs_events
Where cogs_om_line_id= &om_line_id;

Note:-
Event_type = 1 (Sales Order Issue)
= 3 (COGs Recognition)
= 2 & 6 (RMA)

4] RMA Accounting

Symptom:-
RMA accounting hits COGS some times and hits DCOGS sometimes.

Explanation:-
This depends on when RMA is created i.e., before COGS Recognition or
after COGS Recognition.

If RMA is created before COGS Recognition then RMA hits DCOGS account.

If RMA is created after complete COGS Recognition then RMA hits COGS account.
Partial COGS Recognition then RMA hits both COGS & DCOGs accounts based on COGs
recognition percentage.

Refer to
Oracle Cost Manager User Guide - Revenue and COGS Matching chapter - Supported
Business Scenarios
Scenarios of DCOGS account:

Case 1:
Transaction Type Debit Credit
================= =====
Sales Order Issue DCOGS INV
COGS Recognition COGS DCOGS
DCOGS balance will be cleared after COGS Recognition.

Case 2:
Transaction Type Debit Credit
================= =====
Sales Order Issue DCOGS INV
RMA(Before COGS Rec.) INV DCOGS
DCOGS balance will be cleared after RMA.

Case 3:
Transaction Type Debit Credit
================= =====
Sales Order Issue DCOGS INV
COGS Recognition COGS DCOGS
RMA(After COGS Rec.) INV COGS
DCOGS balance will be cleared after COGS Recognition.

Queries

select trx_source_line_id "rma_line_id"

from mtl_material_transactions
where transaction_id=&transaction_id;

/* Use RMA Receipt */

select reference_line_id "om_line_id"

from oe_order_lines_all
where line_id=&rma_line_id;

select * from cst_cogs_events

where cogs_om_line_id=&om_line_id;
Note:-
If RMA is not referring to any Sales Order, then RMA accounting hits only COGS
account.

5] Duplicate accounting

Symptom:-
Duplicate accounting for COGS Recognition transactions.

Explanation:-
This happens when Cost manager and Generate COGS Recognition events programs
run in parallel.
Issue has been fixed and latest patch is recommended for root cause fix.
For old data, ct. has to do manual GL adjustments.

Queries-

SELECT 3/4*SUM(NVL(ln.accounted_dr,0)-NVL(ln.accounted_cr,0)) ,
ln.accounting_class_code ,
ln.code_Combination_id
FROM
(SELECT mmt.transaction_id ,
COUNT(mta.inv_sub_ledger_id) ,
SUM(mta.base_transaction_value)
FROM apps.mtl_material_transactions mmt ,
apps.mtl_transaction_accounts mta
WHERE mmt.organization_id = &org_id
AND mmt.transaction_type_id = 10008
AND mmt.transaction_date BETWEEN to_date('&from_date','DD-MM-YYYY') AND
to_date('&to_date','DD-MM-YYYY') + .99999
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type = 36
AND mta.cost_element_id =1
GROUP BY mmt.transaction_id
HAVING COUNT(mta.inv_sub_ledger_id) > 1
) list ,
xla_transaction_entities_upg xte ,
xla_events xe ,
xla_ae_headers hr ,
xla_ae_lines ln
WHERE xte.ledger_id = &ledger_id
AND xte.application_id = 707
AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'
AND NVL(xte.source_id_int_1, -9999) = list.transaction_id
AND xe.application_id = 707
AND xte.entity_id = xe.entity_id
AND hr.application_id = 707
AND ln.application_id = 707
AND hr.event_id = xe.event_id
AND hr.ledger_id = xte.ledger_id
AND hr.ae_header_id = ln.ae_header_id
GROUP BY ln.accounting_class_code ,
ln.code_Combination_id;

Note:-
Accounting is ducplicated 2 times in MTA and 4 times in XLA tables.

Also review <Doc ID 1103513.1> DUPLICATE COGS RECOGNITIONS TRANSACTIONS

6] DCOGS account is not zero

Symptom:-
Amount exists in DCOGS.

Explanation:-
Get the output of COGS queries and also output of COGS Revenue Matching Report.

Take sample Sales Order/Sales Order Line.

Check whether COGS is not recognized. If COGS event/transaction is not created,
please check whether revenue information exists in CRRL table or not.
If CRRL data does not exist, verify revenue recognition. If not done, do revenue
recognition and then run Collect Revenue Recognition Information & Generate COGS
Recognition Events programs.
If COGS event/transaction exists but still having balance then check whether COGS is
recognized fully or not. Also verify whether cogs event/transaction is costed or not
7] COGS Revenue Matching Report

Symptom:-
a. COGS Revenue Matching Report Performance issue.
b. Displaying rows even though cogs & revenue are matching.
c. Not displaying quantity correctly.
d. RMA accounting does not have credit lines.

Explanation:-

Apply latest patch.

R12 = Patch 13437574:R12.BOM.A to update file to latest version CSTRCMVB.pls

120.37.12000000.51 or higher

12.1 = Patch 13948107:R12.BOM.C file version CSTRCMVB.pls 120.45.12000000.63 or

higher
8. In order to recognize revenue for the Cost of Goods Sold and Deferred Cost of Goods
Sold, the following steps need to be performed:

Run these AR steps:

Run autoinvoice and generate the invoice transaction.
Run Revenue Recognition Master Program and generate the AR revenue recognition

Then the remaining steps:

Record Order Management Transactions

Collect Revenue Recognition Information
Generate COGS Recognition Events

----------------------
A) Once Invoice is created,
Recognize the revenue in AR >
Navigate to : Accounts Receivables>Control>Run Revenue Recognition request

B) After recognizing the revenue, accept it.

Navigate to : Accounts Receivables>Control>Revenue Accounting>Enter reference
number: SO (ie.SO Number)Manage Revenue>Schedule Revenue

C) Run a set of concurrent processes 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.

1. Record Order Management Transactions: records new sales order transaction

activity such as shipments and RMA returns in Oracle Order Management.

2. Collect Revenue Recognition Information: determines the percentage of recognized

or earned revenue related to invoiced sales order shipment lines
in Oracle Receivables.

3. 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.

4. When querying for COGS Recognition Transactions :

a. Navigate to Inventory > Transactions > Material Transactions
b. Select the 'Include Logical Transactions' checkbox in the Find Material
Transactions Form when running your query and the query will return 3 transaction
types:
1) Sales Order Pick
2) Sales Order Issue
3) COGS Recognition
c. While on the Sales Order Issue transaction line, select the Distributions
button. Distributions are shown for Inventory Valuation and Deferred Cost of Goods
Sold.
d. While on the COGS Recognition transaction line, select the Distributions
button. Distributions are shown for Deferred COGS and COGS. - Cost > View
Transactions > Material Transactions

-- Please ensure all the steps are performed and check to see whether COGS
Recognition transactions are generated. If you forgot to run COGS Recognition
processes and INV and GL periods are closed, the COGS Recognition would go to the
next open period .

References

NOTE:1307906.1 - COGS Dataflow and Diagnostics [Video]

Attachments

 DCOGS Flowchart.bmp (1.23 MB)

COGS Dataflow and Diagnostics [Video] [ID 1307906.1]

In this Document

Purpose

In this Document:

Troubleshooting Steps

Goal
 Solution

1. COGS Data flow

2. DIAGNOSTICS

3. Video Recording for the COGS Workflow

References

Applies to:

Oracle Cost Management - Version 12.0.0 and later

Information in this document applies to any platform.

Purpose

In this Document:

Goal
Solution
1. COGS Data Flow
2. Diagnostics
3. Video Recording for the COGS Workflow

References

Troubleshooting Steps

Goal

 Create a Sales Order, Schedule Revenue and Recognize the COGS.

 Understand the Dataflow and tables affected for better troubleshooting
 Use the diagnostics and understand the relevance of various columns and their values

Solution

1. COGS Data flow

Note:
1. In the above and below flow, We have assumed that the Cost Manager has picked
up all uncosted Sales order issues and costed them (costed_flag in
mtl_material_transactions should be NULL).
2. If the Sales order issue transactions/ RMA Return transactions remain uncosted, then
The Record Order Management Transactions concurrent program can be executed,
which picks up these transactions and inserts rows in Cogs events and Cogs Revenue
Matching Tables to enable remaining programs (Collect Revenue Recognition
Information and Generate COGS Recognition) to complete.
This process is not mandatory if the transactions are already costed.

Important columns affected:

A. After Sales Order Issue:

1.Cst_cogs_events.COGS_Percentage = '0'.
2. (i) Cst_cogs_events.Mmt_Transaction_id will be the Transaction Id in
Mtl_Material_Transactions Table for Sales Order Issue Transaction.
(ii) Cst_cogs_events.costed = Mtl_Material_Transactions.costed_flag will be NULL
when the sales order transaction is costed
3.Cst_cogs_events.Event Type = '1'[Event Type '1' signifies Sales Order Issue].
4. In cst_revenue_cogs_match_lines Table, Deferred_COGS_Acct_id and COGS_Acct_id,
Unit_cost and Original_shipped_Qty gets populated.

Note: Oe_order_lines_all.Invoice_Interface_Status_Code =
'Yes'.[Invoice_Interface_Status_Code 'Yes' signifies Standard Sales Order and
Invoice_Interface_Status_Code 'Not_Eligible' signifies Ship Only Lines].

B. After Revenue Recognition and Revenue Accounting:

RA_CUST_TRX_LINE_GL_DIST_ALL.Account_Set_flag = 'N'. [ Account_Set_flag 'Y'

signifies, Revenue recognition is not performed and Account_Set_flag 'N' signifies that
Revenue recognition is run].

C. After Collect Revenue Recognition Information is run:

1.Cst_revenue_recognition_lines.Potentially_unmatched_flag = 'Y'
2.Cst_revenue_recognition_lines.Revenue_recognition_percent = '1' [Revenue
Recognition percentage of 1 signifies 100%]

D. After Generate COGS Recognition is run:

1. Cst_cogs_events.COGS_Percentage = '1'[COGS Percentage '1' signifies 100%].

2.Cst_cogs_events.Mmt_Transaction_id will be the Transaction Id in
Mtl_Material_Transactions Table for COGS Recognition Transaction.
3.Cst_cogs_events.Event Type = '3'[Event Type '3' signifies COGS Recognition].
4. Cst_revenue_recognition_lines.Potentially_unmatched_flag = 'Null' .

Accounting Impact:

A.After Sales Order Issue:

1. In Mtl_material_transactions Table, three lines will be populated.Sales order issue,

staging (source) and Staging (Destination) respectively.
2. In Mtl_transaction_accounts Table, the following is accounting impact:

Accounting Entries:-

Staging transactions:-
Inventory Account (Destination) is debited and Inventory Account (Source) is credited.

Sales Order issue:-

DCOGS Account is Debited and Inventory Account is Credited.
Accounting Line Type '1' and '36' in mtl_transaction_accounts signifies 'Inventory
Valuation' and 'DCOGS' respectively.

B.After Generate COGS Recognition is run:

1. In Mtl_material_transactions Table,one line will be populated for COGS Recognition.

2. In Mtl_transaction_accounts Table, the following is accounting impact:

Accounting Entries:-

COGS Recognition:
COGS Account is Debited and DCOGS Account is Credited.

Accounting Line Type '35' signifies 'COGS' in table mtl_transaction_accounts.

2. DIAGNOSTICS

COGSDIAG.sql

How to Use COGSDIAG.sql

----------------------------------------

1. Execute COGSDIAG.sql for any Sales Order you wish to troubleshoot using 'Run Script'
command (or) run individual queries as per requirement.
2. The SQL returns rows for all tables related to COGS Recognition from OM, AR, Inventory
and Costing.
3. View a Sample Output here - Sample Output
3. Video Recording for the COGS Workflow

o Creation of Sales Order

Video - Creation of Sales Order (05:29)

o Revenue Recognition and Accounting

Video -

Revenue Recognition (03:02)

 COGS Recognition

Video - COGS Recognition (03:37)

You can also download the below Zip files if you are not able to view the videos/ video
controls properly
- Sales_Order.zip
- Revenue_Recognition.zip
- COGS.zip

References

NOTE:778.1 - Multimedia Content Reference

NOTE:867912.1 - COGS and DCOGS Workflow
NOTE:1314335.1 - COGS/ Deferred COGS common problems

Attachments

 COGS Recognition (565.69 KB)

 COGS Recognition zipfile (566.81 KB)

 COGS Diagnostics File (3.55 KB)

 COGS_Workflow (90.02 KB)

 Revenue Recognition from AR (613.67 KB)

 Revenue Recognition zipfile (614.95 KB)

 Creation of Sales Order - Demo (764.47 KB)

 Sales Order zipfile (765.67 KB)

 Sample output for COGSDIAG.sql for the SO (66488) in Video (530.95 KB)