RCV_TRANSACTIONS is the table used by Oracle Fusion to store the physical receiving details like Quantity, UOM, PO Number, PO Line Number, Requisition Number, Received By etc. RCV_TRANSACTIONS table is child table of RCV_SHIPMENT_LINE table and grandchild of RCV_SHIPMENT_HEADER Table.
In this blog we will discuss about the table structure & Indexes of this table. But in case you need the query to fetch the receiving transaction details, click here.
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_TRANSACTIONS: Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| TRANSACTION_ID | NUMBER | N |
| INTF_RETURNS_TO_SHIPPING | VARCHAR2(4) | Y |
| CONSUMED_QUANTITY | NUMBER | Y |
| 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 |
| REQUEST_ID | NUMBER | Y |
| TRANSACTION_TYPE | VARCHAR2(100) | N |
| TRANSACTION_DATE | DATE | N |
| QUANTITY | NUMBER | Y |
| UOM_CODE | VARCHAR2(12) | Y |
| SHIPMENT_HEADER_ID | NUMBER | N |
| SHIPMENT_LINE_ID | NUMBER | N |
| USER_ENTERED_FLAG | VARCHAR2(4) | N |
| INTERFACE_SOURCE_CODE | VARCHAR2(100) | Y |
| INTERFACE_SOURCE_LINE_ID | NUMBER | Y |
| INV_TRANSACTION_ID | NUMBER | Y |
| SOURCE_DOCUMENT_CODE | VARCHAR2(100) | Y |
| DESTINATION_TYPE_CODE | VARCHAR2(100) | Y |
| PRIMARY_QUANTITY | NUMBER | Y |
| PRIMARY_UOM_CODE | VARCHAR2(12) | Y |
| EMPLOYEE_ID | NUMBER | Y |
| PARENT_TRANSACTION_ID | NUMBER | Y |
| PO_HEADER_ID | NUMBER | Y |
| PO_LINE_ID | NUMBER | Y |
| PO_LINE_LOCATION_ID | NUMBER | Y |
| PO_DISTRIBUTION_ID | NUMBER | Y |
| PO_REVISION_NUM | NUMBER | Y |
| REQUISITION_LINE_ID | NUMBER | Y |
| PO_UNIT_PRICE | NUMBER | Y |
| CURRENCY_CODE | VARCHAR2(60) | Y |
| CURRENCY_CONVERSION_TYPE | VARCHAR2(120) | Y |
| CURRENCY_CONVERSION_RATE | NUMBER | Y |
| CURRENCY_CONVERSION_DATE | DATE | Y |
| ROUTING_HEADER_ID | NUMBER | Y |
| ROUTING_STEP_ID | NUMBER | Y |
| DELIVER_TO_PERSON_ID | NUMBER | Y |
| DELIVER_TO_LOCATION_ID | NUMBER | Y |
| VENDOR_ID | NUMBER | Y |
| VENDOR_SITE_ID | NUMBER | Y |
| ORGANIZATION_ID | NUMBER | Y |
| SUBINVENTORY | VARCHAR2(40) | Y |
| LOCATOR_ID | NUMBER | Y |
| WIP_ENTITY_ID | NUMBER | Y |
| WIP_LINE_ID | NUMBER | Y |
| WIP_REPETITIVE_SCHEDULE_ID | NUMBER | Y |
| WIP_OPERATION_SEQ_NUM | NUMBER | Y |
| WIP_RESOURCE_SEQ_NUM | NUMBER | Y |
| BOM_RESOURCE_ID | NUMBER | Y |
| LOCATION_ID | NUMBER | Y |
| SUBSTITUTE_UNORDERED_CODE | VARCHAR2(100) | Y |
| RECEIPT_EXCEPTION_FLAG | VARCHAR2(4) | Y |
| INSPECTION_STATUS_CODE | VARCHAR2(100) | Y |
| ACCRUAL_STATUS_CODE | VARCHAR2(100) | Y |
| INSPECTION_QUALITY_CODE | VARCHAR2(100) | Y |
| VENDOR_LOT_NUM | VARCHAR2(120) | Y |
| RMA_REFERENCE | VARCHAR2(120) | Y |
| COMMENTS | VARCHAR2(960) | 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 |
| REQ_DISTRIBUTION_ID | NUMBER | Y |
| DEPARTMENT_CODE | VARCHAR2(40) | Y |
| REASON_ID | NUMBER | Y |
| DESTINATION_CONTEXT | VARCHAR2(120) | Y |
| LOCATOR_ATTRIBUTE | VARCHAR2(600) | Y |
| CHILD_INSPECTION_FLAG | VARCHAR2(4) | Y |
| SOURCE_DOC_QUANTITY | NUMBER | Y |
| SOURCE_DOC_UOM_CODE | VARCHAR2(12) | Y |
| INTERFACE_TRANSACTION_ID | NUMBER | Y |
| INTERFACE_GROUP_ID | NUMBER | Y |
| GROUP_ID | NUMBER | Y |
| MOVEMENT_ID | NUMBER | Y |
| INVOICE_ID | NUMBER | Y |
| INVOICE_STATUS_CODE | VARCHAR2(100) | Y |
| QA_COLLECTION_ID | NUMBER | Y |
| COUNTRY_OF_ORIGIN_CODE | VARCHAR2(8) | Y |
| MVT_STAT_STATUS | VARCHAR2(120) | Y |
| QUANTITY_BILLED | NUMBER | Y |
| MATCH_FLAG | VARCHAR2(4) | Y |
| AMOUNT_BILLED | NUMBER | Y |
| BASE_AMOUNT_BILLED | NUMBER | Y |
| MATCH_OPTION | VARCHAR2(100) | Y |
| CONSIGNED_FLAG | VARCHAR2(4) | Y |
| BACK_TO_BACK_FLAG | VARCHAR2(4) | Y |
| CUSTOMER_ID | NUMBER | Y |
| CUSTOMER_SITE_ID | NUMBER | Y |
| LPN_ID | NUMBER | Y |
| TRANSFER_LPN_ID | NUMBER | Y |
| MOBILE_TXN | VARCHAR2(8) | Y |
| SECONDARY_QUANTITY | NUMBER | Y |
| SECONDARY_UOM_CODE | VARCHAR2(12) | Y |
| QC_GRADE | VARCHAR2(600) | Y |
| PA_ADDITION_FLAG | VARCHAR2(4) | Y |
| SOURCE_TRANSACTION_NUM | VARCHAR2(100) | Y |
| FROM_SUBINVENTORY | VARCHAR2(960) | Y |
| FROM_LOCATOR_ID | NUMBER | Y |
| AMOUNT | NUMBER | Y |
| DROPSHIP_TYPE_CODE | NUMBER | Y |
| LPN_GROUP_ID | NUMBER | Y |
| JOB_ID | NUMBER | Y |
| TIMECARD_ID | NUMBER | Y |
| TIMECARD_OVN | NUMBER | Y |
| PROJECT_ID | NUMBER | Y |
| TASK_ID | NUMBER | Y |
| REQUESTED_AMOUNT | NUMBER | Y |
| MATERIAL_STORED_AMOUNT | NUMBER | Y |
| REPLENISH_ORDER_LINE_ID | NUMBER | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2(3600) | Y |
| JOB_DEFINITION_NAME | VARCHAR2(400) | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| RECEIPT_ADVICE_HEADER_ID | NUMBER | Y |
| RECEIPT_ADVICE_LINE_ID | NUMBER | Y |
| RCPT_CONF_STATUS | VARCHAR2(100) | Y |
| RCPT_CONF_MESSAGE_NUMBER | NUMBER | Y |
| SP_QUANTITY | NUMBER | Y |
| SP_UOM_CODE | VARCHAR2(12) | Y |
| SP_GROUP_ID | NUMBER | Y |
| PARENT_SP_GROUP_ID | NUMBER | Y |
| TXN_FLOW_HEADER_ID | NUMBER | Y |
| TRANSFER_ORDER_HEADER_ID | NUMBER | Y |
| TRANSFER_ORDER_LINE_ID | NUMBER | Y |
| TRANSFER_ORDER_DIST_ID | NUMBER | Y |
| PHYSICAL_RETURN_REQD | VARCHAR2(4) | 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 |
| PRODUCT_FISC_CLASS_ID | NUMBER | 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 |
| DS_RECEIPT_EVENT_TYPE | VARCHAR2(400) | Y |
| DS_RECEIPT_SOURCE_LINE_ID | NUMBER | Y |
| CSE_INTERFACE_STATUS_CODE | VARCHAR2(100) | Y |
| FISCAL_DOC_HEADER_ID | NUMBER | Y |
| FISCAL_DOC_ACCESS_KNUM | VARCHAR2(256) | Y |
| FISCAL_DOC_LINE_ID | NUMBER | Y |
| REF_FISCAL_DOC_HEADER_ID | NUMBER | Y |
| REF_FISCAL_DOC_LINE_ID | NUMBER | Y |
| REF_FISCAL_DOC_ACCESS_KNUM | VARCHAR2(256) | Y |
| RETURN_TO_HEADER_ID | NUMBER | Y |
| RETURN_TO_LINE_ID | NUMBER | Y |
| BUSINESS_EVENT_PUBLISHED | VARCHAR2(4) | Y |
| INTF_FROM_EXT_SYSTEM_FLAG | VARCHAR2(4) | Y |
| FISCAL_DOC_SCHEDULE_ID | NUMBER | Y |
| REF_FISCAL_DOC_SCHEDULE_ID | NUMBER | Y |
| QA_IP_EVENT_ID | NUMBER | Y |
| QA_SKIP_INSPECTION | VARCHAR2(4) | Y |
| ERECORD_ID | NUMBER | Y |
| EXTERNAL_SYSTEM_PACKING_UNIT | VARCHAR2(600) | Y |
| AUTO_DEBIT_MEMO_CREATED | VARCHAR2(4) | Y |
| EWAY_BILL_NUMBER | NUMBER | Y |
| EWAY_BILL_DATE | DATE | 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 |
| RECALL_HEADER_ID | NUMBER | Y |
| RECALL_LINE_ID | NUMBER | Y |
| EXTERNAL_SYS_TXN_REFERENCE | VARCHAR2(1200) | Y |
RCV_TRANSACTIONS: Indexes
| INDEX_NAME | INDEX_TYPE | UNIQUENESS | COLUMN_NAME |
| RCV_TRANSACTIONS_N1 | NORMAL | NONUNIQUE | PARENT_TRANSACTION_ID |
| RCV_TRANSACTIONS_N10 | NORMAL | NONUNIQUE | TRANSACTION_DATE |
| RCV_TRANSACTIONS_N11 | NORMAL | NONUNIQUE | INTERFACE_TRANSACTION_ID |
| RCV_TRANSACTIONS_N12 | NORMAL | NONUNIQUE | RECEIPT_EXCEPTION_FLAG |
| RCV_TRANSACTIONS_N13 | NORMAL | NONUNIQUE | ACCRUAL_STATUS_CODE |
| RCV_TRANSACTIONS_N14 | NORMAL | NONUNIQUE | DELIVER_TO_PERSON_ID |
| RCV_TRANSACTIONS_N15 | NORMAL | NONUNIQUE | VENDOR_ID |
| RCV_TRANSACTIONS_N16 | NORMAL | NONUNIQUE | INVOICE_STATUS_CODE |
| RCV_TRANSACTIONS_N16 | NORMAL | NONUNIQUE | SOURCE_DOCUMENT_CODE |
| RCV_TRANSACTIONS_N16 | NORMAL | NONUNIQUE | TRANSACTION_TYPE |
| RCV_TRANSACTIONS_N17 | NORMAL | NONUNIQUE | GROUP_ID |
| RCV_TRANSACTIONS_N18 | NORMAL | NONUNIQUE | TRANSFER_ORDER_HEADER_ID |
| RCV_TRANSACTIONS_N19 | NORMAL | NONUNIQUE | EMPLOYEE_ID |
| RCV_TRANSACTIONS_N2 | NORMAL | NONUNIQUE | SHIPMENT_HEADER_ID |
| RCV_TRANSACTIONS_N2 | NORMAL | NONUNIQUE | SOURCE_DOCUMENT_CODE |
| RCV_TRANSACTIONS_N2 | NORMAL | NONUNIQUE | ROUTING_HEADER_ID |
| RCV_TRANSACTIONS_N2 | NORMAL | NONUNIQUE | INSPECTION_STATUS_CODE |
| RCV_TRANSACTIONS_N20 | NORMAL | NONUNIQUE | PO_DISTRIBUTION_ID |
| RCV_TRANSACTIONS_N21 | NORMAL | NONUNIQUE | ERECORD_ID |
| RCV_TRANSACTIONS_N22 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| RCV_TRANSACTIONS_N22 | NORMAL | NONUNIQUE | SOURCE_DOCUMENT_CODE |
| RCV_TRANSACTIONS_N22 | NORMAL | NONUNIQUE | ROUTING_HEADER_ID |
| RCV_TRANSACTIONS_N22 | NORMAL | NONUNIQUE | INSPECTION_STATUS_CODE |
| RCV_TRANSACTIONS_N23 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
| RCV_TRANSACTIONS_N24 | NORMAL | NONUNIQUE | REQUEST_ID |
| RCV_TRANSACTIONS_N25 | NORMAL | NONUNIQUE | SOURCE_TRANSACTION_NUM |
| RCV_TRANSACTIONS_N26 | NORMAL | NONUNIQUE | CUSTOMER_ID |
| RCV_TRANSACTIONS_N27 | NORMAL | NONUNIQUE | EXTERNAL_SYS_TXN_REFERENCE |
| RCV_TRANSACTIONS_N3 | NORMAL | NONUNIQUE | SHIPMENT_LINE_ID |
| RCV_TRANSACTIONS_N4 | NORMAL | NONUNIQUE | PO_HEADER_ID |
| RCV_TRANSACTIONS_N5 | NORMAL | NONUNIQUE | PO_LINE_ID |
| RCV_TRANSACTIONS_N6 | NORMAL | NONUNIQUE | PO_LINE_LOCATION_ID |
| RCV_TRANSACTIONS_N7 | NORMAL | NONUNIQUE | REQUISITION_LINE_ID |
| RCV_TRANSACTIONS_N8 | NORMAL | NONUNIQUE | REQ_DISTRIBUTION_ID |
| RCV_TRANSACTIONS_N9 | NORMAL | NONUNIQUE | RECEIPT_ADVICE_HEADER_ID |
| RCV_TRANSACTIONS_N9 | NORMAL | NONUNIQUE | RECEIPT_ADVICE_LINE_ID |
| RCV_TRANSACTIONS_U1 | NORMAL | UNIQUE | TRANSACTION_ID |
Related Posts
Receiving Transaction Query in Oracle Fusion
RCV_SHIPMENT_HEADERS: Columns & Indexes
RCV_SHIPMENT_LINES: Columns, Indexes & Few Tricks
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..
1 thought on “RCV_TRANSACTIONS: Columns, Indexes & Tricks”
Comments are closed.