DW_AGREEMENT_CF

Agreement Fact

Details

Module: Purchasing

Key Columns

AGREEMENT_ID, AGREEMENT_LINE_ID, PRICE_BREAK_ID

Columns

Columns
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 999     Note to the supplier    
NOTE_TO_RECEIVER VARCHAR2 999     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