INV_PICK_OPEN_LINES_V

Details

• Schema: FUSION

• Object owner: INV

• Object type: VIEW

Columns

Name

PICK_SLIP_LINE PICK_SLIP ORGANIZATION_ID ITEM REVISION REQUESTED_QUANTITY UOM SECONDARY_REQUESTED_QUANTITY SECONDARY_UOM SOURCE_SUBINVENTORY SOURCE_LOCATOR DESTINATION_SUBINVENTORY DESTINATION_LOCATOR DESTINATION_ACCOUNT SOURCE_ORDER SOURCE_ORDER_LINE MOVEMENT_REQUEST MOVEMENT_REQUEST_LINE SHIPPING_PRIORITY CUSTOMER SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_TYPE REQUIRED_DATE TRANSACTION_TYPE TRANSACTION_DATE TRANSACTION_ID PJC_CONTEXT_CATEGORY PJC_PROJECT_ID PJC_TASK_ID PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_WORK_TYPE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_RESERVED_ATTRIBUTE1 PJC_RESERVED_ATTRIBUTE2 PJC_RESERVED_ATTRIBUTE3 PJC_RESERVED_ATTRIBUTE4 PJC_RESERVED_ATTRIBUTE5 PJC_RESERVED_ATTRIBUTE6 PJC_RESERVED_ATTRIBUTE7 PJC_RESERVED_ATTRIBUTE8 PJC_RESERVED_ATTRIBUTE9 PJC_RESERVED_ATTRIBUTE10 PJC_USER_DEF_ATTRIBUTE1 PJC_USER_DEF_ATTRIBUTE2 PJC_USER_DEF_ATTRIBUTE3 PJC_USER_DEF_ATTRIBUTE4 PJC_USER_DEF_ATTRIBUTE5 PJC_USER_DEF_ATTRIBUTE6 PJC_USER_DEF_ATTRIBUTE7 PJC_USER_DEF_ATTRIBUTE8 PJC_USER_DEF_ATTRIBUTE9 PJC_USER_DEF_ATTRIBUTE10 PJC_ORGANIZATION_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_NUMBER6 ATTRIBUTE_NUMBER7 ATTRIBUTE_NUMBER8 ATTRIBUTE_NUMBER9 ATTRIBUTE_NUMBER10 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 ATTRIBUTE_TIMESTAMP1 ATTRIBUTE_TIMESTAMP2 ATTRIBUTE_TIMESTAMP3 ATTRIBUTE_TIMESTAMP4 ATTRIBUTE_TIMESTAMP5 INV_STRIPING_CATEGORY PROJECT_ID TASK_ID COUNTRY_OF_ORIGIN_CODE INV_RESERVED_ATTRIBUTE1 INV_RESERVED_ATTRIBUTE2 INV_USER_DEF_ATTRIBUTE1 INV_USER_DEF_ATTRIBUTE2 INV_USER_DEF_ATTRIBUTE3 INV_USER_DEF_ATTRIBUTE4 INV_USER_DEF_ATTRIBUTE5 INV_USER_DEF_ATTRIBUTE6 INV_USER_DEF_ATTRIBUTE7 INV_USER_DEF_ATTRIBUTE8 INV_USER_DEF_ATTRIBUTE9 INV_USER_DEF_ATTRIBUTE10 COMN_SUPPLY_PRJ_DEMAND_FLAG SO_BUSINESS_UNIT_ID MAXIMUM_PICKED_QUANTITY ERROR_CODE ERROR_EXPLANATION

Query

SQL_Statement

SELECT /*+ index(mmtt INV_MATERIAL_TXNS_TEMP_N13) */ mmtt.pick_slip_line_number pick_slip_line , mmtt.pick_slip_number as pick_slip , mmtt.organization_id , esib.item_number as item , mmtt.revision , transaction_quantity as requested_quantity , txnUom.unit_of_measure as uom , secondary_transaction_quantity as secondary_requested_quantity , secondaryUom.unit_of_measure as secondary_uom , mmtt.subinventory_code as source_subinventory , FND_FLEX_EXT.GET_SEGS('INV','MTLL',sourceLoc.structure_instance_number,mmtt.locator_id,sourceLoc.subinventory_id) AS source_locator , transfer_subinventory as destination_subinventory , FND_FLEX_EXT.GET_SEGS('INV','MTLL',destLoc.structure_instance_number,mmtt.transfer_to_location,destLoc.subinventory_id) AS destination_locator , FND_FLEX_EXT.GET_SEGS('GL','GL#',iodv.chart_of_accounts_id,distribution_account_id) AS destination_account , wdd.source_header_number as source_order , wdd.source_line_number as source_order_line , moh.request_number as movement_request , mol.line_number as movement_request_line , shippingPriorityLK.meaning as shipping_priority , hzp.party_name as customer , wdd.ship_to_location_id , wdd.ship_to_location_type , nvl(wdd.date_requested, mol.DATE_REQUIRED) as required_date , ittv.transaction_type_name as transaction_type , mmtt.transaction_date , mmtt.transaction_temp_id transaction_id , mmtt.PJC_CONTEXT_CATEGORY , mmtt.PJC_PROJECT_ID , mmtt.PJC_TASK_ID , mmtt.PJC_EXPENDITURE_TYPE_ID , mmtt.PJC_EXPENDITURE_ITEM_DATE , mmtt.PJC_BILLABLE_FLAG , mmtt.PJC_CAPITALIZABLE_FLAG , mmtt.PJC_WORK_TYPE_ID , mmtt.PJC_CONTRACT_ID , mmtt.PJC_CONTRACT_LINE_ID , mmtt.PJC_FUNDING_ALLOCATION_ID , mmtt.PJC_RESERVED_ATTRIBUTE1 , mmtt.PJC_RESERVED_ATTRIBUTE2 , mmtt.PJC_RESERVED_ATTRIBUTE3 , mmtt.PJC_RESERVED_ATTRIBUTE4 , mmtt.PJC_RESERVED_ATTRIBUTE5 , mmtt.PJC_RESERVED_ATTRIBUTE6 , mmtt.PJC_RESERVED_ATTRIBUTE7 , mmtt.PJC_RESERVED_ATTRIBUTE8 , mmtt.PJC_RESERVED_ATTRIBUTE9 , mmtt.PJC_RESERVED_ATTRIBUTE10 , mmtt.PJC_USER_DEF_ATTRIBUTE1 , mmtt.PJC_USER_DEF_ATTRIBUTE2 , mmtt.PJC_USER_DEF_ATTRIBUTE3 , mmtt.PJC_USER_DEF_ATTRIBUTE4 , mmtt.PJC_USER_DEF_ATTRIBUTE5 , mmtt.PJC_USER_DEF_ATTRIBUTE6 , mmtt.PJC_USER_DEF_ATTRIBUTE7 , mmtt.PJC_USER_DEF_ATTRIBUTE8 , mmtt.PJC_USER_DEF_ATTRIBUTE9 , mmtt.PJC_USER_DEF_ATTRIBUTE10 , mmtt.PJC_ORGANIZATION_ID , mmtt.ATTRIBUTE_CATEGORY , mmtt.ATTRIBUTE1 , mmtt.ATTRIBUTE2 , mmtt.ATTRIBUTE3 , mmtt.ATTRIBUTE4 , mmtt.ATTRIBUTE5 , mmtt.ATTRIBUTE6 , mmtt.ATTRIBUTE7 , mmtt.ATTRIBUTE8 , mmtt.ATTRIBUTE9 , mmtt.ATTRIBUTE10 , mmtt.ATTRIBUTE11 , mmtt.ATTRIBUTE12 , mmtt.ATTRIBUTE13 , mmtt.ATTRIBUTE14 , mmtt.ATTRIBUTE15 , mmtt.ATTRIBUTE16 , mmtt.ATTRIBUTE17 , mmtt.ATTRIBUTE18 , mmtt.ATTRIBUTE19 , mmtt.ATTRIBUTE20 , mmtt.ATTRIBUTE_NUMBER1 , mmtt.ATTRIBUTE_NUMBER2 , mmtt.ATTRIBUTE_NUMBER3 , mmtt.ATTRIBUTE_NUMBER4 , mmtt.ATTRIBUTE_NUMBER5 , mmtt.ATTRIBUTE_NUMBER6 , mmtt.ATTRIBUTE_NUMBER7 , mmtt.ATTRIBUTE_NUMBER8 , mmtt.ATTRIBUTE_NUMBER9 , mmtt.ATTRIBUTE_NUMBER10 , mmtt.ATTRIBUTE_DATE1 , mmtt.ATTRIBUTE_DATE2 , mmtt.ATTRIBUTE_DATE3 , mmtt.ATTRIBUTE_DATE4 , mmtt.ATTRIBUTE_DATE5 , mmtt.ATTRIBUTE_TIMESTAMP1 , mmtt.ATTRIBUTE_TIMESTAMP2 , mmtt.ATTRIBUTE_TIMESTAMP3 , mmtt.ATTRIBUTE_TIMESTAMP4 , mmtt.ATTRIBUTE_TIMESTAMP5 , mmtt.INV_STRIPING_CATEGORY , mmtt.PROJECT_ID , mmtt.TASK_ID , mmtt.COUNTRY_OF_ORIGIN_CODE , mmtt.INV_RESERVED_ATTRIBUTE1 , mmtt.INV_RESERVED_ATTRIBUTE2 , mmtt.INV_USER_DEF_ATTRIBUTE1 , mmtt.INV_USER_DEF_ATTRIBUTE2 , mmtt.INV_USER_DEF_ATTRIBUTE3 , mmtt.INV_USER_DEF_ATTRIBUTE4 , mmtt.INV_USER_DEF_ATTRIBUTE5 , mmtt.INV_USER_DEF_ATTRIBUTE6 , mmtt.INV_USER_DEF_ATTRIBUTE7 , mmtt.INV_USER_DEF_ATTRIBUTE8 , mmtt.INV_USER_DEF_ATTRIBUTE9 , mmtt.INV_USER_DEF_ATTRIBUTE10 , NVL(esib.COMN_SUPPLY_PRJ_DEMAND_FLAG, 'Y') AS COMN_SUPPLY_PRJ_DEMAND_FLAG , NVL(CASE WHEN (NVL(WDD.source_document_type_id, -1) IN (8, 10, 18)) THEN (SELECT TOH.req_bu_id FROM INV_TRANSFER_ORDER_HEADERS TOH WHERE TOH.header_number = WDD.sales_order_number) ELSE WDD.org_id END , IODV.business_unit_id) AS SO_BUSINESS_UNIT_ID , CASE WHEN ( MOH.move_order_type != 3 ) THEN NULL ELSE transaction_quantity + INV_REPLENISH_DETAIL_PUB.get_max_over_pick_quantity( MMTT.trx_source_line_id , MMTT.transaction_uom , MMTT.item_primary_uom_code , MMTT.inventory_item_id , MMTT.organization_id) END AS maximum_picked_quantity ,mmtt.error_code ,mmtt.error_explanation FROM inv_material_txns_temp mmtt , inv_txn_request_lines mol , inv_txn_request_headers moh , wsh_delivery_details wdd , egp_system_items_vl esib , inv_units_of_measure_vl txnUom , inv_units_of_measure_vl secondaryUom , inv_organization_definitions_v iodv , fnd_lookups shippingPriorityLK , hz_parties hzp , hz_party_usg_assignments hza , inv_transaction_types_vl ittv , inv_item_locations sourceLoc , inv_item_locations destLoc 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.trx_source_line_id = wdd.delivery_detail_id (+) AND mmtt.inventory_item_id = esib.inventory_item_id and mmtt.organization_id = esib.organization_id AND mmtt.transaction_uom = txnUom.uom_code AND mmtt.secondary_uom_code = secondaryUom.uom_code (+) AND nvl(transfer_organization, mmtt.organization_id) = iodv.organization_id 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 hzp.party_id = hza.party_id (+) AND hza.status_flag (+) = 'A' AND hzp.party_type (+) = 'ORGANIZATION' AND hza.party_usage_code (+) = 'CUSTOMER' 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,3) UNION ALL SELECT /*+ index(mmtt INV_MATERIAL_TXNS_TEMP_N13) */ mmtt.pick_slip_line_number pick_slip_line , mmtt.pick_slip_number as pick_slip , mmtt.organization_id , esib.item_number as item , mmtt.revision , transaction_quantity as requested_quantity , txnUom.unit_of_measure as uom , secondary_transaction_quantity as secondary_requested_quantity , secondaryUom.unit_of_measure as secondary_uom , mmtt.subinventory_code as source_subinventory , FND_FLEX_EXT.GET_SEGS('INV','MTLL',sourceLoc.structure_instance_number,mmtt.locator_id,sourceLoc.subinventory_id) AS source_locator , transfer_subinventory as destination_subinventory , FND_FLEX_EXT.GET_SEGS('INV','MTLL',destLoc.structure_instance_number,mmtt.transfer_to_location,destLoc.subinventory_id) AS destination_locator , FND_FLEX_EXT.GET_SEGS('GL','GL#',iodv.chart_of_accounts_id,distribution_account_id) AS destination_account , wwo.work_order_number as source_order , to_char(wwop.operation_seq_number) as source_order_line , moh.request_number as movement_request , mol.line_number as movement_request_line , NULL as shipping_priority , NULL as customer , NULL as ship_to_location_id , NULL AS ship_to_location_type , mol.DATE_REQUIRED as required_date , ittv.transaction_type_name as transaction_type , mmtt.transaction_date , mmtt.transaction_temp_id transaction_id , mmtt.PJC_CONTEXT_CATEGORY , mmtt.PJC_PROJECT_ID , mmtt.PJC_TASK_ID , mmtt.PJC_EXPENDITURE_TYPE_ID , mmtt.PJC_EXPENDITURE_ITEM_DATE , mmtt.PJC_BILLABLE_FLAG , mmtt.PJC_CAPITALIZABLE_FLAG , mmtt.PJC_WORK_TYPE_ID , mmtt.PJC_CONTRACT_ID , mmtt.PJC_CONTRACT_LINE_ID , mmtt.PJC_FUNDING_ALLOCATION_ID , mmtt.PJC_RESERVED_ATTRIBUTE1 , mmtt.PJC_RESERVED_ATTRIBUTE2 , mmtt.PJC_RESERVED_ATTRIBUTE3 , mmtt.PJC_RESERVED_ATTRIBUTE4 , mmtt.PJC_RESERVED_ATTRIBUTE5 , mmtt.PJC_RESERVED_ATTRIBUTE6 , mmtt.PJC_RESERVED_ATTRIBUTE7 , mmtt.PJC_RESERVED_ATTRIBUTE8 , mmtt.PJC_RESERVED_ATTRIBUTE9 , mmtt.PJC_RESERVED_ATTRIBUTE10 , mmtt.PJC_USER_DEF_ATTRIBUTE1 , mmtt.PJC_USER_DEF_ATTRIBUTE2 , mmtt.PJC_USER_DEF_ATTRIBUTE3 , mmtt.PJC_USER_DEF_ATTRIBUTE4 , mmtt.PJC_USER_DEF_ATTRIBUTE5 , mmtt.PJC_USER_DEF_ATTRIBUTE6 , mmtt.PJC_USER_DEF_ATTRIBUTE7 , mmtt.PJC_USER_DEF_ATTRIBUTE8 , mmtt.PJC_USER_DEF_ATTRIBUTE9 , mmtt.PJC_USER_DEF_ATTRIBUTE10 , mmtt.PJC_ORGANIZATION_ID , mmtt.ATTRIBUTE_CATEGORY , mmtt.ATTRIBUTE1 , mmtt.ATTRIBUTE2 , mmtt.ATTRIBUTE3 , mmtt.ATTRIBUTE4 , mmtt.ATTRIBUTE5 , mmtt.ATTRIBUTE6 , mmtt.ATTRIBUTE7 , mmtt.ATTRIBUTE8 , mmtt.ATTRIBUTE9 , mmtt.ATTRIBUTE10 , mmtt.ATTRIBUTE11 , mmtt.ATTRIBUTE12 , mmtt.ATTRIBUTE13 , mmtt.ATTRIBUTE14 , mmtt.ATTRIBUTE15 , mmtt.ATTRIBUTE16 , mmtt.ATTRIBUTE17 , mmtt.ATTRIBUTE18 , mmtt.ATTRIBUTE19 , mmtt.ATTRIBUTE20 , mmtt.ATTRIBUTE_NUMBER1 , mmtt.ATTRIBUTE_NUMBER2 , mmtt.ATTRIBUTE_NUMBER3 , mmtt.ATTRIBUTE_NUMBER4 , mmtt.ATTRIBUTE_NUMBER5 , mmtt.ATTRIBUTE_NUMBER6 , mmtt.ATTRIBUTE_NUMBER7 , mmtt.ATTRIBUTE_NUMBER8 , mmtt.ATTRIBUTE_NUMBER9 , mmtt.ATTRIBUTE_NUMBER10 , mmtt.ATTRIBUTE_DATE1 , mmtt.ATTRIBUTE_DATE2 , mmtt.ATTRIBUTE_DATE3 , mmtt.ATTRIBUTE_DATE4 , mmtt.ATTRIBUTE_DATE5 , mmtt.ATTRIBUTE_TIMESTAMP1 , mmtt.ATTRIBUTE_TIMESTAMP2 , mmtt.ATTRIBUTE_TIMESTAMP3 , mmtt.ATTRIBUTE_TIMESTAMP4 , mmtt.ATTRIBUTE_TIMESTAMP5 , mmtt.INV_STRIPING_CATEGORY , mmtt.PROJECT_ID , mmtt.TASK_ID , mmtt.COUNTRY_OF_ORIGIN_CODE , mmtt.INV_RESERVED_ATTRIBUTE1 , mmtt.INV_RESERVED_ATTRIBUTE2 , mmtt.INV_USER_DEF_ATTRIBUTE1 , mmtt.INV_USER_DEF_ATTRIBUTE2 , mmtt.INV_USER_DEF_ATTRIBUTE3 , mmtt.INV_USER_DEF_ATTRIBUTE4 , mmtt.INV_USER_DEF_ATTRIBUTE5 , mmtt.INV_USER_DEF_ATTRIBUTE6 , mmtt.INV_USER_DEF_ATTRIBUTE7 , mmtt.INV_USER_DEF_ATTRIBUTE8 , mmtt.INV_USER_DEF_ATTRIBUTE9 , mmtt.INV_USER_DEF_ATTRIBUTE10 , NVL(esib.COMN_SUPPLY_PRJ_DEMAND_FLAG, 'Y') AS COMN_SUPPLY_PRJ_DEMAND_FLAG , iodv.BUSINESS_UNIT_ID AS SO_BUSINESS_UNIT_ID , NULL AS maximum_picked_quantity , mmtt.error_code , mmtt.error_explanation FROM inv_material_txns_temp mmtt , inv_txn_request_lines mol , inv_txn_request_headers moh , egp_system_items_vl esib , inv_units_of_measure_vl txnUom , inv_units_of_measure_vl secondaryUom , inv_organization_definitions_v iodv , inv_transaction_types_vl ittv , inv_item_locations sourceLoc , inv_item_locations destLoc , wie_work_orders_vl wwo , WIE_WO_OPERATIONS_VL wwop 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.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 = esib.inventory_item_id and mmtt.organization_id = esib.organization_id AND mmtt.transaction_uom = txnUom.uom_code AND mmtt.secondary_uom_code = secondaryUom.uom_code (+) AND nvl(transfer_organization, mmtt.organization_id) = iodv.organization_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 = 5