eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

DBA Data [Home] [Help] [Dependency Information]

TABLE: ICX.ICX_CAT_PRICE_HISTORY

Object Details

Object Name:	ICX_CAT_PRICE_HISTORY
Object Type:	TABLE
Owner:	ICX
FND Design Data:	ICX.ICX_CAT_PRICE_HISTORY
Subobject Name:
Status:	VALID

This table stores price history for outdated bulk-loaded prices or deleted bulk-loaded prices.
When a category/item is deleted, the corresponding price rows are also deleted from this table. The structure is the same as ICX_CAT_ITEM_PRICES table, except for the two extra columns, ARCHIVED_DATE and STATUS.
When we load a price, we first move over the existing price row from the ICX_CAT_ITEM_PRICES table to this table, with the status set to 'OUTDATED'. The status is set to 'DELETED' when the price has been deleted.
Only bulk-loaded prices are moved to this history table. Price type of "BULKLOAD" and "CONTRACT" are the only supported price types for this table. Lines with CONTRACT_REFERENCE_NUM, should have a price_type of CONTRACT.
The SEARCH_TYPE for the price history row will always be "SUPPLIER".

Storage Details

Tablespace:	APPS_TS_TX_DATA
PCT Free:	10
PCT Used:

Indexes

Index	Type	Uniqueness	Tablespace	Column
ICX_CAT_PRICE_HISTORY_N1	NORMAL	NONUNIQUE	APPS_TS_TX_IDX	RT_ITEM_ID

Columns

Name	Datatype	Length	Mandatory	Comments
RT_ITEM_ID	NUMBER		Yes	Internally generated Item Id. (References ICX_CAT_ITEMS_B.RT_ITEM_ID)
STATUS	VARCHAR2	(10)	Yes	Price Status. Possible values are OUTDATED/DELETED. Status is set to OUTDATED when there is a more recent price and to DELETED when the price has been deleted.
PRICE_TYPE	VARCHAR2	(20)		Indicates what type of price this row represents. The valid price types are BULKLOAD (Price has been bulkloaded-using Loader.) or CONTRACT(Price belongs to a Contract Agreement)
ACTIVE_FLAG	VARCHAR2	(1)		Flag Attribute Indication if a price is active or not. Possible values are 'Y'/'N'
OBJECT_VERSION_NUMBER	NUMBER		Yes	Object Version Number. Set to 1 initially, incremented every time row is updated.
ASL_ID	NUMBER			Approved Supplier List ID. Not populated, as we don't have a PRICE_TYPE of ASL in this table.
SUPPLIER_SITE_ID	NUMBER			Supplier Site ID. When there is no valid value for Supplier Site, we store a value of -2 by default.
CONTRACT_ID	NUMBER			Contract ID. Populated only for the price rows having the PRICE_TYPE as CONTRACT. Stores the PO_HEADER_ID of the Contract Agreement from PO_HEADERS_ALL. Other price history rows are defaulted with the value -2.
CONTRACT_LINE_ID	NUMBER			Contract Line ID. Stores the value null for price rows referencing a Contract Purchase Agreement. Defaulted with the value -2 for other price rows
TEMPLATE_ID	VARCHAR2	(25)		Template ID. Not populated, as we don't have a PRICE_TYPE of TEMPLATE in this table.
TEMPLATE_LINE_ID	NUMBER			Template line ID. Not populated, as we don't have a PRICE_TYPE of TEMPLATE in this table
MTL_CATEGORY_ID	NUMBER			Stores the Purchasing Category ID. Not populated, as we don't have a PRICE_TYPE of PURCHASING_ITEM in this table.
ORG_ID	NUMBER			Operating Unit Id, to which the item belongs. A value of '-2' indicates that the item will be available for All Operating Units.
SEARCH_TYPE	VARCHAR2	(8)		Store Search TypeSearch Type will be "SUPPLIER" for all bulkloaded items
UNIT_PRICE	NUMBER			Unit Price.
CURRENCY	VARCHAR2	(15)		Transactional Currency.
UNIT_OF_MEASURE	VARCHAR2	(3)		Unit of measure for the Item. (References MTL_UNITS_OF_MEASURE.unit_of_measure.)
FUNCTIONAL_PRICE	NUMBER			Functional price of the Item. Calculated as per the Rate (derived for the Rate Type, Functional currency of the Operating Unit and the Transactional Currency) and the Unit Price.
SUPPLIER_SITE_CODE	VARCHAR2	(100)		Vendor Site Code (References PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE)
CONTRACT_NUM	VARCHAR2	(20)		The Contract Purchase Agreement Reference. Populated with the Segment1 value of the Contract Purchase Agreement. Valid only for the price rows of type CONTRACT, other price rows have a value of -2.
CONTRACT_LINE_NUM	NUMBER			Contract line number reference. Stores the value null for the price rows of type CONTRACT, and -2 for the other price rows.
PRICE_LIST_ID	NUMBER			The Price List Header Id for the price row. (References ICX_CAT_ITEM_PRICLISTS. PRICE LIST ID) Populated if this price is bulk loaded.
ARCHIVED_DATE	DATE			Date when this price was archived
LAST_UPDATE_LOGIN	NUMBER			Standard Who column: identifies sign-on audit information of the user who last updated this record. (References FND_LOGINS.login_id.)
LAST_UPDATED_BY	NUMBER			Standard Who column: the user who last updated this record. (References FND_USERS.user_id.)
LAST_UPDATE_DATE	DATE			Standard Who column: date when this record was last updated
CREATED_BY	NUMBER			Standard Who column: the user who created this record. (References FND_USERS.user_id.)
CREATION_DATE	DATE			Standard Who column: date when this record was created
REQUEST_ID	NUMBER			Request Id of Concurrent Job submitted. The concurrent program that created or updated this record. (References FND_CONCURRENT_PROGRAMS.request_id)
PROGRAM_APPLICATION_ID	NUMBER			Standard Who column: the application that owns the concurrent program that created or updated this record. (References FND_CONCURRENT_PROGRAMS.application_id.)
PROGRAM_ID	NUMBER			Standard Who column: the concurrent program that created or updated this record. (References FND_CONCURRENT_PROGRAMS.concurrent_program_id.)
PROGRAM_UPDATE_DATE	DATE			Standard Who column: the date when this record was created or updated by a concurrent program

Query Text

Cut, paste (and edit) the following text to query this object:


SELECT RT_ITEM_ID
,      STATUS
,      PRICE_TYPE
,      ACTIVE_FLAG
,      OBJECT_VERSION_NUMBER
,      ASL_ID
,      SUPPLIER_SITE_ID
,      CONTRACT_ID
,      CONTRACT_LINE_ID
,      TEMPLATE_ID
,      TEMPLATE_LINE_ID
,      MTL_CATEGORY_ID
,      ORG_ID
,      SEARCH_TYPE
,      UNIT_PRICE
,      CURRENCY
,      UNIT_OF_MEASURE
,      FUNCTIONAL_PRICE
,      SUPPLIER_SITE_CODE
,      CONTRACT_NUM
,      CONTRACT_LINE_NUM
,      PRICE_LIST_ID
,      ARCHIVED_DATE
,      LAST_UPDATE_LOGIN
,      LAST_UPDATED_BY
,      LAST_UPDATE_DATE
,      CREATED_BY
,      CREATION_DATE
,      REQUEST_ID
,      PROGRAM_APPLICATION_ID
,      PROGRAM_ID
,      PROGRAM_UPDATE_DATE
FROM ICX.ICX_CAT_PRICE_HISTORY;

Dependencies

[top of page]

ICX.ICX_CAT_PRICE_HISTORY does not reference any database object

ICX.ICX_CAT_PRICE_HISTORY is referenced by following:

ICX: ICX_CAT_PRICE_HISTORY#