Produce a reconciliation report for material movement including financials

Jimbo's picture
Submitted by Jimbo on Sat, 11/05/2011 - 14:35

Reconciling inventory loads can be a daunting task involving complex table joins, custom queries and massive Excel spreadsheets. Accounting professionals will seldom turn down enriched data that saves them hours of mind-numbing number crunching and this snippet generates a report with almost all the information that they need.

When material is moved (incoming inventory created), SAP creates a financial document with the information from the move. The accountants want the information in the new production system to match the information in the legacy system not just for business reasons involving inventory, but for financial reasons. Auditors routinely check books and inventory that appears from nowhere or disappears to nowhere can cause a lot of unwanted problems because it represents so much of a company's market value.

This snippet not only links the SAP material movement document number to the record in the source file based on a 10-character discreet key, but also shows the standard price, the quantity and the value that SAP assigned to the inventory. The source data in an Excel spreadsheet enriched by the data produced with this snippet might very well be all the accountants need to reconcile and allow the project team to move on to the next task.

This snippet requires a handful of variables and the best place to define them is in the GLOBAL_DATA section under the Change Field Mapping and Conversion Rules section of the LSMW object. The parameters are a flag that triggers the creation of the report and a 5-character field that holds the first 5 characters of the discreet keys used to track the inventory. 

selection-screen begin of block breport with frame title breport.
  selection-screen begin of line.
    PARAMETERS: p_Reconc as CHECKBOX default ' '.
    selection-screen comment 3(60) comm14.
    PARAMETERS: p_Prefix(5) type c.
    selection-screen comment 70(50) comm15.
  selection-screen end of line.
selection-screen end of block breport.
initialization. 
breport = 'Reporting tools'.
comm14 = 'Produce reconciliation report with all document numbers.*'.
comm15 = '<-- *Provide prefix (first 5 characters).'.

data: lMKPF like MKPF, lMSEG like MSEG, lMBEW like MBEW.
data: wa_MKPF like MKPF.

In the BEGIN_OF_PROCESSING section add this code. This will load a subsection of the material movement document header table (MKPF) into memory and drastically improve the speed of the LSMW object. This article better explains why this is so effective. Additionally, the system will comb through all of the loaded material movement documents and provide a report with all of the document numbers, standard prices, quantities and the assigned value in the system. Note: The system will list no documents when the p_Prefix field is left blank or populated with a value that matches none of the documents created in the system. 

if p_Reconc eq 'X'.  " Produce reconciliation report.
  write: / 'BKTXT      MB11       STPRS          ',
    'MENGE            ', 'DMBTR          '.

  select * from MKPF into wa_MKPF where BKTXT ne '' order by BKTXT.
    if wa_MKPF-BKTXT+0(5) eq p_Prefix.  "Found one!
      write: / wa_MKPF-BKTXT+0(10), wa_MKPF-MBLNR color 1 inverse.
      select * from MSEG into lMSEG where MBLNR eq wa_MKPF-MBLNR.
        select * from MBEW into lMBEW
         where MATNR eq lMSEG-MATNR and BWKEY eq lMSEG-WERKS.
          write: lMBEW-STPRS, lMSEG-ERFMG, lMSEG-DMBTR.
        endselect.
      endselect.
    endif.
  endselect.
endif.
Programming Language: 
ABAP