COGS/ Deferred COGS common problems

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 */ 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. 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) 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. 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 duplicated 2 times in MTA and 4 times in XLA tables. Also review DUPLICATE COGS RECOGNITIONS TRANSACTIONS 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.12010000.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 . The above details are posted from the Oracle Support Document (Doc ID 1314335.1), People having access to Oracle Support can refer the Original document from metalink. Thanks & Regards, S.Grace Paul Regan +965-97100377