[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle EBS 12 - COGS for a period

Posted on 2011-05-11
5
Medium Priority
?
3,012 Views
Last Modified: 2013-12-18
I'm trying to match COGS in v12 to Sales Revenue for a date range. My Sales Revenue script looks like this:

SELECT mmt.source_Line_id
      ,mmt.transaction_date
      ,mmt.parent_transaction_id
      ,mtt.transaction_type_name
      ,cce.event_type
      ,DECODE(cce.costed,'N','Not Processed','Processed') Event_Status
      ,DECODE(cce.costed,'N','Not Generated','Generated') Account_generation
      ,gcc.concatenated_segments account_info
      ,mta.base_transaction_value
      ,oet.name
      ,oeh.order_number
FROM   mtl_transaction_accounts mta
      ,mtl_material_transactions mmt
      ,mtl_transaction_types mtt
      ,gl_code_combinations_kfv gcc
      ,cst_cogs_events cce,
      ,oe_order_Lines_all oel,
      ,oe_order_headers_all oeh,
      ,oe_transaction_types_tl oet
WHERE cce.cogs_om_line_id        =oel.line_id
AND   cce.event_type IN (1,2)
AND   oel.header_id              =oeh.header_id
AND   oeh.order_type_id          =oet.transaction_type_id
AND   mmt.transaction_id         =cce.MMT_TRANSACTION_ID
AND   mmt.transaction_type_id    = mtt.transaction_type_id
AND   mta.transaction_id(+)      = mmt.transaction_id
AND   gcc.code_combination_id(+) = mta.reference_account
AND   oeh.order_number           = &order_number
ORDER BY order_number;

I understand COGS has changed in 12 (along with a new SLA). Looking for a query that I can use to match COGS to the above Revenue rows returned.

Thanks,
0
Comment
Question by:globalwm2
  • 4
5 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 35745228
From Oracle 10.2.0.7 there sooport for the Non ANSI syntax has been removed. The sytle of the FROM statement now needs joins. It Sgould be something like - I may have the right joins back to front here

FROM   cst_cogs_events cce INNER JOIN
         oe_order_Lines_all oel ON cce.cogs_om_line_id =oel.line_id INNER JOIN   oe_order_headers_all oeh ON oel.header_id  =oeh.header_id INNER JOIN
mtl_material_transactions mmt ON mmt.transaction_id =cce.MMT_TRANSACTION_ID INNER JOIN
mtl_transaction_types mtt ON mtl_transaction_types mtt INNER JOINRIGHT JOIN mtl_transaction_accounts mta ON mmt.transaction_id = mta.transaction_id RIGHTJOIN
gl_code_combinations_kfv gcc ON mta.reference_account=gcc.code_combination_id
WHERE cce.event_type IN (1,2)
AND oeh.order_number           = &order_number
ORDER BY order_number;

The SELECT statement won't change

Kelvin
0
 

Author Comment

by:globalwm2
ID: 35748232
The SELECT statement above works fine with no errors.

Since Oracle 7, I don't use ANSI-Joins in my FROM statements.

(There is also a bug with ANSI joins using DBLINK on any table that contains a LOB colum - even if the LOB is not in your SELECT. I've had to convert SQL from ANSI to the WHERE clause joins to overcome this error.)
0
 

Author Comment

by:globalwm2
ID: 35748239
The question is not to fix the above SQL - only to match COGS to Revenue.
0
 

Accepted Solution

by:
globalwm2 earned 0 total points
ID: 35773328
Figured it out - here's a working COGS script that ties to the GL:

SELECT MTL_TRANSACTION_ACCOUNTS.TRANSACTION_DATE,
       FND_FLEX_VALUES_TL.DESCRIPTION,
       MTL_TRANSACTION_ACCOUNTS.BASE_TRANSACTION_VALUE
  FROM (((APPLSYS.FND_FLEX_VALUES FND_FLEX_VALUES
          INNER JOIN APPLSYS.FND_FLEX_VALUE_SETS FND_FLEX_VALUE_SETS
             ON (FND_FLEX_VALUES.FLEX_VALUE_SET_ID =
                    FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID))
         INNER JOIN APPLSYS.FND_FLEX_VALUES_TL FND_FLEX_VALUES_TL
            ON (FND_FLEX_VALUES_TL.FLEX_VALUE_ID =
                   FND_FLEX_VALUES.FLEX_VALUE_ID))
        INNER JOIN GL.GL_CODE_COMBINATIONS GL_CODE_COMBINATIONS
           ON (GL_CODE_COMBINATIONS.SEGMENT2 =
                  FND_FLEX_VALUES_TL.FLEX_VALUE_MEANING))
       RIGHT OUTER JOIN INV.MTL_TRANSACTION_ACCOUNTS MTL_TRANSACTION_ACCOUNTS
          ON (MTL_TRANSACTION_ACCOUNTS.REFERENCE_ACCOUNT =
                 GL_CODE_COMBINATIONS.CODE_COMBINATION_ID)
 WHERE (MTL_TRANSACTION_ACCOUNTS.ACCOUNTING_LINE_TYPE IN (13, 35, 36))
       AND (MTL_TRANSACTION_ACCOUNTS.TRANSACTION_DATE BETWEEN TO_DATE (
                                                                 '2009-01-01 00:00:00',
                                                                 'yyyy/mm/dd hh24:mi:ss')
                                                          AND  TO_DATE (
                                                                  '2009-12-31 23:59:59',
                                                                  'yyyy/mm/dd hh24:mi:ss'))
       AND (FND_FLEX_VALUES_TL.DESCRIPTION <> 'Balance Sheet')
       AND (FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME = 'GL_COA_DIVISION')
0
 

Author Closing Comment

by:globalwm2
ID: 35807086
Self answered
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question