Fact tables Purchase lines

The fact table of purchase document belongs to the Purchase datamart (PURCHASE).
It contains information coming from:

• the purchase request lines
• the purchase order lines and invoicing elements
• the delivery request lines
• the purchase receipt lines and invoicing elements
• the purchase return lines
• validated invoice (and credit memo) lines, invoicing elements and open items settled or only partly settled
  

These data are reorganized in order to allow a simple elaboration of the reports in the BI:

- some information deemed secondary is not transmitted,
- conversely, other information is calculated,
- finally, the information is restructured if needed.

Description

To this fact table are linked:

Synchronization rules

These are the rules used to define the way the data are extracted from the database and the way the fact table is loaded. There are 10 of them for the Purchase lines fact table:

• PREQUIS: Purchase order lines not ordered,
• PREQUISO: Purchase order lines ordered or ordered in part,
• PORDER: Order lines and delivery requests,
• PORDERINV: Invoicing elements of the orders,
• PRECEIPT: Receipt lines,
• PRECEIPTINV: Invoicing elements of the receipts,
• PRETURN: Return lines not validated,
• PINVOICE:Validated delivery lines,
• PINVOICEIN: Invoicing elements of the posted invoices,
• PINVOICEDU: Paid or partly paid open items of the posted invoices.

The table fields can be loaded directly using a field in the database or their value determined via a calculation formula or the call result to a function. Their detailed list is provided below.

The invoicing elements associated with a document are only extracted if the following conditions are complied with:

• there is a product code associated with the setup of the invoicing element,
• the invoicing element is active,
• it is not distributed over the document lines.

Dimensions

These are database tables such as the country table, the site table, the accounting code table, etc.
Some dimensions belong to the Purchase moduleand others are common to all the modules (Company, Site, Dimension type). Moreover they can be linked to several fact tables. Links have been established between these dimensions.
When a field in a fact table is linked to a dimension, the corresponding link expression is defined in the fact table (Links tab). The dimension list linked to the Purchase lines fact table is provided below.

Hierarchies

These are pre-defined grouping criteria, used to navigate in the report according to the required detail level, by grouping or zooming on the detail of the hierarchy criteria.
These hierarchies are defined for the Datamart. They are detailed below.

Predefined conditions

These are conditions that will be used as filter criteria in the report itself. They are defined for each fact tables.
It can either be simple conditions using fields or constants, or conditions requiring the entry of values (conditions corresponding to the prompts to be entered in the reports).
The list is provided below.

Sub-classes (or Indicators)

In the fact table, each field having as object type: Measurecan be identified as belonging to a sub-class. Thanks to the BO Infoview tool, this classification is used to display this information grouped by indicator (each indicator having been assigned to a sub-class) in the Purchasingenvironment. In the fact table, these indicators are of Amount or Quantity type: Order amounts, Receipt amounts, Purchase request quantities, etc.

For the Purchase lines fact table, the sub-classes listand the list of the fields from which they are made are provided below.

Technical supplements

• Data detail
• List of the specific loading formulas and functions
• List of dimensions
• Hierarchy list
• List of the predefined conditions
• Sub-classes list (Indicators)

Data detail

All quantities are also defined in statistical units. *_STK fields also exist as *_ST.

All amounts are expressed in three currencies.

• the fields without extension in transaction currency,
• the fields with the extension *_C in company currency,
• the fields with the extension *_F in folder currency,

List of the specific loading formulas and functions

Specific loading formulas

• ([F]BETCPY - 1) * 2 + !([F]BETCPY - 1) * ( ([F]BETFCY - 1) * 3 + !([F]BETFCY - 1) ): this formula is used to specify whether the document line concerns a standard flow, an inter-company flow or an inter-site flow.

Specific loading functions

• BICPT.SETDIE(Index,"CAL") : this function returns the dimension type code, stored in the table of origin. It corresponds to the dimension type code stored in parameter CPT\BI\BIDIE+index, with the index being used as parameter.
• BICPT.SETDIE(Index,"CAL") : this function returns the dimension code, stored in the table of origin. It corresponds to the dimension type code stored in parameter CPT\BI\BIDIE+index.
• TRTX3BI.SETCOA(Index,[F]CPY): this function returns the chart of accounts, the ledger number and the company used as parameter.
• TRTX3BI.SETACC(Index,[F]CPY): this function returns the account stored in the table of origin (CPTANALIN), the ledger number and the company used as parameter.
• TRTX3BI.CNVAMT(CUR_ORI,CUR_DES,CUR_SOC,0,0,TYPCUR,DAT,AMOUNT): this function returns the amount of origin converted to the destination currency. Either the exchange rate type and date or the multiplying or dividing exchange rate is used as parameter.
• TRTX3BI.GETCUR(FCY): this function returns the site currency.
• TRTX3BI.GETSYSCUR(): this function returns the folder currency.
• TRTX3BI.CNVQTY(QTY_ORI, UNIT_ORI, UNIT_DES, ITMREF, ABVRFIC , NUMDOC, NUMDOC_LIN, NUMDOC_SEQ, ROUND_CODE, NB_DEC): this function returns the quantity of origin converted to the destination unit.
• TRTACHBI.GETPURDCA( TYPDOC,[F]NUMDOC,[F]NUMDOC_LIN, NUMDOC_SEQ, [F]CUR, TYPVALEUR): this function returns the discount or expense amount for a document line in the requested currency. The last argument makes it possible to choose between expenses(1), discounts(2) and gross amount (3).
• TRTACHBI.GETPFIDCA(TYPDOC,[F]NUMDOC,[F]NUMDOC_LIN,NUMDOC_SEQ,[F]CUR,TYPVALEUR): this function returns the amounts of the expense or discount-type invoicing elements distributed on the lines for a document line. The last argument makes it possible to choose between expenses(1) and discounts(2).
• TRTACHBI.GETPOHCPR(NUMDOC, NUMDOC_LIN, NUMDOC_SEQ, [F]CUR): this function returns the provisional cost price of an order line in the requested currency.
• TRTACHBI.GETPIHFCY(NUM_INV,NUM_INV_LIN,TYP_FCY): this function returns the purchase or storage site of an invoice line based on the last argument (1 = Purchase; 2 = Storage).
• TRTACHBI.GETPFIFCY(NUM_INV, TYP_FCY): this function returns the purchase or storage site of the invoicing elements of an invoice based on the last argument (1 = Purchase; 2 = Storage).
• TRTX3BI.GETLASTLIN("TABLE", NUMDOC): this function returns the last line number of a document. This function is used to assign line numbers for invoicing elements and open items.

For the various functions using parameter TYPDOC, said parameter can contain the following values:

• 1 = Orders,
• 2 = Receipts,
• 3 = Returns,
• 4 = Invoices/credit memos,
• 5 = Purchase requests.

List of dimensions

Common dimensions

• Folder - FOLDERX3
  Table of origin: ADOSSIER
  Update type: Cancels and replaces
  
• Company - COMPANY
  Table of origin: COMPANY
  Update type: Incremental
  
• Site - FACILITY
  Table of origin: FACILITY
  Update type: Incremental
  
• Suppliers - BPSUPPLIER 
  Table of origin: BPSUPPLIER
  Update type: Incremental
  
• BP - BPARTNER
  Table of origin: BPARTNER
  Update type: Incremental
  
• Projects - PJT
  Table of origin: OPPOR
  Update type: Incremental
  
• Currency - CURRENCY
  Table of origin: TABCUR
  Update type: Incremental
  
• Product - ITMMASTER
  Table of origin: ITMMASTER
  Update type: Incremental
• Account- ACCOUNT
  Table of origin: GACCOUNT
  Update type: Cancels and replaces
  
• Dimension - CENTER
  Table of origin: CACCE
  Update type: Incremental

Dimensions of the Purchasing module

• Purchase requests - PREQUIS
  Table of origin: PREQUISD
  Update type: Incremental
  
• Purchase orders - PORDER
  Table of origin: PORDERQ
  Update type: Incremental
  
• Receipts - PRECEIPT
  Table of origin: PRECEIPTD
  Update type: Incremental
  
• Returns - PRETURN
  Table of origin: PRETURND
  Update type: Incremental
  
• Invoices - PINVOICE
  Table of origin: PINVOICED
  Update type: Incremental

Dimensions of the Financials module

• Account pyramid- PYRACC
  Table of origin: GACCPYMPRT
  Update type: Cancels and replaces
  
• Dimension pyramid- PYRCCE
  Table of origin: GCCEPYMPRT
  Update type: Cancels and replaces
  

Hierarchy list

• PURBPSGGEO - Group supplier. geography

- Group Supplier
- Country
- Continent

• PURBPSGITM - Group supplier - product

- Group supplier
- Product

• PURBPSGPJT - Group supplier - project

- Group supplier
- Project

• PURBPSGSTA - Group supplier statistics

- Group supplier
- Supplier stat.1
- Supplier stat.2
- Supplier stat.3
- Supplier stat.4
- Supplier stat.5

• PURBPSIGEO - Invoicing supplier geography

- Invoicing Supplier
- Country
- Continent

• PURBPSIITM - Invoicing supplier - product

- Invoicing supplier
- Product

• PURBPSIPJT - Invoicing supplier - project

- Invoicing supplier
- Project

• PURBPSISTA - Invoicing supplier statistics

- invoicing supplier
- Supplier stat.1
- Supplier stat.2
- Supplier stat.3
- Supplier stat.4
- Supplier stat.5

• PURBPSOGEO - Sold-to supplier geography

- Sold-to supplier
- Country
- Continent

• PURBPSOITM - Sold-to supplier - product

- Sold-to supplier
- Product

• PURBPSOPJT - Sold-to supplier - project

- Sold-to supplier
- Project

• PURBPSOSTA - Sold-to supplier statistics

- Sold-to supplier
- Supplier stat.1
- Supplier stat.2
- Supplier stat.3
- Supplier stat.4
- Supplier stat.5

• PURBPSPGEO - Pay-to BP geography

- Pay-to BP
- Country
- Continent

• PURBPSRGEO - Risk supplier geography

- Risk supplier
- Country
- Continent

• PURBPSROL - Supplier roles

- Sold-to supplier
- Invoicing supplier
- Group supplier
- Risk supplier

• PURBPSRSTA - Risk supplier statistics

- Risk supplier
- Supplier stat.1
- Supplier stat.2
- Supplier stat.3
- Supplier stat.4
- Supplier stat.5

• PURINVGEO - Invoice geography

- Postal code invoice
- Town invoice
- Department code invoice
- Region code invoice
- Country invoice
- Continent invoice

• PURITMSTA - Product statistics

- Product
- Product stat.1
- Product stat.2
- Product stat.3
- Product stat.4
- Product stat.5

• PURPURCPY - Purchase site - company

- Purchase site
- Financial site
- Company

• PURSTMBPSG - Product group - Group supplier

- Group supplier
- Product stat 1

• PURSTMBPSI - Product group - Invoicing supplier

- Invoicing supplier
- Product stat 1

• PURSTMBPSO - Product group - Sold-to supplier

- Sold-to supplier
- Product stat 1

List of the predefined conditions

Sub-classes list (Indicators)

Sub-classes (or Indicators)

• Purchase request quantities indicators
• Order quantities indicators
• Receipt quantities indicators
• Invoice quantities indicators
• Return quantities indicators
• Purchase request amounts indicators
• Order Amountsindicators
• Receipt amountsindicators
• Invoice Amountsindicators
• Return Amountsindicators

List of the fields from which the sub-classes are made.

Purchase request quantities indicators

- STK purchase request quantity
- STA purchase request quantity

Order quantities indicators

- STK order quantity
- STA order quantity
-STK quantity to receive
- STA quantity to receive

Receipt quantities indicators

- STK receipt quantity
- STA receipt quantity

Invoice quantities indicators

- STK invoice quantity
- STA invoice quantity

Return quantities indicators

- STK return quantity
- STA return quantity

Purchase request amounts indicators

- Ex tax amt purchase request line
- Ex tax amt purchase request line Cy
- Ex tax amt purchase request line Folder.
- Tax incl amt purchase request line
- Tax incl amt purchase request line Cy
- Tax incl amt purchase request line Folder
- Deductible tax PR line
- Deductible tax PR line Cy
- Deductible tax PR line Folder
- Discount amt purchase request line
- Discount amt purchase request line Cy
- Discount amt purchase request line Folder
- Expense amt purchase request line
- Expense amt purchase request line Cy
- Expense amt purchase request line Folder

Order Amounts indicators

- Ex tax amt order line
- Ex tax amt order line Company
- Ex tax amt order line Folder
- Tax incl amt order line
- Tax incl amt order line Company
- Tax incl amt order line Folder
- Deductible tax order line
- Deductible tax order line Cy
- Deductible tax order line Folder
- Order price order line
- Order price order line Cy
- Order price order line Folder
- Discount amt order line
- Discount amt order line Cy
- Discount amt order line Folder
- Expense amt order line
- Expense amt order line Cy
- Expense amt order line Folder
- Discount amt order inv. elt
- Discount amt order inv. elt Cy
- Discount amt order inv. elt Folder
- Expens amt order inv. elt
- Expens amt order inv. elt Cy
- Expens amt order inv. elt Folder

Receipt amounts indicators

- Ex tax amt receipt line
- Ex tax amt receipt line Company
- Ex tax amt receipt line Folder
- Tax incl amt receipt line
- Tax incl amt receipt line Company
- Tax incl amt receipt line Folder
- Deductible tax receipt line
- Deductible tax receipt line Cy
- Deductible tax receipt line Folder
- Order price receipt line
- Order price receipt line Cy
- Order price receipt line Folder
- Discount amt receipt line
- Discount amt receipt line Cy
- Discount amt receipt line Folder
- Expense amt receipt line
- Expense amt receipt line Cy
- Expense amt receipt line Folder
- Discount amt receipt inv. elt
- Discount amt receipt inv. elt Cy
- Discount amt receipt inv. elt Folder
- Expens amt receipt inv. elt
- Expens amt receipt inv. elt Cy
- Expens amt receipt inv. elt Folder

Invoice Amounts indicators

- Ex tax amt invoice line
- Ex tax amt invoice line Cy
- Ex tax amt invoice line Folder
- Tax incl amt invoice line
- Tax incl amt invoice line Company
- Tax incl amt invoice line Folder
- Deductible tax invoice line
- Deductible tax invoice line Cy
- Deductible tax invoice line Folder
- Order price invoice line
- Order price invoice line Cy
- Order price invoice line Folder
- Discount amt invoice line
- Discount amt invoice line Cy
- Discount amt invoice line Folder
- Expense amt invoice line
- Expense amt invoice line Cy
- Expense amt invoice line Folder
- Discount amt invoice inv. elt
- Discount amt invoice inv. elt Cy
- Discount amt invoice inv. elt Folder
- Expense amt invoice inv. elt
- Expense amt invoice inv. elt Cy
- Expense amt invoice inv. elt Folder
- Final amount paid
- Final amount paid Cy
- Final amount paid Folder
- Temporary amount paid
- Temporary amount paid Cy
- Temporary amount paid Folder

Return Amounts indicators

- Ex tax amt return line
- Ex tax amt return line Company
- Ex tax amt return line Folder
- Tax incl. return line
- Tax incl. return line Company
- Tax incl. return line Folder
- Deductible tax return line
- Deductible tax return line Cy
- Deductible tax return line Folder
- Order price return line
- Order price return line Cy
- Order price return line Folder
- Discount amt return line
- Discount amt return line Cy
- Discount amt return line Folder
- Expense amt return line
- Expense amt return line Cy
- Expense amt return line Folder