1. Tables and Views for SCM
  2. CMR_I_PURCHASE_ORDER_DTLS

CMR_I_PURCHASE_ORDER_DTLS

Interface table to hold Purchase Order information at the distribution level. This information will be used to perform accrual accounting and calculate acquisition costs. Only approved purchase orders need to be interfaced into Cost Management.

Details

  • Schema: FUSION

  • Object owner: CMR

  • Object type: TABLE

  • Tablespace: INTERFACE

Primary Key

Name Columns

CMR_I_PURCHASE_ORDER_DTLS_PK

EXTERNAL_SYSTEM_REFERENCE, EXTERNAL_SYSTEM_REF_ID, EVENT_DATE

Columns

Name Datatype Length Precision Not-null Comments
EXTERNAL_SYSTEM_REFERENCE VARCHAR2 60 Yes Identifier of the source system that owns the purchase order entity
EXTERNAL_SYSTEM_REF_ID VARCHAR2 30 Yes Identifier of the purchase order distribution line in the source system
PO_NUMBER VARCHAR2 40 User specified number for the purchase order
LINE_NUMBER VARCHAR2 40 User specified number for the purchase order line
SHIPMENT_NUMBER VARCHAR2 40 User specified number for the purchase order shipment
DISTRIBUTION_NUMBER VARCHAR2 40 User specified number for the purchase order distribution
PO_HEADER_ID NUMBER 18 Purchase order header identifier
PO_LINE_LOCATION_ID NUMBER 18 Purchase order shipment schedule identifier
PO_LINE_ID NUMBER 18 Purchase Order Line identifier and references to {PO_LINES_ALL.PO_LINE_ID}
INVENTORY_ITEM_ID NUMBER 18 Inventory Item Identifier andReferences {EGP_SYSTEM_ITEMS_B.inventory_item_id}.
ITEM_DESCRIPTION VARCHAR2 240 Item description. Especially needed for POs involving non inventory items
SOLD_TO_BUSINESS_UNIT_ID NUMBER 18 OBSOLETE: Business unit identifier where invoice is created and accrual is accounted
CATEGORY_ID NUMBER 18 Purchasing category that the inventory item belongs to
DELIVER_TO_INVENTORY_ORG_ID NUMBER 18 Inventory organization where the material is scheduled to be received
DELIVER_TO_BUSINESS_UNIT_ID NUMBER 18 Business unit of the inventory organization
PRICE NUMBER Purchase price (per unit) specified by the buyer on the purchase order
NONRECOVERABLE_TAX NUMBER Non recoverable tax (per unit) amount
EVENT_TYPE VARCHAR2 30 Can be one of the following: PO Approval, Retroactive Price Update, Quantity Cancellation, PO Close
EVENT_DATE TIMESTAMP Yes Date when the event in the EVENT_TYPE happened
VALUE_BASIS VARCHAR2 25 Value basis on the purchase order document. Post R9 - obsoleted column
ACCRUAL_ACCOUNT_ID NUMBER 18 Code combination identifier of the accrual account
CHARGE_ACCOUNT_ID NUMBER 18 Code combination identifier of the charge account
VARIANCE_ACCOUNT_ID NUMBER 18 Code combination identifier of the variance account
DESTINATION_CHARGE_ACCOUNT_ID NUMBER 18 Code combination identifier of the destination charge account
DESTINATION_VARIANCE_ACCT_ID NUMBER 18 Code combination identifier of the destination variance account
PROCUREMENT_BUSINESS_UNIT_ID NUMBER 18 Business unit that has created the purchase order.
BILL_TO_BUSINESS_UNIT_ID NUMBER 18 Business unit that will create the invoice for the purchase.
BILL_TO_LOCATION_ID NUMBER 18 The identifier of the specific location that will create the invoice for the purchase.
SOLD_TO_LEGAL_ENTITY_ID NUMBER 18 Legal entity where the accrual will be performed for the purchase.
SFO_PRIMARY_TRADE_RELATION_ID NUMBER 18 Captures the primary trade relationship identifier of an orchestration agreement line.
ORCHESTRATION_ORDER_FLAG VARCHAR2 1 Identifies if this is an orchestrated purchase order.
TRADE_ORGANIZATION_ID NUMBER 18 Captures the inventory organization where the trade buy-sell transactions defined by orchestration agreement are accounted.
PURCHASE_BASIS VARCHAR2 30 Identifies whether the PO line is for Goods or Services. Valid values are 'GOODS' and 'SERVICES'. But for Service PO of Payment Type RATE, this will be GOODS.
PO_PURCHASE_BASIS VARCHAR2 30 Identifies whether the PO line is for Goods or Services. Valid values are 'GOODS' and 'SERVICES'.
PAYMENT_TYPE VARCHAR2 30 For Complex Service PO, this value will be either RATE or LUMPSUM. For normal Service PO this value will be null.
NEED_BY_DATE DATE Need by date for the shipment schedule.
PROMISED_DATE DATE Supplier promised delivery date.
AGENT_ID NUMBER 18 Unique person identifier of the Buyer.
DEFAULT_INVENTORY_ORG_ID NUMBER 18 Identifier of the default inventory organization for the PO. This is used along with the inventory_item_id at the PO line level to get item information from the product data hub.
QUANTITY_ORDERED NUMBER Quantity of the material ordered for this distribution
UOM_CODE VARCHAR2 3 Unit of measure for the quantity
AMOUNT_ORDERED NUMBER Amount ordered for this service based purchase order distribution
QUANTITY_CANCELLED NUMBER Quantity cancelled for this distribution
AMOUNT_CANCELLED NUMBER Amount cancelled for this distribution
SECONDARY_QUANTITY NUMBER Quantity ordered in the secondary unit of the measure
SECONDARY_UOM_CODE VARCHAR2 3 Secondary unit of measure code of item
SECONDARY_QTY_CANCELLED NUMBER Quantity cancelled in the secondary unit of measure
CURRENCY_CODE VARCHAR2 15 Identifies the currency of the price
CURRENCY_CONVERSION_TYPE VARCHAR2 30 Conversion type that should be used for currency conversion
CURRENCY_CONVERSION_DATE TIMESTAMP The date to be used to determine the exchange rates for currency conversion
CURRENCY_CONVERSION_RATE NUMBER Optional user specified currency conversion rate.
ACCRUE_ON_RECEIPT_FLAG VARCHAR2 1 Indicates whether the accrual should be done on receipt or at the end of the period
MATCHING_BASIS VARCHAR2 30 OBSOLETE: This identifies the Matching basis for the PO line. This information is interfaced from Purchase / payable application. The values are QUANTITY, AMOUNT.
MATCH_OPTION VARCHAR2 25 Indicates whether the invoice match should happen at the purchase order or the receipt
DESTINATION_TYPE_CODE VARCHAR2 25 Identifies type of destination for the material: Inventory, Expense
VENDOR_ID NUMBER 18 Identifies the Supplier and {References PO_VENDORS.vendor_id}
VENDOR_SITE_ID NUMBER 18 Identifies the Supplier Site and References {PO_VENDOR_SITES_ALL.vendor_site_id}
SHIP_TO_LOCATION_ID NUMBER 18 The identifier of the specific location that the goods are being shipped to.
TAX_EXCLUSIVE_PRICE NUMBER PO line price exclusive of all taxes
NONRECOVERABLE_INCLUSIVE_TAX NUMBER The portion of taxes paid that is included in the Distribution Ordered amount and which is nonrecoverable.
CONSIGNED_FLAG VARCHAR2 1 Indicates whether the item will be purchased under consignment arrangement from supplier. Reference Entity: PO_LINES_ALL.CONSIGNMENT_LINE_FLAG
PRODUCT_FISCAL_CLASSIFICATION VARCHAR2 240 The Product Fiscal Classification is a classification of the product(s) by tax authority for tax purposes. It is based on the inventory item category.
INTENDED_USE VARCHAR2 240 Intended Use is the purpose for which a product is likely to be used in the business by the purchaser
SUPPLIER_ITEM VARCHAR2 300 Supplier item number on the document.
COUNTRY_OF_ORIGIN_CODE VARCHAR2 2 Code for the country of manufacture.
REQUISITIONING_BU_ID NUMBER 18 Business unit of the inventory organization.
PROCESSING_STATUS VARCHAR2 20 Used by the costing preprocessors to indicate the processing status of the interface record. The records in status 'ERROR' will remain in the interface until the underlying issue is fixed. The corresponding error codes will be in the table CMR_PROCESS_ERRORS.
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.
PO_CLOSED_REOPENED_DATE DATE The actual closed or reopened date. If event_type is 'PO Close' this column shows closed date and if event_type is 'PO Reopen' this column shows reopen date.
WORK_ORDER_ID NUMBER 18 Unique identifier for the contract manufacturing work order.
WORK_ORDER_NUMBER VARCHAR2 120 Indicates the contract manufacturing work order number.
WORK_ORDER_PRODUCT VARCHAR2 300 Indicates the finished good product of the contract manufacturing work order.
WO_OPERATION_SEQ_NUMBER NUMBER Indicates the sequence number of the operation in the work order.
WORK_ORDER_OPERATION_ID NUMBER 18 Unique identifier of the work order operation.
RECOVERABLE_TAX NUMBER Recoverable tax (per unit) amount and which is excluded in the Distribution Ordered amount.
RECOVERABLE_INCLUSIVE_TAX NUMBER The portion of taxes paid that is included in the Distribution Ordered amount and which is recoverable.

Indexes

Index Uniqueness Tablespace Columns
CMR_I_PURCHASE_ORDER_DTLS_N1 Non Unique FUSION_TS_TX_DATA EXTERNAL_SYSTEM_REFERENCE, PO_LINE_LOCATION_ID
CMR_I_PURCHASE_ORDER_DTLS_N2 Non Unique Default PROCESSING_STATUS, REQUEST_ID, NVL("PROCESSING_STATUS", 1)
CMR_I_PURCHASE_ORDER_DTLS_N3 Non Unique Default INVENTORY_ITEM_ID, UOM_CODE, NVL("TRADE_ORGANIZATION_ID", "DELIVER_TO_INVENTORY_ORG_ID"), PROCESSING_STATUS
CMR_I_PURCHASE_ORDER_DTLS_PK Unique FUSION_TS_TX_DATA EXTERNAL_SYSTEM_REFERENCE, EXTERNAL_SYSTEM_REF_ID, EVENT_DATE