Agreement Fact
Module: Purchasing
AGREEMENT_ID, PRICE_BREAK_ID, AGREEMENT_LINE_ID
Name | Datatype | Length | Precision | Not Null | Comments | Referred Table | Referred Column |
---|---|---|---|---|---|---|---|
AGREEMENT_ID | NUMBER | 38 | 0 | True | Agreement Header unique identifer | ||
AGREEMENT_LINE_ID | NUMBER | 38 | 0 | True | Agreement Line unique identifer. For Contract Purchase Agreements (CPA) it will be -99999 | ||
PRICE_BREAK_ID | NUMBER | 38 | 0 | True | Agreement Price Break (PO Line Location) unique identifer. If no price break then -99999 | ||
SOURCE_RECORD_ID | VARCHAR2 | 128 | Source record identifier. | ||||
AGREEMENT_NUMBER | VARCHAR2 | 32 | Purchase order number | ||||
AGREEMENT_LINE_NUMBER | NUMBER | 38 | 0 | Agreement Line number | |||
PRICE_BREAK_NUMBER | NUMBER | 38 | 0 | Price Break Number | |||
UNIQUE_AGREEMENT_LINE_INDICATOR | VARCHAR2 | 16 | This column will help in identifying unique agreement line records in the fact . If agreement line has multiple price breaks , only one of them will be set as Y , rest all will be set as N. If agreement line has a single (or dummy) price breaks, that price break record will be set as Y. If the unique_agreement_line_indicator ='Y' filter is applied , it will bring the fact to agreement line grain | DW_Y_N_D_TL | CODE | ||
AGREEMENT_TYPE | VARCHAR2 | 32 | Type of the agreement document BLANKET/CONTRACT. For translations, need to filter DOCUMENT_TYPE_CODE='PA' in DW_PO_DOCUMENT_TYPES_ALL_D_TL. To be joined with DW_PO_DOCUMENT_TYPES_ALL_D_TL (DOCUMENT_SUBTYPE, PRC_BU_ID) using (AGREEMENT_TYPE, PROCUREMENT_BU_ID) | DW_PO_DOCUMENT_TYPES_ALL_D_TL | DOCUMENT_SUBTYPE ,PRC_BU_ID | ||
AGREEMENT_STATUS | VARCHAR2 | 32 | Document Status. Values are ONHOLD, OPEN, CANCELLED. | DW_AGREEMENT_STATUS_D_TL | CODE | ||
AGREEMENT_REVISION_NUM | NUMBER | Agreement final revision number | |||||
PROCUREMENT_BU_ID | NUMBER | 38 | 0 | Procurement BU Identifer | DW_INTERNAL_ORG_D | ORGANIZATION_ID | |
LEGAL_ENTITY_ID | NUMBER | 38 | 0 | Business unit legal entity identifier. This is derived using Procument BU | DW_LEGAL_ENTITY_D | LEGAL_ENTITY_ID | |
LEDGER_ID | NUMBER | 38 | 0 | Business unit primary ledger. This is derived using Procument BU | DW_LEDGER_D | LEDGER_ID | |
PROC_FISCAL_PERIOD_SET_NAME | VARCHAR2 | 16 | Accounting calendar name. | DW_FISCAL_DAY_D | FISCAL_PERIOD_SET_NAME | ||
PROC_FISCAL_PERIOD_TYPE | VARCHAR2 | 16 | Accounting period type. | DW_FISCAL_DAY_D | FISCAL_PERIOD_TYPE | ||
FISCAL_PERIOD_NAME | VARCHAR2 | 64 | Accounting period name. | DW_FISCAL_DAY_D | FISCAL_PERIOD_NAME | ||
INVENTORY_ORGANIZATION_ID | NUMBER | 38 | 0 | Procurement BU Inventory Org. This will be used while getting item details | DW_INV_ORGANIZATION_D | INV_ORGANIZATION_ID | |
VENDOR_ID | NUMBER | 38 | 0 | Supplier unique identifier | DW_PARTY_D | SUPPLIER_ID | |
VENDOR_SITE_ID | NUMBER | 38 | 0 | Supplier site unique identifier | DW_SUPPLIER_SITE_D | SUPPLIER_SITE_ID | |
BUYER_ID | NUMBER | 38 | 0 | Buyer unique identifier. | DW_PERSON_NAME_CURRENT_D | PERSON_ID | |
FINAL_APPROVER_ID | NUMBER | 38 | 0 | Final approver id for revision zero of the agreement (Derived from versions table). If the agreement is System approved, Approver id will not be populated (-99999) | DW_PERSON_NAME_CURRENT_D | PERSON_ID | |
SUBMITTER_ID | NUMBER | 38 | 0 | Submitter for revision zero of the agreement (Derived from action history table) | DW_PERSON_NAME_CURRENT_D | PERSON_ID | |
TERMS_ID | NUMBER | 38 | 0 | Payment terms unique identifier | DW_AP_TERMS_D | PAYMENT_TERMS_ID | |
CARRIER_ID | NUMBER | 38 | 0 | Carrier identifier | DW_PARTY_D | PARTY_ID | |
DOCUMENT_STYLE_ID | NUMBER | 38 | 0 | Document Style Identifier . Along with LANGUAGE, fact (DOCUMENT_STYLE_ID,AGREEMENT_TYPE ) will join with DW_DOC_STYLE_LINES_TL (STYLE_ID,DOCUMENT_SUBTYPE) | DW_DOC_STYLE_LINES_TL | STYLE_ID ,DOCUMENT_SUBTYPE | |
DOCUMENT_CREATION_METHOD | VARCHAR2 | 32 | Method in which the document got created (not shown in UI). Values are AWARD_SOURCING, COPY_DOCUMENT, ENTER_PO. | DW_PO_DOC_CREATION_METHOD_D_TL | CODE | ||
COMMUNICATION_METHOD | VARCHAR2 | 32 | Supplier Notification Method. Values can be EMAIL, PRINT, ,FAX etc. | DW_DOC_COMM_METHOD_D_TL | CODE | ||
MODE_OF_TRANSPORT | VARCHAR2 | 32 | Type of transportation used to ship the product, such as truck, air or boat. | DW_PO_MODE_OF_TRANSPORT_D_TL | CODE | ||
SERVICE_LEVEL | VARCHAR2 | 32 | Priority of transportation that affects how quickly goods are transported | DW_PO_SERVICE_LEVELS_D_TL | CODE | ||
DESCRIPTION | VARCHAR2 | 256 | Descriptive comments for the document | ||||
NOTE_TO_VENDOR | VARCHAR2 | 1024 | Note to the supplier | ||||
NOTE_TO_RECEIVER | VARCHAR2 | 1024 | Note to the receiver of the purchase order | ||||
AGING_ONSET_POINT | VARCHAR2 | 32 | Indicates the mutually agreed event point at which consigned material begins to age. | DW_AGING_ONSET_POINT_D_TL | CODE | ||
AGING_PERIOD_DAYS | NUMBER | 38 | 0 | Indicates the maximum number of days the material may be on consignment. | |||
CONSUMPTION_ADVICE_FREQUENCY | VARCHAR2 | 32 | Freq default value at which the Create Consumption Advice program will be run for all consumption transactions. | DW_CONSMPTN_ADVICE_FREQ_D_TL | CODE | ||
CONSUMPTION_ADVICE_SUMMARY | VARCHAR2 | 32 | Granularity at which consumption advices will be generated. | DW_CONSMPTN_ADVICE_SUMMRY_D_TL | CODE | ||
APPROVER_COUNT | NUMBER | Count of Approvers (not System approved) for Agreement Revision | |||||
TOTAL_AMENDMENT_COUNT | NUMBER | Number of amendments to the agreement across all its versions. Amendment count does not include REVISION_NUM = | |||||
BUYER_AMENDMENT_COUNT | NUMBER | Ammendment count when ORIGINATOR_ROLE ='BUYER' | |||||
SUPPLIER_AMENDMENT_COUNT | NUMBER | Ammendment count when ORIGINATOR_ROLE ='SUPPLIER' | |||||
LINE_TYPE_ID | NUMBER | 38 | 0 | Line type unique identifier | DW_PURCHASING_LINE_TYPE_D | LINE_TYPE_ID | |
LINE_STATUS | VARCHAR2 | 32 | Line status. Values are ONHOLD, OPEN, CANCELLED. | DW_AGREEMENT_LINE_STATUS_D_TL | CODE | ||
SHIP_TO_ORGANIZATION_ID | NUMBER | 38 | 0 | Inventory Organization (what is seen in Price Break UI, not mandatory) | DW_INV_ORGANIZATION_D | INV_ORGANIZATION_ID | |
SHIP_TO_LOCATION_ID | NUMBER | 38 | 0 | Unique identifier for the ship-to location | DW_WORKER_LOCATION_D | LOCATION_ID | |
INVENTORY_ITEM_ID | NUMBER | 38 | 0 | Item Number. This fact (INVENTORY_ITEM_ID,INVENTORY_ORGANIZATION_ID) joins DW_INVENTORY_ITEM_D(INVENTORY_ITEM_ID, ORGANIZATION_ID) | DW_INVENTORY_ITEM_D | INVENTORY_ITEM_ID ,ORGANIZATION_ID | |
INVENTORY_ITEM_DESCRIPTION | VARCHAR2 | 256 | Item description. | ||||
SUPPLIER_ITEM_NAME | VARCHAR2 | 512 | Supplier item number | ||||
PURCHASING_CATEGORY_ID | NUMBER | 38 | 0 | Item category unique identifier | DW_ITEM_CATEGORIES_D | CATEGORY_ID | |
ITEM_REVISION | VARCHAR2 | 32 | Item revision. | ||||
NEGOTIATION_ID | NUMBER | 38 | 0 | Reference to the negotiation id in sourcing (AUCTION_HEADER_ID) | |||
NEGOTIATION_LINE_NUMBER | NUMBER | Reference to the negotiation line number in Sourcing (AUCTION_LINE_NUMBER) | |||||
PRICE_BREAK_LOOKUP_CODE | VARCHAR2 | 32 | Cumulative or Non cumulative price break | ||||
LINE_AGREEMENT_QUANTITY | NUMBER | Quantity agreed for an agreement line | |||||
PUOM_LINE_AGREEMENT_QUANTITY | NUMBER | Quantity agreed for an agreement line in PUOM | |||||
LINE_UOM_CODE | VARCHAR2 | 16 | Unit of measure for the quantity ordered. | DW_UNIT_OF_MEASURE_D_TL | UOM_CODE | ||
PUOM_LINE_UOM_CODE | VARCHAR2 | 16 | Unit of measure for the quantity ordered in PUOM | DW_UNIT_OF_MEASURE_D_TL | UOM_CODE | ||
PUOM_CONVERSION_FACTOR | NUMBER | Conversion Factor from Transaction UOM to Primary UOM | |||||
PRICE_BREAK_QUANTITY | NUMBER | Price break quantity | |||||
PUOM_PRICE_BREAK_QUANTITY | NUMBER | Price break quantity in PUOM | |||||
PRICE_BREAK_UOM_CODE | VARCHAR2 | 16 | Unit of measure for the price break quantity | DW_UNIT_OF_MEASURE_D_TL | UOM_CODE | ||
AGREEMENT_AMOUNT | NUMBER | Amount agreed for the planned or blanket purchase order. It will be null (not ) if not populated in UI | |||||
GLOBAL_AGREEMENT_AMOUNT | NUMBER | AGREEMENT_AMOUNT in global currency | |||||
AGREEMENT_RELEASED_AMOUNT | NUMBER | sum of all the approved standard PO amounts. This is as seen in the UI screen | |||||
GLOBAL_AGREEMENT_RELEASED_AMOUNT | NUMBER | AGREEMENT_RELEASED_AMOUNT in global currency | |||||
UNIT_PRICE | NUMBER | Unit price for the line | |||||
GLOBAL_UNIT_PRICE | NUMBER | Unit price in global currency | |||||
UNIT_PRICE_DERIVED | NUMBER | Derived unit price for the line with logic : WHEN PO_LINES_ALL.ORDER_TYPE_LOOKUP_CODE = 'FIXED PRICE' THEN PO_LINES_ALL.AMOUNT ELSE UNIT_PRICE | |||||
GLOBAL_UNIT_PRICE_DERIVED | NUMBER | Derived unit price in global currency | |||||
LINE_AGREEMENT_AMOUNT | NUMBER | Amount agreed for an agreement line | |||||
GLOBAL_LINE_AGREEMENT_AMOUNT | NUMBER | LINE_AGREEMENT_AMOUNT in global currency | |||||
LINE_RELEASED_AMOUNT | NUMBER | The total value of goods and services ordered for line item | |||||
GLOBAL_LINE_RELEASED_AMOUNT | NUMBER | LINE_RELEASED_AMOUNT in global currency | |||||
PRICE_BREAK_PRICE | NUMBER | Price break price | |||||
DISCOUNT_PERCENT | NUMBER | Discount percentage for price break | |||||
CURRENCY_CODE | VARCHAR2 | 16 | Unique identifier for the currency | DW_CURRENCY_DETAILS_D_TL | CURRENCY_CODE | ||
GLOBAL_CURRENCY_CODE | VARCHAR2 | 16 | Global currency code. | DW_CURRENCY_DETAILS_D_TL | CURRENCY_CODE | ||
GLOBAL_CURRENCY_EXCH_RATE | NUMBER | Global currency conversion rate. | |||||
AGREEMENT_CREATED_BY | VARCHAR2 | 64 | Indicates the user who created the agreement row | DW_USER_D | USERNAME | ||
AGREEMENT_LINE_CREATED_BY | VARCHAR2 | 64 | Indicates the user who created the agreement line row | DW_USER_D | USERNAME | ||
AGREEMENT_LINE_TYPE_LOOKUP_CODE | VARCHAR2 | 32 | Value basis of the line | ||||
AGREEMENT_LINE_AMOUNT | NUMBER | Budget Amout for temp labor standard PO lines | |||||
GLOBAL_AGREEMENT_LINE_AMOUNT | NUMBER | Budget Amout for temp labor standard PO lines in global currency | |||||
PRICE_BREAK_CREATED_BY | VARCHAR2 | 64 | Indicates the user who created the price break row | DW_USER_D | USERNAME | ||
AGREEMENT_CREATION_DATE | DATE | Agreement creation date. | |||||
AGREEMENT_DOCUMENT_DATE | DATE | The document date for revision of the agreement | |||||
AGREEMENT_SUBMIT_DATE | DATE | The submit date for revision of the agreement | |||||
AGREEMENT_OPEN_DATE | DATE | The processed date of revision of the agreement | |||||
AGREEMENT_START_DATE | DATE | Agreement Header Start Date | |||||
AGREEMENT_END_DATE | DATE | Agreement Header End Date | |||||
AGREEMENT_APPROVED_DATE | DATE | The approved date of revision of the agreement. If approved date is not populated , we will use document date of revision | |||||
AGREEMENT_CLOSED_DATE | DATE | Date the document was closed | |||||
AGRMNT_APPR_CYCLE_TIME | NUMBER | No of days between Agreement Creation and Agreement Approved Date . This value will be calculated only for below cases. Else it will be null . 1] Approved date is populated | |||||
LINE_CREATION_DATE | DATE | Agreement Line Creation Date . | |||||
LINE_CANCEL_DATE | DATE | Agreement Line Cancel Date | |||||
LINE_CLOSED_DATE | DATE | Agreement Line Closed Date | |||||
LINE_EXPIRATION_DATE | DATE | Agreement Line Expiration Date | |||||
LINE_EXPIRY_AGRMNT_END_DATE | DATE | This is a derived column which takes the Line EXPIRATION_DATE, If EXPIRATION_DATE is null then HEADER END_DATE. | |||||
NEGOTIATION_LINE_CREATION_DATE | DATE | Creation Date for Negotiation line | |||||
NEGO_TO_AGRMNT_APPR_CYCLE_TIME | NUMBER | No of days between Negotiation Line Creation Date and Agreement Approved Date This value will be calculated only for below cases. Else it will be null . 1] For BPA 2] It is sourcing based agreement 3] Approved date is populated | |||||
PRICE_BREAK_START_DATE | DATE | Effective date of the price break | |||||
PRICE_BREAK_END_DATE | DATE | End date of the price break | |||||
AGREEMENT_APPROVED_FLAG | VARCHAR2 | 32 | Indicates whether the purchase order is approved or not. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
AUTO_GENERATE_ORDERS_FLAG | VARCHAR2 | 16 | Automatically Generate Orders flag. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
ENABLE_RETROACTIVE_PRICE_FLAG | VARCHAR2 | 16 | Indicates whether price updates on the agreement should be propagated to orders. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
REQUIRED_ACKNOWLEDGMENT_FLAG | VARCHAR2 | 16 | Indicates whether acceptance from the supplier is required or not. Values can be L/N/D . | DW_ACCEPTANCE_REQUIRED_D_TL | CODE | ||
REQUIRES_SIGNATURE_FLAG | VARCHAR2 | 16 | Indicate if signatures are required on the document before it can be opened. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
BUYER_MANAGED_TRANSPORT_FLAG | VARCHAR2 | 16 | A flag to indicate that the buying company is responsible for arranging the transportation. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
CONFIRMING_ORDER_FLAG | VARCHAR2 | 16 | Indicates whether the purchase order is a confirming order or not. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
PAY_ON_RECEIPT_FLAG | VARCHAR2 | 16 | Indicator of whether the purchase order will be paid upon receipt. WHEN PAY_ON_CODE= 'RECEIPT' THEN YES ELSE NO. Values can be Y/N. | DW_Y_N_D_TL | CODE | ||
PAY_ON_USE_FLAG | VARCHAR2 | 16 | Indicate whether self-billing is enabled for consumption of consigned material. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
IS_SOURCING_BASED_FLAG | VARCHAR2 | 16 | Flag that indicates if purchase order line has sourcing negotiation id assigned. Values can be Y/N. | DW_Y_N_D_TL | CODE | ||
NEGOTIATED_FLAG | VARCHAR2 | 16 | Flag that indicates if negotiation check box was selected for purchase order line. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE | ||
CONSIGNMENT_LINE_FLAG | VARCHAR2 | 16 | Indicates whether the item will be purchased under consignment from supplier. Values can be Y/N/NO VALUE. | DW_Y_N_D_TL | CODE |