无票应付报表,其实挺简单的逻辑,但却调试了几天才对上数,也许是测试阶段数据太乱的原因吧,现在主要代码贴在这里,备忘,同时也给有同样需求的人一个参考。
一、一般采购
- 取数
SELECT ekbe~gjahr ekbe~ebeln ekbe~ebelp ekbe~budat ekbe~menge ekbe~dmbtr ekbe~matnr
ekbe~belnr ekbe~buzei ekbe~bwart ekbe~gjahr ekbe~lfbnr ekbe~lfpos
ekko~lifnr ekbe~wesbs
INTO CORRESPONDING FIELDS OF TABLE it_result
FROM ekbe
INNER JOIN ekko ON ekko~ebeln = ekbe~ebeln
WHERE ekbe~budat <= v_date_e
AND ekbe~bwart IN ('101','103')
AND ekbe~bewtp = 'E'
AND ekbe~werks IN p_bukrs
AND ekko~lifnr IN p_lifnr .
" 添加先做发票效验后收货的情况
SELECT ekbe~gjahr ekbe~ebeln ekbe~ebelp ekbe~budat ekbe~menge ekbe~dmbtr ekbe~matnr
ekbe~belnr ekbe~buzei ekbe~bwart ekbe~gjahr ekbe~lfbnr ekbe~lfpos
ekko~lifnr ekbe~wesbs
APPENDING CORRESPONDING FIELDS OF TABLE it_result
FROM ekbe
INNER JOIN ekko ON ekko~ebeln = ekbe~ebeln
WHERE ekbe~budat > v_date_e
AND ekbe~bwart IN ('101','103')
AND ekbe~bewtp = 'E'
AND ekbe~werks IN p_bukrs
AND ekko~lifnr IN p_lifnr
AND EXISTS ( SELECT belnr FROM ekbe WHERE lfbnr = ekbe~belnr
AND lfpos = ekbe~buzei
AND budat <= v_date_e ).
- 循环整理数据
" 只要正常PO的记录
SELECT SINGLE pstyp loekz netwr INTO (v_pstyp,v_loekz,v_netwr)
FROM ekpo
WHERE ebeln = <wa>-ebeln
AND ebelp = <wa>-ebelp.
IF v_pstyp <> '0' OR v_loekz IS NOT INITIAL OR v_netwr EQ 0.
DELETE it_result.
CONTINUE.
ENDIF.
READ TABLE it_ekpo WITH KEY ebeln = <wa>-ebeln ebelp = <wa>-ebelp INTO wa_ekpo.
IF sy-subrc EQ 0. " 已经处理过
DELETE it_result.
CONTINUE.
ENDIF.
SELECT SINGLE SUM( menge ) SUM( dmbtr ) INTO (v_menge_s,v_dmbtr_s)
FROM ekbe
WHERE ebeln = <wa>-ebeln
AND ebelp = <wa>-ebelp
AND shkzg = 'S'
AND bewtp = 'E'
AND budat <= v_date_e .
SELECT SINGLE SUM( menge ) SUM( dmbtr ) INTO (v_menge_h,v_dmbtr_h)
FROM ekbe
WHERE ebeln = <wa>-ebeln
AND ebelp = <wa>-ebelp
AND shkzg = 'H'
AND bewtp = 'E'
AND budat <= v_date_e.
SELECT SINGLE SUM( menge ) SUM( dmbtr ) INTO (v_menge_s_q,v_dmbtr_s_q)
FROM ekbe
WHERE ebeln = <wa>-ebeln
AND ebelp = <wa>-ebelp
AND shkzg = 'S'
AND bewtp = 'Q'
AND budat <= v_date_e.
SELECT SINGLE SUM( menge ) SUM( dmbtr ) INTO (v_menge_h_q,v_dmbtr_h_q)
FROM ekbe
WHERE ebeln = <wa>-ebeln
AND ebelp = <wa>-ebelp
AND shkzg = 'H'
AND bewtp = 'Q'
AND budat <= v_date_e.
" 暂存PO号和行号
CLEAR wa_ekpo.
wa_ekpo-ebeln = <wa>-ebeln.
wa_ekpo-ebelp = <wa>-ebelp.
APPEND wa_ekpo TO it_ekpo .
v_menge = v_menge_s - v_menge_h - ( v_menge_s_q - v_menge_h_q ).
IF v_menge EQ 0.
DELETE it_result.
CONTINUE.
ELSE.
<wa>-menge = v_menge.
<wa>-dmbtr = v_dmbtr_s - v_dmbtr_h - ( v_dmbtr_s_q - v_dmbtr_h_q ).
ENDIF.
二、寄售采购
- 选取供应商列表
SELECT DISTINCT lifnr matnr
INTO CORRESPONDING FIELDS OF TABLE it_result
FROM rkwa
WHERE budat <= v_date_e
AND hkont = '0021211020'
AND werks IN p_bukrs
AND lifnr IN p_lifnr
AND sobkz = 'K' .
- 再循环计算供应商的寄售数据
SELECT SINGLE SUM( rkwa~bstmg ) SUM( rkwa~wrbtr ) INTO (v_menge_s,v_dmbtr_s)
FROM rkwa
WHERE rkwa~lifnr = <wa>-lifnr
AND rkwa~matnr = <wa>-matnr
AND rkwa~shkzg = 'S'
AND rkwa~sobkz = 'K'
AND rkwa~hkont = '0021211020'
AND rkwa~budat <= v_date_e
AND rkwa~bukrs IN p_bukrs
.
SELECT SINGLE SUM( rkwa~bstmg ) SUM( rkwa~wrbtr ) INTO (v_menge_h,v_dmbtr_h)
FROM rkwa
WHERE rkwa~lifnr = <wa>-lifnr
AND rkwa~matnr = <wa>-matnr
AND rkwa~shkzg = 'H'
AND rkwa~sobkz = 'K'
AND rkwa~hkont = '0021211020'
AND rkwa~budat <= v_date_e
AND rkwa~bukrs IN p_bukrs
.
SELECT SINGLE SUM( rkwa~bstmg ) SUM( rkwa~wrbtr ) INTO (v_menge_s_01,v_dmbtr_s_01)
FROM rkwa
INNER JOIN bkpf ON bkpf~belnr = rkwa~belnr
AND bkpf~blart = 'RE'
AND bkpf~gjahr = rkwa~gjahr
AND bkpf~bukrs = rkwa~werks
AND bkpf~gjahr = p_gjahr
WHERE rkwa~lifnr = <wa>-lifnr
AND rkwa~matnr = <wa>-matnr
AND rkwa~shkzg = 'S'
AND rkwa~sobkz = 'K'
AND rkwa~hkont = '0021211020'
AND rkwa~bukrs IN p_bukrs
AND bkpf~bukrs IN p_bukrs
AND bkpf~monat <= p_monat
AND rkwa~belnr <> ''
AND rkwa~status = '01'.
SELECT SINGLE SUM( rkwa~bstmg ) SUM( rkwa~wrbtr ) INTO (v_menge_h_01,v_dmbtr_h_01)
FROM rkwa
INNER JOIN bkpf ON bkpf~belnr = rkwa~belnr
AND bkpf~blart = 'RE'
AND bkpf~gjahr = rkwa~gjahr
AND bkpf~bukrs = rkwa~werks
AND bkpf~gjahr = p_gjahr
WHERE rkwa~lifnr = <wa>-lifnr
AND rkwa~matnr = <wa>-matnr
AND rkwa~shkzg = 'H'
AND rkwa~sobkz = 'K'
AND rkwa~hkont = '0021211020'
AND rkwa~bukrs IN p_bukrs
AND bkpf~bukrs IN p_bukrs
AND bkpf~monat <= p_monat
AND rkwa~belnr <> ''
AND rkwa~status = '01'.
<wa>-menge = v_menge_h - v_menge_s -
( ABS( v_menge_h_01 ) - ABS( v_menge_s_01 ) )
.
<wa>-dmbtr = v_dmbtr_h - v_dmbtr_s -
( ABS( v_dmbtr_h_01 ) - ABS( v_dmbtr_s_01 ) )
.
IF <wa>-menge EQ 0.
DELETE it_result.
CONTINUE.
ENDIF.
以上代码,ECC6下测试通过。虽然不是完整代码,相信有点ABAP经验的,一眼就能看出所以然吧。