VIEW: APPS.WIP_FLOW_ASSEMBLIES_V

Source

View Text - Preformatted

SELECT msik.concatenated_segments||':'||bor.alternate_routing_designator||':'||mrir.process_revision||':'||bos.operation_seq_num "NODE_LABEL", msik.organization_id, msik.concatenated_segments "ASSEMBLY_NAME", /*label*/ msik.inventory_item_id, /* pk */ bor.alternate_routing_designator, /*pk */ bor.line_id, /* fk */ bos.standard_operation_id, /* fk */ bos.routing_sequence_id, bos.operation_sequence_id, /* pk */ mrir.process_revision, /* pk */ trunc(mrir.effectivity_date) effectivity_date, /* pk */ msik.primary_uom_code, ml1.meaning "LOT_CONTROL", ml2.meaning "SERIAL_CONTROL", ml3.meaning "EFFECTIVITY_CONTROL", msik.description, 1 "NODE_TYPE" from mfg_lookups ml1, mfg_lookups ml2, mfg_lookups ml3, bom_operation_sequences bos, bom_operational_routings bor, mtl_routing_rev_highdate_v mrir, mtl_system_items_kfv MSIK WHERE mrir.inventory_item_id = msik.inventory_item_id and mrir.organization_id = msik.organization_id and bor.assembly_item_id = mrir.inventory_item_id and bor.organization_id = mrir.organization_id and bor.cfm_routing_flag = 1 and bos.routing_sequence_id = bor.common_routing_Sequence_id and bos.operation_type = 3 /* line operation type */ and ml1.lookup_code = msik.lot_control_code and ml1.lookup_type = 'MTL_LOT_CONTROL' and ml2.lookup_code = msik.serial_number_control_code and ml2.lookup_type = 'MTL_SERIAL_NUMBER' and ml3.lookup_code = 1 and ml3.lookup_type = 'MTL_EFFECTIVITY_CONTROL' and WIP_SFCB_Utilities.line_op_is_pending ( BOS.operation_seq_num, BOR.common_routing_sequence_id, MSIK.inventory_item_id, MSIK.organization_id, BOR.alternate_routing_designator ) = 1

View Text - HTML Formatted

SELECT MSIK.CONCATENATED_SEGMENTS||':'||BOR.ALTERNATE_ROUTING_DESIGNATOR||':'||MRIR.PROCESS_REVISION||':'||BOS.OPERATION_SEQ_NUM "NODE_LABEL"
, MSIK.ORGANIZATION_ID
, MSIK.CONCATENATED_SEGMENTS "ASSEMBLY_NAME"
, /*LABEL*/ MSIK.INVENTORY_ITEM_ID
, /* PK */ BOR.ALTERNATE_ROUTING_DESIGNATOR
, /*PK */ BOR.LINE_ID
, /* FK */ BOS.STANDARD_OPERATION_ID
, /* FK */ BOS.ROUTING_SEQUENCE_ID
, BOS.OPERATION_SEQUENCE_ID
, /* PK */ MRIR.PROCESS_REVISION
, /* PK */ TRUNC(MRIR.EFFECTIVITY_DATE) EFFECTIVITY_DATE
, /* PK */ MSIK.PRIMARY_UOM_CODE
, ML1.MEANING "LOT_CONTROL"
, ML2.MEANING "SERIAL_CONTROL"
, ML3.MEANING "EFFECTIVITY_CONTROL"
, MSIK.DESCRIPTION
, 1 "NODE_TYPE"
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, BOM_OPERATION_SEQUENCES BOS
, BOM_OPERATIONAL_ROUTINGS BOR
, MTL_ROUTING_REV_HIGHDATE_V MRIR
, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MRIR.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MRIR.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND BOR.ASSEMBLY_ITEM_ID = MRIR.INVENTORY_ITEM_ID
AND BOR.ORGANIZATION_ID = MRIR.ORGANIZATION_ID
AND BOR.CFM_ROUTING_FLAG = 1
AND BOS.ROUTING_SEQUENCE_ID = BOR.COMMON_ROUTING_SEQUENCE_ID
AND BOS.OPERATION_TYPE = 3 /* LINE OPERATION TYPE */
AND ML1.LOOKUP_CODE = MSIK.LOT_CONTROL_CODE
AND ML1.LOOKUP_TYPE = 'MTL_LOT_CONTROL'
AND ML2.LOOKUP_CODE = MSIK.SERIAL_NUMBER_CONTROL_CODE
AND ML2.LOOKUP_TYPE = 'MTL_SERIAL_NUMBER'
AND ML3.LOOKUP_CODE = 1
AND ML3.LOOKUP_TYPE = 'MTL_EFFECTIVITY_CONTROL'
AND WIP_SFCB_UTILITIES.LINE_OP_IS_PENDING ( BOS.OPERATION_SEQ_NUM
, BOR.COMMON_ROUTING_SEQUENCE_ID
, MSIK.INVENTORY_ITEM_ID
, MSIK.ORGANIZATION_ID
, BOR.ALTERNATE_ROUTING_DESIGNATOR ) = 1