Batch Costing details with Transactions

Batch Header details for Batch id: &&batch_id
==============================================

select * from gme_batch_header where batch_id='&&batch_id';


Recipe details for Batch id: &&batch_id
==========================================

select r.*
from gme_batch_header b
,gmd_recipes r
,gmd_recipe_validity_rules vr
where b.batch_id= &&batch_id
and b.recipe_validity_rule_id=vr.recipe_validity_rule_id
and vr.recipe_id=r.recipe_id;


Recipe Validity Rules details for Batch id: &&batch_id
======================================================

select vr.*
from gme_batch_header b
,gmd_recipes r
,gmd_recipe_validity_rules vr
where b.batch_id= &&batch_id
and b.recipe_validity_rule_id=vr.recipe_validity_rule_id
and vr.recipe_id=r.recipe_id;

Batch Material Details for Batch id: &&batch_id
===============================================

SELECT d.*
FROM gme_material_details d
WHERE d.batch_id = &&batch_id;

Inventory Transactions details for Batch id: &&batch_id
=======================================================

SELECT t.*
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5;

Batch Material Transaction Pairs for Batch Number: &&batch_id
=============================================================

SELECT *
FROM gme_transaction_pairs p
WHERE p.batch_id = &&batch_id;

Batch Resource Transactions details for Batch Number: &&batch_id
================================================================

SELECT t.*
FROM gme_batch_header h, gme_resource_txns t
WHERE h.batch_id = &&batch_id
AND h.batch_id = t.doc_id
AND t.doc_type = 'PROD';

Yield Layers for Batch id: &&batch_id
=======================================

SELECT *
FROM gmf_incoming_material_layers il
WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id, t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5);


Material Consumption Layers for Batch id: &&batch_id
=====================================================

SELECT *
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id, t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5);

Resource Consumption Layers for Batch id: &&batch_id
====================================================

SELECT *
FROM gmf_resource_layers il
WHERE il.poc_trans_id IN
(SELECT t.poc_trans_id
FROM gme_resource_txns t
WHERE t.doc_id = &&batch_id
AND t.doc_type = 'PROD');

VIB Details for Batch id: &&batch_id
=====================================

SELECT *
FROM gmf_batch_vib_details bvd
WHERE bvd.requirement_id IN
(SELECT br.requirement_id
FROM gmf_batch_requirements br
WHERE br.batch_id = &&batch_id);

Batch Requirement Details for Batch id: &&batch_id
===================================================

SELECT *
FROM gmf_batch_requirements br
WHERE br.batch_id = &&batch_id;


Layer cost details for Batch id: &&batch_id
============================================

SELECT *
FROM gmf_layer_cost_details c
WHERE
c.layer_id IN
(SELECT il.layer_id
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
WHERE h.batch_id = &&batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id = t.transaction_id
AND il.mmt_organization_id = t.organization_id
);


Extract Header details for Batch id: &&batch_id
=================================================

select geh.*
from gmf.gmf_xla_extract_headers geh
where geh.entity_code = 'PRODUCTION'
and source_document_id = &&batch_id
and txn_source = 'PM';

Extract Lines details for Batch id: &&batch_id
==============================================

select gel.*
from gmf.gmf_xla_extract_lines gel
where gel.header_id in
(
select geh.header_id
from gmf.gmf_xla_extract_headers geh
where geh.entity_code = 'PRODUCTION'
and source_document_id = &&batch_id
and txn_source = 'PM');

Sla Events for Batch id: &&batch_id
====================================

select xe.*
from gmf.gmf_xla_extract_headers geh, xla.xla_events xe
where geh.entity_code= 'PRODUCTION'
and xe.event_id = geh.event_id
and geh.transaction_id in
(SELECT t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5);


Item Component Cost details for Batch id: &&batch_id
====================================================

select a.*
from gl_item_dtl a
where a.itemcost_id
in (select itemcost_id
from gl_item_cst
where (inventory_item_id, organization_id,cost_type_id, period_id)
IN (select distinct mmt.inventory_item_id, mmt.organization_id,gps.cost_type_id,gps.period_id
from gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 5
AND god.organization_id = mmt.organization_id
AND mmt.transaction_source_id = &&batch_id
AND gfp.legal_entity_id = god.legal_entity_id
AND mthd.cost_type_id = gfp.cost_type_id
AND gps.legal_entity_id = gfp.legal_entity_id
AND gps.cost_type_id = gfp.cost_type_id
AND mmt.transaction_date >= gps.start_date
AND mmt.transaction_date <= gps.end_date));