VIEW: APPS.CSI_TXN_ERRORS_V

Source

View Text - Preformatted

SELECT cte.transaction_error_id transaction_error_id, cte.transaction_id transaction_id, cte.source_type source_type, cte.source_id source_id, cte.transaction_type_id transaction_type_id, cte.inv_material_transaction_id inv_material_transaction_id, cte.transaction_error_date transaction_error_date, mmt.inventory_item_id inventory_item_id, mmt.organization_id inv_organization_id, mmt.revision revision, mmt.subinventory_code inv_subinventory, mmt.locator_id locator_id, null lot_number, mut.serial_number serial_number, mmt.transaction_quantity shipped_quantity, mmt.transaction_uom transaction_uom, mmt.transaction_date transaction_date, msi.reservable_type reservable_type, haou.location_id hr_location_id, msei.location_id subinv_location_id, cte.processed_flag processed_flag, cte.src_lot_ctrl_code src_lot_ctrl_code, cte.src_serial_num_ctrl_code src_serial_num_ctrl_code, cte.src_location_ctrl_code src_location_ctrl_code, cte.src_rev_qty_ctrl_code src_rev_qty_ctrl_code, cte.dst_lot_ctrl_code dst_lot_ctrl_code, cte.dst_serial_num_ctrl_code dst_serial_num_ctrl_code, cte.dst_location_ctrl_code dst_location_ctrl_code, cte.dst_rev_qty_ctrl_code dst_rev_qty_ctrl_code, cte.comms_nl_trackable_flag comms_nl_trackable_flag FROM csi_txn_errors cte, mtl_system_items msi, mtl_unit_transactions mut, mtl_material_transactions mmt, mtl_secondary_inventories msei, hr_all_organization_units haou WHERE mmt.transaction_id(+) = cte.inv_material_transaction_id AND mmt.transaction_id = mut.transaction_id(+) AND msi.organization_id = mmt.organization_id AND msi.inventory_item_id = mmt.inventory_item_id AND msi.lot_control_code = 1 AND mmt.organization_id = haou.organization_id(+) AND mmt.subinventory_code = msei.secondary_inventory_name(+) AND mmt.organization_id = msei.organization_id(+) UNION SELECT cte.transaction_error_id transaction_error_id, cte.transaction_id transaction_id, cte.source_type source_type, cte.source_id source_id, cte.transaction_type_id transaction_type_id, cte.inv_material_transaction_id inv_material_transaction_id, cte.transaction_error_date transaction_error_date, mmt.inventory_item_id inventory_item_id, mmt.organization_id inv_organization_id, mmt.revision revision, mmt.subinventory_code subinventory, mmt.locator_id locator_id, mtln.lot_number lot_number, mut.serial_number serial_number, mtln.transaction_quantity shipped_quantity, mmt.transaction_uom transaction_uom, mmt.transaction_date transaction_date, msi.reservable_type reservable_type, haou.location_id hr_location_id, msei.location_id subinv_location_id, cte.processed_flag processed_flag, cte.src_lot_ctrl_code src_lot_ctrl_code, cte.src_serial_num_ctrl_code src_serial_num_ctrl_code, cte.src_location_ctrl_code src_location_ctrl_code, cte.src_rev_qty_ctrl_code src_rev_qty_ctrl_code, cte.dst_lot_ctrl_code dst_lot_ctrl_code, cte.dst_serial_num_ctrl_code dst_serial_num_ctrl_code, cte.dst_location_ctrl_code dst_location_ctrl_code, cte.dst_rev_qty_ctrl_code dst_rev_qty_ctrl_code, cte.comms_nl_trackable_flag comms_nl_trackable_flag FROM csi_txn_errors cte, mtl_system_items msi, mtl_unit_transactions mut, mtl_transaction_lot_numbers mtln, mtl_material_transactions mmt, mtl_secondary_inventories msei, hr_all_organization_units haou WHERE mmt.transaction_id(+) = cte.inv_material_transaction_id AND mmt.transaction_id = mtln.transaction_id(+) AND mtln.serial_transaction_id = mut.transaction_id(+) AND msi.organization_id = mmt.organization_id AND msi.inventory_item_id = mmt.inventory_item_id AND msi.lot_control_code = 2 AND mmt.organization_id = haou.organization_id(+) AND mmt.subinventory_code = msei.secondary_inventory_name(+) AND mmt.organization_id = msei.organization_id(+)

View Text - HTML Formatted

SELECT CTE.TRANSACTION_ERROR_ID TRANSACTION_ERROR_ID
, CTE.TRANSACTION_ID TRANSACTION_ID
, CTE.SOURCE_TYPE SOURCE_TYPE
, CTE.SOURCE_ID SOURCE_ID
, CTE.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID
, CTE.INV_MATERIAL_TRANSACTION_ID INV_MATERIAL_TRANSACTION_ID
, CTE.TRANSACTION_ERROR_DATE TRANSACTION_ERROR_DATE
, MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MMT.ORGANIZATION_ID INV_ORGANIZATION_ID
, MMT.REVISION REVISION
, MMT.SUBINVENTORY_CODE INV_SUBINVENTORY
, MMT.LOCATOR_ID LOCATOR_ID
, NULL LOT_NUMBER
, MUT.SERIAL_NUMBER SERIAL_NUMBER
, MMT.TRANSACTION_QUANTITY SHIPPED_QUANTITY
, MMT.TRANSACTION_UOM TRANSACTION_UOM
, MMT.TRANSACTION_DATE TRANSACTION_DATE
, MSI.RESERVABLE_TYPE RESERVABLE_TYPE
, HAOU.LOCATION_ID HR_LOCATION_ID
, MSEI.LOCATION_ID SUBINV_LOCATION_ID
, CTE.PROCESSED_FLAG PROCESSED_FLAG
, CTE.SRC_LOT_CTRL_CODE SRC_LOT_CTRL_CODE
, CTE.SRC_SERIAL_NUM_CTRL_CODE SRC_SERIAL_NUM_CTRL_CODE
, CTE.SRC_LOCATION_CTRL_CODE SRC_LOCATION_CTRL_CODE
, CTE.SRC_REV_QTY_CTRL_CODE SRC_REV_QTY_CTRL_CODE
, CTE.DST_LOT_CTRL_CODE DST_LOT_CTRL_CODE
, CTE.DST_SERIAL_NUM_CTRL_CODE DST_SERIAL_NUM_CTRL_CODE
, CTE.DST_LOCATION_CTRL_CODE DST_LOCATION_CTRL_CODE
, CTE.DST_REV_QTY_CTRL_CODE DST_REV_QTY_CTRL_CODE
, CTE.COMMS_NL_TRACKABLE_FLAG COMMS_NL_TRACKABLE_FLAG
FROM CSI_TXN_ERRORS CTE
, MTL_SYSTEM_ITEMS MSI
, MTL_UNIT_TRANSACTIONS MUT
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SECONDARY_INVENTORIES MSEI
, HR_ALL_ORGANIZATION_UNITS HAOU
WHERE MMT.TRANSACTION_ID(+) = CTE.INV_MATERIAL_TRANSACTION_ID
AND MMT.TRANSACTION_ID = MUT.TRANSACTION_ID(+)
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.LOT_CONTROL_CODE = 1
AND MMT.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND MMT.SUBINVENTORY_CODE = MSEI.SECONDARY_INVENTORY_NAME(+)
AND MMT.ORGANIZATION_ID = MSEI.ORGANIZATION_ID(+) UNION SELECT CTE.TRANSACTION_ERROR_ID TRANSACTION_ERROR_ID
, CTE.TRANSACTION_ID TRANSACTION_ID
, CTE.SOURCE_TYPE SOURCE_TYPE
, CTE.SOURCE_ID SOURCE_ID
, CTE.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID
, CTE.INV_MATERIAL_TRANSACTION_ID INV_MATERIAL_TRANSACTION_ID
, CTE.TRANSACTION_ERROR_DATE TRANSACTION_ERROR_DATE
, MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MMT.ORGANIZATION_ID INV_ORGANIZATION_ID
, MMT.REVISION REVISION
, MMT.SUBINVENTORY_CODE SUBINVENTORY
, MMT.LOCATOR_ID LOCATOR_ID
, MTLN.LOT_NUMBER LOT_NUMBER
, MUT.SERIAL_NUMBER SERIAL_NUMBER
, MTLN.TRANSACTION_QUANTITY SHIPPED_QUANTITY
, MMT.TRANSACTION_UOM TRANSACTION_UOM
, MMT.TRANSACTION_DATE TRANSACTION_DATE
, MSI.RESERVABLE_TYPE RESERVABLE_TYPE
, HAOU.LOCATION_ID HR_LOCATION_ID
, MSEI.LOCATION_ID SUBINV_LOCATION_ID
, CTE.PROCESSED_FLAG PROCESSED_FLAG
, CTE.SRC_LOT_CTRL_CODE SRC_LOT_CTRL_CODE
, CTE.SRC_SERIAL_NUM_CTRL_CODE SRC_SERIAL_NUM_CTRL_CODE
, CTE.SRC_LOCATION_CTRL_CODE SRC_LOCATION_CTRL_CODE
, CTE.SRC_REV_QTY_CTRL_CODE SRC_REV_QTY_CTRL_CODE
, CTE.DST_LOT_CTRL_CODE DST_LOT_CTRL_CODE
, CTE.DST_SERIAL_NUM_CTRL_CODE DST_SERIAL_NUM_CTRL_CODE
, CTE.DST_LOCATION_CTRL_CODE DST_LOCATION_CTRL_CODE
, CTE.DST_REV_QTY_CTRL_CODE DST_REV_QTY_CTRL_CODE
, CTE.COMMS_NL_TRACKABLE_FLAG COMMS_NL_TRACKABLE_FLAG
FROM CSI_TXN_ERRORS CTE
, MTL_SYSTEM_ITEMS MSI
, MTL_UNIT_TRANSACTIONS MUT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SECONDARY_INVENTORIES MSEI
, HR_ALL_ORGANIZATION_UNITS HAOU
WHERE MMT.TRANSACTION_ID(+) = CTE.INV_MATERIAL_TRANSACTION_ID
AND MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID(+)
AND MTLN.SERIAL_TRANSACTION_ID = MUT.TRANSACTION_ID(+)
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.LOT_CONTROL_CODE = 2
AND MMT.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND MMT.SUBINVENTORY_CODE = MSEI.SECONDARY_INVENTORY_NAME(+)
AND MMT.ORGANIZATION_ID = MSEI.ORGANIZATION_ID(+)