Receiving shipment fact table
Module: Purchasing
SHIPMENT_LINE_ID
Name | Datatype | Length | Precision | Not Null | Comments | Referred Table | Referred Column |
---|---|---|---|---|---|---|---|
SHIPMENT_LINE_ID | NUMBER | 38 | 0 | True | Receiving shipment receipt line unique identifier. | ||
SOURCE_RECORD_ID | VARCHAR2 | 256 | Source record identifier. | ||||
SHIPMENT_HEADER_ID | NUMBER | 38 | 0 | Receiving shipment receipt header unique identifier. | |||
RECEIPT_NUM | VARCHAR2 | 32 | Receipt number for the receiving shipment receipt header. | ||||
SUPPLIER_ID | NUMBER | 38 | 0 | Supplier unique identifier. | DW_PARTY_D | SUPPLIER_ID | |
SUPPLIER_SITE_ID | NUMBER | 38 | 0 | Supplier site unique identifier. | DW_SUPPLIER_SITE_D | SUPPLIER_SITE_ID | |
SHIP_TO_ORG_ID | NUMBER | 38 | 0 | Inventory organization unique identifier where the receipt and shipment are created. | DW_INV_ORGANIZATION_D | INV_ORGANIZATION_ID | |
EXPECTED_RECEIPT_DATE | DATE | This column stores the expected arrival date of the shipment. | |||||
ASN_TYPE | VARCHAR2 | 32 | ASN (Advanced Shipment Notification) type of the shipment. Valid values are ASN, ASBN and STD for a receipt. | DW_RCV_ASN_TYPE_D_TL | CODE | ||
SHIPPED_DATE | DATE | This column stores the date when the shipment was created. | |||||
SHIPMENT_NUM | VARCHAR2 | 32 | Shipment number assigned by the shipment source. Corresponds to the ASN number, Intransit number and Transfer order number. | ||||
FREIGHT_CARRIER_ID | NUMBER | 38 | 0 | This column stores the Freight Carrier unique identifier. | DW_PARTY_D | PARTY_ID | |
PACKING_SLIP | VARCHAR2 | 32 | This column stores the Packing slip number. | ||||
BILL_OF_LADING | VARCHAR2 | 32 | This column stores the Bill of lading number. | ||||
WAYBILL_AIRBILL_NUM | VARCHAR2 | 32 | This column stores the waybill or air bill number. | ||||
NUM_OF_CONTAINERS | NUMBER | 38 | 0 | This column stores the number of containers in the shipment. | |||
COMMENTS | VARCHAR2 | 999 | This column stores the note created by the receiver. | ||||
HEADER_CREATION_DATE | TIMESTAMP | Indicates the date and time of the creation of the header row. | |||||
HEADER_CREATION_DATE_1 | DATE | Indicates the date of the creation of the header row. | |||||
HEADER_CREATED_BY_USER | VARCHAR2 | 128 | Indicates the user who created the header row. | DW_USER_D | USERNAME | ||
HEADER_LAST_UPDATE_DATE | TIMESTAMP | Indicates the date and time of the last update of the header row. | |||||
HEADER_LAST_UPDATED_BY_USER | VARCHAR2 | 128 | Indicates the user who last updated the header row. | DW_USER_D | USERNAME | ||
PROC_FISCAL_PERIOD_SET_NAME | VARCHAR2 | 16 | Accounting calendar name based on primary ledger of procurement org. | DW_FISCAL_DAY_D | FISCAL_PERIOD_SET_NAME | ||
PROC_FISCAL_PERIOD_TYPE | VARCHAR2 | 16 | Accounting period type based on primary ledger of procurement org. | DW_FISCAL_DAY_D | FISCAL_PERIOD_TYPE | ||
INV_FISCAL_PERIOD_SET_NAME | VARCHAR2 | 16 | Accounting calendar name based on primary ledger of receiving inventory org. | DW_FISCAL_DAY_D | FISCAL_PERIOD_SET_NAME | ||
INV_FISCAL_PERIOD_TYPE | VARCHAR2 | 16 | Accounting period type based on primary ledger of receiving inventory org. | DW_FISCAL_DAY_D | FISCAL_PERIOD_TYPE | ||
REQ_FISCAL_PERIOD_SET_NAME | VARCHAR2 | 16 | Accounting calendar name based on primary ledger of requisition org. | DW_FISCAL_DAY_D | FISCAL_PERIOD_SET_NAME | ||
REQ_FISCAL_PERIOD_TYPE | VARCHAR2 | 16 | Accounting period type based on primary ledger of requisition org. | DW_FISCAL_DAY_D | FISCAL_PERIOD_TYPE | ||
FISCAL_PERIOD_NAME | VARCHAR2 | 64 | Accounting period name. | DW_FISCAL_DAY_D | FISCAL_PERIOD_NAME | ||
SHIPMENT_LINE_NUM | NUMBER | 38 | 0 | This column stores the number corresponding to the shipment line. | |||
ITEM_ID | NUMBER | 38 | 0 | This column stores the inventory item unique identifier of the document for which the receiving shipment receipt line is created. Columns (INVENTORY_ITEM_ID, SHIP_TO_ORG_ID) joins DW_INVENTORY_ITEM_D (INVENTORY_ITEM_ID, ORGANIZATION_ID) | DW_INVENTORY_ITEM_D | INVENTORY_ITEM_ID ,ORGANIZATION_ID | |
ITEM_DESCRIPTION | VARCHAR2 | 256 | This column stores the purchase order line item description for PO receipts. For other documents, it stores the inventory item description. | ||||
SUPPLIER_ITEM_NAME | VARCHAR2 | 512 | Supplier item number. | ||||
CATEGORY_ID | NUMBER | 38 | 0 | This column stores the Item category unique identifier. | DW_ITEM_CATEGORIES_D | CATEGORY_ID | |
PO_HEADER_ID | NUMBER | 38 | 0 | This column stores the purchase order header unique identifier. | |||
PO_LINE_ID | NUMBER | 38 | 0 | This column stores the purchase order line unique identifier. | |||
PO_LINE_LOCATION_ID | NUMBER | 38 | 0 | This column stores the purchase order schedule unique identifier. | |||
PO_DISTRIBUTION_ID | NUMBER | 38 | 0 | This column stores the purchase order distribution unique identifier. This is populated once shipment lines are put away i.e. delivered. | |||
QUANTITY_SHIPPED | NUMBER | This column stores the net quantity shipped for the receiving shipment receipt line. | |||||
QUANTITY_RECEIVED | NUMBER | This column stores the net quantity received for the receiving shipment receipt line. | |||||
QUANTITY_ACCEPTED | NUMBER | This column stores the net quantity accepted for the receiving shipment receipt line. | |||||
QUANTITY_REJECTED | NUMBER | This column stores the net quantity rejected for the receiving shipment receipt line. | |||||
QUANTITY_DELIVERED | NUMBER | This column stores the net quantity delivered for the receiving shipment receipt line. | |||||
QUANTITY_RETURNED | NUMBER | This column stores the net quantity returned for the receiving shipment receipt line. | |||||
QUANTITY_CONSUMED | NUMBER | Supplier consigned Inventory consumed quantity. | |||||
UOM_CODE | VARCHAR2 | 16 | This column stores the unit of measure code for the receiving shipment receipt line. | DW_UNIT_OF_MEASURE_D_TL | UOM_CODE | ||
CONSIGNED_FLAG | VARCHAR2 | 16 | This column if the receipt line is created for consigned material. Values are Y/N/NO VALUE | DW_Y_N_D_TL | CODE | ||
SHIP_TO_LOCATION_ID | NUMBER | 38 | 0 | Receiving location unique identifier. | DW_WORKER_LOCATION_D | LOCATION_ID | |
DESTINATION_TYPE_CODE | VARCHAR2 | 32 | Destination type code for the shipment line. Values are RECEIVING, DROP SHIP, EXPENSE, INVENTORY, MANUFACTURING | DW_RCV_DESTINATION_TYPE_D_TL | CODE | ||
SHIPMENT_LINE_STATUS_CODE | VARCHAR2 | 32 | Shipment status of receipt line. Values are EXPECTED, PARTIALLY RECEIVED, FULLY RECEIVED, CANCELLED. | DW_RCV_SHIPMENT_STATUS_D_TL | CODE | ||
SOURCE_DOCUMENT_CODE | VARCHAR2 | 32 | Source document code for the receiving transactions. Values are PO, RMA, ASN, REQ, INVENTORY, TRANSFER ORDER etc. Currently only SOURCE_DOCUMENT_CODE = PO is considered | DW_RCV_SOURCE_DOCUMENT_TYPE_D_TL | CODE | ||
LINE_CREATION_DATE | TIMESTAMP | Indicates the date and time of the creation of the receipt line. | |||||
LINE_CREATION_DATE_1 | DATE | Indicates the date of the creation of the receipt line. | |||||
LINE_CREATED_BY_USER | VARCHAR2 | 128 | Indicates the user who created the receipt line. | DW_USER_D | USERNAME | ||
LINE_LAST_UPDATE_DATE | TIMESTAMP | Indicates the date and time of the last update of the receipt line. | |||||
LINE_LAST_UPDATED_BY_USER | VARCHAR2 | 128 | Indicates the user who last updated the row. | DW_USER_D | USERNAME | ||
RECEIPT_DATE | DATE | Receiving date of goods/services. In case of multiple receive transactions for the shipment line, this column will hold the latest transaction date for RECEIVE or UNORDERED transactions. | |||||
DELIVER_DATE | DATE | Delivery date of goods/services. In case of multiple deliver transactions for the shipment line, this column will hold the latest transaction date for DELIVER transactions. | |||||
PUOM_CODE | VARCHAR2 | 16 | This column stores the primary unit of measure code for the receiving shipment receipt line. | ||||
PUOM_CONVERSION_FACTOR | NUMBER | Conversion factor based on the Transation UOM and Primary UOM. | |||||
PUOM_QUANTITY_SHIPPED | NUMBER | This column stores the net quantity shipped for the receiving shipment receipt line in PUOM. | |||||
PUOM_QUANTITY_RECEIVED | NUMBER | This column stores the net quantity received for the receiving shipment receipt line in PUOM. | |||||
PUOM_QUANTITY_ACCEPTED | NUMBER | This column stores the net quantity accepted for the receiving shipment receipt line in PUOM. | |||||
PUOM_QUANTITY_REJECTED | NUMBER | This column stores the net quantity rejected for the receiving shipment receipt line in PUOM. | |||||
PUOM_QUANTITY_DELIVERED | NUMBER | This column stores the net quantity delivered for the receiving shipment receipt line in PUOM. | |||||
PUOM_QUANTITY_RETURNED | NUMBER | This column stores the net quantity returned for the receiving shipment receipt line in PUOM. | |||||
PUOM_QUANTITY_CONSUMED | NUMBER | Supplier consigned Inventory consumed quantity in PUOM. | |||||
RECEIVED_AMOUNT_NON_QTY | NUMBER | Amount received for a fixed price service receipt line in purchase order currency. | |||||
GLOBAL_RECEIVED_AMOUNT_NON_QTY | NUMBER | Amount received for a fixed price service receipt line in Global Currency. | |||||
RECEIVED_AMOUNT_QTY | NUMBER | Amount received for a Quantity based receipt line in purchase order currency. | |||||
GLOBAL_RECEIVED_AMOUNT_QTY | NUMBER | Amount received for a Quantity based receipt line in Global Currency. | |||||
RECEIVED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Received PUOM Quantity Amount is calculated | |||||
ACCEPTED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Accepted PUOM Quantity Amount is calculated | |||||
DELIVERED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and DeliveredPUOM Quantity Amount is calculated | |||||
REJECTED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Rejected PUOM Quantity Amount is calculated | |||||
RETURNED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Returned PUOM Quantity Amount is calculated | |||||
GLOBAL_CURRENCY_CODE | VARCHAR2 | 16 | Global currency code. | DW_CURRENCY_DETAILS_D_TL | CURRENCY_CODE | ||
GLOBAL_CURRENCY_EXCH_RATE | NUMBER | Global currency conversion rate. | |||||
GLOBAL_RECEIVED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Received PUOM Quantity Amount is calculated in Global Currency | |||||
GLOBAL_ACCEPTED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Accepted PUOM Quantity Amount is calculated in Global Currency | |||||
GLOBAL_DELIVERED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and DeliveredPUOM Quantity Amount is calculated in Global Currency | |||||
GLOBAL_REJECTED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Rejected PUOM Quantity Amount is calculated in Global Currency | |||||
GLOBAL_RETURNED_AMOUNT | NUMBER | Based on the Line Location PUOM Unit Price and Returned PUOM Quantity Amount is calculated in Global Currency | |||||
PURCHASE_ORDER_NUMBER | VARCHAR2 | 32 | Purchase order number. | ||||
PO_DOCUMENT_STATUS | VARCHAR2 | 32 | Purchase order status. Values are CLOSED FOR INVOICING, OPEN, CLOSED FOR RECEIVING etc. | DW_DOCUMENT_STATUS_D_TL | CODE | ||
PO_REVISION_NUM | NUMBER | 38 | 0 | Purchase order document revision number. | |||
PO_APPROVED_DATE | DATE | Date on which first version of purchase order was approved. | |||||
PROCUREMENT_BU_ID | NUMBER | 38 | 0 | Procurement business unit identifier. | DW_INTERNAL_ORG_D | ORGANIZATION_ID | |
REQUISITION_BU_ID | NUMBER | 38 | 0 | Requisition business unit identifer. | DW_INTERNAL_ORG_D | ORGANIZATION_ID | |
REQUISITION_LEDGER_ID | NUMBER | 38 | 0 | Primary ledger id of requisition BU | |||
PROCUREMENT_LEDGER_ID | NUMBER | 38 | 0 | Primary ledger id of procurment BU | |||
BUYER_ID | NUMBER | 38 | 0 | Buyer unique identifier. | DW_PERSON_NAME_CURRENT_D | PERSON_ID | |
PO_LINE_NUMBER | NUMBER | 38 | 0 | Purchase order line number. | |||
PO_LINE_TYPE_ID | NUMBER | 38 | 0 | Purchase order line type unique identifier. | DW_PURCHASING_LINE_TYPE_D | LINE_TYPE_ID | |
PO_LINE_STATUS | VARCHAR2 | 32 | Purchase order line status. Values are CLOSED FOR INVOICING, OPEN, CLOSED FOR RECEIVING etc. | DW_PO_LINE_STATUS_D_TL | CODE | ||
PO_LINE_UNIT_PRICE | NUMBER | Unit price for the purchase order line. | |||||
PO_CURRENCY_CODE | VARCHAR2 | 16 | Unique identifier for the purchase order currency. | DW_CURRENCY_DETAILS_D_TL | CURRENCY_CODE | ||
PO_MANUFACTURER | VARCHAR2 | 999 | Name of the company that manufactures the item being purchased. | ||||
PO_MANUFACTURER_PART_NUM | VARCHAR2 | 999 | Number that uniquely identifies the item as provided by the manufacturer. | ||||
PO_LINE_LOCATION_NUMBER | NUMBER | 38 | 0 | Purchase order line location (schedule) number. | |||
SCHEDULE_STATUS | VARCHAR2 | 32 | Purchase order line location (schedule) status. | DW_PO_SCHEDULE_STATUS_D_TL | CODE | ||
PRODUCT_TYPE | VARCHAR2 | 256 | Product type specifies whether the transaction line is for a good, or a service, or it is a memo line. Values could be GOODS/SERVICES. | DW_ZX_PRODUCT_TYPE_D_TL | CODE | ||
RECEIVING_ROUTING_ID | NUMBER | 38 | 0 | Receipt routing unique identifier. | DW_RCV_ROUTING_HEADERS_D_TL | CODE | |
QUANTITY_ORDERED | NUMBER | Purchase order line location quantity ordered. | |||||
PO_UOM_CODE | VARCHAR2 | 16 | Unit of measure code for the purchase order line. | DW_UNIT_OF_MEASURE_D_TL | UOM_CODE | ||
NEED_BY_DATE | DATE | Date that the buying company requests the ordered item or service to be delivered. | |||||
ACCEPTABLE_EARLY_DAYS | NUMBER | Maximum acceptable number of days items can be received early. | |||||
ACCEPTABLE_LATE_DAYS | NUMBER | Maximum acceptable number of days items can be received late. | |||||
PROMISED_DELIVERY_DATE | DATE | Date that the supplier has promised to deliver the ordered item or service. | |||||
MODE_OF_TRANSPORT | VARCHAR2 | 32 | Type of transportation used to ship the product, such as truck, air or boat (values could be AIR, PARCEL etc.). This column has coalesced value from PO Line Location and then PO Header. | DW_PO_MODE_OF_TRANSPORT_D_TL | CODE | ||
SERVICE_LEVEL | VARCHAR2 | 32 | Priority of transportation that affects how quickly goods are transported (values could be Standard, Express, Air etc.). This column has coalesced value from PO Line Location and then PO Header. | DW_PO_SERVICE_LEVELS_D_TL | CODE | ||
PO_PUOM_CODE | VARCHAR2 | 16 | Primary Unit of measure code for the purchase order line. | ||||
PO_PUOM_CONVERSION_FACTOR | NUMBER | Conversion factor based on the Transation UOM and Primary UOM at Purchase Order Level | |||||
PUOM_QUANTITY_ORDERED | NUMBER | Purchase order line location quantity ordered in PUOM. | |||||
PRICE_OVERRIDE | NUMBER | Order shipment price or break price for blanket purchase orders, RFQs, and quotations | |||||
PO_LINE_PUOM_UNIT_PRICE | NUMBER | Unit price for the purchase order line in PUOM. | |||||
WORK_ORDER_ID | NUMBER | 38 | 0 | Unique identifier for the work order | |||
WORK_ORDER_OPERATION_ID | NUMBER | 38 | 0 | The unique identifier of a work order operation | |||
WORK_ORDER_NUMBER | VARCHAR2 | 128 | Number of the manufacturing work order | ||||
WORK_ORDER_OPERATION_SEQ | NUMBER | Indicates execution sequence of the work order operation | |||||
RECEIPT_CORRECTION_FLAG | VARCHAR2 | 32 | Indicates if the shipment line contains any correction transaction. Values are YES, NO. | DW_Y_N_D_TL | CODE | ||
RECEIPT_ACCEPTED_FLAG | VARCHAR2 | 32 | Indicates if shipment line has Accepted Quantity = Received Quantity. Values are Y,N. | DW_Y_N_D_TL | CODE | ||
RECEIPT_REJECTED_FLAG | VARCHAR2 | 32 | Indicates if shipment line has Rejected Quantity > . Values are Y,N. | DW_Y_N_D_TL | CODE | ||
RECEIPT_RETURNED_FLAG | VARCHAR2 | 32 | Indicates if shipment line has Returned Quantity > . Values are Y,N. | DW_Y_N_D_TL | CODE | ||
UNORDERED_RECEIPT_FLAG | VARCHAR2 | 32 | Indicates if receipt line is unordered and not yet matched to any purchase order i.e. no MATCH transaction type for the receipt line. Values are Y,N. | DW_Y_N_D_TL | CODE | ||
SHIPMENT_RECEIVED_FLAG | VARCHAR2 | 32 | Indicates if receipt line has been received i.e. receipt line has RECEIVE/UNORDERED transaction types. Values are Y,N. | DW_Y_N_D_TL | CODE | ||
ASN_BASED_FLAG | VARCHAR2 | 32 | Indicates if receipt line is ASN or ASBN based. Values are Y,N. | DW_Y_N_D_TL | CODE | ||
SUBSTITUTE_ITEM_FLAG | VARCHAR2 | 32 | Indicates if receipt line is a substitute item. Values are Y,N. | DW_Y_N_D_TL | CODE | ||
PROCESSING_TIME | NUMBER | Indicates no. of days to receive goods after purchase order (revision ). RECEIPT DATE - PO APPROVED DATE (revision ) Value will be NULL if any one of the date attributes are NULL. In cases where PO APPROVED DATE > RECEIPT DATE (example in back dated receipts), values will be capped at . | |||||
POST_PROCESSING_TIME | NUMBER | Indicates no. of days to deliver goods to requester after receiving the goods. DELIVER DATE - RECEIPT DATE Value will be NULL if any one of the date attributes are NULL. Value will change in cases where line is not fully delivered. | |||||
TRANSPORTATION_TIME | NUMBER | Indicates no. of days to received goods after it is shipped. Applicable only for ASN based shipments. RECEIPT HEADER CREATION DATE - SHIPPED DATE Value will be NULL if any one of the date attributes are NULL. | |||||
EARLY_DELIVERY_FLAG | VARCHAR2 | 32 | Indicates if shipment was received early. RECEIPT DATE < PROMISED DELIVEY DATE, if null NEED BY DATE. Value will be N if any one of the date attributes are NULL. | DW_Y_N_D_TL | CODE | ||
ONTIME_DELIVERY_FLAG | VARCHAR2 | 32 | Indicates if shipment was received on-time. RECEIPT DATE = PROMISED DELIVEY DATE, if null NEED BY DATE. Value will be N if any one of the date attributes are NULL. | DW_Y_N_D_TL | CODE | ||
LATE_DELIVERY_FLAG | VARCHAR2 | 32 | Indicates if shipment was received late. RECEIPT DATE > PROMISED DELIVEY DATE, if null NEED BY DATE. Value will be N if any one of the date attributes are NULL. | DW_Y_N_D_TL | CODE | ||
EARLY_DELIVERY_TOLERANCE_FLAG | VARCHAR2 | 32 | Indicates if shipment was received early including receipt tolerances. RECEIPT DATE < PROMISED DELIVEY DATE, if null NEED BY DATE - ACCEPTABLE EARLY DAYS. Value will be N if any one of the date attributes are NULL. | DW_Y_N_D_TL | CODE | ||
ONTIME_DELIVERY_TOLERANCE_FLAG | VARCHAR2 | 32 | Indicates if shipment was received on-time including receipt tolerances. RECEIPT DATE BETWEEN (PROMISED DELIVEY DATE, if null NEED BY DATE) - ACCEPTABLE EARLY DAYS AND (PROMISED DELIVEY DATE, if null NEED BY DATE) + ACCEPTABLE LATE DAYS Value will be N if any one of the date attributes are NULL. | DW_Y_N_D_TL | CODE | ||
LATE_DELIVERY_TOLERANCE_FLAG | VARCHAR2 | 32 | Indicates if shipment was received late including receipt tolerances. RECEIPT DATE > PROMISED DELIVEY DATE, if null NEED BY DATE + ACCEPTABLE LATE DAYS Value will be N if any one of the date attributes are NULL. | DW_Y_N_D_TL | CODE |
Copyright © 2019, 2023, Oracle and/or its affiliates.