1. Tables and Views for SCM
  2. CST_TRANSACTION_COSTS

CST_TRANSACTION_COSTS

This table holds the actual cost of receipts and issues for transactions in parent table CST_TRANSACTIONS. It holds the entire history of what each actual cost was after every adjustment, and the cost is stored in its entirety on each adjustment instead of being stored in incremental costs. Receipt costs can come in externally from the incoming acquisition costs flow or internally from the costing engine (transfers at cost and transactions that use current item cost). Issue costs can come from RTV transactions, miscellaneous issue transactions, transfer credits and freight charges, and negative PO quantity adjustments.

Details

  • Schema: FUSION

  • Object owner: CST

  • Object type: TABLE

  • Tablespace: FUSION_TS_TX_DATA

Primary Key

Name Columns

CST_TRANSACTION_COSTS_PK

TRANSACTION_COST_ID

Columns

Name Datatype Length Precision Not-null Comments
TRANSACTION_COST_ID NUMBER 18 Yes Surrogate key that identifies a row in this table.
TRANSACTION_ID NUMBER 18 Yes Single column FK: Identifies the cost transaction.
COST_DATE TIMESTAMP Cost date for the adjustment costs.
EFF_DATE TIMESTAMP Yes The effective date of the cost record. It is the date when the cost became in effect for the transaction. For new costs, the effective date is the date stamped on the transaction. For user adjustments, the effective date is the user entry date. For acquisition cost adjustments, the effective date is the system date at time of cost processing.
PERIOD_NAME VARCHAR2 15 Period in which the transaction is processed. This value is populated for the transactions in periodic average cost method.
COST_ELEMENT_ID NUMBER 18 Yes Cost element corresponding to the cost of the transaction.
EXPENSE_POOL_ID NUMBER 18 Yes Identifies the expense pool for which this cost is created. Indicates the following when negative value: -1: Freight Cost -2: Gain/loss on transfer price -3: Transfer Price -4: Expensed LCM cost element (capitalization flag deselected on mapping setup) -5: Additional taxes for Brazil, comes on top of TP -6: IMT outbound taxes -8: NR trade event tax -22: Used by internal logic to ignore cost for this node but transfer it to the next node for costing as gain/loss
UNIT_COST NUMBER Indicates the unit cost of the cost element.
CURRENCY_CODE VARCHAR2 15 Identifies the currency of the transaction / cost.
ABSORPTION_TYPE VARCHAR2 30 Identifies the absorption type for which this cost is created.
POSTED_FLAG VARCHAR2 5 Yes Specifies if the receipt has been propagated to CST_PERPAVG_COST or CST_WRITEOFF_COSTS, or if the expense (overhead, transfer credit) has been propagated to CST_LAYER_COSTS. N - No, Y - Yes, X - Not Required. There is no LOOKUP_TYPE for these codes.
QUANTITY_ONHAND NUMBER On hand quantity as of the cost date for the adjustment costs.
PERPAVG_COST_ID NUMBER 18 Identifies the perpetual cost created for this record.
BASE_TXN_TYPE_ID NUMBER 18 Inventory transaction type identifier. For origin as INV the value comes from INV_TRANSACTION_TYPES_TL table.
BASE_TXN_SOURCE_TYPE_ID NUMBER 18 Inventory transaction source type identifier. For origin as INV the value comes from INV_TXN_SOURCE_TYPES_TL table, and for CST it comes from lookup type CST_BASE_TXN_SOURCE_TYPES.
BASE_TXN_ACTION_ID NUMBER 18 Inventory transaction action identifier. For Origin as INV, the value comes from lookup INV_TRANSACTION_ACTIONS and for Origin as CST the value is from lookup type CST_BASE_TXN_ACTIONS.
CST_INV_TRANSACTION_ID NUMBER 18 Single column FK: Identifies the Inventory transaction.
ADJUSTMENT_TRANSACTION_ID NUMBER 18 Single column FK: Identifies the cost adjustment.
USER_ADJUSTMENT_ID NUMBER 18 Single column FK: Identifies the user adjustment.
ADJUSTMENT_DATE TIMESTAMP Stores the adjustment date if specified by the user in the manual adjustments.
VALID_COST_FLAG VARCHAR2 1 Indicates if this is a valid cost. e.g., acquisition cost after a manual cost adjustment is invalid and should not be used for inventory valuation.
OVERHEAD_GROUP_ID NUMBER 18 Identifies the overhead group reference rows on CST_TRANSACTION_OVERHEADS.
TXN_UNIT_COST NUMBER Indicates the unit cost of the transaction before currency conversion.
TXN_CURRENCY_CODE VARCHAR2 15 Identifies the currency code of the transaction.
TXN_CURRENCY_CONVERSION_TYPE VARCHAR2 30 Identifies the rate type that was used for currency conversion.
TXN_CURRENCY_CONVERSION_DATE DATE Identifies the date that was used for currency conversion. For Acquisition Cost Adjustments, this value defaults to the EFF_DATE if a specific currency conversion date override was not provided. An Acquisition Cost Adjustment could be a result of a combination of multiple retro price adjustments and invoices, and each of these could have their own corresponding accounting dates and as such we cannot pick a specific price change or invoice to decide on the currency conversion date. So instead of having a complicated set of rules to pick the currency conversion date, we keep it simple and use the date of computation of the acquisition cost for the currency conversion.
TXN_CURRENCY_CONVERSION_RATE NUMBER Identifies the rate that was used for currency conversion. This value is null if conversion was not required.
COST_SOURCE VARCHAR2 30 Identifies the source of the cost, such as Purchase Order (PO) or Accounts Payable (AP). The LOOKUP_TYPE for these values is CST_COST_SOURCES.
COST_REFERENCE VARCHAR2 100 Identifies a specific business object or value that further refines the cost source information, such as the source cost organization identifier or an effective date.
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.
OVERHEAD_ID NUMBER 18 Single column FK: Identifies the transaction overhead.
COSTED_QTY NUMBER Quantity which has been processed by costing.
COSTING_UOM_CODE VARCHAR2 3 Unit of measure of the costed quantity.
COSTING_STATUS VARCHAR2 1 Status whether the transaction has been processed by preprocessor, processed by cost processor, distributions have been created or transactions have been processed by sub-ledger accounting. N-Not Processed: no distribution lines are created for this transaction at the current time - lines are expected. Staged by QPP P-Partially Costed: some distribution lines are created, but not all. More lines are expected. C-Fully Costed: All distributions have been created - more lines are not expected. E-ERROR: TRANSACTION IS IN ERROR, NO LINES CAN BE GENERATED AS THE CURRENT TIME. STAGED BY QPP X-Excluded from Costing: No distribution lines will be created for this transaction.
ACCOUNTING_STATUS VARCHAR2 1 Status of sub-ledger accounting process: N-Not accounted - SLA/GL Accounting is expected/pending. Z-Not Applicable - Distributions are NOT created, and the accounting is not sent to GL. D-Draft accounted - SLA Accounting in draft mode completed. Final Accounting expected. F-Final accounted - SLA Accounting in final mode completed. No further accounting is expected. E-ERROR - AN ERROR IN CREATE ACCOUNTING WAS ENCOUNTERED. FURTHER ACCOUNTING IS EXPECTED AFTER ERROR IS RESOLVED. X-Excluded from accounting. Distributions are created, but no accounting is expected.
CODE_COMBINATION_ID NUMBER 18 Contains the Code combination of the account entered by the users while creating costs (IMT outbound taxes).
PERIODIC_COST_ID NUMBER 18 Unique identifier for a periodic average cost row.
PAC_PROCESSED_FLAG VARCHAR2 1 Indicates whether the transaction cost has been processed by the PAC processor.
COST_ORG_ID NUMBER 18 Surrogate ID identifying the cost organization of the transaction.
COST_BOOK_ID NUMBER 18 Surrogate ID identifying the cost book of the transaction.

Foreign Keys

Table Foreign Table Foreign Key Column
CST_TRANSACTION_COSTS cst_transactions TRANSACTION_ID
CST_TRANSACTION_COSTS cst_cost_elements_b COST_ELEMENT_ID
CST_TRANSACTION_COSTS cst_expense_pools_b EXPENSE_POOL_ID
CST_TRANSACTION_COSTS cst_inv_transactions CST_INV_TRANSACTION_ID
CST_TRANSACTION_COSTS cst_acq_cost_adjustments ADJUSTMENT_TRANSACTION_ID
CST_TRANSACTION_COSTS cst_user_adjustments USER_ADJUSTMENT_ID
cst_user_adjustments cst_transaction_costs SOURCE_TRANSACTION_COST_ID
cst_txn_component_costs cst_transaction_costs TRANSACTION_COST_ID

Indexes

Index Uniqueness Tablespace Columns Status
CST_TRANSACTION_COSTS_N1 Non Unique FUSION_TS_TX_DATA POSTED_FLAG, BASE_TXN_SOURCE_TYPE_ID, BASE_TXN_ACTION_ID
CST_TRANSACTION_COSTS_N2 Non Unique Default DECODE((TO_CHAR("QUANTITY_ONHAND")),NULL,'N','Y') Obsolete
CST_TRANSACTION_COSTS_N3 Non Unique Default BASE_TXN_SOURCE_TYPE_ID, BASE_TXN_ACTION_ID, BASE_TXN_TYPE_ID
CST_TRANSACTION_COSTS_N4 Non Unique Default COSTING_STATUS, ACCOUNTING_STATUS, BASE_TXN_SOURCE_TYPE_ID
CST_TRANSACTION_COSTS_N5 Non Unique DEFAULT PERIOD_NAME, COST_ORG_ID, COST_DATE
CST_TRANSACTION_COSTS_N6 Non Unique Default COST_DATE
CST_TRANSACTION_COSTS_U1 Unique FUSION_TS_TX_DATA TRANSACTION_ID, EFF_DATE, COST_ELEMENT_ID, EXPENSE_POOL_ID, ABSORPTION_TYPE
CST_TRANSACTION_COSTS_U2 Unique FUSION_TS_TX_DATA TRANSACTION_COST_ID