topic Re: help with selection in Application Development and Automation Discussions

help with selection

Former Member — Mon, 25 Sep 2006 14:28:15 GMT

I am stuck with a problem and I cannot figure it out. I need to be able to extract data based on the records in the BSIS table which do NOT have a corrosponding entries in the BSIK or the BSID table. I need only the GL records. I am trying the code below and I am not getting any data in the wt_bkpf table to use for selection the BSEG records. I have includedpart of my code that is relavant to this problem with hopes that someone could look at it and let me know what I am doing wrong

thanks in advance for the help.

REPORT y_tlh_extgl.

***

tables declaration

TABLES:

bkpf, " Accounting Document Header

bseg. " Accounting Document Segment

DATA: BEGIN OF t_upload_file OCCURS 0,

line(3000) TYPE c.

DATA: END OF t_upload_file.

***

variables declaration

DATA:

wv_index LIKE sy-index,

wv_item_index LIKE sy-index.

***

constants definition

DATA: BEGIN OF ws_bkpf,

belnr LIKE bkpf-belnr, " Reference

waers LIKE bkpf-waers, " Currency

bktxt LIKE bkpf-bktxt. " header text

INCLUDE STRUCTURE bkpf.

DATA: END OF ws_bkpf.

***

structures definition

DATA: BEGIN OF wt_bkpf OCCURS 0.

INCLUDE STRUCTURE ws_bkpf.

*INCLUDE STRUCTURE bkpf.

DATA: END OF wt_bkpf.

DATA: BEGIN OF wt_bkpf_hold OCCURS 0.

INCLUDE STRUCTURE ws_bkpf.

INCLUDE STRUCTURE bkpf.

DATA: END OF wt_bkpf_hold.

DATA: BEGIN OF ws_bseg,

belnr LIKE bseg-belnr, " Reference

hkont LIKE bseg-hkont, " G/L Account

bschl LIKE bseg-bschl,

wrbtr LIKE bseg-wrbtr,

dmbtr LIKE bseg-dmbtr,

dmbe2 LIKE bseg-dmbe2,

kostl LIKE bseg-kostl, " Cost center

sgtxt LIKE bseg-sgtxt, " Item text

aufnr LIKE bseg-aufnr, " Project

zuonr LIKE bseg-zuonr, " Assignment/Cntry of Dest

prctr LIKE bseg-prctr, " Profit center

vbund LIKE bseg-vbund, " Trading partner

umskz LIKE bseg-umskz, " Special G/L Indicator

mwskz LIKE bseg-mwskz. " Tax code

DATA: END OF ws_bseg.

DATA: BEGIN OF wt_bseg OCCURS 0.

INCLUDE STRUCTURE ws_bseg.

DATA: END OF wt_bseg.

DATA: BEGIN OF wt_gl_bsis OCCURS 0.

INCLUDE STRUCTURE bsis.

DATA: END OF wt_gl_bsis.

***

SELECTION-SCREEN BEGIN OF BLOCK blck WITH FRAME.

PARAMETERS:

p_bukrs LIKE bkpf-bukrs OBLIGATORY

DEFAULT wc_bukrs_5520.

SELECT-OPTIONS:

s_belnr FOR bkpf-belnr,

s_monat FOR bkpf-monat,

s_gjahr FOR bkpf-gjahr OBLIGATORY.

SELECTION-SCREEN SKIP 1.

PARAMETERS:

p_svfile LIKE rlgrap-filename

DEFAULT '/DEV/MFGPRO/TLH_EXTRACT_xx.txt'

NO-DISPLAY.

SELECTION-SCREEN END OF BLOCK blck.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-012 .

PARAMETERS:p_ipath TYPE rlgrap-filename OBLIGATORY." LOWER CASE.

SELECTION-SCREEN END OF BLOCK b2.

***

INITIALIZATION

INITIALIZATION.

PERFORM f_initialize_some_data.

***

AT SELECTION-SCREEN.

PERFORM f_preliminary_checks.

***

START-OF-SELECTION.

PERFORM f_get_data.

PERFORM f_split_data.

PERFORM f_transfer_data_to_server.

PERFORM write_data.

END-OF-SELECTION.

***

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

Routines definition

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

FORM f_initialize_some_data .

s_gjahr-option = 'BT'.

s_gjahr-sign = 'I'.

s_gjahr-low = '2005'.

s_gjahr-high = '2006'.

APPEND s_gjahr.

ENDFORM. " f_initialize_some_data

FORM f_get_data .

SELECT * INTO TABLE wt_GL_BSIS

FROM bsis

WHERE bukrs EQ p_bukrs

AND belnr IN s_belnr

AND gjahr IN s_gjahr.

IF sy-dbcnt IS INITIAL.

MESSAGE i208(00) WITH text-001.

STOP.

ENDIF.

loop at wt_GL_BSIS.

DATA: wa_bsik type bsik.

DATA: wa_bsid type bsid.

SELECT SINGLE * FROM BSIK

INTO CORRESPONDING FIELDS OF wa_bsik

WHERE bukrs EQ p_bukrs

AND belnr EQ wt_GL_BSIS-belnr

AND gjahr EQ s_gjahr.

if sy-subrc = 0.

exit.

ELSE.

SELECT SINGLE * FROM BSId

INTO CORRESPONDING FIELDS OF wa_bsid

WHERE bukrs EQ p_bukrs

AND belnr EQ wt_GL_BSIS-belnr

AND gjahr EQ s_gjahr.

if sy-subrc = 0.

exit.

ELSE.

write: / 'getting gl record'.

SELECT belnr waers bktxt

INTO TABLE wt_bkpf_hold

FROM bkpf

WHERE belnr EQ wt_gl_bsis-belnr

AND bukrs EQ wt_gl_bsis-bukrs

AND monat IN s_monat.

IF sy-dbcnt IS INITIAL.

MESSAGE i208(00) WITH text-001.

STOP.

ENDIF.

MOVE wt_bkpf_hold-belnr TO wt_bkpf-belnr.

MOVE wt_bkpf_hold-waers TO wt_bkpf-waers.

MOVE wt_bkpf_hold-bktxt TO wt_bkpf-bktxt.

APPEND wt_bkpf.

ENDIF.

ENDLOOP.

***

SORT wt_bkpf BY belnr.

***

SELECT belnr hkont bschl wrbtr dmbtr dmbe2 kostl sgtxt aufnr zuonr

prctr vbund umskz mwskz

INTO TABLE wt_bseg

FROM bseg

FOR ALL ENTRIES IN wt_bkpf

WHERE bukrs EQ p_bukrs

AND belnr EQ wt_bkpf-belnr

AND gjahr EQ s_gjahr.

*ENDIF.

*ENDLOOP.

ENDFORM. " f_get_data

Re: help with selection

Former Member — Mon, 25 Sep 2006 15:22:18 GMT

You can try to check directly in BSEG:

SELECT * INTO TABLE wt_GL_BSIS

FROM bsis

WHERE bukrs EQ p_bukrs

AND belnr IN s_belnr

AND gjahr IN s_gjahr.

IF sy-dbcnt IS INITIAL.

MESSAGE i208(00) WITH text-001.

STOP.

ENDIF.

SORT WT_GL_BSIS BY BUKRS GJAHR BELNR.

LOOP AT WT_GL_BSIS.

IF WT_GL_BSIS-BUKRS = OLDBUKRS OR

WT_GL_BSIS-BELNR = OLDBELNR OR

WT_GL_BSIS-GJAHR = OLDGJAHR.

SELECT BUKRS FROM BSEG INTO WT_GL_BSIS-BUKRS

WHERE BUKRS = WT_GL_BSIS-BUKRS

AND BELNR = WT_GL_BSIS-BELNR

AND GJAHR = WT_GL_BSIS-GJAHR

Check vendor/customer item:
KOART = K ---> Vendor Item (BSIK)
KOART = D ---> Customer Item (BSID)

AND ( KOART = 'D' OR KOART = 'K' ).

EXIT.

ENDSELECT.

RC = SY-SUBRC.

ENDIF.

OLDBUKRS = WT_GL_BSIS-BUKRS .

OLDBELNR = WT_GL_BSIS-BELNR.

OLDGJAHR = WT_GL_BSIS-GJAHR.

IF RC = 0.

write: / 'getting gl record'.

.............................

ENDIF.

ENDLOOP.

I don't know which header and item fields you need, but BSIS has the main fields of BKPF and BSEG so perhaps you don't need to read those tables.

Max

Re: help with selection

Former Member — Mon, 25 Sep 2006 15:38:48 GMT

Max,

thanks for the reply. what I need to do is to get only the GL record that do not have any customer or vendor records associated with them. I need to create extract files by document number for all customer, vendor and GL. I have all ready done the customer and vendor extractions of all the records(including the GL). What I need now is the remaining GL that I dod not extract with the customer and vendor process. I am having a problem filling the data from the selection of the bkpf record. I can't seem to get a table of only the bkpf records that I have selected. when I run in debug and click on wt_bkpf_hold from the select statement, I see the record. when I click on any one of the wt_bkpf_hold-belnr, wt_bkpf_hold-waers or the wt_bkpf_hold-bktxt from the MOCE statement, I see nothing. I am not sure if I have the data statementsset up correctly or if I need type statements. I am not sure what I need.

Re: help with selection

Former Member — Mon, 25 Sep 2006 15:48:39 GMT

Try the below change as marked in BOLD and delete the code as in UNDERLINE

loop at wt_GL_BSIS.

DATA: wa_bsik type bsik.

DATA: wa_bsid type bsid.

SELECT SINGLE * FROM BSIK

INTO CORRESPONDING FIELDS OF wa_bsik

WHERE bukrs EQ p_bukrs

AND belnr EQ wt_GL_BSIS-belnr

AND gjahr EQ s_gjahr.

if sy-subrc = 0.

exit.

ELSE.

SELECT SINGLE * FROM BSId

INTO CORRESPONDING FIELDS OF wa_bsid

WHERE bukrs EQ p_bukrs

AND belnr EQ wt_GL_BSIS-belnr

AND gjahr EQ s_gjahr.

if sy-subrc = 0.

exit.

ELSE.

write: / 'getting gl record'.

SELECT belnr waers bktxt

INTO TABLE wt_bkpf_hold

FROM bkpf

WHERE belnr EQ wt_gl_bsis-belnr

AND bukrs EQ wt_gl_bsis-bukrs

AND monat IN s_monat.

IF sy-dbcnt IS INITIAL.

MESSAGE i208(00) WITH text-001.

STOP.

ENDIF.

MOVE wt_bkpf_hold-belnr TO wt_bkpf-belnr.

MOVE wt_bkpf_hold-waers TO wt_bkpf-waers.

MOVE wt_bkpf_hold-bktxt TO wt_bkpf-bktxt.

APPEND wt_bkpf.ENDIF.

ENDIF.

ENDLOOP.

Loop at wt_bkpf_hold.

move-corresponding wt_bkpf_hold to wt_bkpf.

append wt_bkpf.

Endloop.

Re: help with selection

Former Member — Mon, 25 Sep 2006 15:55:29 GMT

You have used INTO TABLE statament so u've filled an internal table not a workarea:

*SELECT belnr waers bktxt

*INTO TABLE wt_bkpf_hold

SELECT SINGLE belnr waers bktxt

INTO wt_bkpf_hold

FROM bkpf

WHERE bukrs EQ wt_gl_bsis-bukrs

AND belnr EQ wt_gl_bsis-belnr

AND GJAHR EQ WT_GL_BSIS-GJAHR.

AND MONAT IN s_monat.

IF sy-SUBRC <> 0.

MESSAGE i208(00) WITH text-001.

STOP.

ENDIF.

MOVE wt_bkpf_hold-belnr TO wt_bkpf-belnr.

MOVE wt_bkpf_hold-waers TO wt_bkpf-waers.

MOVE wt_bkpf_hold-bktxt TO wt_bkpf-bktxt.

APPEND wt_bkpf.

ENDIF.

Max

Re: help with selection

Former Member — Mon, 25 Sep 2006 16:37:06 GMT

Is it correct to say that you are looking for documents that have just GL entries?

Rob

Re: help with selection

Former Member — Mon, 25 Sep 2006 16:40:49 GMT

Max and Anurag,

I tried both of the solutions and still does not work.

Re: help with selection

Former Member — Mon, 25 Sep 2006 16:46:57 GMT

So, have you debugged this to see if you can find out where it goes wrong?

Rob

Re: help with selection

Former Member — Mon, 25 Sep 2006 16:47:33 GMT

Incredible? You've all keys to read BKPF table, another error is how you've defined wt_bkpf_hold:

DATA: BEGIN OF wt_bkpf_hold OCCURS 0.

INCLUDE STRUCTURE ws_bkpf.

DATA: END OF wt_bkpf_hold.

So you can use this statament:

SELECT SINGLE belnr waers bktxt INTO wt_bkpf_hold

but:

SELECT SINGLE * INTO CORRESPONDING FIELDS OF wt_bkpf_hold

SELECT SINGLE * INTO wt_bkpf_hold

FROM bkpf WHERE bukrs EQ wt_gl_bsis-bukrs

AND belnr EQ wt_gl_bsis-belnr

AND GJAHR EQ WT_GL_BSIS-GJAHR.

IF wt_bkpf_hold-MONAT IN s_monat.

MOVE wt_bkpf_hold-belnr TO wt_bkpf-belnr.

MOVE wt_bkpf_hold-waers TO wt_bkpf-waers.

MOVE wt_bkpf_hold-bktxt TO wt_bkpf-bktxt.

APPEND wt_bkpf.

ELSE.

IF sy-SUBRC <> 0.

MESSAGE i208(00) WITH text-001.

ENDIF.

Max

Re: help with selection

Former Member — Mon, 25 Sep 2006 16:47:52 GMT

Rob,

yes. only the open item gl documents that do NOT have customer or vendor records associated with the document.

Re: help with selection

Former Member — Mon, 25 Sep 2006 16:53:08 GMT

Have you tried entering a document number on the selection screen that you know only had GL items?

Rob

Re: help with selection

Former Member — Mon, 25 Sep 2006 16:53:40 GMT

If you want this:

SELECT * INTO TABLE wt_GL_BSIS

FROM bsis WHERE bukrs EQ p_bukrs

AND gjahr IN s_gjahr

AND belnr IN s_belnr

AND MONAT IS S_MONAT.

IF sy-dbcnt IS INITIAL.

MESSAGE i208(00) WITH text-001.

STOP.

ENDIF.

SORT WT_GL_BSIS BY BUKRS GJAHR BELNR.

LOOP AT WT_GL_BSIS.

u can have several items of the same document so u
should try to read BSEG and BKPF table once per every
document

IF WT_GL_BSIS-BUKRS <> OLDBUKRS OR

WT_GL_BSIS-BELNR <> OLDBELNR OR

WT_GL_BSIS-GJAHR <> OLDGJAHR.

Check if there're vendor or customer items

SELECT BUKRS FROM BSEG INTO WT_GL_BSIS-BUKRS

WHERE BUKRS = WT_GL_BSIS-BUKRS

AND BELNR = WT_GL_BSIS-BELNR

AND GJAHR = WT_GL_BSIS-GJAHR

Check vendor/customer item:
KOART = K ---> Vendor Item (BSIK)
KOART = D ---> Customer Item (BSID)

AND ( KOART = 'D' OR KOART = 'K' ).

EXIT.

ENDSELECT.

IF SY-SUBRC <> 0.

Get header data

SELECT SINGLE * INTO wt_bkpf_hold

FROM bkpf WHERE bukrs EQ wt_gl_bsis-bukrs

AND belnr EQ wt_gl_bsis-belnr

AND GJAHR EQ WT_GL_BSIS-GJAHR.

MOVE wt_bkpf_hold-belnr TO wt_bkpf-belnr.

MOVE wt_bkpf_hold-waers TO wt_bkpf-waers.

MOVE wt_bkpf_hold-bktxt TO wt_bkpf-bktxt.

APPEND wt_bkpf.

ENDIF.

OLDBUKRS = WT_GL_BSIS-BUKRS.

OLDBELNR = WT_GL_BSIS-BELNR.

OLDGJAHR = WT_GL_BSIS-GJAHR.

ENDLOOP.

Re: help with selection

Former Member — Mon, 25 Sep 2006 17:12:54 GMT

Hi,

it is now working. here is the code that I used

SELECT single belnr waers bktxt

INTO wt_bkpf_hold

FROM bkpf

WHERE belnr EQ wt_gl_bsis-belnr

AND bukrs EQ wt_gl_bsis-bukrs

AND monat EQ s_monat.

IF sy-dbcnt IS INITIAL.

write: / ' record not found ' , wt_GL_BSIS-belnr.

ENDIF.

MOVE wt_bkpf_hold-belnr TO wt_bkpf-belnr.

MOVE wt_bkpf_hold-waers TO wt_bkpf-waers.

MOVE wt_bkpf_hold-bktxt TO wt_bkpf-bktxt.

APPEND wt_bkpf.

Thanks for all the help

Re: help with selection

Former Member — Mon, 25 Sep 2006 17:12:56 GMT

By the way, since you know BIKRS, BELNR and GJAHR, your first select should be against BSEG not BSIS. You will have to add some logic (KOART = 'S') to only get GL items. As written, it will not perform well in production.

On further examination, all of your selects should be against BSEG.

Something like:

SELECT * FROM BSEG
  WHERE bukrs EQ p_bukrs
    AND belnr IN s_belnr
    AND gjahr IN s_gjahr 
    and AUGBL = space
    and KOART <> 'S'.

IF sy-subrc = 0.
* The document either doesn't exist or it has customers or vendors.
ENDIF.

Rob

Message was edited by: Rob Burbank

Re: help with selection

Former Member — Mon, 25 Sep 2006 17:18:15 GMT

Rob,

I do not know the document number (belnr) until I read the BSIS (open iem GL)table for the given company code and month. Am I missing something in the response that you sent? If there is a better way to get this info, Please let me know

thanks...

Re: help with selection

Former Member — Mon, 25 Sep 2006 17:24:23 GMT

See my earlier response.

Rob

Re: help with selection

Former Member — Mon, 25 Sep 2006 17:36:14 GMT

Rob,

still a little confused. your last response

See my earlier response.

which earlier response?

if i need to get all of the open item GL only documents, and I don't know the document number, isn't this the way to do it?

Re: help with selection

Former Member — Mon, 25 Sep 2006 17:42:22 GMT

The one at 1:12.

Rob