Identifying sales order transactions that have been finally accounted but have no revenue recognition transactions

With the help of the following script, we can find out the sales order issues which got finally accounted, but have no revenue recognition transactions like COGS & DCOGS.

SELECT * FROM mtl_material_transactions

WHERE organization_id IN

(SELECT organization_id FROM gmf_organization_definitions

WHERE legal_entity_id = &Legal_entity_id)

AND TRUNC (transaction_date) > :p_from_date

AND transaction_date < :p_to_date

AND transaction_source_type_id = 2

AND transaction_action_id = 1

AND opm_costed_flag IS NULL

AND trx_source_line_id IN

(SELECT DISTINCT trx_source_line_id FROM mtl_material_transactions

WHERE organization_id IN

(SELECT organization_id FROM gmf_organization_definitions

WHERE legal_entity_id = &Legal_entity_id)

AND TRUNC (transaction_date) > :p_from_date

AND transaction_date < :p_to_date

AND transaction_source_type_id = 2

AND transaction_action_id = 1

AND opm_costed_flag IS NULL

MINUS

SELECT DISTINCT trx_source_line_id FROM mtl_material_transactions

WHERE organization_id IN

(SELECT organization_id FROM gmf_organization_definitions

WHERE legal_entity_id = &Legal_entity_id)

AND transaction_source_type_id = 2

AND transaction_action_id = 36

);

Parameters to be passed are – legal entity id, from date & to date.

To find legal entity id, execute the below query

Select * from gmf_legal_entities

To know about how to generate revenue recognition transactions click the following link

http://dj-oracleapps.blogspot.in/2013/07/cogs-recognition-and-concurrent.html 

---X---