1. Tables and Views for SCM
  2. ACA_PD_ITEM_COST

ACA_PD_ITEM_COST

The PD Item Cost data model contains the following costs: * Item costs from the Costing System * Item costs entered by end users for Items without costs from the Costing System * Rolled up costs for sub-assemblies to Item assemblies. The PD Items Costs are accessible by Item ID / Revision ID. This allows each Item revision to have its own set of costs. Costs which come from the Costing System are applicable to all revisions for an Item.

Details

  • Schema: FUSION

  • Object owner: ACA

  • Object type: TABLE

  • Tablespace: Default

Primary Key

Name Columns

ACA_PD_ITEM_COST_PK

ITEM_COST_ID

Columns

Name Datatype Length Precision Not-null Comments
ITEM_COST_ID NUMBER 18 Yes Primary Key for this table. This can be used as the surrogate key.
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.
INVENTORY_ITEM_ID NUMBER 18 Yes Foreign key to the Item (PK on the egp_system_items_b) table. Note: The complete Item PK contains Organization. PD only uses a single Organization. For this reason, Organization has been omitted from the Item Cost table. Note: A special row is used in this table to determine the last time the data was retrieved from the Costing System. ???-1??? is used as the inventory_item_id for the special row.
ORGANIZATION_ID NUMBER 18 Yes Foreign key to the Item's organization (PK on the egp_system_items_b) table. This represent's the customer's Master Organization which was in effect at the time the cost data was pulled in from the Costing System.
IS_LEAF NUMBER 1 Yes This column indicates if the item the cost is associated with is either a Leaf Item or an Assembly Item. This column is used when evaluating (filtering) on the From_Revision and To_Revision columns. Leaf Items: Use both the From_Revision and To_Revision. Revision ranges do not overlap. If To_Revision is not specified, then the revision range is considered to be open-ended. Assembly Items: The From_Revision and To_Revision columns will contain the same value; therefore, the To_Revision may be omitted when filtering. Values: ??? 1 = Leaf Item ??? 0 = Assembly Item ??? -1 = Special Row ??? to be ignored when filtering
FROM_REVISION VARCHAR2 18 Yes This column indicates the starting Revision in a revision range for a leaf item. This column contains the Revision of a specific revision for an assembly item. WHERE CLAUSE: (Is_Leaf = 1 AND From_Revision <= Revision) OR (Is_Leaf = 0 AND From_Revision = Revision) Alternatively (to avoid the OR): DECODE(Is_Leaf, 1, DECODE(SIGN(From_Revision - Revision), 1, ???NO_MATCH???, ???MATCH???), 0, DECODE(SIGN(From_Revision - Revision), 0, ???MATCH???, ???NO_MATCH???), ???NO_MATCH???) = ???MATCH???
TO_REVISION VARCHAR2 18 This column indicates the ending Revision in a revision range for a leaf item. Revision ranges may be open ended (i.e. To_Revisoon = null). This column contains the Revision of a specific revision for an assembly item. WHERE CLAUSE: (Is_Leaf = 1 AND NVL(To_Revision, Revision) >= Revision) OR (Is_Leaf = 0) Alternatively (to avoid the OR): DECODE(Is_Leaf, 1, DECODE(SIGN(NVL(To_Revision, Revision) - Revision), -1, ???NO_MATCH???, ???MATCH???), 0, ???MATCH???, ???NO_MATCH???) = ???MATCH???
LAST_COST_EXTRACT_DATE TIMESTAMP This is the date the cost for the current row was last extracted from the Costing System. Note: This column is only for the special row or the rows which have been updated with data from the Costing System.
LAST_ROLLUP_DATE TIMESTAMP This is the date the rollup costs for the Item assembly were last calculated. This date would only be specified for Items which represent assemblies (top level or sub assemblies). This date is not set for assembly leaf items.
COST_SOURCE VARCHAR2 1 Yes This column indicates where the costing data for the Item came from. Values: * Z = special row which tracks when the Retrieve Costing System Costs process was last run. * C = Costing System * P = PD Edit Item
INVENTORY_ORG_ID NUMBER 18 This is the organization ID used to request cost information from the Costing System. If multiple organization IDs are used to request cost information for the same item, then the inventory organization is the organization for which the cost information was actually retrieved.
CURRENCY_CODE VARCHAR2 15 This is the currency of the cost information stored on the row. This currency is the currency requested from the Costing System.
MATERIAL_COST NUMBER The material cost to use for the Item. It would come from the Costing System or user-entered in PD Edit Item. This is a cost that is directly associated with the Item ID. Note: As of this design, material costs would only be tracked for assembly leaf items, i.e. items without sub-structures.
OVERHEAD_COST NUMBER The overhead cost to use for the Item. It would come from the Costing System or user-entered in PD Edit Item. This is a cost that is directly associated with the Item ID. Note: As of this design, overhead costs would only be tracked for assembly items, i.e. items with sub-structures.
ROLLUP_MATERIAL_COST NUMBER The sum of the material costs times the quantities needed for the items that make up the assembly Item. These costs can be considered indirect costs because their origin does not come directly from this Item???s Item ID.
ROLLUP_OVERHEAD_COST NUMBER The sum of the overhead costs times the quantities needed for the items that make up the assembly Item. These costs can be considered indirect costs because their origin does not come directly from this Item???s Item ID.
INCOMPLETE_COST_CNT NUMBER The number of items, leaf or assembly, directly tied to the Item Revision for the cost row which do not have costs or complete costs. Leaf items will either have a cost or not. Assembly item???s costs will either be complete or incomplete.
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.

Indexes

Index Uniqueness Tablespace Columns
ACA_PD_ITEM_COST_U1 Unique Default ITEM_COST_ID
ACA_PD_ITEM_COST_U2 Unique Default INVENTORY_ITEM_ID, ORGANIZATION_ID, IS_LEAF, FROM_REVISION, TO_REVISION, COST_SOURCE