asked on

Oracle EBS 12 - COGS for a period

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,

Kelvin Sparks

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

globalwm2

ASKER

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

globalwm2

ASKER

The question is not to fix the above SQL - only to match COGS to Revenue.

ASKER CERTIFIED SOLUTION

globalwm2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

globalwm2

ASKER

Self answered