RCV_SHIPMENT_LINES is the table used by Oracle Fusion to store the line level shipment details like Item Number, Quantity Shipped, Quantity Received etc. This table is child table of RCV_SHIPMENT_HEADERS Table. Both of the tables are connected by column SHIPMENT_HEADER_ID. An important point to remember is that data in this table does not mean physical receiving, rather it gives you the plan of receiving. Physical receiving data is stored in RCV_TRANSACTIONS table which is the child table of RCV_SHIPMENT_LINES table.
In case you are looking for query to fetch you receiving transaction details, click here.
In this post we’ll have a look at the table structure of RCV_SHIPMENT_LINES table. By querying this table, you can primarily find out following details:
1. Shipment Line Number
2. Shipped Quantity
3. Received Quantity
4. Delivered Quantity
5. Returned Quantity
6. Quantity Accepted
7. Quantity Rejected
8. Item Description
9. PO Number
10. PO Line Number
So let’s see how the table is defined in Oracle Fusion:
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
RCV_SHIPMENT_LINES: Column Details
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| SHIPMENT_LINE_ID | NUMBER | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| CREATION_DATE | TIMESTAMP(6) | N |
| CREATED_BY | VARCHAR2(256) | N |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| SHIPMENT_HEADER_ID | NUMBER | N |
| LINE_NUM | NUMBER | N |
| CATEGORY_ID | NUMBER | Y |
| QUANTITY_SHIPPED | NUMBER | Y |
| QUANTITY_RECEIVED | NUMBER | Y |
| QUANTITY_DELIVERED | NUMBER | Y |
| QUANTITY_RETURNED | NUMBER | Y |
| QUANTITY_ACCEPTED | NUMBER | Y |
| QUANTITY_REJECTED | NUMBER | Y |
| UOM_CODE | VARCHAR2(12) | Y |
| ITEM_DESCRIPTION | VARCHAR2(960) | Y |
| ITEM_ID | NUMBER | Y |
| ITEM_REVISION | VARCHAR2(72) | Y |
| VENDOR_ITEM_NUM | VARCHAR2(1200) | Y |
| VENDOR_LOT_NUM | VARCHAR2(120) | Y |
| UOM_CONVERSION_RATE | NUMBER | Y |
| SHIPMENT_LINE_STATUS_CODE | VARCHAR2(100) | Y |
| SOURCE_DOCUMENT_CODE | VARCHAR2(100) | Y |
| PO_HEADER_ID | NUMBER | Y |
| PO_LINE_ID | NUMBER | Y |
| PO_LINE_LOCATION_ID | NUMBER | Y |
| PO_DISTRIBUTION_ID | NUMBER | Y |
| REQUISITION_LINE_ID | NUMBER | Y |
| REQ_DISTRIBUTION_ID | NUMBER | Y |
| ROUTING_HEADER_ID | NUMBER | Y |
| PACKING_SLIP | VARCHAR2(100) | Y |
| FROM_ORGANIZATION_ID | NUMBER | Y |
| DELIVER_TO_PERSON_ID | NUMBER | Y |
| EMPLOYEE_ID | NUMBER | Y |
| DESTINATION_TYPE_CODE | VARCHAR2(100) | Y |
| TO_ORGANIZATION_ID | NUMBER | Y |
| TO_SUBINVENTORY | VARCHAR2(40) | Y |
| LOCATOR_ID | NUMBER | Y |
| DELIVER_TO_LOCATION_ID | NUMBER | Y |
| SHIPMENT_UNIT_PRICE | NUMBER | Y |
| TRANSFER_COST | NUMBER | Y |
| TRANSPORTATION_COST | NUMBER | Y |
| COMMENTS | VARCHAR2(4000) | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(120) | Y |
| ATTRIBUTE1 – ATTRIBUTE20 | VARCHAR2(600) | Y |
| ATTRIBUTE_NUMBER1 – ATTRIBUTE_NUMBER10 | NUMBER | Y |
| ATTRIBUTE_DATE1 – ATTRIBUTE_DATE5 | DATE | Y |
| ATTRIBUTE_TIMESTAMP1 – ATTRIBUTE_TIMESTAMP5 | TIMESTAMP(6) | Y |
| REASON_ID | NUMBER | Y |
| GOVERNMENT_CONTEXT | VARCHAR2(120) | Y |
| REQUEST_ID | NUMBER | Y |
| DESTINATION_CONTEXT | VARCHAR2(120) | Y |
| PRIMARY_UOM_CODE | VARCHAR2(12) | Y |
| EXCESS_TRANSPORT_REASON | VARCHAR2(100) | Y |
| EXCESS_TRANSPORT_RESPONSIBLE | VARCHAR2(100) | Y |
| EXCESS_TRANSPORT_AUTH_NUM | VARCHAR2(120) | Y |
| ASN_LINE_FLAG | VARCHAR2(4) | Y |
| ORIGINAL_ASN_PARENT_LINE_ID | VARCHAR2(4) | Y |
| ORIGINAL_ASN_LINE_FLAG | VARCHAR2(4) | Y |
| VENDOR_CUM_SHIPPED_QUANTITY | NUMBER | Y |
| NOTICE_UNIT_PRICE | NUMBER | Y |
| TAX_NAME | VARCHAR2(60) | Y |
| TAX_AMOUNT | NUMBER | Y |
| INVOICE_STATUS_CODE | VARCHAR2(100) | Y |
| CUM_COMPARISON_FLAG | VARCHAR2(4) | Y |
| CONTAINER_NUM | VARCHAR2(140) | Y |
| TRUCK_NUM | VARCHAR2(140) | Y |
| BAR_CODE_LABEL | VARCHAR2(140) | Y |
| TRANSFER_PERCENTAGE | NUMBER | Y |
| SHIP_TO_LOCATION_ID | NUMBER | Y |
| COUNTRY_OF_ORIGIN_CODE | VARCHAR2(8) | Y |
| CUSTOMER_ITEM_NUM | VARCHAR2(200) | Y |
| SECONDARY_QUANTITY_SHIPPED | NUMBER | Y |
| SECONDARY_QUANTITY_RECEIVED | NUMBER | Y |
| SECONDARY_UOM_CODE | VARCHAR2(12) | Y |
| QC_GRADE | VARCHAR2(600) | Y |
| MMT_TRANSACTION_ID | NUMBER | Y |
| ASN_LPN_ID | NUMBER | Y |
| AMOUNT | NUMBER | Y |
| AMOUNT_RECEIVED | NUMBER | Y |
| JOB_ID | NUMBER | Y |
| TIMECARD_ID | NUMBER | Y |
| TIMECARD_OVN | NUMBER | Y |
| OSA_FLAG | VARCHAR2(4) | Y |
| REQUESTED_AMOUNT | NUMBER | Y |
| MATERIAL_STORED_AMOUNT | NUMBER | Y |
| APPROVAL_STATUS | VARCHAR2(100) | Y |
| AMOUNT_SHIPPED | NUMBER | Y |
| JOB_DEFINITION_NAME | VARCHAR2(400) | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2(3600) | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| RECEIPT_ADVICE_LINE_NUMBER | VARCHAR2(320) | Y |
| RA_DOCUMENT_LINE_NUMBER | VARCHAR2(320) | Y |
| RA_DOO_HEADER_NUMBER | VARCHAR2(320) | Y |
| RA_DOO_LINE_NUMBER | VARCHAR2(320) | Y |
| RA_DOO_FULFILLMENT_LINE_NUMBER | VARCHAR2(320) | Y |
| RA_DOC_SCHEDULE_NUMBER | VARCHAR2(320) | Y |
| RA_DOC_LINE_CREATION_DATE | DATE | Y |
| RA_DOC_LINE_LAST_UPDATE_DATE | DATE | Y |
| RA_EXPECTED_RECEIPT_DATE | DATE | Y |
| RA_DAYS_EARLY_RECEIPT_ALLOWED | NUMBER | Y |
| RA_DAYS_LATE_RECEIPT_ALLOWED | NUMBER | Y |
| RA_RECEIPT_DAYS_EXCEPTION_CODE | VARCHAR2(100) | Y |
| RA_ENFORCE_SHIP_TO_LOC_CODE | VARCHAR2(100) | Y |
| RA_ALLOW_SUBSTITUTE_RECEIPT | VARCHAR2(4) | Y |
| RA_QTY_RCV_TOLERANCE | NUMBER | Y |
| RA_QTY_RCV_EXCEPTION_CODE | VARCHAR2(100) | Y |
| RA_NOTE_TO_RECEIVER | VARCHAR2(1920) | Y |
| RECEIPT_ADVICE_HEADER_ID | NUMBER | Y |
| RECEIPT_ADVICE_LINE_ID | NUMBER | Y |
| RA_ORIG_DOO_HEADER_NUMBER | VARCHAR2(320) | Y |
| RA_ORIG_DOO_LINE_NUMBER | VARCHAR2(320) | Y |
| RA_ORIG_OC_HEADER_NUMBER | VARCHAR2(320) | Y |
| RA_ORIG_OC_LINE_NUMBER | VARCHAR2(320) | Y |
| RA_ORIG_DOO_FULFIL_LINE_NUM | VARCHAR2(320) | Y |
| CUSTOMER_ID | NUMBER | Y |
| CUSTOMER_SITE_ID | NUMBER | Y |
| RA_QUANTITY_EXPECTED | NUMBER | Y |
| RA_SECONDARY_QUANTITY_EXPECTED | NUMBER | Y |
| RA_UNIT_PRICE | NUMBER | Y |
| SP_QUANTITY | NUMBER | Y |
| SP_UOM_CODE | VARCHAR2(12) | Y |
| SP_GROUP_ID | NUMBER | Y |
| RA_LAST_ACTION_CODE | VARCHAR2(100) | Y |
| RA_MESSAGE_NUMBER | NUMBER | Y |
| INTERFACE_TRANSACTION_ID | NUMBER | Y |
| TRANSFER_ORDER_HEADER_ID | NUMBER | Y |
| TRANSFER_ORDER_LINE_ID | NUMBER | Y |
| CONSIGNED_FLAG | VARCHAR2(4) | Y |
| CONSUMED_QUANTITY | NUMBER | Y |
| EXTERNAL_SYS_INTF_STATUS | VARCHAR2(100) | Y |
| FIRST_PTY_REG_ID | NUMBER | Y |
| THIRD_PTY_REG_ID | NUMBER | Y |
| DEFAULT_TAXATION_COUNTRY | VARCHAR2(8) | Y |
| DOCUMENT_FISCAL_CLASSIFICATION | VARCHAR2(960) | Y |
| TRX_BUSINESS_CATEGORY | VARCHAR2(960) | Y |
| TAX_INVOICE_NUMBER | VARCHAR2(600) | Y |
| TAX_INVOICE_DATE | DATE | Y |
| SHIP_FROM_LOCATION_ID | NUMBER | Y |
| USER_DEFINED_FISC_CLASS | VARCHAR2(120) | Y |
| TAX_CLASSIFICATION_CODE | VARCHAR2(200) | Y |
| INTENDED_USE | VARCHAR2(960) | Y |
| INTENDED_USE_CLASSIF_ID | NUMBER | Y |
| PRODUCT_TYPE | VARCHAR2(960) | Y |
| PRODUCT_CATEGORY | VARCHAR2(960) | Y |
| ASSESSABLE_VALUE | NUMBER | Y |
| FINAL_DISCHARGE_LOCATION_ID | NUMBER | Y |
| PRODUCT_FISC_CLASS_ID | NUMBER | Y |
| ORIG_TO_INV_SHIP_TXN_ID | NUMBER | Y |
| CUSTOMER_ITEM_ID | NUMBER | Y |
| BACK_TO_BACK_FLAG | VARCHAR2(4) | Y |
| PROJECT_ID | NUMBER | Y |
| TASK_ID | NUMBER | Y |
| INV_STRIPING_CATEGORY | VARCHAR2(160) | Y |
| INV_RESERVED_ATTRIBUTE1 | VARCHAR2(600) | Y |
| INV_RESERVED_ATTRIBUTE2 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE1 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE2 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE3 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE4 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE5 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE6 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE7 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE8 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE9 | VARCHAR2(600) | Y |
| INV_USER_DEF_ATTRIBUTE10 | VARCHAR2(600) | Y |
| EWAY_BILL_NUMBER | NUMBER | Y |
| EWAY_BILL_DATE | DATE | Y |
| EXT_SYS_GROUP_ID | NUMBER | Y |
| DS_LINE_INTERFACED_TO_OM | VARCHAR2(4) | Y |
Indexes
| INDEX_NAME | INDEX_TYPE | UNIQUENESS | COLUMN_NAME |
| RCV_SHIPMENT_LINES_N1 | NORMAL | NONUNIQUE | ITEM_ID |
| RCV_SHIPMENT_LINES_N1 | NORMAL | NONUNIQUE | TO_ORGANIZATION_ID |
| RCV_SHIPMENT_LINES_N1 | NORMAL | NONUNIQUE | FROM_ORGANIZATION_ID |
| RCV_SHIPMENT_LINES_N11 | NORMAL | NONUNIQUE | ASN_LPN_ID |
| RCV_SHIPMENT_LINES_N12 | NORMAL | NONUNIQUE | TRANSFER_ORDER_HEADER_ID |
| RCV_SHIPMENT_LINES_N13 | NORMAL | NONUNIQUE | SHIPMENT_LINE_STATUS_CODE |
| RCV_SHIPMENT_LINES_N13 | NORMAL | NONUNIQUE | SOURCE_DOCUMENT_CODE |
| RCV_SHIPMENT_LINES_N13 | NORMAL | NONUNIQUE | ASN_LINE_FLAG |
| RCV_SHIPMENT_LINES_N14 | NORMAL | NONUNIQUE | TO_ORGANIZATION_ID |
| RCV_SHIPMENT_LINES_N15 | NORMAL | NONUNIQUE | EXT_SYS_GROUP_ID |
| RCV_SHIPMENT_LINES_N16 | NORMAL | NONUNIQUE | DS_LINE_INTERFACED_TO_OM |
| RCV_SHIPMENT_LINES_N2 | NORMAL | NONUNIQUE | PO_HEADER_ID |
| RCV_SHIPMENT_LINES_N3 | NORMAL | NONUNIQUE | PO_LINE_ID |
| RCV_SHIPMENT_LINES_N4 | NORMAL | NONUNIQUE | PO_LINE_LOCATION_ID |
| RCV_SHIPMENT_LINES_N5 | NORMAL | NONUNIQUE | EMPLOYEE_ID |
| RCV_SHIPMENT_LINES_N6 | NORMAL | NONUNIQUE | DELIVER_TO_PERSON_ID |
| RCV_SHIPMENT_LINES_N7 | NORMAL | NONUNIQUE | CONTAINER_NUM |
| RCV_SHIPMENT_LINES_N7 | NORMAL | NONUNIQUE | TO_ORGANIZATION_ID |
| RCV_SHIPMENT_LINES_N7 | NORMAL | NONUNIQUE | SHIPMENT_HEADER_ID |
| RCV_SHIPMENT_LINES_N8 | NORMAL | NONUNIQUE | REQUISITION_LINE_ID |
| RCV_SHIPMENT_LINES_N9 | NORMAL | NONUNIQUE | PACKING_SLIP |
| RCV_SHIPMENT_LINES_U1 | NORMAL | UNIQUE | SHIPMENT_LINE_ID |
| RCV_SHIPMENT_LINES_U2 | NORMAL | UNIQUE | SHIPMENT_HEADER_ID |
| RCV_SHIPMENT_LINES_U2 | NORMAL | UNIQUE | LINE_NUM |
Related Posts:
Receiving Transaction Query in Oracle Fusion
RCV_SHIPMENT_HEADERS: Columns & Indexes
Query To Get The Shipment Details In Oracle Fusion
Item Shipment History Query in Fusion
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
3 thoughts on “RCV_SHIPMENT_LINES: Columns, Indexes & Few Tricks”
Comments are closed.