How to generate a list of PIRs active on a specific date

Jimbo's picture

https://i.ytimg.com/vi/qyMpVtdtzO4/hqdefault.jpg|https://c2.staticflickr.com/4/3190/2401684674_3698f03911.jpg|http://s621.photobucket.com/user/billygoat_100/media/IMG_1197.jpg|https://i.ytimg.com/vi/cSi1zaN7x9I/hqdefault.jpg|http://i3.ytimg.com/vi/9ixckCSoN9M/mqdefault.jpg|http://www.jcolquhoun-cd.com/images/goat-fg.jpgFiscal year-end closing can be a very stressful time for any firm's accounting department. Having access to data in its most useful form can mean the difference between successfully performing procedures vital to the success and organization of a company and frustrating missed timelines.

The ME1M transaction provides a list of Purchase Info Records with some available filters, but none of those are based on start, end or "active on" date. The user must drill down into the PIR to see the validity period to determine if the PIR is active on a specific date.
Info record with validity date

Making this even more difficult is the fact that the join table A017 cannot be used as a join in SQ00. A017 is the table that holds the information for plant-level PIR data and A018 is the table that holds the non-plant-level PIR data.
Table A017 cannot be used in a join.

Ultimately, the solution is a custom ABAP report. With a little knowledge of the tables that hold the data in SAP, the report comes together quite simply. Start by making a new LSMW object of Standard Batch/Direct Input type. The Object and Method don't matter as this LSMW will be used for reporting. Alternatively, the code below can be used in a traditional SE38 program, but transporting is so much easier in LSMW.

In Global Data Definitions it is necessary to define some work areas with the same structure as the tables that hold the PIR data. Additionally, a parameter to hold the specific date on which the PIRs to be listed are active is included.

data: lEINE like EINE, lEINA like EINA, lA017 like A017,
      lA018 like A018, lKONP like KONP, lKONH like KONH.
parameters: pDate like A017-DATBI.

The rest is simply looping through the tables and checking the Start and End validity against the date parameter. The first three lines simply write a header that looks fine when the data is imported to Excel and "Text to Columns" is used to spread the data over the cells. This code is added to the Begin of Processing area.

write: / 'KSCHL', 'LIFNR', 'MATNR', 'EKORG', 'WERKS', 'DATAB', 'DATBI',
         'DATAB', 'ATBI', 'KOPOS', 'KBETR', 'KONWA', 'KPEIN', 'KMEIN',
         'INFNR'.
select * from A017 into lA017
 where DATBI ge pDate and DATAB le pDate.
  select * from KONP into lKONP
   where KNUMH eq lA017-KNUMH and KSCHL eq lA017-KSCHL
   order by KOPOS.
    select * from KONH into lKONH where KNUMH eq lA017-KNUMH
     and KAPPL eq lKONP-KAPPL and KSCHL eq lKONP-KSCHL.

      write: / lA017-KSCHL, lA017-LIFNR, lA017-MATNR, lA017-EKORG,
       lA017-WERKS, lA017-DATAB, lA017-DATBI.
      write: lKONH-DATAB, lKONH-DATBI.
      write: lKONP-KOPOS, lKONP-KBETR, lKONP-KONWA, lKONP-KPEIN,
             lKONP-KMEIN.
      select single EINE~INFNR into lEINE-INFNR
         from EINE join EINA on ( EINE~INFNR eq EINA~INFNR )
        where EINE~EKORG eq lA017-EKORG
          and EINE~WERKS eq lA017-WERKS
          and EINA~MATNR eq lA017-MATNR
          and EINA~LIFNR eq lA017-LIFNR.
      if sy-subrc eq 0.
        write: lEINE-INFNR.
      endif.
    endselect.  "KONP
  endselect.  "KONH
endselect.  "A017
select * from A018 into lA018
 where DATBI ge pDate and DATAB le pDate.
  select * from KONP into lKONP
   where KNUMH eq lA018-KNUMH and KSCHL eq lA018-KSCHL
   order by KOPOS.
    select * from KONH into lKONH where KNUMH eq lA018-KNUMH
     and KAPPL eq lKONP-KAPPL and KSCHL eq lKONP-KSCHL.

      write: / lA018-KSCHL, lA018-LIFNR, lA018-MATNR, lA018-EKORG,
       '    ', lA018-DATAB, lA018-DATBI.
      write: lKONH-DATAB, lKONH-DATBI.
      write: lKONP-KOPOS, lKONP-KBETR, lKONP-KONWA, lKONP-KPEIN,
             lKONP-KMEIN.
      select single EINE~INFNR into lEINE-INFNR
         from EINE join EINA on ( EINE~INFNR eq EINA~INFNR )
        where EINE~EKORG eq lA018-EKORG

          and EINA~MATNR eq lA018-MATNR
          and EINA~LIFNR eq lA018-LIFNR.
      if sy-subrc eq 0.
        write: lEINE-INFNR.
      endif.
    endselect.  "KONP
  endselect.  "KONH
endselect.  "A018

A skip_transaction added to the Begin of Transaction area ensures that nothing is processed after the report is written out. The last step is to load some dummy data so that LSMW will run.

Just make it more confusing.