COGS Revenue Matching Report Incorrect Qty And Amounts

(Doc ID 2394605.1)

Last updated on MAY 03, 2018

Applies to:

Oracle Cost Management - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.5 version, Revenue-COGS Matching

ACTUAL BEHAVIOR
---------------
COGS Revenue Matching Report Incorrect Qty and Amounts

Below query in the report is not grouping correctly. Sum(cce.event_quantity)


SELECT /*+ index(srclines RA_CUSTOMER_TRX_LINES_N9) LEADING (Q, srclines) use_nl(Q, srclines) */
  distinct Q.ORDER_NUMBER order_number,
  Q.booked_date /* maybe ordered_date? */ order_date,
  substrb(PARTY.PARTY_NAME,1,50) customer_name,
  Q.transactional_curr_code currency,
  Q.line_number sales_order_line,
 Q.REVENUE_OM_LINE_ID sales_order_line_id,
 Q.ITEM_TYPE_CODE item_type_code,
 msi.concatenated_segments item,
 srclines.line_number invoice_line,
  srclines.customer_trx_line_id,
  srclines.customer_trx_id,
 sum(cce.EVENT_QUANTITY) total_line_quantity,
  --cce.EVENT_QUANTITY total_line_quantity,
 Q.COGS_BALANCE Earned_COGS,
  Q.TOTAL_COGS_BALANCE Total_Earned_COGS,
  Q.DEF_COGS_BALANCE Deferred_COGS,
  Q.TOTAL_DEF_COGS_BALANCE Total_Deferred_COGS,
 cogs_acct.concatenated_segments COGS_account,
  dcogs_acct.concatenated_segments Deferred_COGS_account
FROM
(select * from ( SELECT /*+ leading(crcml,ool,ooh,cce,mta) index(crcml CST_REV_COGS_MATCH_LINES_N2) */
  OOH.ORDER_NUMBER,
  OOH.BOOKED_DATE,
  OOH.transactional_curr_code,
  OOL.LINE_NUMBER,
  OOL.SOLD_TO_ORG_ID,
  OOL.ITEM_TYPE_CODE,
CRCML.REVENUE_OM_LINE_ID,
  CRCML.ORGANIZATION_ID,
  --CRCML.COGS_ACCT_ID, -- bug#17713008 [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=17713008] , removed it as PTO item may have different COGS Account for one SO line.
  CRCML.DEFERRED_COGS_ACCT_ID,
  CRCML.COGS_OM_LINE_ID,
  CRCML.inventory_item_id,
  CRCML.UNIT_COST,
  sum(decode(mta.accounting_line_type, 35, MTA.BASE_TRANSACTION_VALUE,0)) COGS_BALANCE,
  Last_Value(sum(decode(mta.accounting_line_type, 35, MTA.BASE_TRANSACTION_VALUE,0)))
OVER( PARTITION BY OOH.ORDER_NUMBER,OOH.BOOKED_DATE,OOH.transactional_curr_code,OOL.LINE_NUMBER,OOL.SOLD_TO_ORG_ID,OOL.ITEM_TYPE_CODE,CRCML.REVENUE_OM_LINE_ID,CRCML.ORGANIZATION_ID, /*CRCML.COGS_ACCT_ID,*/ CRCML.DEFERRED_COGS_ACCT_ID
ORDER BY OOH.ORDER_NUMBER,OOH.BOOKED_DATE,OOH.transactional_curr_code,OOL.LINE_NUMBER,OOL.SOLD_TO_ORG_ID,OOL.ITEM_TYPE_CODE,CRCML.REVENUE_OM_LINE_ID,CRCML.ORGANIZATION_ID, /*CRCML.COGS_ACCT_ID,*/ CRCML.DEFERRED_COGS_ACCT_ID ) As TOTAL_COGS_BALANCE,
  sum(decode(mta.accounting_line_type, 36, MTA.BASE_TRANSACTION_VALUE,0)) DEF_COGS_BALANCE,
  Last_Value(sum(decode(mta.accounting_line_type, 36, MTA.BASE_TRANSACTION_VALUE,0)))
OVER( PARTITION BY OOH.ORDER_NUMBER,OOH.BOOKED_DATE,OOH.transactional_curr_code,OOL.LINE_NUMBER,OOL.SOLD_TO_ORG_ID,OOL.ITEM_TYPE_CODE,CRCML.REVENUE_OM_LINE_ID,CRCML.ORGANIZATION_ID, /*CRCML.COGS_ACCT_ID,*/ CRCML.DEFERRED_COGS_ACCT_ID
ORDER BY OOH.ORDER_NUMBER,OOH.BOOKED_DATE,OOH.transactional_curr_code,OOL.LINE_NUMBER,OOL.SOLD_TO_ORG_ID,OOL.ITEM_TYPE_CODE,CRCML.REVENUE_OM_LINE_ID,CRCML.ORGANIZATION_ID, /*CRCML.COGS_ACCT_ID,*/
  CRCML.DEFERRED_COGS_ACCT_ID ) as TOTAL_DEF_COGS_BALANCE,
  Grouping(CRCML.COGS_OM_LINE_ID) FLG_COGS_OM_LINE_ID,
  Grouping(CRCML.inventory_item_id) FLG_INV_ITEM_ID,
  Grouping(CRCML.UNIT_COST) FLG_UNIT_COST
FROM
  CST_REVENUE_COGS_MATCH_LINES CRCML,
  CST_COGS_EVENTS CCE,
  OE_ORDER_LINES_ALL OOL,
  OE_ORDER_HEADERS_ALL OOH,
  MTL_TRANSACTION_ACCOUNTS MTA
WHERE
  1=1-- CRCML.SALES_ORDER_ISSUE_DATE BETWEEN trunc(TO_DATE('01-JAN-2018') ) AND trunc(TO_DATE('31-JAN-2018') )
  AND CRCML.OPERATING_UNIT_ID = 81
  AND CRCML.PAC_COST_TYPE_ID IS NULL

EXPECTED BEHAVIOR
-----------------------
 Expect COGS Revenue Matching Report to show correct Qty and Amounts

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
When there are partial kit component shipments the COGs revenue matching report is reporting incorrect quantity and COGS amounts.



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms