FOS_PRICING_DETAILS
The FOS_PRICING_DETAILS table stores off the accounting transfer prices, seller's assessable value and buyer's assessable value after they are calculated so that they will be readily available, rather than having to recalculate them each time a request is made for them.
Details
-
Schema: FUSION
-
Object owner: FOS
-
Object type: TABLE
-
Tablespace: Default
Primary Key
| Name | Columns |
|---|---|
|
FOS_PRICING_DETAILS_PK |
PRICING_DETAIL_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| PRICING_DETAIL_ID | NUMBER | 18 | Yes | This is the primary key and its value is an application generated unique id. | |
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| DOCUMENT_ID | VARCHAR2 | 100 | Yes | The source document identifier for which the unit price was calculated. For example, in procurement it would store the PO_LINE_LOCATION_ID; for distributed order orchestration it would store the FULFILLMENT_LINE_DETAIL_ID; and for internal material transfers it would store the LINE_ID. | |
| DOCUMENT_TYPE | VARCHAR2 | 30 | Yes | The type of the document for which the unit price was calculated. Valid values are contained within the FOS_IMT_DOCUMENT_TYPES, FOS_PROCUREMENT_DOCUMENT_TYPES, and FOS_SHIPMENT_DOCUMENT_TYPES lookup types. | |
| SOURCE_SYSTEM_ID | NUMBER | 18 | Yes | Identifier of the system that created the document for which the FOS_PRICING_DETAILS record is based off. | |
| PRICE_TYPE | VARCHAR2 | 30 | Yes | Identifies unit price calculated (i.e., Accounting Transfer Price, Seller's Assessable Value or Buyer's Assessable Value). Will be populated with 1 of the following 3 values to identify the unit price type: ACCOUNTING, SELLER or BUYER. | |
| BASIS | VARCHAR2 | 30 | Yes | Identifies the basis used in calculating the unit price. Will be populated with 1 of the following 3 values to identify the basis: PRICELIST, COST_BASED or SOURCE_DOCUMENT_PRICE (based on FOS_TRANSFER_PRICE_OPTIONS lookup). | |
| EVENT_DATA_ID | VARCHAR2 | 100 | Event identifier which is received in the payload of the event. | ||
| EVENT_DEFINITION_ID | NUMBER | 18 | Foreign key reference for the event definition type from FOS_EVENT_DEFINITIONS_B table. | ||
| AGREEMENT_FTR_ID | NUMBER | 18 | Yes | Foreign key to the FOS_AGREEMENT_FTR_F table. | |
| AS_ON_EFFECTIVE_DATE | DATE | Yes | Date used for the pricing calculation. | ||
| UNIT_PRICE | NUMBER | Yes | The net unit price or charge component value calculated for a given systems document Id and document type. This unit price can correspond to either an accounting transfer price or seller's assessable value or buyer's assessable value when the cost component code is equal to 'NetTransferPrice'. When the cost component code is set to anything else it indicates the unit price is for a charge component. | ||
| UNIT_OF_MEASURE | VARCHAR2 | 3 | The unit of measure which corresponds to the unit price. | ||
| CURRENCY_CODE | VARCHAR2 | 15 | Yes | The currency code which corresponds to the unit price. | |
| CONVERSION_TYPE | VARCHAR2 | 30 | Identifies the conversion type used for currency exchange. | ||
| CHARGE_ID | NUMBER | 18 | The cost component id which corresponds to the unit price. This value will be NULL if the unit price was not calculated by the Pricing (QP) system. | ||
| PRICE_ELEMENT_CODE | VARCHAR2 | 120 | Yes | The price element code which corresponds to the unit price. | |
| PRICING_ID | NUMBER | 18 | Yes | Foreign key to the FOS_PRICING_OPTION_F table. | |
| ENTERPRISE_ID | NUMBER | 18 | Identifier of Enterprise, used for multi-tenancy partitioning. | ||
| 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. | ||
| REFERENCE_INFORMATION | VARCHAR2 | 2000 | Column Reference Information is used to store references from Fusion Pricing like Pricing Strategy ID |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| FOS_PRICING_DETAILS | fos_event_doc_info | DOCUMENT_ID, DOCUMENT_TYPE, SOURCE_SYSTEM_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns | Status |
|---|---|---|---|---|
| FOS_PRICING_DETAILS_N1 | Non Unique | Default | DOCUMENT_ID, DOCUMENT_TYPE, SOURCE_SYSTEM_ID, PRICE_TYPE, BASIS | Obsolete |
| FOS_PRICING_DETAILS_N2 | Non Unique | Default | DOCUMENT_ID, DOCUMENT_TYPE, SOURCE_SYSTEM_ID, PRICE_TYPE, PRICE_ELEMENT_CODE, UNIT_OF_MEASURE | Obsolete |
| FOS_PRICING_DETAILS_N3 | Non Unique | Default | UPPER("DOCUMENT_ID"), AGREEMENT_FTR_ID, UPPER("DOCUMENT_TYPE"), SOURCE_SYSTEM_ID, UPPER("PRICE_TYPE"), UPPER("UNIT_OF_MEASURE"), UPPER("PRICE_ELEMENT_CODE") | |
| FOS_PRICING_DETAILS_N4 | Non Unique | Default | DOCUMENT_ID, DOCUMENT_TYPE, SOURCE_SYSTEM_ID, PRICE_TYPE, BASIS, EVENT_DATA_ID, EVENT_DEFINITION_ID | |
| FOS_PRICING_DETAILS_N5 | Non Unique | Default | DOCUMENT_ID, DOCUMENT_TYPE, SOURCE_SYSTEM_ID, PRICE_TYPE, PRICE_ELEMENT_CODE, UNIT_OF_MEASURE, EVENT_DATA_ID, EVENT_DEFINITION_ID | |
| FOS_PRICING_DETAILS_PK | Unique | Default | PRICING_DETAIL_ID | |
| FOS_PRICING_DETAILS_U1 | Unique | Default | AGREEMENT_FTR_ID, DOCUMENT_ID, DOCUMENT_TYPE, SOURCE_SYSTEM_ID, PRICE_TYPE, UNIT_OF_MEASURE, PRICE_ELEMENT_CODE | Obsolete |
| FOS_PRICING_DETAILS_U2 | Unique | Default | AGREEMENT_FTR_ID, DOCUMENT_ID, DOCUMENT_TYPE, SOURCE_SYSTEM_ID, PRICE_TYPE, UNIT_OF_MEASURE, PRICE_ELEMENT_CODE, EVENT_DATA_ID, EVENT_DEFINITION_ID |