1. Tables and Views for SCM
  2. CMR_TRANSACTIONS

CMR_TRANSACTIONS

This table will store the receipt transactions related to Purchase Order flows and Inter org flows. It also stores all trade events. All these transactions will be accounted.

Details

  • Schema: FUSION

  • Object owner: CMR

  • Object type: TABLE

  • Tablespace: FUSION_TS_TX_DATA

Primary Key

Name Columns

CMR_TRANSACTIONS_PK

TRANSACTION_ID

Columns

Name Datatype Length Precision Not-null Comments
TRANSACTION_ID NUMBER 18 Yes Transaction identifier and surrogate key column.
LCM_PROCESSED_FLAG VARCHAR2 1 Flag to indicate whether LCM charges are processed for this transaction.
CMR_PO_DISTRIBUTION_ID NUMBER 18 Single column FK: Identifies entity in Receipt Accounting PO distributions. This value will exist for match-to-PO as well as match-to-receipt cases. For inter-organization flows, it will be NULL.
CMR_RCV_TRANSACTION_ID NUMBER 18 Single Column FK: Identifies the receiving transaction. For trade event flow, it will be NULL.
TRADE_EVENT_ID NUMBER 18 Trade Event Identifier and references to {CST_TRADE_EVENTS.TRADE_EVENT_ID}.
PARENT_TRANSACTION_ID NUMBER 18 Yes Parent receiving transaction identifier: -1 for receipt transaction, for return to vendor transaction it is the parent receipt's transaction ID.
RCV_PARENT_TRANSACTION_ID NUMBER 18 The receiving parent transaction identifier denotes the parent of each receiving transaction.
CMR_ROOT_RECEIVE_TXN_ID NUMBER 18 Internal identifier that links the transaction with its first receiving transaction identifier.
RETURN_FOR_CREDIT_FLAG VARCHAR2 1 Flag which determines if the return to supplier is for credit only where the purchase order isn't reopened because the supplier won't ship any replacements against the same purchase order.
CMR_ROOT_DELIVER_TXN_ID NUMBER 18 Internal root deliver transaction identifier.
TRANSACTION_TYPE VARCHAR2 40 Yes Single column FK: Identifies the receiving event types associated with the transaction.
TRANSACTION_DATE DATE Yes The date when this transaction happened.
SOURCE_DOC_QTY NUMBER Transaction quantity in source document unit of measure.
SOURCE_DOC_UOM_CODE VARCHAR2 3 Source document unit of measure. This is PO Unit of measure for Purchase Order transactions and Transfer Order document Unit of measure for Transfer Order flows.
TRANSACTION_AMT NUMBER Transaction amount for service line items.
BILL_TO_BUSINESS_UNIT_ID NUMBER 18 Bill to business unit identifier.
PROFIT_CENTER_BUSINESS_UNIT_ID NUMBER 18 Profit center business where accounting entry will be created.
SHIP_TO_BUSINESS_UNIT_ID NUMBER 18 The business unit where the item is shipped or delivered.
SHIP_TO_ORGANIZATION_ID NUMBER 18 Yes Inventory Organization where the item is stored.
INVENTORY_ITEM_ID NUMBER 18 Inventory item identifier and it references {EGP_SYSTEM_ITEMS_B.inventory_item_id}.
SOLD_TO_BUSINESS_UNIT_ID NUMBER 18 Business unit identifier where the invoice is created and accrual is accounted.
POSTED_FLAG VARCHAR2 5 Flag to indicate if the transaction is processed or not.
ERROR_CODE VARCHAR2 30 Identifies error code during the processing of transaction. For global procurement receipts, missing trade events average cost.
SOURCE_DOCUMENT_CODE VARCHAR2 25 Source document code of receipt transaction. Maps to FND_LOOKUP_VALUES_B. LOOKUP_CODE, where LOOKUP_TYPE is RCV_SOURCE_DOCUMENT_TYPE.
EXTERNAL_SYSTEM_REFERENCE VARCHAR2 60 External system reference of transaction. FUSION for fusion based transactions.
CMR_PO_LINE_LOCATION_ID NUMBER 18 Receipt accounting purchase order line location identifier.
PO_LINE_LOCATION_ID NUMBER 18 Purchase order shipment schedule identifier.
PO_DISTRIBUTION_ID NUMBER 18 Purchase Order distribution identifier and maps to {CMR_PURCHASE_ORDER_DTLS.EXTERNAL_SYSTEM_REF_ID}.
FISCAL_DOC_ACCESS_KNUM VARCHAR2 64 Fiscal document access key number.
FISCAL_DOC_HEADER_ID NUMBER 18 Fiscal document header unique identifier.
FISCAL_DOC_LINE_ID NUMBER 18 Fiscal document line unique identifier.
FISCAL_DOC_SCHEDULE_ID NUMBER 18 Fiscal Document Schedule identifier as captured in Fiscal Document Capture.
REF_FISCAL_DOC_ACCESS_KNUM VARCHAR2 64 Reference Fiscal Document Access Key Number as captured in Fiscal Document Capture.
REF_FISCAL_DOC_HEADER_ID NUMBER 18 Reference Fiscal Document Header identifier as captured in Fiscal Document Capture.
REF_FISCAL_DOC_LINE_ID NUMBER 18 Reference Fiscal Document Line ID as captured in Fiscal Document Capture.
REF_FISCAL_DOC_SCHEDULE_ID NUMBER 18 This column stores the unique identifier for the reference fiscal document schedule.
INV_SHIPPING_TRANSACTION_ID NUMBER 18 Shipping transaction identifier for inter-organization flows.
PRIOR_TRADE_INV_ORG_ID NUMBER 18 Prior trade Inventory organization identifier on complex flows.
SHIP_FROM_INV_ORG_ID NUMBER 18 Inventory organization identifier.
ROOT_RECEIVE_TXN_ID NUMBER 18 Root receiving transaction identifier.
RCV_TRANSACTION_ID NUMBER 18 Receiving transaction identifier.
SHIP_FROM_LOCATION_ID NUMBER 18 Tax Determinant: Ship from location identifier.
TRANSACTION_QTY NUMBER Receiving quantity in transaction unit of measure.
TRANSACTION_UOM_CODE VARCHAR2 3 Transaction unit of measure of receipt transaction.
PRIMARY_QTY NUMBER Transaction quantity in item's primary unit of measure.
PRIMARY_UOM_CODE VARCHAR2 3 Primary unit of measure of the item.
SECONDARY_TRANSACTION_QTY NUMBER Transaction quantity in terms of the item's secondary unit of measure.
SECONDARY_UOM_CODE VARCHAR2 3 The unit of measure code in which secondary transaction quantity is specified.
TRANSFER_PRICE_QTY NUMBER Identifies the transfer price quantity.
TRANSFER_PRICE_UOM_CODE VARCHAR2 3 The unit of measure code in which transfer price quantity is specified.
CURRENCY_CONVERSION_RATE NUMBER Optional user specified currency conversion rate.
CURRENCY_CONVERSION_DATE TIMESTAMP The date to be used to determine the exchange rates for currency conversion.
CURRENCY_CONVERSION_TYPE VARCHAR2 30 Stores currency conversion type for consigned global purchase orders where purchase order currency is different from ledger currency. This is used to account consigned receipts.
RECEIPT_NUMBER VARCHAR2 50 Receipt number for the receiving shipment receipt header row and references to {RCV_SHIPMENT_HEADERS.RECEIPT_NUM}.
RECEIPT_LINE_NUMBER NUMBER 18 Receipt line number of receipt transaction.
SHIPMENT_NUMBER VARCHAR2 30 Shipment number of receipt transaction and references to RCV_SHIPMENT_HEADERS{SHIPMENT_NUM}.
SHIPMENT_LINE_NUMBER NUMBER 18 Shipmen line number of receipt transaction
DESTINATION_TYPE_CODE VARCHAR2 25 Purchase order distribution destination. It can be Inventory, Expense, Manufacturing or Drop-ship.
PO_UNIT_PRICE NUMBER Unit price defined on purchase order line in purchase order unit of measure.
NONRECOVERABLE_TAX NUMBER Purchase order non-recoverable tax, at this value transaction, is processed. This column is used in global procurement flows.
XCC_NET_LIQUIDATION_QTY NUMBER Sum of all receipt transaction quantity against a PO schedule for deliver transactions.
XCC_NET_LIQUIDATION_AMOUNT NUMBER Sum of all receipt transaction amount against a service PO schedule for deliver transactions.
LIQUIDATED_QTY NUMBER Liquidated quantity that will be used for calculation of reversal amount.
LIQUIDATED_AMOUNT NUMBER The net liquidated amount that will be used in case of service PO for calculating the XCC reversal amount.
CREATED_BY VARCHAR2 64 Yes Who column: indicates the user who created the row.
CREATION_DATE TIMESTAMP Yes Who column: indicates the date and time of the creation of the row.
LAST_UPDATED_BY VARCHAR2 64 Yes Who column: indicates the user who last updated the row.
LAST_UPDATE_DATE TIMESTAMP Yes Who column: indicates the date and time of the last update of the row.
LAST_UPDATE_LOGIN VARCHAR2 32 Who column: indicates the session login associated to the user who last updated the row.
REQUEST_ID NUMBER 18 Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row.
JOB_DEFINITION_NAME VARCHAR2 100 Enterprise Service Scheduler: indicates the name of the job that created or last updated the row.
JOB_DEFINITION_PACKAGE VARCHAR2 900 Enterprise Service Scheduler: indicates the package name of the job that created or last updated the row.
DEFAULT_TAXATION_COUNTRY VARCHAR2 2 Tax Determinant: Default taxation country code.
FIRST_PTY_REG_ID NUMBER 18 Tax Determinant: First Party Tax Registration identifier for the transaction.
THIRD_PTY_REG_ID NUMBER 18 Tax Determinant: Third-Party Tax Registration identifier for the transaction.
FINAL_DISCHARGE_LOCATION_ID NUMBER 18 Tax Determinant: The final discharge location identifier.
DOCUMENT_FISCAL_CLASSIFICATION VARCHAR2 240 Tax Determinant: Fiscal document classification code.
TRX_BUSINESS_CATEGORY VARCHAR2 240 Transaction business category code.
USER_DEFINED_FISC_CLASS VARCHAR2 30 User defined fiscal classification code.
TAX_INVOICE_NUMBER VARCHAR2 150 Tax Determinant: The number of the fiscal document.
TAX_INVOICE_DATE DATE Tax Determinant: Date of the fiscal document.
PRODUCT_FISC_CLASS_ID NUMBER 18 Tax Determinant: Product fiscal classification identifier for the inventory item.
PRODUCT_CATEGORY VARCHAR2 240 Tax Determinant: Product category code of the transaction line.
INTENDED_USE_CLASSIF_ID NUMBER 18 Tax Determinant: Intended use classification identifier.
INTENDED_USE VARCHAR2 240 Tax Determinant: Intended use code of the receiving transaction. This identifies the purpose for which the product is used.
COUNTRY_OF_ORIGIN_CODE VARCHAR2 2 This column stores the code for the country of manufacture.
PRODUCT_TYPE VARCHAR2 240 Tax Determinant: Product type code of the inventory item. Possible values are GOODS and SERVICES.
ASSESSABLE_VALUE NUMBER Tax Determinant: Assessable value is the deemed price at which a product is valued, by a tax authority for a given tax, for tax calculation purposes.
TAX_CLASSIFICATION_CODE VARCHAR2 50 Tax Determinant: Tax classification code.
FTR_ID NUMBER 18 Financial Trade Relation identifier on complex flows.
TXN_FLOW_HEADER_ID NUMBER 18 OBSOLETE: Transaction flow header identifier. Obsolete column post-R9.
TAX_PROCESSED_FLAG VARCHAR2 1 Tax Processed Flag. Y means tax call completed on the transaction. N/NULL - not completed or not applicable for tax call.
CONSIGNED_FLAG VARCHAR2 1 Consigned Purchase Order receipt transaction indicator. Possible values - Y, N, NULL.
ROOT_RECEIPT_EVENT_DATE TIMESTAMP Event Date on the Purchase order that is used to process the root receipt transaction.
TRANSFER_ORDER_DIST_ID NUMBER 18 Stores transfer order identifier and maps to {CST_TRANSFER_ORDER_DISTS.DISTRIBUTION_ID}.
TRANSFER_ORDER_HEADER_ID NUMBER 18 Transfer order header identifier and maps to {INV_TRANSFER_ORDER_HEADERS.HEADER_ID}.
TRANSFER_ORDER_LINE_ID NUMBER 18 Transfer order line identifier and maps to {CST_TRANSFER_ORDER_LINES.EXTERNAL_SYSTEM_REF_ID}.
PHYSICAL_RETURN_REQD_FLAG VARCHAR2 1 Flag to store if physical return is required.
RECEIPT_CREATION_DATE DATE Receipt creation date to calculate the back dated receipt or not.
BUDGETARY_CONTROL_FLAG VARCHAR2 1 Budgetary control enabled flag. Y - Budget control enabled transaction. N/NULL - Not enabled.
ENCUMBRANCE_ACCOUNTING_FLAG VARCHAR2 1 Encumbrance Accounting Flag indicator. Y - Encumbrance enabled transaction. X - Might be applicable or not. N/NULL - Not enabled.
CST_INV_TRANSACTION_ID NUMBER 18 Costing Inventory transaction identifier and maps CST_INV_TRANSACTIONS.CST_INV_TRANSACTION_ID for Transfer Order based inter-organization flow with expense destination.
MANUAL_RECEIPT_REQD_FLAG VARCHAR2 1 Manual receipt required flag for Internal Transfers based Transfer Order document for Expense Destination type.
LE_TIMEZONE_CODE VARCHAR2 50 Represents the time zone code of the Legal Entity.
OWNERSHIP_CHANGE_EVENT_NUMBER VARCHAR2 30 Stores trade event's ownership change transaction number. For drop-ship flow - it can be an ASN number or invoice number.
TRANSFER_TO_OWNED_TXN_ID NUMBER 18 Inventory transfer to owned transaction identifier with positive quantity to indicate quantity is moved inventory from consigned.
ROOT_DELIVER_TXN_DATE DATE Indicates the transaction date on the associated root/first delivery transaction.
PROJECT_FLAG VARCHAR2 1 Project enabled identifier flag. Y means project enabled transaction.
DS_RECEIPT_SOURCE_LINE_ID NUMBER 18 Indicates the source document line identifier that created drop-ship receipt.
LOT_NUMBER VARCHAR2 80 This stores lot number information for inventory item.
SERIAL_NUMBER VARCHAR2 80 This stores serial number information for inventory item.
DIRECT_TRANSFER_ORDER_FLAG VARCHAR2 1 This column indicates if the current transaction belongs to a transfer order that has been shipped directly from source to destination locations. Valid values are Y and NULL.

Indexes

Index Uniqueness Tablespace Columns
CMR_TRANSACTIONS_N1 Non Unique FUSION_TS_TX_DATA SOURCE_DOCUMENT_CODE, CMR_PO_DISTRIBUTION_ID, CMR_RCV_TRANSACTION_ID, TRADE_EVENT_ID, POSTED_FLAG, TAX_PROCESSED_FLAG, CST_INV_TRANSACTION_ID, TRANSFER_ORDER_DIST_ID
CMR_TRANSACTIONS_N2 Non Unique Default CMR_ROOT_RECEIVE_TXN_ID, CMR_ROOT_DELIVER_TXN_ID
CMR_TRANSACTIONS_N3 Non Unique Default CMR_RCV_TRANSACTION_ID, CMR_PO_LINE_LOCATION_ID
CMR_TRANSACTIONS_N4 Non Unique Default TRADE_EVENT_ID
CMR_TRANSACTIONS_N5 Non Unique Default INV_SHIPPING_TRANSACTION_ID, SHIP_TO_ORGANIZATION_ID
CMR_TRANSACTIONS_N6 Non Unique Default PO_LINE_LOCATION_ID, SHIP_TO_ORGANIZATION_ID
CMR_TRANSACTIONS_N7 Non Unique Default POSTED_FLAG, BILL_TO_BUSINESS_UNIT_ID
CMR_TRANSACTIONS_N8 Non Unique Default CST_INV_TRANSACTION_ID
CMR_TRANSACTIONS_PK Unique FUSION_TS_TX_DATA TRANSACTION_ID
CMR_TRANSACTIONS_U1 Unique Default CMR_PO_DISTRIBUTION_ID, CMR_RCV_TRANSACTION_ID, TRADE_EVENT_ID, CST_INV_TRANSACTION_ID, TRANSFER_ORDER_DIST_ID, TRANSACTION_TYPE, LOT_NUMBER, SERIAL_NUMBER