VIEW: APPS.MTL_TXN_BACKORDERS_V

Source

View Text - Preformatted

SELECT moh.header_id, moh.request_number, moh.move_order_type, mfg1.meaning, moh.organization_id, moh.date_required, mfg2.meaning, moh.status_date, mol.line_id, mol.line_number, mol.inventory_item_id, mol.revision, mol.from_subinventory_code, mol.from_locator_id, mol.to_subinventory_code, mol.to_locator_id, mol.lot_number, mol.serial_number_start, mol.serial_number_end, mol.uom_code, (mol.quantity -mol.quantity_detailed) backordered_quantity, mol.reason_id, mol.reference, mol.reference_type_code, mol.reference_id, mol.project_id, mol.task_id, mol.transaction_header_id, mfg3.meaning, mol.status_date, mol.to_organization_id, mol.transaction_type_id, mol.transaction_source_type_id, mol.txn_source_id, mol.txn_source_line_id, mol.txn_source_line_detail_id, mol.pick_strategy_id, mol.put_away_strategy_id, mol.from_cost_group_id, cst.cost_group, mol.to_cost_group_id, cst2.cost_group from mtl_txn_request_headers moh, mtl_txn_request_lines mol, mfg_lookups mfg1, mfg_lookups mfg2, mfg_lookups mfg3, cst_cost_groups cst, cst_cost_groups cst2 WHERE moh.header_id = mol.header_id and moh.move_order_type = mfg1.lookup_code and mfg1.lookup_type = 'MOVE_ORDER_TYPE' and moh.header_status = mfg2.lookup_code and mfg2.lookup_type = 'MTL_TXN_REQUEST_STATUS' and mol.line_status = mfg3.lookup_code and mfg3.lookup_type = 'MTL_TXN_REQUEST_STATUS' and mol.quantity_detailed is not null and mol.quantity > mol.quantity_detailed and cst.cost_group_id(+) = mol.from_cost_group_id and cst2.cost_group_id(+) = mol.to_cost_group_id

View Text - HTML Formatted

SELECT MOH.HEADER_ID
, MOH.REQUEST_NUMBER
, MOH.MOVE_ORDER_TYPE
, MFG1.MEANING
, MOH.ORGANIZATION_ID
, MOH.DATE_REQUIRED
, MFG2.MEANING
, MOH.STATUS_DATE
, MOL.LINE_ID
, MOL.LINE_NUMBER
, MOL.INVENTORY_ITEM_ID
, MOL.REVISION
, MOL.FROM_SUBINVENTORY_CODE
, MOL.FROM_LOCATOR_ID
, MOL.TO_SUBINVENTORY_CODE
, MOL.TO_LOCATOR_ID
, MOL.LOT_NUMBER
, MOL.SERIAL_NUMBER_START
, MOL.SERIAL_NUMBER_END
, MOL.UOM_CODE
, (MOL.QUANTITY -MOL.QUANTITY_DETAILED) BACKORDERED_QUANTITY
, MOL.REASON_ID
, MOL.REFERENCE
, MOL.REFERENCE_TYPE_CODE
, MOL.REFERENCE_ID
, MOL.PROJECT_ID
, MOL.TASK_ID
, MOL.TRANSACTION_HEADER_ID
, MFG3.MEANING
, MOL.STATUS_DATE
, MOL.TO_ORGANIZATION_ID
, MOL.TRANSACTION_TYPE_ID
, MOL.TRANSACTION_SOURCE_TYPE_ID
, MOL.TXN_SOURCE_ID
, MOL.TXN_SOURCE_LINE_ID
, MOL.TXN_SOURCE_LINE_DETAIL_ID
, MOL.PICK_STRATEGY_ID
, MOL.PUT_AWAY_STRATEGY_ID
, MOL.FROM_COST_GROUP_ID
, CST.COST_GROUP
, MOL.TO_COST_GROUP_ID
, CST2.COST_GROUP
FROM MTL_TXN_REQUEST_HEADERS MOH
, MTL_TXN_REQUEST_LINES MOL
, MFG_LOOKUPS MFG1
, MFG_LOOKUPS MFG2
, MFG_LOOKUPS MFG3
, CST_COST_GROUPS CST
, CST_COST_GROUPS CST2
WHERE MOH.HEADER_ID = MOL.HEADER_ID
AND MOH.MOVE_ORDER_TYPE = MFG1.LOOKUP_CODE
AND MFG1.LOOKUP_TYPE = 'MOVE_ORDER_TYPE'
AND MOH.HEADER_STATUS = MFG2.LOOKUP_CODE
AND MFG2.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
AND MOL.LINE_STATUS = MFG3.LOOKUP_CODE
AND MFG3.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
AND MOL.QUANTITY_DETAILED IS NOT NULL
AND MOL.QUANTITY > MOL.QUANTITY_DETAILED
AND CST.COST_GROUP_ID(+) = MOL.FROM_COST_GROUP_ID
AND CST2.COST_GROUP_ID(+) = MOL.TO_COST_GROUP_ID