My Oracle Support Banner

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

To view full details, 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 a vibrant support community of peers and Oracle experts.