VIEW: APPS.GMF_XLA_PM_TXNS_V

Source

View Text - Preformatted

SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, bh.batch_id , bh.batch_no batch_number, bh.organization_code, bh.plant_code, bh.batch_status_desc batch_status, bh.actual_start_date batch_actual_start_dt, bh.actual_cmplt_date batch_actual_cmplt_dt, bh.batch_close_date batch_close_date, bh.formula_no ||' / '|| bh.formula_vers formula, bh.formula_desc1 formula_desc, bh.routing_no ||' / '|| bh.routing_vers routing_no, bh.routing_desc routing_desc, bh.recipe_no ||' / '||bh.recipe_version recipe_no, bh.recipe_description recipe_desc FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt , gme_batch_header_vw bh, mtl_system_items_b_kfv msi, xle_entity_profiles le WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_document_id = bh.batch_id AND eh.transaction_source_type_id = 5 AND eh.transaction_action_id IN (1,32,27,31) AND bh.batch_id = mmt.transaction_source_id AND eh.inventory_item_id = mmt.inventory_item_id AND eh.organization_id = mmt.organization_id AND eh.event_class_code = 'BATCH_MATERIAL' UNION ALL SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||''||NULL primary_quantity, NULL primary_uom_code, NULL secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, NULL item_number, NULL item_description, eh.organization_id, bh.batch_id , bh.batch_no batch_number, bh.organization_code, bh.plant_code, bh.batch_status_desc batch_status, bh.actual_start_date batch_actual_start_dt, bh.actual_cmplt_date batch_actual_cmplt_dt, bh.batch_close_date batch_close_date, bh.formula_no ||' / '|| bh.formula_vers formula, bh.formula_desc1 formula_desc, bh.routing_no ||' / '|| bh.routing_vers routing_no, bh.routing_desc routing_desc, bh.recipe_no ||' / '||bh.recipe_version recipe_no, bh.recipe_description recipe_desc FROM gmf_xla_extract_headers eh, gme_batch_header_vw bh, xle_entity_profiles le WHERE eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = bh.batch_id AND eh.source_document_id = bh.batch_id AND eh.transaction_source_type_id = 0 AND eh.transaction_action_id = 0 AND eh.event_class_code = 'BATCH_CLOSE'

View Text - HTML Formatted

SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, BH.BATCH_ID
, BH.BATCH_NO BATCH_NUMBER
, BH.ORGANIZATION_CODE
, BH.PLANT_CODE
, BH.BATCH_STATUS_DESC BATCH_STATUS
, BH.ACTUAL_START_DATE BATCH_ACTUAL_START_DT
, BH.ACTUAL_CMPLT_DATE BATCH_ACTUAL_CMPLT_DT
, BH.BATCH_CLOSE_DATE BATCH_CLOSE_DATE
, BH.FORMULA_NO ||' / '|| BH.FORMULA_VERS FORMULA
, BH.FORMULA_DESC1 FORMULA_DESC
, BH.ROUTING_NO ||' / '|| BH.ROUTING_VERS ROUTING_NO
, BH.ROUTING_DESC ROUTING_DESC
, BH.RECIPE_NO ||' / '||BH.RECIPE_VERSION RECIPE_NO
, BH.RECIPE_DESCRIPTION RECIPE_DESC
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, GME_BATCH_HEADER_VW BH
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_DOCUMENT_ID = BH.BATCH_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 5
AND EH.TRANSACTION_ACTION_ID IN (1
, 32
, 27
, 31)
AND BH.BATCH_ID = MMT.TRANSACTION_SOURCE_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.EVENT_CLASS_CODE = 'BATCH_MATERIAL' UNION ALL SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||''||NULL PRIMARY_QUANTITY
, NULL PRIMARY_UOM_CODE
, NULL SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, NULL ITEM_NUMBER
, NULL ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, BH.BATCH_ID
, BH.BATCH_NO BATCH_NUMBER
, BH.ORGANIZATION_CODE
, BH.PLANT_CODE
, BH.BATCH_STATUS_DESC BATCH_STATUS
, BH.ACTUAL_START_DATE BATCH_ACTUAL_START_DT
, BH.ACTUAL_CMPLT_DATE BATCH_ACTUAL_CMPLT_DT
, BH.BATCH_CLOSE_DATE BATCH_CLOSE_DATE
, BH.FORMULA_NO ||' / '|| BH.FORMULA_VERS FORMULA
, BH.FORMULA_DESC1 FORMULA_DESC
, BH.ROUTING_NO ||' / '|| BH.ROUTING_VERS ROUTING_NO
, BH.ROUTING_DESC ROUTING_DESC
, BH.RECIPE_NO ||' / '||BH.RECIPE_VERSION RECIPE_NO
, BH.RECIPE_DESCRIPTION RECIPE_DESC
FROM GMF_XLA_EXTRACT_HEADERS EH
, GME_BATCH_HEADER_VW BH
, XLE_ENTITY_PROFILES LE
WHERE EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = BH.BATCH_ID
AND EH.SOURCE_DOCUMENT_ID = BH.BATCH_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 0
AND EH.TRANSACTION_ACTION_ID = 0
AND EH.EVENT_CLASS_CODE = 'BATCH_CLOSE'