CSE_GENEALOGY_OBJECTS_V

Details

• Schema: FUSION

• Object owner: CSE

• Object type: VIEW

Columns

Name

SERIAL_LOT_INDICATOR GEN_OBJECT_ID ITEM_ID ORGANIZATION_ID SERIAL_NUMBER SUPPLIER_SERIAL_NUMBER LOT_NUMBER PARENT_LOT_NUMBER LOT_DESCRIPTION SUPPLIER_LOT_NUMBER ORIGINATION_DATE SUPPLIER_ID SERIAL_STATUS_ID SERIAL_STATUS SERIAL_CURRENT_SUBINV_CODE SERIAL_CURRENT_LOCATOR_ID SERIAL_SHIPMENT_DATE MATERIAL_STATUS_ID MATERIAL_STATUS ORIGINATION_QUANTITY ORIGINATION_DOCUMENT_TYPE ORIGINATION_DOCUMENT__NUMBER LOT_GRADE_CODE LOT_GRADE_DESC LOT_EXPIRATION_DATE LOT_MATURITY_DATE LOT_RETEST_DATE LOT_HOLD_DATE LOT_EXPIRATION_ACTION_CODE LOT_EXPIRATION_ACTION_DESC LOT_EXPIRATION_ACTION_DATE LOT_DISABLE_FLAG LOT_DISABLE_FLAG_MEANING

Query

SQL_Statement

SELECT 'S' serial_lot_indicator, gen_obj.gen_object_id gen_object_id, isn.inventory_item_id item_id, gen_obj.organization_id organization_id, isn.serial_number serial_number, isn.vendor_serial_number supplier_serial_number, gen_obj.lot_number lot_number, to_char(null) parent_lot_number, to_char(null) lot_description, to_char(null) supplier_lot_number, isn.initialization_date origination_date, isn.original_unit_vendor_id supplier_id, isn.current_status serial_status_id, serial_status_lookup.meaning serial_status, isn.current_subinventory_code serial_current_subinv_code, isn.current_locator_id serial_current_locator_id, isn.ship_date serial_shipment_date, isn.status_id material_status_id, serial_material_status.status_code material_status, gen_obj.origination_quantity origination_quantity, gen_obj.origination_document_type origination_document_type, gen_obj.origination_document_number origination_document__number, to_char(null) lot_grade_code, to_char(null) lot_grade_desc, to_date(null) lot_expiration_date, to_date(null) lot_maturity_date, to_date(null) lot_retest_date, to_date(null) lot_hold_date, to_char(null) lot_expiration_action_code, to_char(null) lot_expiration_action_desc, to_date(null) lot_expiration_action_date, to_char(null) lot_disable_flag, to_char(null) lot_disable_flag_meaning FROM inv_serial_numbers isn, cse_genealogy_objects gen_obj, fnd_lookups serial_status_lookup, inv_material_statuses_vl serial_material_status WHERE gen_obj.serial_number IS NOT NULL AND gen_obj.item_id = isn.inventory_item_id AND gen_obj.serial_number = isn.serial_number AND serial_status_lookup.lookup_type = 'INV_SERIAL_NUM_STATUS' AND serial_status_lookup.lookup_code = decode(isn.current_status,'6','1',isn.current_status) AND serial_material_status.status_id = isn.status_id UNION SELECT 'L' serial_lot_indicator, gen_obj.gen_object_id gen_object_id, iln.inventory_item_id item_id, iln.organization_id organization_id, to_char(null) serial_number, to_char(null) supplier_serial_number, iln.lot_number lot_number, iln.parent_lot_number parent_lot_number, iln.description lot_description, iln.supplier_lot_number supplier_lot_number, iln.origination_date origination_date, iln.vendor_id supplier_id, to_char(null) serial_status_id, to_char(null) serial_status, to_char(null) serial_current_subinv_code, cast(null as number(18)) serial_current_locator_id, to_date(null) serial_shipment_date, iln.status_id material_status_id, lot_material_status.status_code material_status, gen_obj.origination_quantity origination_quantity, gen_obj.origination_document_type origination_document_type, gen_obj.origination_document_number origination_document__number, iln.grade_code lot_grade_code, ig.description lot_grade_desc, iln.expiration_date lot_expiration_date, iln.maturity_date lot_maturity_date, iln.retest_date lot_retest_date, iln.hold_date lot_hold_date, iln.expiration_action_code lot_expiration_action_code, ia.description lot_expiration_action_desc, iln.expiration_action_date lot_expiration_action_date, nvl(iln.disable_flag,'2') lot_disable_flag, LotDisableFlagLookup.meaning lot_disable_flag_meaning FROM inv_lot_numbers iln, cse_genealogy_objects gen_obj, inv_material_statuses_vl lot_material_status, inv_grades ig, inv_actions_vl ia, fnd_lookups LotDisableFlagLookup WHERE gen_obj.lot_number IS NOT NULL AND gen_obj.serial_number IS NULL AND gen_obj.item_id = iln.inventory_item_id AND gen_obj.organization_id = iln.organization_id AND gen_obj.lot_number = iln.lot_number AND lot_material_status.status_id = nvl(iln.status_id,1) AND ig.grade_code(+) = iln.grade_code AND ia.action_code(+) = iln.expiration_action_code AND LotDisableFlagLookup.lookup_type(+)= 'INV_YES_NO_NUMERIC' AND LotDisableFlagLookup.lookup_code(+)= nvl(iln.disable_flag,'2')