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.
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