Magical Function (2) of SAP Query

[Former Member]

Query Report of PO Payment Status

Several days ago I read one thread "Re: PO wise venodr payment status" in SCN forum, let me remind that long time ago my SAP users asked me the same question --"How to get the payment status of purchase orders in SAP system?" as well. Somebodies suggested to use standard report ME80RN, but it only shows whether invoice verification of PO was done, or not? Not payment status! Because base on SAP standard process, after invoice verification is completed, the purchasing process will be regarded as closed in MM module, although the PO is really closed after FI account payable cleared. Till now I don't find any standard report to display PO payment status. But as far as we know there are very perfect function of document management in SAP system, in the tables we must have found the data records of relationship status between PO, IV and AP clearing, so I decide to work it out by self-development.

After search tables, I found several key tables for my case, they are:
EKKO: Purchasing document header
EKPO: Purchasing document item
RSEG: Document item: Incoming invoice
BKPF: Accounting Document Header
BSAK: Accounting: Secondary Index for Vendors (Cleared Items)
From table EKKO and EKPO, we can find the PO number; if PO did invoice verification, then we can find the invoice document number with table RSEG and accounting document number of IV with table BKPF as well; at last if the AP is cleared, we will find the clearing account document number in table BSAK. With these tables, we can easily generate the report of PO payment status.

Now let's generate the query report:
 

1. In the SAP Query InfoSet create the extras as follows:
   
    
   
2. Join these tables as follows:
   
   
   
3. Because table BKPF can't be used in SAP query directly, here we should use the embedded ABAP code again in SAP Query InfoSet.
   * Concatenate IV number and fiscal year into the reference key from table RSEG;
   
   
   
   * Using reference key to get the accounting document number of IV;
   
   
   
   * Check whether the AP is cleared, or not?
   
   
   
4. Get the report of PO payment status.
   
   
   

Hope it can help you! Good day!