Select Page

SAP Substantive testing for 3 Way Match

SAP Substantive testing for 3 Way Match

SAP substantive analytical procedures from start to end from the 3-way match perspective.

About SAP Substantive Testing / Analytical Procedure

Definition

For application controls perspective, Substantive Testing is an audit procedure that examines the financial transaction to see if a transaction has bypassed the controls. We don’t perform substantive testing of all the controls we test as part of application controls. Only those controls where Walk-through or Configuration testing fails at design or implementation level then we carry out substantive testing.

Why 3 Way Match substantive testing is Complex?

From 3-way match perspective, the substantive testing brings us new challenges which we don’t face in other controls substantive testing. Challenges mainly arise out of the volume of data and complexity of the tables from which data is to be extracted.

Before we begin

Before you continue reading this post on How to perform substantive testing for 3 way match in sap, I would recommend you read about – How 3-way match is tested in SAP here. You will get an idea of what configurations are required to be tested.

What are the steps that be perfomed?

  1. Defined on what grounds the 3-way match is failing
  2. Identify what data you need to check (based on 1 above)
  3. Identify tables where this data is stored
  4. Extract this tables. Link tables (if necessary)
  5. Analyse data extracted and conclude.

In this post, we are going to assume that 3-way match filled in all possible way. Hence we need to test each and every aspect of the 3 way match substantively.

Identifying Tables for Substantive Testing

Following is the list of tables for 3 way match testing

Technical NameDescriptionData Stored
EKKOPurchase Order Header DataPO Number, Date, Org Data (Company Code, Purchasing Org), Document Category and Type, Vendor, Currency and Exchange Rate
EKPOPurchase Order Line Item DataDeletion Indicator, Material (Number, Group, Valuation Type) , Plant, PO Quantity, Rate, Amount, GR Based IV Check, Delivery Tolerance, GR Non Valuated.
EKBEPurchase Order History Line Item DataEvent Type (GRN / Invoice), Invoice details and GRN Details

While tables – EKKO and EKPO are straight forward, EKBE is a complex table. We cannot directly use EKBE table.

Identifying Data to be extracted for Substantive Analytical procedures

Following are the fields with corresponding tables for 3 way match testing.

Table NameField NameField DescriptionMy CommentsLink Tbl.Sel.
EKKOEBELNPurchasing Document Number
EKKOBUKRSCompany CodeCompany in ScopeYes
EKKOBSTYPPurchasing Document CategoryOnly "F"Yes
EKKOBSARTPurchasing Document TypeYes
EKKOLOEKZDeletion indicator in purchasing documentExclude PO which are deletedYes
EKKOSTATUStatus of Purchasing Document
EKKOAEDATDate on which the record was createdIt is System Generated Date
EKKOERNAMName of Person who Created the ObjectSAP User ID
EKKOLIFNRVendor's account numberLFA1
EKKOZTERMTerms of payment key
EKKOEKORGPurchasing organizationT024EYes
EKKOEKGRPPurchasing GroupT024Yes
EKKOWAERSCurrency KeyTCURCYes
EKKOWKURSExchange Rate
EKKOKUFIXIndicator: Fixing of Exchange RateYes
EKKOBEDATPurchasing Document DateUser entered DateYes
EKKOLLIEFSupplying VendorLFA1
EKKOFRGGRRelease group
EKKOFRGSXRelease StrategyT16FS
EKKOFRGKERelease Indicator: Purchasing Document
EKKOFRGZURelease status
EKKOFRGRLRelease Not Yet Completely Effected
EKPOEBELNPurchasing Document Number
EKPOEBELPItem Number of Purchasing Document
EKPOLOEKZDeletion indicator in purchasing documentExclude PO lines which are deleted
EKPOMATNRMaterial Number
EKPOWERKSPlantCan restrict testing to a PlantT001WYes
EKPOMATKLMaterial GroupCan restrict testing to a Material GroupT023Yes
EKPOMENGEPurchase Order Quantity
EKPOMEINSOrder unitT006
EKPOBPRMEOrder Price Unit (purchasing)
EKPONETPRNet Price in Purchasing Document (in Document Currency)
EKPOPEINHPrice unit
EKPONETWRNet Order Value in PO Currency
EKPOMWSKZTax on sales/purchases code
EKPOINSMKStock TypeRestricted, Un restricted, Blocked
EKPOUEBTOOverdelivery Tolerance Limit
EKPOELIKZDelivery Completed" Indicator
EKPOEREKZFinal Invoice Indicator
EKPOPSTYPItem category in purchasing documentService or Material or standardYes
EKPOWEPOSGoods Receipt Indicator
EKPOWEUNBGoods Receipt, Non-Valuated
EKPOREPOSInvoice receipt indicator
EKPOWEBREIndicator: GR-Based Invoice Verification
EKBEEBELNPurchasing Document NumberEKKO
EKBEEBELPItem Number of Purchasing DocumentEKPO
EKBEVGABETransaction/event type, purchase order history
EKBEGJAHRYear of material document
EKBEBELNRNumber of Material DocumentGRN / Invoice Number Depending upon - BUZEI
EKBEBUZEIItem in material document
EKBEBEWTPPurchase Order History CategoryT163B
EKBEBWARTMovement type (inventory management)T156
EKBEBUDATPosting Date in the Document
EKBEMENGEQuantity
EKBEBPMNGQuantity in purchase order price unit
EKBEDMBTRAmount in local currency
EKBEWRBTRAmount in document currency
EKBEWAERSCurrency KeyTCURC
EKBEAREWRGR/IR account clearing value in local currency
EKBESHKZGDebit/Credit Indicator
EKBEREEWRInvoice Value Entered (in Local Currency)
EKBEREFWRInvoice Value in Foreign Currency
EKBEMWSKZTax on sales/purchases code
EKBEAREWWClearing value on GR/IR clearing account (transac. currency)

Note: PO Value in EKPO table, is calculated using below formula.

NETWR = NETPR / PEINH X MENGE   [i.e. PO Line Value = PO Price / Pirce per X PO Price]

Data Extraction for Substative Testing of 3 Way Match

We have 2 options (TCodes) for extracting data 1st – SE16N 2nd – SQVI

The best option is to use SQVI, as it allows for one data extraction. If you don’t have access to SQVI, then use SE16N to extract one table and use this data as an input for another table. If data volume is more than a 50 thousand rows then SE16N may give ABAP error. Using SE16N we can only filter and extract data from one table. Using SQVI, we can filter based on one table and extract data of another table (of course as long as there is a link between these 2 tables).

Using SQVI

We need to extract 3 different data.

  1. EKKO (PO header) – Filter using Date, Company Code fields from EKKO table itself
  2. EKPO (PO Line) – Link EKKO and then filter Date, Company Code fields from EKKO
  3. EKBE (PO History) – Link EKPO, then Link EKKO and filter Date, Company Code fields from EKKO.

Analysis for Substative Testing of 3 Way Match

1. Invoice without GRN

In default SAP installation, a key field named “GR-Bsd IV” i.e. “Goods Receipt based Invoice” is editable at the time of PO by users. This is a cause for concern for auditors because this will enable users to create an invoice without GRN. The best case scenario for this configuration to be “Display only”, i.e. users should not be able to edit this field in Purchase Order. If the field is editable, we need to extract data and check whether any users have left it unchecked in any PO. The field is in EKPO table, Field Name – WEBRE.

Practically, we might find that this field is not ticked for some of the scenarios listed below

  1. Service PO. Check EKPO-PSTYP (Item category in purchasing document) to exclude “Service” Category.
  2. Import PO. Check EKKO-WAERS (Currency Key) to exclude Local Currency, or check EKKO-BSART (Purchasing Document Type) to exclude the PO document type used for Import PO.
  3. PO from Affiliate vendors. Check EKKO-LIFNR (Vendor’s account number) to Exclude 3rd party affiliated vendors or check EKKO-LLIEF (Supplying Vendor) to exclude non-3rd party affiliated vendors.
  4. Bulk low-value materials. Check EKPO-MATKL (Material Group) to exclude such type of materials.

2.Tolerance for GRN

Tolerance for GRN quantity is copied to PO from PO Configuration or from Material Master (if defined). At the time of PO, tolerance fields may be editable. If users change the tolerance fields in PO, then GRN can be created against that PO up to that tolerance. In best case scenario, the tolerance fields should not be editable in PO. If it is editable, then we need to extract data and check whether users have modified the tolerances. For the setting of tolerance in PO, we have 2 fields – UEBTO (Overdelivery Tolerance Limit ), UEBTK (Indicator: Unlimited Overdelivery Allowed). If UEBTK is ticked, then users can create GRN for any quantity.

In some cases, we might find that “Unlimited Overdelivery Allowed” indicator may be ticked.

  1. Service PO. Check EKPO-PSTYP (Item category in purchasing document) to exclude “Service” Category.
  2. PO from Affiliate vendors. Check EKKO-LIFNR (Vendor’s account number) to Exclude 3rd party affiliated vendors or check EKKO-LLIEF (Supplying Vendor) to exclude non-3rd party affiliated vendors.

3.Tolerance for Invoice Price

Tolerance for invoice creation is dependent upon Receiving Invoice Configuration. In best case scenario, a system should not allow the price to be edited at the time of Invoice Booking (i.e. at the time of MIRO). If the system (SAP ERP) allows prices to be edited, then if the price entered is higher than PO, then invoice should not be allowed to be posted.

To sum up, we will test it substantively in following situations. 1st – Tolerance is not set. 2nd -Tolerance is set but Invoice is allowed to be posted for higher price/quantity. For testing this, we need to compare – PO Price vis a vis Invoice price

To calculate PO Price – NETPR / PEINH (i.e. Price divided by Per Unit) from EKPO table for each individual PO Line Item. This value is in PO Currency. Hence we need to compare Value in Foreign Currency only.

To calculate Invoice Price – REFWR / MENGE  (i.e. Invoice Value in Foreign Currency divided by Invoice Quantity) from EKBE Table. This value will be in Foreign Currency. Hence will not be affected by Currency Fluctuations. Filter for only Invoice Transactions by using a filter on VGABE (Event Type) as 2 (Invoice). There can be multiple Invoice against the same PO, hence when comparing using Invoice price table as your base to compare PO price. In short, in EKBE table, use Vlookup to get values from EKPO table.

3.Tolerance for Invoice Quantity

Tolerance for invoice creation is dependent upon Receiving Invoice Configuration. If the quantity entered is higher than PO, then invoice should not be allowed to be posted. If the system allows Invoice quantity to be higher than GRN quantity then we need to test the control substantively. Thus we need to find cases where Invoice quantity is more than GRN quantity. For testing this, we need to compare – GRN Quantity vis a vis Invoice Quantity.

To calculate GRN Qty – From EKBE table calculate a sum of all MENGE (Quantity) based on BELNR (Material Document) per PO Number and PO Line Number. Filter on VGABE (Event Type) as 1 (GRN).

To calculate Invoice Qty – From EKBE table calculate a sum of all MENGE (Quantity) based on BELNR (Material Document) per PO Number and PO Line Number. Filter on VGABE (Event Type) as 2 (Invoice)

We cannot use MENGE directly to the sum as there can be cases of GRN being cancelled (i.e reversed). Hence we need to recalculate this filed taking into account Debit and Credit in Goods Movement. The same holds true for Invoice too. Thus before SUM, recalculation is based on SHKZG Field (Dr/Cr Indicator). If SHKZG value is “S” then New MENGE value is positive, if SHKZG is “H”, then new MENGE value is Negative.

Also, care needs to be taken to sum based on PO Number and PO Line number and NOT on Material Doc Line Number.

The best way to analyse this is to use Pivot Tables in Excel. Download Sample Excel file from here or Here

The primary key is “PO Number” and “PO Line Number”. Hence First we need to drag PO Doc number and PO Line Number into Row. Then To get the sum of quantity based on Material Document drag “Material Doc Num” to Row. We need to compare Invoice and GRN quantity, hence Drag “Event Type” to Columns. Finally to get sum of quantity, drag “calculated Qty” to Values.

About The Author

CA, ISA, CISA, BCAF. Friends call me Techno Savvy Chartered Accountant. I work at EY in System Audit

7 Comments

  1. I wish i could write that great of a post. Thanks for sharing your knowledge

  2. Companies, non-profit and government agencies make use of requisition forms as a type of internal business document. They use it to notify of the ordering of products from an internal department or outside supplier. This form generally states the number of products required, the time frame, authorization for the item(s) and what should be done in case the items are not currently available.

  3. Great post. I’m going to repost it for my followers.

  4. Awesome post, In-depth coverage.

  5. Great article.

  6. Amazing article on sap substantive analytical procedures. Love it. Thanks for your in-depth knowledge sharing.

  7. I really love to read this post and I am glad to find your distinguished way of writing the post. Thanks and Regards

Subscribe To my Newsletter

Subscribe To my Newsletter

Join the mailing list to receive the latest news and updates from the blog

You have Successfully Subscribed!