Open AP Invoices (Header)
SELECT DISTINCT
APMIHP.SPID,
APMIHP.POID AS POID_1,
APMIHP.INVID AS INVID_1,
SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 7, 2 ) || ‘/’ || SUBSTRING( CAST(
APMIHP.IHINVD AS CHAR( 8 ) ), 5, 2 ) || ‘/’ || SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ),
1, 4 ) AS COLUMN0000,
APMIHP.ATID,
CASE
WHEN ( APMIHP.CMPNBR = ‘301’ AND APMIHP.CURCDE <> ‘USD’ )
THEN SUM ( DGDAMC ) * – 1
ELSE 0
END AS COLUMN0003,
CASE
WHEN ( APMIHP.CMPNBR = ‘301’ AND APMIHP.CURCDE <> ‘USD’ )
THEN APMIHP.CURCDE
ELSE ‘ ‘
END AS COLUMN0004,
CASE
WHEN ( APMIHP.CMPNBR = ‘301’ AND APMIHP.CURCDE = ‘USD’ )
THEN SUM ( DGDAMC ) * – 1
ELSE 0
END AS DGDAMC_1,
CASE
WHEN ( APMIHP.IHHOLD = ‘1’ ) THEN APMIHP.IHHOLD
ELSE ‘ ‘
END AS IHHOLD,
‘ ‘ AS COLUMN0005,
‘ ‘ AS COLUMN0006,
CASE
WHEN ( APMIHP.AYID = ‘CHECK’ ) THEN ‘C’
WHEN ( APMIHP.AYID = ‘ACH’ ) THEN ‘U’
WHEN ( APMIHP.AYID = ‘WIRE’ ) THEN ‘1’
ELSE APMIHP.AYID
END AS AYID,
APMIHP.CMPNBR
FROM
CHICAGO.EPDB.APMIHP APMIHP,
CHICAGO.EPDB.APMDGP APMDGP
WHERE
APMIHP.CMPNBR = APMDGP.CMPNBR
AND APMIHP.PFTCTR = APMDGP.PFTCTR
AND APMIHP.SPID = APMDGP.SPID
AND APMIHP.INVID = APMDGP.INVID
AND APMIHP.AYID = APMDGP.AYID
AND ( ( APMIHP.IHSTAT = ‘1’)
AND ( APMIHP.IHCAMT APMIHP.IHPAMT <> 0)
AND ( APMIHP.CMPNBR = 301)
AND ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’)
AND ( APMIHP.IHINVD > 20110901)))
AND ( APMDGP.GLACCT = 10100))
Open AP Invoices (Comments)
SELECT
APMIHP.SPID,
APMIHP.POID AS POID_1,
APMIHP.INVID AS INVID_1,
SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 7, 2 ) || ‘/’ ||
SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 5, 2 ) || ‘/’ ||
SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 1, 4 ) AS COLUMN0000,
AYFCKCM.CHECK_COMMENT,
APMIHP.CMPNBR
FROM
CHICAGO.EPDB.APMIHP APMIHP,
CHICAGO.EPDB.AYFCKCM AYFCKCM
WHERE
APMIHP.CMPNBR = AYFCKCM.CMPNBR
AND APMIHP.SPID = AYFCKCM.SPID
AND APMIHP.PFTCTR = AYFCKCM.PFTCTR
AND APMIHP.INVID = AYFCKCM.INVID
AND ( ( APMIHP.IHSTAT = ‘1’)
AND ( APMIHP.IHCAMT APMIHP.IHPAMT <> 0)
AND ( APMIHP.CMPNBR = 301)
AND ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’)
AND ( IHINVD > 20110901))))
Note:
SPID is the current AS400 vendor ID
ATID is the AS400 payment term ID this should be converted to SAP payment term ID
The IHINVD is the invoice date select anything from the current run date
The clause (APMIHP.IHCAMT APMIHP.IHPAMT <> 0) stands for Open item with pending $
The clause ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’) means we exclude all the future recurring invoices