INV_OPEN_PICK_LINES_V

Details

• Schema: FUSION

• Object owner: INV

• Object type: VIEW

Columns

Name

PICK_SLIP_LINE PICK_SLIP PICKWAVE_NUMBER ORGANIZATION_ID ORGANIZATION_CODE ITEM REVISION REQUESTED_QUANTITY UOM PRIMARY_QUANTITY ITEM_PRIMARY_UOM_CODE SECONDARY_REQUESTED_QUANTITY SECONDARY_UOM SOURCE_SUBINVENTORY TRANSACTION_SOURCE_TYPE_ID TRANSACTION_TYPE_ID TRANSACTION_ACTION_ID SOURCE_LOCATOR_ID SOURCE_LOCATOR DESTINATION_SUBINVENTORY DESTINATION_LOCATOR DESTINATION_ACCOUNT ORDER_NUMBER SOURCE_ORDER SOURCE_ORDER_LINE MOVEMENT_REQUEST MOVEMENT_REQUEST_LINE PICK_SLIP_TYPE SHIPMENT_NUMBER SHIPPING_METHOD SHIPPING_PRIORITY CUSTOMER SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_TYPE REQUIRED_DATE DUE_DATE TRANSACTION_TYPE TRANSACTION_DATE TRANSACTION_ID CREATION_DATE PROJECT_ID TASK_ID COUNTRY_OF_ORIGIN_CODE COMN_SUPPLY_PRJ_DEMAND_FLAG INVENTORY_ITEM_ID ITEM_DESCRIPTION ITEM_PRIMARY_IMAGE_URL LOT_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE SPLIT_LINE_FLAG

Query

SQL_Statement

SELECT mmtt.pick_slip_line_number pick_slip_line , mmtt.pick_slip_number pick_slip , wdd.batch_id pickwave_number , mmtt.organization_id , iop.organization_code organization_code , esi.item_number item , mmtt.revision , transaction_quantity requested_quantity , txnUom.unit_of_measure uom , mmtt.primary_quantity , mmtt.item_primary_uom_code , secondary_transaction_quantity secondary_requested_quantity , secondaryUom.unit_of_measure secondary_uom , mmtt.subinventory_code source_subinventory , mmtt.transaction_source_type_id , mmtt.transaction_type_id , mmtt.transaction_action_id , mmtt.locator_id source_locator_id , Decode(mmtt.locator_id, null , null, FND_FLEX_EXT.GET_SEGS('INV','MTLL',sourceLoc.structure_instance_number,mmtt.locator_id,sourceLoc.subinventory_id)) source_locator , transfer_subinventory destination_subinventory , DECODE(mmtt.transfer_to_location, null, null, FND_FLEX_EXT.GET_SEGS('INV','MTLL',destLoc.structure_instance_number,mmtt.transfer_to_location,destLoc.subinventory_id)) destination_locator , null destination_account , wdd.sales_order_number order_number , wdd.source_header_number source_order , wdd.source_line_number source_order_line , moh.request_number movement_request , mol.line_number movement_request_line , pickTypeLookup.meaning pick_slip_type , wnd.delivery_name shipment_number , decode (hp.party_name || '-' || modeOfTransportLK.meaning || '-' || serviceLevelLK.meaning , ' , hp.party_name || '-' || modeOfTransportLK.meaning || '-' || serviceLevelLK.meaning) shipping_method , shippingPriorityLK.meaning shipping_priority , hzp.party_name customer , wdd.ship_to_location_id , wdd.ship_to_location_type , wdd.date_requested required_date , NVL(wnd.initial_pickup_date, wdd.date_scheduled) due_date , ittv.transaction_type_name transaction_type , mmtt.transaction_date , mmtt.transaction_temp_id transaction_id , mmtt.creation_date , mmtt.PROJECT_ID , mmtt.TASK_ID , mmtt.COUNTRY_OF_ORIGIN_CODE , NVL(esi.COMN_SUPPLY_PRJ_DEMAND_FLAG, 'Y') COMN_SUPPLY_PRJ_DEMAND_FLAG , mmtt.inventory_item_id , esi.description ITEM_DESCRIPTION , esi.primary_image_url ITEM_PRIMARY_IMAGE_URL , esi.lot_control_code LOT_CONTROL_CODE , esi.serial_number_control_code SERIAL_NUMBER_CONTROL_CODE , decode(mmtt.transaction_header_id, null, 'Y', 'N') split_line_flag FROM inv_material_txns_temp mmtt , inv_txn_request_lines mol , inv_txn_request_headers moh , wsh_delivery_details wdd , wsh_delivery_assignments wda , wsh_new_deliveries wnd , egp_system_items_vl esi , inv_units_of_measure_vl txnUom , inv_units_of_measure_vl secondaryUom , fnd_lookups shippingPriorityLK , hz_parties hzp , inv_transaction_types_vl ittv , inv_item_locations sourceLoc , inv_item_locations destLoc , fnd_lookups pickTypeLookup , inv_org_parameters iop , hz_parties hp , fnd_lookups modeOfTransportLK , fnd_lookups serviceLevelLK WHERE mmtt.move_order_line_id = mol.line_id AND mmtt.transaction_type_id = mol.transaction_type_id AND moh.header_id = mol.header_id AND mmtt.organization_id = iop.organization_id AND wdd.delivery_detail_id = wda.delivery_detail_id(+) AND wda.delivery_id = wnd.delivery_id(+) AND wdd.released_status = 'S' AND mmtt.move_order_line_id = wdd.move_order_line_id AND mmtt.inventory_item_id = esi.inventory_item_id AND mmtt.organization_id = esi.organization_id AND mmtt.transaction_uom = txnUom.uom_code AND mmtt.secondary_uom_code = secondaryUom.uom_code (+) AND wdd.shipment_priority_code = shippingPriorityLK.lookup_code (+) AND shippingPriorityLK.lookup_type (+) = 'WSH_SHIPPING_PRIORITY' AND wdd.ship_to_party_id = hzp.party_id (+) AND mmtt.transaction_type_id = ittv.transaction_type_id AND mmtt.locator_id = sourceLoc.inventory_location_id (+) AND mmtt.subinventory_code = sourceLoc.subinventory_code (+) AND mmtt.organization_id = sourceLoc.organization_id (+) AND mmtt.transfer_to_location = destLoc.inventory_location_id (+) AND mmtt.transfer_subinventory = destLoc.subinventory_code (+) AND nvl(mmtt.transfer_organization, mmtt.organization_id) = destLoc.organization_id (+) AND moh.move_order_type =3 AND pickTypeLookup.lookup_type = 'INV_MOVE_ORDER_TYPE' AND pickTypeLookup.lookup_code = moh.move_order_type AND wdd.carrier_id = hp.party_id (+) AND wdd.service_level = serviceLevelLK.lookup_code (+) AND serviceLevelLK.lookup_type (+) = 'WSH_SERVICE_LEVELS' AND wdd.mode_of_transport = modeOfTransportLK.lookup_code (+) AND modeOfTransportLK.lookup_type (+) = 'WSH_MODE_OF_TRANSPORT' UNION ALL SELECT mmtt.pick_slip_line_number pick_slip_line , mmtt.pick_slip_number pick_slip , NULL pickwave_number , mmtt.organization_id , iop.organization_code organization_code , esi.item_number item , mmtt.revision , transaction_quantity requested_quantity , txnUom.unit_of_measure uom , mmtt.primary_quantity , mmtt.item_primary_uom_code , secondary_transaction_quantity secondary_requested_quantity , secondaryUom.unit_of_measure secondary_uom , mmtt.subinventory_code source_subinventory , mmtt.transaction_source_type_id , mmtt.transaction_type_id , mmtt.transaction_action_id , mmtt.locator_id source_locator_id , Decode(mmtt.locator_id, null , null, FND_FLEX_EXT.GET_SEGS('INV','MTLL',sourceLoc.structure_instance_number,mmtt.locator_id,sourceLoc.subinventory_id)) source_locator , transfer_subinventory destination_subinventory , DECODE(mmtt.transfer_to_location, null, null, FND_FLEX_EXT.GET_SEGS('INV','MTLL',destLoc.structure_instance_number,mmtt.transfer_to_location,destLoc.subinventory_id)) destination_locator , DECODE(mmtt.transaction_action_id, 1, (FND_FLEX_EXT.GET_SEGS('GL','GL#',(select chart_of_accounts_id from inv_organization_definitions_v where organization_id = mmtt.organization_id),distribution_account_id)), null) destination_account , moh.request_number order_number , null source_order , null source_order_line , moh.request_number movement_request , mol.line_number movement_request_line , pickTypeLookup.meaning pick_slip_type , NULL shipment_number , NULL shipping_method , null shipping_priority , null customer , null ship_to_location_id , null ship_to_location_type , mol.DATE_REQUIRED required_date , mol.date_required due_date , ittv.transaction_type_name transaction_type , mmtt.transaction_date , mmtt.transaction_temp_id transaction_id , mmtt.creation_date , mmtt.PROJECT_ID , mmtt.TASK_ID , mmtt.COUNTRY_OF_ORIGIN_CODE , NVL(esi.COMN_SUPPLY_PRJ_DEMAND_FLAG, 'Y') COMN_SUPPLY_PRJ_DEMAND_FLAG , mmtt.inventory_item_id , esi.description ITEM_DESCRIPTION , esi.primary_image_url ITEM_PRIMARY_IMAGE_URL , esi.lot_control_code LOT_CONTROL_CODE , esi.serial_number_control_code SERIAL_NUMBER_CONTROL_CODE , decode(mmtt.transaction_header_id, null, 'Y', 'N') split_line_flag FROM inv_material_txns_temp mmtt , inv_txn_request_lines mol , inv_txn_request_headers moh , egp_system_items_vl esi , inv_units_of_measure_vl txnUom , inv_units_of_measure_vl secondaryUom , inv_transaction_types_vl ittv , inv_item_locations sourceLoc , inv_item_locations destLoc , fnd_lookups pickTypeLookup , inv_org_parameters iop WHERE mmtt.move_order_line_id = mol.line_id AND mmtt.transaction_type_id = mol.transaction_type_id AND moh.header_id = mol.header_id AND mmtt.organization_id = iop.organization_id AND mmtt.inventory_item_id = esi.inventory_item_id AND mmtt.organization_id = esi.organization_id AND mmtt.transaction_uom = txnUom.uom_code AND mmtt.secondary_uom_code = secondaryUom.uom_code (+) AND mmtt.transaction_type_id = ittv.transaction_type_id AND mmtt.locator_id = sourceLoc.inventory_location_id (+) AND mmtt.subinventory_code = sourceLoc.subinventory_code (+) AND mmtt.organization_id = sourceLoc.organization_id (+) AND mmtt.transfer_to_location = destLoc.inventory_location_id (+) AND mmtt.transfer_subinventory = destLoc.subinventory_code (+) AND nvl(mmtt.transfer_organization, mmtt.organization_id) = destLoc.organization_id (+) AND moh.move_order_type IN (1,2) AND pickTypeLookup.lookup_type = 'INV_MOVE_ORDER_TYPE' AND pickTypeLookup.lookup_code = moh.move_order_type UNION ALL SELECT mmtt.pick_slip_line_number pick_slip_line , mmtt.pick_slip_number pick_slip , NULL pickwave_number , mmtt.organization_id , iop.organization_code organization_code , esi.item_number item , mmtt.revision , transaction_quantity requested_quantity , txnUom.unit_of_measure uom , mmtt.primary_quantity , mmtt.item_primary_uom_code , secondary_transaction_quantity secondary_requested_quantity , secondaryUom.unit_of_measure secondary_uom , mmtt.subinventory_code source_subinventory , mmtt.transaction_source_type_id , mmtt.transaction_type_id , mmtt.transaction_action_id , mmtt.locator_id source_locator_id , Decode(mmtt.locator_id, null , null, FND_FLEX_EXT.GET_SEGS('INV','MTLL',sourceLoc.structure_instance_number,mmtt.locator_id,sourceLoc.subinventory_id)) source_locator , transfer_subinventory destination_subinventory , DECODE(mmtt.transfer_to_location, null, null, FND_FLEX_EXT.GET_SEGS('INV','MTLL',destLoc.structure_instance_number,mmtt.transfer_to_location,destLoc.subinventory_id)) destination_locator , null destination_account , wwo.work_order_number order_number , wwo.work_order_number source_order , to_char(wwop.operation_seq_number) source_order_line , moh.request_number movement_request , mol.line_number movement_request_line , pickTypeLookup.meaning pick_slip_type , NULL shipment_number , NULL shipping_method , NULL shipping_priority , NULL customer , NULL ship_to_location_id , NULL ship_to_location_type , mol.DATE_REQUIRED required_date , mol.date_required due_date , ittv.transaction_type_name transaction_type , mmtt.transaction_date , mmtt.transaction_temp_id transaction_id , mmtt.creation_date , mmtt.PROJECT_ID , mmtt.TASK_ID , mmtt.COUNTRY_OF_ORIGIN_CODE , NVL(esi.COMN_SUPPLY_PRJ_DEMAND_FLAG, 'Y') COMN_SUPPLY_PRJ_DEMAND_FLAG , mmtt.inventory_item_id , esi.description ITEM_DESCRIPTION , esi.primary_image_url ITEM_PRIMARY_IMAGE_URL , esi.lot_control_code LOT_CONTROL_CODE , esi.serial_number_control_code SERIAL_NUMBER_CONTROL_CODE , decode(mmtt.transaction_header_id, null, 'Y', 'N') split_line_flag FROM inv_material_txns_temp mmtt , inv_txn_request_lines mol , inv_txn_request_headers moh , egp_system_items_vl esi , inv_units_of_measure_vl txnUom , inv_units_of_measure_vl secondaryUom , inv_transaction_types_vl ittv , inv_item_locations sourceLoc , inv_item_locations destLoc , wie_work_orders_b wwo , WIE_WO_OPERATIONS_B wwop , fnd_lookups pickTypeLookup , inv_org_parameters iop WHERE mmtt.move_order_line_id = mol.line_id AND mmtt.transaction_type_id = mol.transaction_type_id AND moh.header_id = mol.header_id AND mmtt.organization_id = iop.organization_id AND mmtt.TRANSACTION_SOURCE_ID = wwo.work_order_id AND mmtt.trx_source_line_id = wwop.operation_seq_number AND wwo.work_order_id = wwop.work_order_id AND mmtt.inventory_item_id = esi.inventory_item_id AND mmtt.organization_id = esi.organization_id AND mmtt.transaction_uom = txnUom.uom_code AND mmtt.secondary_uom_code = secondaryUom.uom_code (+) AND mmtt.transaction_type_id = ittv.transaction_type_id AND mmtt.locator_id = sourceLoc.inventory_location_id (+) AND mmtt.subinventory_code = sourceLoc.subinventory_code (+) AND mmtt.organization_id = sourceLoc.organization_id (+) AND mmtt.transfer_to_location = destLoc.inventory_location_id (+) AND mmtt.transfer_subinventory = destLoc.subinventory_code (+) AND nvl(mmtt.transfer_organization, mmtt.organization_id) = destLoc.organization_id (+) AND moh.move_order_type = 5 AND pickTypeLookup.lookup_type = 'INV_MOVE_ORDER_TYPE' AND pickTypeLookup.lookup_code = moh.move_order_type