Fact tables Purchase lines

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

Field code	Normal title	Menu	Table	Objecttype
DOC	Document	566		Dimension
LINDOC	Document line type	2216		Dimension
DOSSIER	Folder			Technical
DAT	Document date			Dimension
CPY	Company			Technical
FCY	Financial site			Technical
PURFCY	Purchase site			Technical
STOFCY	Receipt site			Technical
BPSORD	Sold-to-supplier			Technical
BPSINV	Invoicing supplier			Technical
BPSPAY	Paid supplier			Technical
BPSGRU	Group Supplier			Technical
BPSRSK	Risk supplier			Technical
PJT	Project			Technical
CUR	Transaction currency			Technical
CURLOC	Company currency			Technical
CURFOLD	Folder currency			Technical
ITMREF	Product			Technical
AMTSIG	Signed:			Technical
LINPURTYP	Purchase type	646		Dimension
PSHNUM	Purchase request number			Technical
PSDLIN	Purchase request line no.			Technical
PSHPOH	Incremental no. for ordered PR line			Technical
POHNUM	Order number			Technical
POPLIN	Order line no.			Technical
POQSEQ	Order line sequence no.			Technical
PTHNUM	Receipt number			Technical
PTDLIN	Receipt line no.			Technical
PIHNUM	Invoice number			Technical
PIDLIN	Invoice line no.			Technical
PNHNUM	Return number			Technical
PNDLIN	Return line no.			Technical
DIE1..DIE10	Dimension type codes			Technical
CCE1..CCE10	Analytical dimensions.			Technical
COA1..COA3	Charts of accounts			Technical
ACC1..ACC3	Accounts			Technical
FLOW	Flows	2215		Dimension
QTYPSH_US	STK purchase request quantity			Measurement
QTYPOH_US	STK order quantity			Measurement
QTYPOD_US	STK quantity to be received			Measurement
QTYPTH_US	STK receipt quantity			Measurement
QTYPIH_US	STK invoice quantity			Measurement
QTYPNH_US	STK return quantity			Measurement
AMTNOT_PSH	Ex tax amount purchase request line			Measurement
AMTNOT_POH	Order line total ex tax			Measurement
AMTNOT_PTH	Ex tax amount receipt line			Measurement
AMTNOT_PIH	Invoice line total ex tax			Measurement
AMTNOT_PNH	Return line total ex tax			Measurement
AMTATI_PSH	Tax incl amount purchase request line			Measurement
AMTATI_POH	Order line total tax incl			Measurement
AMTATI_PTH	Tax incl amount receipt line			Measurement
AMTATI_PIH	Invoice line total tax incl			Measurement
AMTATI_PNH	Return line total tax incl			Measurement
AMTDED_PSH	Deductible tax purchase request line			Measurement
AMTDED_POH	Deductible tax order line			Measurement
AMTDED_PTH	Deductible tax receipt line			Measurement
AMTDED_PIH	Deductible tax invoice line			Measurement
AMTDED_PNH	Deductible tax return line			Measurement
CPR_POH	Order price order line			Measurement
CPR_PTH	Order price receipt line			Measurement
CPR_PIH	Order price invoice line			Measurement
CPR_PNH	Order price return line			Measurement
DISAMT_PSH	Discount amount purchase request line			Measurement
DISAMT_POH	Order line discount amount			Measurement
DISAMT_PTH	Receipt line discount amount			Measurement
DISAMT_PIH	Invoice line discount amount			Measurement
DISAMT_PNH	Return line discount amount			Measurement
EXPAMT_PSH	Expense amount purchase request line			Measurement
EXPAMT_POH	Order line expense amount			Measurement
EXPAMT_PTH	Expense amount receipt line			Measurement
EXPAMT_PIH	Invoice line expense amount			Measurement
EXPAMT_PNH	Expense amount return line			Measurement
DISDTA_POH	Discount amount of order invoicing elements			Measurement
DISDTA_PTH	Discount amount of receipt invoicing element			Measurement
DISDTA_PIH	Discount amount of invoice invoicing elements			Measurement
EXPDTA_POH	Expense amount of order invoicing elements			Measurement
EXPDTA_PTH	Expense amount of receipt invoicing element			Measurement
EXPDTA_PIH	Expense amount of invoice invoicing elements			Measurement
AMTPAY	Final amount paid			Measurement
TMPPAY	Temporary amount paid			Measurement

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

Code	Title	Dimension
IPURAMTK	Ex tax limit in K currency
IPURBPSGE	Group supplier End	BPSUPPLIER
IPURBPSGS	Group supplier Start	BPSUPPLIER
IPURBPSIE	Invoicing supplier End	BPSUPPLIER
IPURBPSIS	Invoicing supplier Start	BPSUPPLIER
IPURBPSOE	Sold-to supplier End	BPSUPPLIER
IPURBPSOS	Sold-to supplier Start	BPSUPPLIER
IPURBPSPE	Pay-to Supplier End	BPARTNER
IPURBPSPS	Pay-to Supplier Start	BPARTNER
IPURBPSRE	Risk supplier End	BPSUPPLIER
IPURBPSRS	Risk supplier Start	BPSUPPLIER
IPURCPYE	Company End	COMPANY
IPURCPYS	Company Start	COMPANY
IPURDAT	Reference deadline
IPURFCYE	Purchase site End	FACILITY
IPURFCYS	Purchase site Start	FACILITY
IPURITME	Product End	ITMMASTER
IPURITMS	Product Start	ITMMASTER
IPURPERN1E	N-1 analysis end date
IPURPERN1S	N-1 analysis start date
IPURPERNE	N analysis end date
IPURPERNS	N analysis start date
IPURPJT	Off-project included	PJT
IPURPJTE	Project End	PJT
IPURPJTS	Project Start	PJT
IPURRAT	Weighing percentage
IPURSFI	Invoicing elements included?
IPURTSGE	Group 1 Group supplier End	BPSUPPLIER
IPURTSGS	Group 1 Group supplier Start	BPSUPPLIER
IPURTSIE	Product group 1 End	ITMMASTER
IPURTSIS	Product group 1 Start	ITMMASTER
IPURTSOE	Group 1 Sold-to-supplier End	BPSUPPLIER
IPURTSOS	Group 1 Sold-to-supplier Start	BPSUPPLIER
IPURTSRE	Group 1 Risk supplier End	BPSUPPLIER
IPURTSRS	Group 1 Risk supplier Start	BPSUPPLIER
IPURTSSE	Group 1 Invoicing supplier End	BPSUPPLIER
IPURTSSS	Group 1 Invoicing supplier Start
PURANNCOU	Current year
PURANNPRE	Previous year
PURCNO	Credit memo	PINVOICE
PURCOUINFR	Country : France	PINVOICE
PURINV	Purchase invoices

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