I NEED FIELDS FROM VBFA TABLE

[Former Member]

HI ALL,

I NEED FIELDS FROM VBFA TABLE

THE FIELDS I WANT IS :

CUSTOMER-ID

CUSTOMER NAME

CONTACT NAME

PROJECTID

ORDER NO

SALES MAN ID

ORDER PROCESS DATE

INVOICE DATE

GROSS AMOUNT

NET AMOUNT POSTAL CODE.

THANKS & REGARDS,

R.VINOD.

[varma_narayana]

Hi Vinoth..

VBFA table stores the Sales Doc flow.

That means the Preceding document (Eg: Sales Order) and Subsequent Document

(Eg Delivery or Invoice) .

So you can use this table only to get the Invoice numbers for a particular ORDER . etc...

But all the fields you mentioned will come from different tables like VBRK, VBRP, KNVP etc..

<b>

reward If Helpful.</b>

[Former Member]

hi,

check SE11 t-code. Open VBFA table. Check structure of this table. You can get data which are exists in this table. otherwise you have to use join with vbrk,vbrp,kna1 tables...

raj

[varma_narayana]

Hi Vinod..

Try this Code. I made all the modifications in your code .. It will solve ur issues..

REPORT zsdr_omvsa40.

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

• TYPE-POOLS

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

TYPE-POOLS: slis.

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

• TABLE DECLARATIONS

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

TABLES : vbak, vbkd,

• zzvbak,

kna1, vbrk, vbrp, knvp .

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

• INTERNALTABLE DECLARATION *

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

DATA: BEGIN OF i_vbak OCCURS 0,

vbelv LIKE vbfa-vbelv, " Sales Order no

vbeln like vbfa-vbeln, "Invoice No

erdat LIKE vbak-erdat, " Date on Which Record Was Created

kunnr LIKE vbak-kunnr,

ps_psp_pnr LIKE vbak-ps_psp_pnr, " Work Breakdown Structure Element

END OF i_vbak.

*DATA : BEGIN OF i_zzvbak OCCURS 0,

*vbeln LIKE zzvbak-vbeln,

*zssidc LIKE zzvbak-zssidc, "Salesman ID

*END OF i_zzvbak.

*

DATA : BEGIN OF i_vbrk OCCURS 0,

vbeln LIKE vbrk-vbeln,

fkdat LIKE vbrk-fkdat, "Invoice Date

END OF i_vbrk.

DATA : BEGIN OF i_kna1 OCCURS 0,

kunnr LIKE kna1-kunnr , " Customer Number 1

name1 LIKE kna1-name1, " Customer Name

pstlz LIKE kna1-pstlz , " Postal Code

END OF i_kna1.

DATA : BEGIN OF i_vbrp OCCURS 0,

vbeln LIKE vbrp-vbeln,

aubel LIKE vbrp-aubel,

netwr LIKE vbrp-netwr , " Net Value in Document Currency

kzwi1 LIKE vbrp-kzwi1, " Subtotal 1 from pricing procedure for condition

• erdat LIKE vbrp-erdat, "Billing document.

END OF i_vbrp.

DATA : BEGIN OF i_knvp OCCURS 0,

parvw LIKE knvp-parvw , " Partner Function

kunnr LIKE knvp-kunnr ,

parnr LIKE knvp-parnr , " Number of contact person

END OF i_knvp .

DATA : BEGIN OF i_data OCCURS 0,

erdat LIKE vbak-erdat, " Date on Which Record Was Created

vbeln LIKE vbak-vbeln, " Sales Order no

fkdat LIKE vbrk-fkdat, " Invoice date.

kunnr LIKE kna1-kunnr , " Customer Number

ps_psp_pnr LIKE vbak-ps_psp_pnr, " Work Breakdown Structure Element

name1 LIKE kna1-name1, " Customer Name

netwr LIKE vbrp-netwr , " Net Value in Document Currency

kzwi1 LIKE vbrp-kzwi1, " Subtotal 1 from pricing procedure for condition

• parvw LIKE knvp-parvw , " Partner Function

parnr LIKE knvp-parnr , " Number of contact person

*zssidc LIKE zzvbak-zssidc, "Salesman ID

pstlz LIKE kna1-pstlz , " Postal Code

END OF i_data.

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

• ALV Declaraton

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

DATA: fieldcatalog TYPE slis_t_fieldcat_alv WITH HEADER LINE,

gd_tab_group TYPE slis_t_sp_group_alv,

gd_layout TYPE slis_layout_alv,

it_listheader TYPE slis_t_listheader,

gd_repid LIKE sy-repid.

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

• Selection - Screen

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

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS creation FOR vbak-erdat . " Sales Order Date

SELECT-OPTIONS period FOR vbrk-fkdat . " Invoice Date

SELECT-OPTIONS order FOR vbak-vbeln . " Sales order no

SELECT-OPTIONS name FOR kna1-name1 . " Customer Name

SELECT-OPTIONS contact FOR knvp-parnr . " Contact Name.

*SELECT-OPTIONS ssid FOR zzvbak-zssidc . " Salesman ID

SELECT-OPTIONS project FOR vbak-ps_psp_pnr . " Work Breakdown Structure Element

SELECTION-SCREEN : END OF BLOCK b1.

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

• START-OF-SELECTION

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

START-OF-SELECTION.

PERFORM data_retrieval.

PERFORM build_fieldcatalog.

• PERFORM BUILD_LAYOUT.

• PERFORM top_of_page.

PERFORM fill_listheader USING it_listheader.

PERFORM display_alv_report.

END-OF-SELECTION.

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

*TOP-OF-PAGE.

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

TOP-OF-PAGE.

END-OF-PAGE.

&----

---

*& Form BUILD_FIELDCATALOG

&----

---

• text

----

---

FORM build_fieldcatalog.

fieldcatalog-fieldname = 'KUNNR'.

fieldcatalog-seltext_m = 'Sold to Party'.

fieldcatalog-col_pos = 0.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'X'.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'NAME1'.

fieldcatalog-seltext_m = 'Hlev Customer'.

fieldcatalog-col_pos = 1.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'PARNR'.

fieldcatalog-seltext_m = 'Contact name'.

fieldcatalog-col_pos = 2.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'PS_PSP_PNR'.

fieldcatalog-seltext_m = 'Project ID'.

fieldcatalog-col_pos = 3.

fieldcatalog-do_sum = 'X'.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'VBELN'.

fieldcatalog-seltext_m = 'Sales Document Type'.

fieldcatalog-col_pos = 4.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'ZSSIDC'.

fieldcatalog-seltext_m = 'SSID'.

fieldcatalog-col_pos = 5.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'ERDAT'.

fieldcatalog-seltext_m = 'so date'.

fieldcatalog-col_pos = 6.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'FKDAT'.

fieldcatalog-seltext_m = 'inv date'.

fieldcatalog-col_pos = 7.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'KWZI1'.

fieldcatalog-seltext_m = 'gross amt'.

fieldcatalog-col_pos = 8.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'NETWR'.

fieldcatalog-seltext_m = 'net amt'.

fieldcatalog-col_pos = 9.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

fieldcatalog-fieldname = 'PSTLZ'.

fieldcatalog-seltext_m = 'Postal code'.

fieldcatalog-col_pos = 10.

APPEND fieldcatalog TO fieldcatalog.

CLEAR fieldcatalog.

ENDFORM. "BUILD_FIELDCATALOG

&----

---

*& Form DATA_RETRIEVAL

&----

---

• text

----

---

FORM data_retrieval.

SELECT VBFA_{vbelv VBFA}vbeln

VBAK_{erdat VBAK}kunnr VBAK~ps_psp_pnr

INTO TABLE i_vbak

FROM VBFA

INNER JOIN vbak

ON VBFA_{VBELV = VBAK}VBELN

WHERE VBAK~erdat IN creation

AND VBFA~vbelV IN ORDER

AND VBAK~ps_psp_pnr IN project

AND VBFA~VBTYP_N = 'M' "Subsequent doc is Invoice

AND VBFA~VBTYP_V = 'C' "Prec doc is Sales order

.

IF NOT i_vbak[] IS INITIAL.

**Change of ORDER in SELECTS HERE

SELECT vbeln fkdat FROM vbrk INTO TABLE i_vbrk

FOR ALL ENTRIES IN i_vbak

WHERE vbeln = i_vbak-vbeln

AND fkdat IN period.

IF NOT i_vbrk[] IS INITIAL.

SELECT vbeln aubel netwr kzwi1 FROM vbrp INTO TABLE i_vbrp

FOR ALL ENTRIES IN i_vbrk

WHERE VBELN = i_vbrk-vbeln.

endif.

SELECT kunnr name1 pstlz FROM kna1 INTO TABLE i_kna1 FOR ALL ENTRIES IN i_vbak

WHERE kunnr = i_vbak-kunnr

AND name1 IN name.

*SELECT vbeln zssidc FROM zzvbak INTO TABLE i_zzvbak FOR ALL ENTRIES IN i_vbak

*WHERE vbeln = i_vbak-vbeln

*AND zssidc IN ssid .

*

• select netwr kzwi1 erdat from vbrp into table i_vbrp for all entries in i_vbak

• where erdat = i_vbak-erdat.

SELECT kunnr parnr parvw FROM knvp INTO CORRESPONDING FIELDS OF TABLE i_knvp FOR ALL ENTRIES IN i_vbak

WHERE kunnr = i_vbak-kunnr

AND parvw = 'AP'

AND parnr IN contact.

ENDIF .

SORT I_VBAK BY VBELN .

SORT I_VBRK BY VBELN .

LOOP AT i_vbrp. "Invoice Item data

MOVE i_vbrp-netwr TO i_data-netwr .

MOVE i_vbrp-kzwi1 TO i_data-kzwi1.

READ table I_VBAK WITH KEY VBELN = I_VBRP-VBELN BINARY SEARCH. "Sales Order info

IF SY-SUBRC = 0.

MOVE I_VBAK-VBELV TO I_DATA-VBELN. "Sales Order no

MOVE I_VBAK-erdat TO I_DATA-erdat. " Date on Which Record Was Created

MOVE I_VBAK-kunnr TO I_DATA-KUNNR. "Customer No

MOVE I_VBAK-ps_psp_pnr TO I_DATA-ps_psp_pnr. " Work Breakdown Structure Element

endif.

READ TABLE I_VBRK WITH KEY VBELN = I_VBRP-VBELN BINARY SEARCH. "Invoice header info

IF SY-SUBRC = 0.

MOVE i_vbrk-fkdat TO i_data-fkdat.

endif.

READ TABLE I_KNA1 WITH KEY KUNNR = I_VBAK-KUNNR BINARY SEARCH. "Customer info

IF SY-SUBRC = 0.

MOVE i_kna1-kunnr TO i_data-kunnr.

MOVE i_kna1-name1 TO i_data-name1.

MOVE i_kna1-pstlz TO i_data-pstlz .

endif.

READ TABLE I_KNvp WITH KEY KUNNR = I_VBAK-KUNNR BINARY SEARCH. "Partner info

IF SY-SUBRC = 0.

MOVE i_knvp-parnr TO i_data-parnr.

endif.

APPEND i_data.

ENDLOOP.

ENDFORM. "DATA_RETRIEVAL

&----

---

*& Form DISPLAY_ALV_REPORT

&----

---

• text

----

---

FORM display_alv_report.

• GD_REPID = SY-REPID.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

• I_INTERFACE_CHECK = ' '

• I_BYPASSING_BUFFER = ' '

• I_BUFFER_ACTIVE = ' '

i_callback_program = sy-repid

• I_CALLBACK_PF_STATUS_SET = ' '

• I_CALLBACK_USER_COMMAND = ' '

i_callback_top_of_page = 'TOP_OF_PAGE'

• I_CALLBACK_HTML_TOP_OF_PAGE = ' '

• I_CALLBACK_HTML_END_OF_LIST = ' '

• I_STRUCTURE_NAME =

• I_BACKGROUND_ID = ' '

• I_GRID_TITLE =

• I_GRID_SETTINGS =

• IS_LAYOUT =

it_fieldcat = fieldcatalog[]

• IT_EXCLUDING =

• IT_SPECIAL_GROUPS =

• IT_SORT =

• IT_FILTER =

• IS_SEL_HIDE =

• I_DEFAULT = 'X'

• I_SAVE = ' '

• IS_VARIANT =

• IT_EVENTS =

• IT_EVENT_EXIT =

• IS_PRINT =

• IS_REPREP_ID =

• I_SCREEN_START_COLUMN = 0

• I_SCREEN_START_LINE = 0

• I_SCREEN_END_COLUMN = 0

• I_SCREEN_END_LINE = 0

• I_HTML_HEIGHT_TOP = 0

• I_HTML_HEIGHT_END = 0

• IT_ALV_GRAPHICS =

• IT_HYPERLINK =

• IT_ADD_FIELDCAT =

• IT_EXCEPT_QINFO =

• IR_SALV_FULLSCREEN_ADAPTER =

• IMPORTING

• E_EXIT_CAUSED_BY_CALLER =

• ES_EXIT_CAUSED_BY_USER =

TABLES

t_outtab = i_data.

• 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. "DISPLAY_ALV_REPORT

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

• FORM FOR FILLING LISTHEADER *

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

FORM fill_listheader USING it_listheader TYPE slis_t_listheader.

DATA : wa_listheader TYPE slis_listheader.

wa_listheader-typ = 'H'.

wa_listheader-info = 'Noel Gifts International Limited '.

APPEND wa_listheader TO it_listheader.

wa_listheader-typ = 'S'.

wa_listheader-info = 'CUSTOMER CREDIT EXCEPTION REPORT' .

APPEND wa_listheader TO it_listheader.

CLEAR wa_listheader.

ENDFORM. "fill_listheader

&----

---

*& Form top_of_page

&----

---

• text

----

---

FORM top_of_page.

CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'

EXPORTING

it_list_commentary = it_listheader.

.

ENDFORM. "top_of_page

REWARD IF HELPFUL.