QP_PRICING_TERMS_B
Base table for storing pricing terms for parent entities such as Sales Agreements, Promotions or Discount Lists.
Details
-
Schema: FUSION
-
Object owner: QP
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
QP_PRICING_TERMS_B_PK |
PRICING_TERM_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| PRICING_TERM_ID | NUMBER | 18 | Yes | Id of the Pricing Term created.System Generated Unique ID. | |
| PARENT_ENTITY_TYPE_CODE | VARCHAR2 | 30 | Type of Entity to which this pricing term is related e.g. Promotion,Contract etc. Based on look Up Type ORA_QP_TERMS_PARENT_ENTITIES. | ||
| PARENT_ENTITY_KEY_COLUMN1 | VARCHAR2 | 30 | Primary Key of parent entity to which this pricing term is related e.g. Promotion Id, Contract Id,etc.If a key is a composite then more than one column may be used. | ||
| PARENT_ENTITY_KEY_COLUMN2 | VARCHAR2 | 30 | Column 2 of the parent entity primary Key. | ||
| PARENT_ENTITY_KEY_COLUMN3 | VARCHAR2 | 30 | Column 3 of the parent entity primary Key. | ||
| PARENT_ENTITY_KEY_COLUMN4 | VARCHAR2 | 30 | Column 4 of the parent entity primary Key. | ||
| PARENT_ENTITY_KEY_COLUMN5 | VARCHAR2 | 30 | Column 5 of the parent entity primary Key. | ||
| ITEM_LEVEL_CODE | VARCHAR2 | 30 | Can be item, Item Category and Product Category. | ||
| ITEM_ID | NUMBER | 18 | Id of the product of type Target Product Type. | ||
| BENEFIT_TYPE_CODE | VARCHAR2 | 30 | Yes | Based on the Look up Type:ORA_QP_PRIC_TERM_BENEFIT_TYPES. | |
| PRICE_TYPE_CODE | VARCHAR2 | 30 | Indicates if the price adjustment is of price types one time,recurring,usuage or ALL. Based on the lookup type ORA_QP_PRICE_TYPES. | ||
| CHARGE_TYPE_CODE | VARCHAR2 | 30 | Indicates the charge type the Pricing term is for. Not applicable for benefit types Price List and Accrual. | ||
| CHARGE_SUBTYPE_CODE | VARCHAR2 | 30 | Indicates the charge subtype the Pricing term is for. Not applicable for benefit types Price List and Accrual. | ||
| PRICING_CURRENCY_CODE | VARCHAR2 | 15 | Indicates the pricing currency code for the pricing term | ||
| ACCRUAL_CURRENCY_CODE | VARCHAR2 | 15 | Relevant when Benefit Type is Accrual e.g. Airmiles | ||
| ACCRUAL_BASIS_ID | NUMBER | 18 | Based on named pricing basis of type accrual basis. The Pricing Basis of type Accrual Basis will have one or more combinations of price element, price type, charge type and charge subtype associated. | ||
| PRICING_UOM_CODE | VARCHAR2 | 3 | UOM in which the price/adjustment for the item is setup. Derived from PIM. | ||
| ADJUSTMENT_ELEMENT_CODE | VARCHAR2 | 30 | Adjustment Element to which the Pricing Term belongs. This is to position the adjustment on the waterfall display. Optionally this field is defaulted with a value that is setup in the QP: Default Adjustment Group application-level Pricing Parameter. | ||
| ADJUSTMENT_LEVEL_CODE | VARCHAR2 | 30 | Yes | Based on lookup type ORA_QP_ADJUSTMENT_LEVELS. Contracts will always use a level of LINE.Can be HEADER or LINE (or SHIPMENT when Benefit Type is Shipping Price Adjustment). | |
| PRICING_RULE_TYPE_CODE | VARCHAR2 | 30 | Indicates if the adjustment is SIMPLE or based on tiered Pricing,Attribute base Pricing,Cp pricing, or Formula Pricing.Based on lookup type ORA_QP_PRICING_RULE_TYPES | ||
| ADJUSTMENT_TYPE_CODE | VARCHAR2 | 30 | e.g. %Discount, %Markup, etc. Based on a lookup type as described below: If (Benefit Type is Accrual) then List of values based on lookup type ORA_QP_ACCRUAL_CALC_TYPES Else if Benefit Type other than Price List List of values based on lookup type ORA_QP_LINE_ADJ_TYPES End if This field is not relevant for Benefit Type is Price List. | ||
| ADJUSTMENT_AMOUNT | NUMBER | Adjustment numeric value associated with the Adjustment Type. | |||
| ADJUSTMENT_BASIS_ID | NUMBER | 18 | Indicates the basis for calculating a % based adjustment.When adjustment is at line level, Benefit Type is Coverage Price Adjustment, Price Adjustment or Price List Based Adjustment and Adjustment Type is Discount % or Markup %, the list of values for this field are Pricing Basis (setup separately as named entities) of type Adjustment Basis. The Pricing Basis of type Adjustment Basis will only have a single price element associated. Price Type, Charge Type, Subtype for calculation purposes are to be same as price type, charge type, subtype to which the term is applied.When adjustment is at line level, Benefit Type is Coverage Price Adjustment and Adjustment Type is Covered Product Percent Price (applicable only when the item on the pricing term is a coverage/warranty item) then the list of values are Pricing Basis of type Coverage Basis. The Pricing Basis of type Coverage Basis will have one or more combinations of price element, price type, charge type and charge subtype associated. | ||
| PRICE_LIST_ID | NUMBER | 18 | FK. Price List id of the Price List | ||
| TIERED_PRICING_HEADER_ID | NUMBER | 18 | Id of the Tiered Pricing Scheme/header attached to the price or adjustment. Note: a)If Adjustment Level is LINE And Price Type is One Time or Recurring then the Tiered Pricing Basis can be Item Quantity or Item Amount. b)If Adjustment Level is LINE and Price Type is Usage then the Tiered Pricing Basis can be Item Quantity, Item Amount, Usage Quantity or Usage Amount. c)If Adjustment Level is HEADER and Price Type is One Time or Recurring then the Tiered Pricing Basis can be Order Amount or other named Order Totals defined in the Totals Administration that are eligible for use in common components | ||
| ATTRIBUTE_PRICING_MATRIX_ID | NUMBER | 18 | Id of the Attribute Based Pricing Model attached to the price or adjustment. For line level shipping price adjustments or shipping policy based adjustments that are dependent on Item Weight, attribute model may be used to setup charges where Item Weight is a condition dimension. | ||
| USAGE_UOM_CODE | VARCHAR2 | 3 | Relevant when price type is Usage. Mutually exclusive with Recurring UOM. | ||
| PRICE_PERIODICITY_CODE | VARCHAR2 | 3 | Relevant when price type is Recurring. Mutually exclusive with Usage UOM. E.g. Possible values are Monthly (MONTHLY), Weekly (WEEKLY), yearly (YEARLY), etc. | ||
| ALLOW_PRICE_LIST_OVERRIDE_FLAG | VARCHAR2 | 30 | For use by QOC/Contracts to prevent override of Price List on the order,Quote or Agreement. Possible values are Y or N.Based lookup type YES_NO. | ||
| ALLOW_CUSTOM_ADJUSTMENTS_FLAG | VARCHAR2 | 30 | For use by QOC/Contracts to prevent manual adjustments to the adjustment. Possible values are Y or N. Based lookup type YES_NO. | ||
| ALLOW_PRICING_DISCOUNTS_FLAG | VARCHAR2 | 30 | Flag to indicate if additional pricing discounts can be applied to a charge on a line associated to this pricing term. Allowed values are 'Y' and 'N' and null will be treated as 'Y'. | ||
| APPLY_TO_ROLLUP_FLAG | VARCHAR2 | 30 | Indicates if the Adjustment is applicable to the coverage item alone or the rollup (applicable when item on term is a a coverage item). Based on lookup type YES_NO. | ||
| START_DATE | TIMESTAMP | Start date for the Term.This date does not contribute to uniqueness of Terms just date enablement. | |||
| END_DATE | TIMESTAMP | End date for the Term.This date does not contribute to uniqueness of Terms just date enablement. | |||
| ORIG_SYSTEM_ID | NUMBER | 18 | Pricing Term Id of source when this pricing term is copied from another pricing term. | ||
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated 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. | ||
| 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. | ||
| 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. | |
| ATTRIBUTE_CATEGORY | VARCHAR2 | 150 | Descriptive Flexfield: structure definition of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR1 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR2 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR3 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR4 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR5 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR6 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR7 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR8 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR9 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR10 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR11 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR12 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR13 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR14 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR15 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR16 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR17 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR18 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR19 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR20 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_NUMBER1 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER2 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER3 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER4 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER5 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER6 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER7 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER8 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER9 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER10 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE1 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE2 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE3 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE4 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE5 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_TIMESTAMP1 | TIMESTAMP | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_TIMESTAMP2 | TIMESTAMP | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_TIMESTAMP3 | TIMESTAMP | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_TIMESTAMP4 | TIMESTAMP | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_TIMESTAMP5 | TIMESTAMP | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ACCRUAL_TYPE_CODE | VARCHAR2 | 30 | Accrual Type is identified as CUSTOMER/SUPPLIER so that the accrual can be used in calculation of margin. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| qp_pricing_terms_tl | qp_pricing_terms_b | PRICING_TERM_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| QP_PRICING_TERMS_B_FK1 | Non Unique | FUSION_TS_TX_IDX | PRICE_LIST_ID |
| QP_PRICING_TERMS_B_FK2 | Non Unique | FUSION_TS_TX_IDX | TIERED_PRICING_HEADER_ID |
| QP_PRICING_TERMS_B_FK3 | Non Unique | Default | ATTRIBUTE_PRICING_MATRIX_ID |
| QP_PRICING_TERMS_B_N1 | Non Unique | FUSION_TS_TX_IDX | PARENT_ENTITY_TYPE_CODE, PARENT_ENTITY_KEY_COLUMN1, PARENT_ENTITY_KEY_COLUMN2, PARENT_ENTITY_KEY_COLUMN3, PARENT_ENTITY_KEY_COLUMN4, PARENT_ENTITY_KEY_COLUMN5 |
| QP_PRICING_TERMS_B_PK | Unique | FUSION_TS_TX_IDX | PRICING_TERM_ID |