Report

[Former Member]

Hi All,

I want to pick the data according the Billing Date, but it comes whole data....

plz check my code.....

***************************************************************************************

TABLES : VBAK , VBRP, VBRK, KNA1, T001, LIKP, VBPA, TVKBT, TVGRT, KONV, VBKD, VBFA, T052, BSAD.

TYPE-POOLS: SLIS.

DATA: BEGIN OF ITAB OCCURS 0,

VKBUR LIKE VBAK-VKBUR , "Sales Office

KUNNR LIKE VBAK-KUNNR , "Sold to party

NAME1 LIKE KNA1-NAME1 , "CUSTOMER NAME

VBELN LIKE VBAK-VBELN , "Sale Order Number

AUDAT LIKE VBAK-AUDAT , "SALES DOC. DATE

NETWR LIKE VBAK-NETWR , "SALES DOC VALUE

VBELN1 LIKE VBRP-VBELN , "BILLING DOCUMENT NO.

FKDAT LIKE VBRK-FKDAT , "BILLING DATE

NETWR1 LIKE VBRK-NETWR , "BILLING DOCUMENT VALUE

MWSBK LIKE VBRK-MWSBK , "TAX AMOUNT

CD_AMT TYPE P DECIMALS 2, "CD AMOUNT

AMT_DUE TYPE P DECIMALS 2 , "AMOUNT DUE

FKART LIKE VBRK-FKART , "BILLING TYPE

BLART LIKE BSID-BLART , "DOC TYPE

DAYS LIKE T052-ZTAG1 , "Number of days

DUE_DT LIKE SY-DATUM , "Due Date

BSTNK LIKE VBAK-BSTNK , "Customer PO No.

ZTERM LIKE VBKD-ZTERM , "PAYMENT TERM

KNUMV LIKE VBRK-KNUMV , "VBRK DOCUMENT CONDITION

BELNR LIKE BSAD-BELNR , " DOC. NO.

AUGBL LIKE BSAD-AUGBL , " CLEARING DOC.

END OF ITAB .

DATA: BEGIN OF ITAB1 OCCURS 0,

KNUMV LIKE KONV-KNUMV , "DOCUMENT CONDITION

KSCHL LIKE KONV-KSCHL ,

KAWRT LIKE KONV-KAWRT ,

KBETR LIKE KONV-KBETR ,

KWERT LIKE KONV-KWERT , "Condition Value

END OF ITAB1 .

DATA : WA1 LIKE ITAB1.

DATA: BEGIN OF ITAB2 OCCURS 0,

KNUMV LIKE KONV-KNUMV , "DOCUMENT CONDITION

KSCHL LIKE KONV-KSCHL ,

KAWRT LIKE KONV-KAWRT ,

KBETR LIKE KONV-KBETR ,

KWERT LIKE KONV-KWERT , "Condition Value

END OF ITAB2 .

*DATA : WA2 LIKE ITAB2.

DATA : X TYPE I.

**************************************************************************

DATA : W_CONTAINER TYPE SCRFNAME VALUE 'CL_GRID',

W_CPROG TYPE LVC_S_LAYO,

G_REPID LIKE SY-REPID,

W_SAVE TYPE C,

W_EXIT TYPE C,

CL_GRID TYPE REF TO CL_GUI_ALV_GRID,

CL_CUSTOM_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER,

IT_FLD_CATALOG TYPE SLIS_T_FIELDCAT_ALV,

WA_FLD_CATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE ,

LAYOUT TYPE SLIS_LAYOUT_ALV,

COL_POS LIKE SY-CUCOL ,

ALVFC TYPE SLIS_T_FIELDCAT_ALV,

IT_SORT TYPE SLIS_T_SORTINFO_ALV.

***----

---

SELECTION SCREEN -

---

SELECTION-SCREEN BEGIN OF BLOCK A WITH FRAME.

SELECT-OPTIONS: VKBUR FOR VBAK-VKBUR, " Sales Office

KUNNR FOR VBAK-KUNNR, " Sold to party

FKDAT FOR VBRK-FKDAT, " OBLIGATORY . " Created on

FKART FOR VBRK-FKART. " Billing Type

SELECTION-SCREEN END OF BLOCK A.

***************************************************************************

PERFORM FILL_CATALOG1 USING:

'VKBUR' 'ITAB' 'SALES OFFICE',

'KUNNR' 'ITAB' 'CUST CODE',

'NAME1' 'ITAB' 'NAME' ,

'VBELN' 'ITAB' 'SALES DOC NO.',

'AUDAT' 'ITAB' 'SALES DOC DATE',

'NETWR' 'ITAB' 'NET VALUE' ,

'KNUMV' 'ITAB' 'DOC. CONDITION NO.',

'VBELN1' 'ITAB' 'BILLING DOC NO.' ,

'FKDAT' 'ITAB' 'BILLING DATE',

'NETWR1' 'ITAB' 'NET VALUE' ,

'MWSBK' 'ITAB' 'TAX AMOUNT' ,

'CD_AMT' 'ITAB' 'CD AMOUNT' ,

'AMT_DUE' 'ITAB' 'AMOUNT DUE' ,

'FKART' 'ITAB' 'BILL TYPE' ,

'BLART' 'ITAB' 'DOC TYPE' ,

'AUGBL' 'ITAB' 'CLEARING DOC.' ,

'DUE_DT' 'ITAB' 'DUE DATE',

'BSTNK' 'ITAB' 'CUST PO NO.' ,

'ZTERM' 'ITAB' 'PAYMENT TERMS'.

********************************************************************************

SELECT DISTINCT A_{VKBUR A}KUNNR A_{VBELN A}AUDAT A_{NETWR A}BSTNK B~VBELN

INTO (ITAB-VKBUR, ITAB-KUNNR, ITAB-VBELN, ITAB-AUDAT, ITAB-NETWR, ITAB-BSTNK, ITAB-VBELN1)

FROM VBAK AS A INNER JOIN VBRP AS B ON A_{VBELN = B}AUBEL

WHERE A~VKBUR IN VKBUR

AND A~KUNNR IN KUNNR.

APPEND ITAB .

ENDSELECT.

SORT ITAB BY VBELN.

LOOP AT ITAB .

SELECT FKDAT NETWR MWSBK KNUMV FKART FROM VBRK INTO (ITAB-FKDAT, ITAB-NETWR1, ITAB-MWSBK, ITAB-KNUMV, ITAB-FKART)

WHERE VBELN = ITAB-VBELN1

AND FKDAT IN FKDAT.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING FKDAT NETWR1 MWSBK KNUMV FKART.

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING VBELN .

ENDSELECT .

SELECT NAME1 FROM KNA1 INTO ITAB-NAME1 WHERE KUNNR = ITAB-KUNNR .

MODIFY ITAB INDEX SY-TABIX TRANSPORTING NAME1 .

ENDSELECT .

**********************************************************************************

SELECT AUGBL BLART FROM BSAD INTO (ITAB-AUGBL, ITAB-BLART) WHERE BELNR = ITAB-VBELN1.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING AUGBL BLART.

ENDSELECT .

*****************************************************************************************

SELECT ZTERM FROM VBKD INTO ITAB-ZTERM WHERE VBELN = ITAB-VBELN .

MODIFY ITAB INDEX SY-TABIX TRANSPORTING ZTERM .

ENDSELECT.

SELECT KNUMV KAWRT KBETR KSCHL INTO CORRESPONDING FIELDS OF TABLE ITAB1 FROM KONV

• FOR ALL ENTRIES IN ITAB

WHERE KNUMV = ITAB-KNUMV AND KSCHL IN ('SKTO') .

X = 0.

LOOP AT ITAB1 INTO WA1.

ON CHANGE OF WA1-KNUMV.

ITAB2[] = ITAB1[].

DELETE ITAB2 WHERE KNUMV NE WA1-KNUMV.

ENDON.

ENDLOOP.

LOOP AT ITAB2.

X = X + ITAB2-KAWRT * ITAB2-KBETR / 1000 .

ENDLOOP .

ITAB-CD_AMT = X.

MODIFY ITAB.

REFRESH ITAB1.

REFRESH ITAB2.

ENDLOOP .

*******************************************************************************

LOOP AT ITAB .

ITAB-AMT_DUE = ITAB-NETWR1 + ITAB-MWSBK + ITAB-CD_AMT .

MODIFY ITAB INDEX SY-TABIX TRANSPORTING AMT_DUE .

ENDLOOP .

*******************************************************************************

LOOP AT ITAB.

SELECT SINGLE ZTAG1 INTO ITAB-DAYS FROM T052 WHERE ZTERM = ITAB-ZTERM.

ITAB-DUE_DT = ITAB-AUDAT + ITAB-DAYS.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING DUE_DT .

ENDLOOP.

*******************************************************************************

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = 'ZSDREPORT_TEST'

IS_LAYOUT = LAYOUT

IT_FIELDCAT = IT_FLD_CATALOG

IT_SORT = IT_SORT

I_DEFAULT = 'X'

I_SAVE = 'A'

TABLES

T_OUTTAB = ITAB

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

*ENDFORM.

FORM FILL_CATALOG1 USING P_FIELDNAME TYPE ANY

P_REF_TABLE TYPE ANY

P_SCRTEXT TYPE ANY.

CLEAR : WA_FLD_CATALOG.

WA_FLD_CATALOG-FIELDNAME = P_FIELDNAME.

WA_FLD_CATALOG-TABNAME = P_REF_TABLE.

WA_FLD_CATALOG-SELTEXT_S = P_SCRTEXT.

WA_FLD_CATALOG-SELTEXT_M = P_SCRTEXT.

WA_FLD_CATALOG-SELTEXT_L = P_SCRTEXT.

APPEND WA_FLD_CATALOG TO IT_FLD_CATALOG.

ENDFORM. " fill_catalog1

****************************************************************************************

plz check it and reply soon......<b>Data should come according to Billing Date</b>.....

Thanks,

<b>Anil Kumar</b>

[former_member195698]

LOOP AT ITAB .

<b>SELECT FKDAT NETWR MWSBK KNUMV FKART FROM VBRK INTO (ITAB-FKDAT, ITAB-NETWR1, ITAB-MWSBK, ITAB-KNUMV, ITAB-FKART)

WHERE VBELN = ITAB-VBELN1

AND FKDAT IN FKDAT.</b>

MODIFY ITAB INDEX SY-TABIX TRANSPORTING FKDAT NETWR1 MWSBK KNUMV FKART.

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING VBELN .

ENDSELECT .

If the select statement fails then you should delete the entry from the table ITAB.

since this Entry will not fall in the Billing date specified

<b>Reward Points if useful.</b>

Regards,

Abhishek

[Former Member]

Hi,

Not working again.......all data displaying..........

Plz check again....

<b>Anil Kumar</b>

[former_member195698]

Please send the updated Code as suggested earlier and also the Input for the Billing date

[Former Member]

Hi Dear,

I changed the code as u told............

this is new code and commented code was the earlier code...........

***********************************************************************

TABLES : VBAK , VBRP, VBRK, KNA1, T001, LIKP, VBPA, TVKBT, TVGRT, KONV, VBKD, VBFA, T052, BSAD.

TYPE-POOLS: SLIS.

DATA: BEGIN OF ITAB OCCURS 0,

VKBUR LIKE VBAK-VKBUR , "Sales Office

KUNNR LIKE VBAK-KUNNR , "Sold to party

NAME1 LIKE KNA1-NAME1 , "CUSTOMER NAME

VBELN LIKE VBAK-VBELN , "Sale Order Number

AUDAT LIKE VBAK-AUDAT , "SALES DOC. DATE

NETWR LIKE VBAK-NETWR , "SALES DOC VALUE

VBELN1 LIKE VBRP-VBELN , "BILLING DOCUMENT NO.

FKDAT LIKE VBRK-FKDAT , "BILLING DATE

NETWR1 LIKE VBRK-NETWR , "BILLING DOCUMENT VALUE

MWSBK LIKE VBRK-MWSBK , "TAX AMOUNT

CD_AMT TYPE P DECIMALS 2, "CD AMOUNT

AMT_DUE TYPE P DECIMALS 2 , "AMOUNT DUE

FKART LIKE VBRK-FKART , "BILLING TYPE

BLART LIKE BSID-BLART , "DOC TYPE

DAYS LIKE T052-ZTAG1 , "Number of days

DUE_DT LIKE SY-DATUM , "Due Date

BSTNK LIKE VBAK-BSTNK , "Customer PO No.

ZTERM LIKE VBKD-ZTERM , "PAYMENT TERM

KNUMV LIKE VBRK-KNUMV , "VBRK DOCUMENT CONDITION

BELNR LIKE BSAD-BELNR , " DOC. NO.

AUGBL LIKE BSAD-AUGBL , " CLEARING DOC.

END OF ITAB .

DATA: BEGIN OF ITAB1 OCCURS 0,

KNUMV LIKE KONV-KNUMV , "DOCUMENT CONDITION

KSCHL LIKE KONV-KSCHL ,

KAWRT LIKE KONV-KAWRT ,

KBETR LIKE KONV-KBETR ,

KWERT LIKE KONV-KWERT , "Condition Value

END OF ITAB1 .

DATA : WA1 LIKE ITAB1.

DATA: BEGIN OF ITAB2 OCCURS 0,

KNUMV LIKE KONV-KNUMV , "DOCUMENT CONDITION

KSCHL LIKE KONV-KSCHL ,

KAWRT LIKE KONV-KAWRT ,

KBETR LIKE KONV-KBETR ,

KWERT LIKE KONV-KWERT , "Condition Value

END OF ITAB2 .

*DATA : WA2 LIKE ITAB2.

DATA : X TYPE I.

**************************************************************************

DATA : W_CONTAINER TYPE SCRFNAME VALUE 'CL_GRID',

W_CPROG TYPE LVC_S_LAYO,

G_REPID LIKE SY-REPID,

W_SAVE TYPE C,

W_EXIT TYPE C,

CL_GRID TYPE REF TO CL_GUI_ALV_GRID,

CL_CUSTOM_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER,

IT_FLD_CATALOG TYPE SLIS_T_FIELDCAT_ALV,

WA_FLD_CATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE ,

LAYOUT TYPE SLIS_LAYOUT_ALV,

COL_POS LIKE SY-CUCOL ,

ALVFC TYPE SLIS_T_FIELDCAT_ALV,

IT_SORT TYPE SLIS_T_SORTINFO_ALV.

***----

---

SELECTION SCREEN -

---

SELECTION-SCREEN BEGIN OF BLOCK A WITH FRAME.

SELECT-OPTIONS: VKBUR FOR VBAK-VKBUR, " Sales Office

KUNNR FOR VBAK-KUNNR, " Sold to party

FKDAT FOR VBRK-FKDAT, " OBLIGATORY . " Created on

FKART FOR VBRK-FKART, " Billing Type

AUDAT FOR VBAK-AUDAT.

SELECTION-SCREEN END OF BLOCK A.

***************************************************************************

PERFORM FILL_CATALOG1 USING:

'VKBUR' 'ITAB' 'SALES OFFICE',

'KUNNR' 'ITAB' 'CUST CODE',

'NAME1' 'ITAB' 'NAME' ,

'VBELN' 'ITAB' 'SALES DOC NO.',

'AUDAT' 'ITAB' 'SALES DOC DATE',

'NETWR' 'ITAB' 'NET VALUE' ,

'KNUMV' 'ITAB' 'DOC. CONDITION NO.',

'VBELN1' 'ITAB' 'BILLING DOC NO.' ,

'FKDAT' 'ITAB' 'BILLING DATE',

'NETWR1' 'ITAB' 'NET VALUE' ,

'MWSBK' 'ITAB' 'TAX AMOUNT' ,

'CD_AMT' 'ITAB' 'CD AMOUNT' ,

'AMT_DUE' 'ITAB' 'AMOUNT DUE' ,

'FKART' 'ITAB' 'BILL TYPE' ,

'BLART' 'ITAB' 'DOC TYPE' ,

'AUGBL' 'ITAB' 'CLEARING DOC.' ,

'DUE_DT' 'ITAB' 'DUE DATE',

'BSTNK' 'ITAB' 'CUST PO NO.' ,

'ZTERM' 'ITAB' 'PAYMENT TERMS'.

********************************************************************************

SELECT DISTINCT A_{VKBUR A}KUNNR A_{VBELN A}AUDAT A_{NETWR A}BSTNK B~VBELN

INTO (ITAB-VKBUR, ITAB-KUNNR, ITAB-VBELN, ITAB-AUDAT, ITAB-NETWR, ITAB-BSTNK, ITAB-VBELN1)

FROM VBAK AS A INNER JOIN VBRP AS B ON A_{VBELN = B}AUBEL

WHERE A~VKBUR IN VKBUR

AND A~KUNNR IN KUNNR

AND A~AUDAT IN AUDAT.

APPEND ITAB .

ENDSELECT.

SORT ITAB BY VBELN.

LOOP AT ITAB .

SELECT FKDAT NETWR MWSBK KNUMV FKART FROM VBRK INTO (ITAB-FKDAT, ITAB-NETWR1, ITAB-MWSBK, ITAB-KNUMV, ITAB-FKART)

WHERE VBELN = ITAB-VBELN1

AND FKDAT IN FKDAT.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING FKDAT NETWR1 MWSBK KNUMV FKART.

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING VBELN .

ENDSELECT .

************

************LOOP AT ITAB .

• SELECT FKDAT NETWR MWSBK KNUMV FKART FROM VBRK INTO (ITAB-FKDAT, ITAB-NETWR1, ITAB-MWSBK, ITAB-KNUMV, ITAB-FKART)

• WHERE VBELN = ITAB-VBELN1

• AND FKDAT IN FKDAT.

• MODIFY ITAB INDEX SY-TABIX TRANSPORTING FKDAT NETWR1 MWSBK KNUMV FKART.

• DELETE ADJACENT DUPLICATES FROM ITAB COMPARING VBELN .

************

• ENDSELECT .

************

SELECT NAME1 FROM KNA1 INTO ITAB-NAME1 WHERE KUNNR = ITAB-KUNNR .

MODIFY ITAB INDEX SY-TABIX TRANSPORTING NAME1 .

ENDSELECT .

**********************************************************************************

SELECT AUGBL BLART FROM BSAD INTO (ITAB-AUGBL, ITAB-BLART) WHERE BELNR = ITAB-VBELN1.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING AUGBL BLART.

ENDSELECT .

*****************************************************************************************

SELECT ZTERM FROM VBKD INTO ITAB-ZTERM WHERE VBELN = ITAB-VBELN .

MODIFY ITAB INDEX SY-TABIX TRANSPORTING ZTERM .

ENDSELECT.

SELECT KNUMV KAWRT KBETR KSCHL INTO CORRESPONDING FIELDS OF TABLE ITAB1 FROM KONV

• FOR ALL ENTRIES IN ITAB

WHERE KNUMV = ITAB-KNUMV AND KSCHL IN ('SKTO') .

X = 0.

LOOP AT ITAB1 INTO WA1.

ON CHANGE OF WA1-KNUMV.

ITAB2[] = ITAB1[].

DELETE ITAB2 WHERE KNUMV NE WA1-KNUMV.

ENDON.

ENDLOOP.

LOOP AT ITAB2.

X = X + ITAB2-KAWRT * ITAB2-KBETR / 1000 .

ENDLOOP .

ITAB-CD_AMT = X.

MODIFY ITAB.

REFRESH ITAB1.

REFRESH ITAB2.

ENDLOOP .

*******************************************************************************

LOOP AT ITAB .

ITAB-AMT_DUE = ITAB-NETWR1 + ITAB-MWSBK + ITAB-CD_AMT .

MODIFY ITAB INDEX SY-TABIX TRANSPORTING AMT_DUE .

ENDLOOP .

*******************************************************************************

LOOP AT ITAB.

SELECT SINGLE ZTAG1 INTO ITAB-DAYS FROM T052 WHERE ZTERM = ITAB-ZTERM.

ITAB-DUE_DT = ITAB-AUDAT + ITAB-DAYS.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING DUE_DT .

ENDLOOP.

*******************************************************************************

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = 'ZSDREPORT_TEST'

IS_LAYOUT = LAYOUT

IT_FIELDCAT = IT_FLD_CATALOG

IT_SORT = IT_SORT

I_DEFAULT = 'X'

I_SAVE = 'A'

TABLES

T_OUTTAB = ITAB

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

*ENDFORM.

FORM FILL_CATALOG1 USING P_FIELDNAME TYPE ANY

P_REF_TABLE TYPE ANY

P_SCRTEXT TYPE ANY.

CLEAR : WA_FLD_CATALOG.

WA_FLD_CATALOG-FIELDNAME = P_FIELDNAME.

WA_FLD_CATALOG-TABNAME = P_REF_TABLE.

WA_FLD_CATALOG-SELTEXT_S = P_SCRTEXT.

WA_FLD_CATALOG-SELTEXT_M = P_SCRTEXT.

WA_FLD_CATALOG-SELTEXT_L = P_SCRTEXT.

APPEND WA_FLD_CATALOG TO IT_FLD_CATALOG.

ENDFORM. " fill_catalog1

*********************************************************************************

Now Check it...........

<b>Anil Kumar</b>

[Former Member]

hi,

Billing Date : 01/11/2003 to 21/11/2003.............that was i m picking.....

<b>Anil Kumar</b>

[former_member195698]

LOOP AT ITAB .

<b>SELECT FKDAT NETWR MWSBK KNUMV FKART FROM VBRK INTO (ITAB-FKDAT, ITAB-NETWR1, ITAB-MWSBK, ITAB-KNUMV, ITAB-FKART)

WHERE VBELN = ITAB-VBELN1

AND FKDAT IN FKDAT.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING FKDAT NETWR1 MWSBK KNUMV FKART.

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING VBELN .

ENDSELECT .</b>

After the select statement given above in BOLD.. check for sy-subrc. if it is not zero Then Delete the Entry in table ITAB and continue.

Other way can be....Since VBRK will have only one entry for a given Document number

LOOP AT ITAB.

SELECT SINGLE FKDAT NETWR MWSBK KNUMV FKART FROM VBRK INTO (ITAB-FKDAT, ITAB-NETWR1, ITAB-MWSBK, ITAB-KNUMV, ITAB-FKART)

WHERE VBELN = ITAB-VBELN1

AND FKDAT IN FKDAT.

IF SY-SUBRC NE 0.

DELETE ITAB.

CONTINUE.

ENDIF.

MODIFY ITAB INDEX SY-TABIX TRANSPORTING FKDAT NETWR1 MWSBK KNUMV FKART.

...

<b>Reward Points if useful.</b>

Regards,

Abhishek

[Former Member]

Hi Dear,

Thanks a LOT......

<b>Anil Kumar</b>