topic Re: help in sql trace for vf03 in Application Development and Automation Discussions

help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 07:00:33 GMT

Hi,

i was tracing the sql statments present in vf03 transaction for the invoice no.'s with accounting document over all status as not clear.

i want to know the flow of the sql statements right from the user giving the invoice no. as input to the document flow window to know from where and how it is picking the accounting doucment over all processing status

waiting for ur replies

regards,

maqsood

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 07:13:09 GMT

ST05 provides raw SQL reads. Use SE30 to scan through the function modules and subroutines which VF03 spends maximum time in. This will help you narrow down your search.

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 07:22:34 GMT

Hi anand,

can u plz expalin me in detail what you want to tell me.

in se30 we will see the performance issues specially realted to database hits.

how it can help me in that.

regards,

maqsood

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 07:23:10 GMT

Hi maqsood

the overall status of the invoice number is retrieved from VBUK in vf03

plz close the previous thread.

regards

kishore

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 07:37:50 GMT

Hi Kishore,

thanx for ur replies.

but i also want to know flow of the sql statements right from the user giving the invoice no. in vf03 till we get the document flow window in that the over all processing status of the accounting document.

my superior is mainly intereset in the flow of sql statements and the tables and the fields from which it is gitting in vf03 transaction.

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 07:55:09 GMT

SE30 is meant for checking performance, but it is also a very useful tool to check important portions of code.

Whenever a transaction like VF03 is run, lots of activities are done: user settings are checked, data entered by user is validated, data fetching is done and the data display is done. A good guess is that the transaction spends the most of its time fetching data from database, right? Then the top few hits in SE30 are the areas of code that select data for invoice # etc, that the user has entered.

By investigating these top few hits, you will get a good idea of the flow of the transaction: what tables are being checked, what is the sequence of data etc. It has worked for me a few times...

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 08:18:02 GMT

hi MAQSOOD

THERE WILL BE LOT OF TRANSACTION TAKING PLACE IN VF03 LIKE DATA RETRIEVAL ,USER CHECKS, CONVERSION.

If you want the data retrieval the flow

will be

1) selection from VBRK using VBELN by the FM

CALL FUNCTION 'RV_INVOICE_DOCUMENT_READ'

EXPORTING

ACTIVITY = ' '
KONV_READ = ' '
NO_NAST = ' '

VBRK_I =

I_NO_AUTHORITY_CHECK = ' '
OIA_UNZIP_FEES =
IMPORTING
VBRK_E =
VBUK_E =

TABLES

XKOMV =

XVBPA =

XVBRK =

XVBRP =

XKOMFK =
XVBFS =
XTHEAD =
XVBSS =
EXCEPTIONS
NO_AUTHORITY = 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.

this FM recieves all the infornmation regardin the given invoice number.

then it receives the Billing documetns from TVFK

and based these document types the particular screens are selected and in the mean time there will lot of user checks

Then comes Item information from VBRP.

using the vbeln it receives the item level information from VBRP, MARA, etc..

I don't know the exact FM which recieves the item information in vf03. you can find it using debugging.

at last the item information is displayed using table control.

this may not be accurate but this is logic used in vf03

hope it will be useful

regards

kishore

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 08:28:54 GMT

You can use fm RV_ORDER_FLOW_INFORMATION to find out the flow of invoice.

If you want to know the accounting doc.:

---> VBRK-RFBSK = 'C'.

---> Header data: this select uses a std index 4

SELECT * FROM BKPF WHERE AWTYP = 'VBRK'

AND AWKEY = VBRK-VBELN.

EXIT.

ENDSELECT.

> Item data

SELECT * FROM BSEG INTO T_BSEG

WHERE BUKRS = BKPF-BUKRS

AND BELNR = BKPF-BELNR

AND GJAHR = BKPF-GJAHR.

Max

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 09:12:45 GMT

HI maqsood

please don't forget to close the threads when your problem is solved.

reward the helpful answers

regards

kishore

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 10:16:38 GMT

Hi kishore,

how to close it.

i have rewarded points for helpfull answers

regards,

maqsood

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 10:24:36 GMT

Hi maqsood

the thread will be closed if you reward any answer as SOLVED PROBLEM.

please close the previous thread 'need help' also.

thanks

kishore

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 11:35:34 GMT

Hi Kishore,

as u have said that invioce no. is retrive from VBUK table. i have traced the select statement through which it is retriving.

Program :SAPMV75F

subroutine: XVBUK_FUELLEN

select vbeln vbtyp kostk koquk lvstk wbstk gbstk into corresponding fields of table xvbuk from vbuk or all entries in beleg

where vbeln = beleg-vbeln and vbtyp = beleg-vbtyp.

then i tried to trace from where its getting the vbtype field value which is giving as one of the i/p in this select statment.

and i think it is taking from VBFA-VBTYP_N field. (correct me if im wrong)

can u tell me what does this N, S and L status stands for in this filed.

regards

maqsood

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 11:40:49 GMT

The field VBTYP indicate the type of sale document you're seeing, all the values are in domain VBTYP:

A Inquiry

B Quotation

C Order

D Item proposal

E Scheduling agreement

F Scheduling agreement with external service agent

G Contract

H Returns

I Order w/o charge

J Delivery

K Credit memo request

L Debit memo request

M Invoice

N Invoice cancellation

O Credit memo

P Debit memo

Q WMS transfer order

R Goods movement

S Credit memo cancellation

T Returns delivery for order

U Pro forma invoice

V Purchase order

W Independent reqts plan

X Handling unit

0 Master contract

1 Sales activities (CAS)

2 External transaction

3 Invoice list

4 Credit memo list

5 Intercompany invoice

6 Intercompany credit memo

7 Delivery/shipping notification

8 Shipment

a Shipment costs

e Allocation table

g Rough Goods Receipt (only IS-Retail)

h Cancel goods issue

i Goods receipt

j JIT call

r TD Shipment (IS-Oil Only)

s Loading Confirmation, Reposting (IS-Oil Only)

t Gain/Loss (IS-Oil Only)

u Placing Back in Stock (IS-Oil Only)

v Two-Step Goods Receipt (IS-Oil Only)

w Reservation (IS-Oil Only)

x Loading Confirmation, Goods Receipt (IS-Oil Only)

$ (AFS)

Max

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 11:44:47 GMT

is it VBUK-GBSTK FIELD

AND FOR INVOICE NO. WHOSE VBFA-VBTYP_N FILED VALUE IS "s" (credit memo cancelled) WHY I CAN NOT SEE A RECORD IN VBUK TABLE.

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 11:59:20 GMT

the final logic which im using is as follows

in vbfa table input invoice no.

and get the VBTYP_N field value

in vbuk table input VBELN & VBTYP and if i get the

GBSTK filed value as c (completely processed) then it will show the overall processing status for the accounting doc. in vf03 as cleared.

correct me if im wrong

regards,

maqsood

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 12:07:14 GMT

HI maqsood

no need of vbtyp in vbuk.

you only need vbeln for getting the status information gbstk which is correct.

for that link VBRK-vbeln and VBUK-vbeln then get the data.

vbtyp is in vbuk and for invoice it is 'M'.

regards

kishore

Message was edited by: Harikishore Sreenivasulu

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 12:35:59 GMT

hi kishore,

then y is it that select statement in vfo3 is using that field as one of the input.

program: sAPMV75F

subroutine : XVBUK_FUELLEN

select vbeln vbtyp kostk koquk lvstk wbstk gbstk into corresponding fields of table xvbuk from vbuk or all entries in beleg where vbeln = beleg-vbeln and vbtyp = beleg-vbtyp.

regards,

maqsood

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 12:44:52 GMT

I can't understand what you're looking for?

Do you know if the accounting document is cleared?

If it's so, you can't get out this information from sale flow, but you have to read the customer items in accounting:

A) IF VBRK-RFBSK = 'C' => Accounting doc. is created.

B) Find the document from BKPF

C) Find the items from BSEG where KOART = D

IF BSEG-AUGDT <> '00000000' => Invoice is cleared

Max

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 14:20:03 GMT

Hi max,

i will tell u what my problem is.

when i use ur logic for tracing the invoice no.'s whose accounting doc. overall processing status is 'cleared' or 'not cleared'.

its the same result which im getting through vf03.

but ur logic is not the logic which is implemented in vf03.

and i want to know that logic. from where it is getting that data ( from which tables and how )

and what kishore told about the vf03 logic ( the overall status of the invoice number is retrieved from VBUK in

vf03) is seems to be false as its not matching with the result of ur logic and with the o/p of vf03 when the status is 'not cleared'.

and so i want to know the vf03 logic for accounting doc. overall processing status

regards,

maqsood

Re: help in sql trace for vf03

Former Member — Wed, 15 Feb 2006 15:15:21 GMT

You should look at the code of std fm RV_ORDER_FLOW_INFORMATION, here you can see all selects the system does.

Anyway the tables are VBFA (document flow), VBUK and VBUP. But you can't find that information here, because it's only in accounting not in sales.

So perhaps the abap code you're looking for it's this:

if vbrk-vbeln <> vbfa_tab-vbeln.

perform vbrk_ermitteln using vbfa_tab-vbeln.

check sy-subrc = 0.

check xvbrk-rfbsk = 'C' or xvbrk-rfbsk = 'J'.

read table xdoc_num with key vbeln = vbfa_tab-vbeln

binary search.

tabix_xdoc = sy-tabix.

if sy-subrc = 0.

check xdoc_num-not_found is initial.

else.

refresh xbkpf.

call function 'FI_DOCUMENT_READ'

exporting

i_awtyp = 'VBRK'

i_awref = vbfa_tab-vbeln

i_awsys = vbrk-logsys

i_bukrs = vbrk-bukrs

i_gjahr = vbrk-gjahr

tables

t_bkpf = xbkpf

t_bseg = xbseg

exceptions

wrong_input = 1

not_found = 2.

describe table xbkpf lines sy-tabix.

if sy-tabix ne 0.

Delete documents from other fiscal year

if sy-tabix > 1.

CALL FUNCTION 'FI_PERIOD_DETERMINE'

EXPORTING

i_budat = vbrk-fkdat

i_bukrs = vbrk-bukrs

IMPORTING

e_gjahr = da_gjahr

e_poper = da_poper

EXCEPTIONS

fiscal_year = 1

period = 2

period_version = 3

posting_period = 4

special_period = 5

version = 6

posting_date = 7

OTHERS = 8.

IF sy-subrc = 0.

CONCATENATE da_gjahr da_poper INTO vbrk-gjahr.

loop at xbkpf where gjahr ne vbrk-gjahr.

delete xbkpf index sy-tabix.

endloop.

ENDIF.

endif.

loop at xbkpf.

clear l_xdoc_num-status.

move 'BKPF' to l_xdoc_num-awtyp.

move xbkpf-bukrs to l_xdoc_num-bukrs.

move xbkpf-belnr to l_xdoc_num-docnr.

move xbkpf-cpudt to l_xdoc_num-cpudt.

move xbkpf-gjahr to l_xdoc_num-gjahr.

l_xdoc_num-vbeln = vbfa_tab-vbeln.

l_xdoc_num-logsys = vbrk-logsys.

clear l_xdoc_num-not_found.

loop at xbseg where bukrs eq xbkpf-bukrs

and belnr eq xbkpf-belnr

and gjahr eq xbkpf-gjahr

and ( koart eq 'D' or koart eq 'K' ).

endloop.

if sy-subrc ne 0.

l_xdoc_num-status = 'C'.

else.

loop at xbseg where not augbl is initial

and ( koart eq 'D' or koart eq 'K' ).

endloop.

if not sy-subrc is initial.

SET STATUS TO 'No items are cleared'

l_xdoc_num-status = 'A'.

else.

loop at xbseg where augbl is initial

and umskz ne 'A'

and vorgn ne 'AZUM'

and ( koart eq 'D' or koart eq 'K' ).

endloop.

if not sy-subrc is initial.

Set status to 'All items are cleared'

l_xdoc_num-status = 'C'.

endif.

Otherwise: If there are customer/vendor positions where AUGBL is
filled and some other ones where AUGBL is blank then set status
to 'Partially cleared'

if l_xdoc_num-status is initial.

l_xdoc_num-status = 'B'.

endif.

append l_xdoc_num.

endloop.

insert lines of l_xdoc_num into xdoc_num index tabix_xdoc.

This is extracted from include LV05CF01

Max