SAP Comments and Good Sql Example using Case
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
Leave a Reply
You must be logged in to post a comment.