Monday, April 5, 2010

Query to fetch inventory transactions of an order in R12

Below query can be used to identify the inventory transactions related to the order.
By specifying the event type we can also check for the specific events related to an order.

CST_COGS_EVENTS.EVENT_TYPE column specifies the type of cogs event happened
(1) Sales Order Issue
(2) RMA Receipt
(3) COGS Recognition Event
(4) COGS Rec. Percent Adjustment
(5) COGS Rec. Qty. Adj.
(6) RMA placeholder

-- Query starts here --

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;

2 Responses to “Query to fetch inventory transactions of an order in R12”

Anonymous said...
May 11, 2011 at 12:55 PM

There are typos in your FROM clause (extra commas)


Anonymous said...
October 14, 2019 at 10:22 PM

What is the value for COGS here?


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.