Query- Problem

[Former Member]

I have created a query which shows the Physical Inventory Audit . This query is basically to take out the difference between the actual inventory and the system inventory and displays the difference and shows the output. Everything seems to be right except for the fact that its not grabing in all the line items for all of the material documents. For instance if a particular material document number has 12 line items it shows me only 2 or 3 of them and rest is not pulled in my report. Its working for majority of the material document numbers and not working for some of them , can anyone explain me why is it happening and what's the solution to this problem. Basically the tables I use din this query are ISEG and IKPF. And to this I have added the additional fields which draws data from bseg table such as wrbtr,bschl, hkont, shkzg, zeile from mseg,

Edited by: Alvaro Tejada Galindo on Apr 16, 2008 3:23 PM

[Sm1tje]

If query doesn't return all data, then I would say that you have entered condition in query which only apply to some of the data and not all items.

[Former Member]

If you look at my query whose download I am enlisting it down, you will find that I have given conditions which should be bringing in all the records and not only a part of it but I am unable to understand why not all records are pulling and where have i commited the mistake.

Query download:-

>QUERY_OBJECT_FILE

TTAUF

VS ZMM_PHYS_INV R

*TAUF

BS ZMM_PHYS_INV R

TDBBS

VZMM_PHYS_INV MM_USER

*DBBS

NHEADSG

VZMM_PHYS_INV J ISEG 00001 X GXXCWONG 20071029STIWARI 20080408161952 017.00 X X

TCLOGSG

V20080408STIWARI

V20080407STIWARI

V20080403STIWARI

V20080327STIWARI

V20080320STIWARI

V20080319STIWARI

V20080318STIWARI

V20080226STIWARI

V20080225STIWARI

V20080220STIWARI

*CLOGSG

TDBSA

V0100004

V0200005

V3 00054

*DBSA

TDBOB

*DBOB

TDBOS

*DBOS

TDBIF

VTEXT_IDENTIFIER CL_TEXT_IDENTIFIER

VTABLE_ACCESS_AUTHORITY CL_QUERY_TAB_ACCESS_AUTHORITY

VDDIC_ACCESS CL_QUERY_DDIC_ACCESS

*DBIF

TDBSF

V01ISEG ISEG-LIFNR 001100011100000C01000 ISEG TEXT_ISEG_LIFNR 00001

V01ISEG TEXT_ISEG_LIFNR 001120011300000C03500F X 00002

V01ISEG ISEG-ZEILI 000550005600000N00300 00003

V01ISEG ISEG-XDIFF 000340003500000C00100 ISEG TEXT_ISEG_XDIFF 00004

V01ISEG TEXT_ISEG_XDIFF 000360003700000C06000F X 00005

V01ISEG ISEG-ZLDAT 000320003300000D01000 00006

V01ISEG ISEG-MBLNR 000300003100000C01000 00007

V01ISEG ISEG-DMBTR 000280002900000P01602 F 00008

V01ISEG ISEG-MEINS 000240002500000C00300 E ISEG TEXT_ISEG_MEINS 00009

V01ISEG TEXT_ISEG_MEINS 000260002700000C03000F X 00010

V01ISEG ISEG-MENGE 000220002300000P01703 M 00011

V01ISEG ISEG-BUCHM 000200002100000P01803 M 00012

V01ISEG ISEG-USNAZ 000180001900000C01200 00013

V01ISEG ISEG-ERFMG 000480004900000P01703 M 00014

V01ISEG ISEG-MATNR 000080000900000C01800 ISEG TEXT_ISEG_MATNR 00015

V01ISEG TEXT_ISEG_MATNR 000100001100000C04000F X 00016

V01ISEG ISEG-IBLNR 000060000700000C01000 00017

V01ISEG ISEG-ZEILE 001310013200000N00400 00018

V02IKPF IKPF-LGORT 000440004500000C00400 ISEG TEXT_IKPF_LGORT 00019

V02ISEG TEXT_IKPF_LGORT 000460004700000C01600F X 00020

V02IKPF IKPF-WERKS 000400004100000C00400 ISEG TEXT_IKPF_WERKS 00021

V02ISEG TEXT_IKPF_WERKS 000420004300000C03000F X 00022

V3 ISEG DC_IND 001240012500000C00100F 00023

V3 ISEG P_KEY 001220012300000C00200F 00024

V3 ISEG LIN_ITM 001200012100000N00400F 00025

V3 ISEG ACC_NO 001140011500000C01000F 00026

V3 ISEG DIFF_AMT 001070010800000P01602FF 00027

V3 ISEG DIFF_QTY 000520005300000P01703FM 00028

*DBSF

TDBSG

VISEG 00002

VIKPF 00003

*DBSG

TDBAN

*DBAN

TDBJT

VISEG I00000000000000000000000000000000 96 13 250 200

VIKPF 00000000000000000000000000000000 87 406 250 200

*DBJT

TDBJC

V ISEG IKPF

V1 ISEG ISEG-GJAHR IKPF IKPF-GJAHR

V2 ISEG ISEG-IBLNR IKPF IKPF-IBLNR

V3 ISEG ISEG-WERKS IKPF IKPF-WERKS

*DBJC

TDBZT

*DBZT

TDBZC

V *DATA*********************** 00000000000000000

VISEG ACC_NO 01001140011500000C01001000CHARBSEG-HKONT

VISEG DC_IND 01001240012500000C00100100CHARBSEG-SHKZG

VISEG DIFF_AMT 01001070010800000P00701602CURRISEG-DMBTR

VISEG DIFF_QTY 01000520005300000P00701703QUANISEG-MENGE

VISEG LIN_ITM 01001200012100000N00400400NUMCISEG-ZEILE

VISEG P_KEY 01001220012300000C00200200CHARBSEG-BSCHL

VISEG TEXT_IKPF_LGORT 00000460004700000C01601600CHART001L-LGOBE XIKPF IKPF-LGORT

VISEG TEXT_IKPF_WERKS 00000420004300000C03003000CHART001W-NAME1 XIKPF IKPF-WERKS

VISEG TEXT_ISEG_LIFNR 00001120011300000C03503500CHARLFA1-NAME1 XISEG ISEG-LIFNR

VISEG TEXT_ISEG_MATNR 00000100001100000C04004000CHARMAKT-MAKTX XISEG ISEG-MATNR

VISEG TEXT_ISEG_MEINS 00000260002700000C03003000CHART006A-MSEHL XISEG ISEG-MEINS

VISEG TEXT_ISEG_XDIFF 00000360003700000C06006000CHARDD07D-DDTEXT XISEG ISEG-XDIFF

*DBZC

TDBZL

V *DATA*********************** 0000data: t_bkpf like standard table of mseg with header line,

V *DATA*********************** 0001 l_buzei like bseg-buzei.

V *DATA*********************** 0002data: l_xblnr like bkpf-xblnr.

V *DATA*********************** 0003data: t_mseg like standard table of mseg with header line.

VISEG ACC_NO 0000CLEAR acc_no.

VISEG ACC_NO 0001IF iseg-mblnr IS NOT INITIAL.

VISEG ACC_NO 0002*break stiwari.

VISEG ACC_NO 0003 CONCATENATE iseg-mblnr iseg-zeile INTO l_xblnr.

VISEG ACC_NO 0004 SELECT * INTO CORRESPONDING FIELDS OF TABLE t_bkpf FROM

VISEG ACC_NO 0005 bkpf

VISEG ACC_NO 0006 WHERE

VISEG ACC_NO 0007 xblnr = l_xblnr AND

VISEG ACC_NO 0008 gjahr = iseg-mjahr.

VISEG ACC_NO 0009 LOOP AT t_bkpf.

VISEG ACC_NO 0010*break stiwari.

VISEG ACC_NO 0011 l_buzei = iseg-zeili.

VISEG ACC_NO 0012 SELECT SINGLE hkont FROM bseg INTO acc_no

VISEG ACC_NO 0013 WHERE

VISEG ACC_NO 0014 belnr = t_bkpf-belnr AND

VISEG ACC_NO 0015 gjahr = t_bkpf-gjahr AND

VISEG ACC_NO 0016 bukrs = t_bkpf-bukrs AND

VISEG ACC_NO 0017 bschl <> 0 AND

VISEG ACC_NO 0018 buzei = l_buzei.

VISEG ACC_NO 0019 IF sy-subrc = 0.

VISEG ACC_NO 0020 EXIT.

VISEG ACC_NO 0021 ENDIF.

VISEG ACC_NO 0022 ENDLOOP.

VISEG ACC_NO 0023 ENDIF.

VISEG DC_IND 0000CLEAR dc_ind.

VISEG DC_IND 0001IF iseg-mblnr IS NOT INITIAL.

VISEG DC_IND 0002*break stiwari.

VISEG DC_IND 0003 CONCATENATE iseg-mblnr iseg-zeile INTO l_xblnr.

VISEG DC_IND 0004 SELECT * INTO CORRESPONDING FIELDS OF TABLE t_bkpf FROM

VISEG DC_IND 0005 bkpf

VISEG DC_IND 0006 WHERE

VISEG DC_IND 0007 xblnr = l_xblnr AND

VISEG DC_IND 0008 gjahr = iseg-mjahr.

VISEG DC_IND 0009 LOOP AT t_bkpf.

VISEG DC_IND 0010*break stiwari.

VISEG DC_IND 0011 l_buzei = iseg-zeili.

VISEG DC_IND 0012 SELECT SINGLE shkzg FROM bseg INTO dc_ind

VISEG DC_IND 0013 WHERE

VISEG DC_IND 0014 belnr = t_bkpf-belnr AND

VISEG DC_IND 0015 gjahr = t_bkpf-gjahr AND

VISEG DC_IND 0016 bukrs = t_bkpf-bukrs AND

VISEG DC_IND 0017 buzei = l_buzei.

VISEG DC_IND 0018 IF sy-subrc = 0.

VISEG DC_IND 0019 EXIT.

VISEG DC_IND 0020 ENDIF.

VISEG DC_IND 0021 ENDLOOP.

VISEG DC_IND 0022ENDIF.

VISEG DIFF_AMT 0000CLEAR diff_amt.

VISEG DIFF_AMT 0001IF iseg-mblnr IS NOT INITIAL.

VISEG DIFF_AMT 0002*break stiwari.

VISEG DIFF_AMT 0003 CONCATENATE iseg-mblnr iseg-zeile INTO l_xblnr.

VISEG DIFF_AMT 0004 SELECT * INTO CORRESPONDING FIELDS OF TABLE t_bkpf FROM

VISEG DIFF_AMT 0005 bkpf

VISEG DIFF_AMT 0006 WHERE

VISEG DIFF_AMT 0007 xblnr = l_xblnr AND

VISEG DIFF_AMT 0008 gjahr = iseg-mjahr.

VISEG DIFF_AMT 0009 LOOP AT t_bkpf.

VISEG DIFF_AMT 0010*break stiwari.

VISEG DIFF_AMT 0011 l_buzei = iseg-zeili.

VISEG DIFF_AMT 0012 SELECT SINGLE wrbtr FROM bseg INTO diff_amt

VISEG DIFF_AMT 0013 WHERE

VISEG DIFF_AMT 0014 belnr = t_bkpf-belnr AND

VISEG DIFF_AMT 0015 gjahr = t_bkpf-gjahr AND

VISEG DIFF_AMT 0016 bukrs = t_bkpf-bukrs AND

VISEG DIFF_AMT 0017 buzei = l_buzei.

VISEG DIFF_AMT 0018 IF sy-subrc = 0.

VISEG DIFF_AMT 0019 EXIT.

VISEG DIFF_AMT 0020 ENDIF.

VISEG DIFF_AMT 0021 ENDLOOP.

VISEG DIFF_AMT 0022 ENDIF.

VISEG DIFF_QTY 0000diff_qty = iseg-menge - iseg-buchm.

VISEG LIN_ITM 0000*break-point.

VISEG LIN_ITM 0001SELECT SINGLE zeile INTO lin_itm

VISEG LIN_ITM 0002 FROM mseg

VISEG LIN_ITM 0003 WHERE

VISEG LIN_ITM 0004 mblnr = iseg-mblnr AND

VISEG LIN_ITM 0005 matnr = iseg-matnr AND

VISEG LIN_ITM 0006 gjahr = iseg-gjahr.

VISEG P_KEY 0000CLEAR p_key.

VISEG P_KEY 0001IF iseg-mblnr IS NOT INITIAL.

VISEG P_KEY 0002*break stiwari.

VISEG P_KEY 0003CONCATENATE iseg-mblnr iseg-zeile INTO l_xblnr.

VISEG P_KEY 0004SELECT * INTO CORRESPONDING FIELDS OF TABLE t_bkpf FROM

VISEG P_KEY 0005 bkpf

VISEG P_KEY 0006 WHERE

VISEG P_KEY 0007 xblnr = l_xblnr AND

VISEG P_KEY 0008 gjahr = iseg-mjahr.

VISEG P_KEY 0009 LOOP AT t_bkpf.

VISEG P_KEY 0010*break stiwari.

VISEG P_KEY 0011 l_buzei = iseg-zeili.

VISEG P_KEY 0012 SELECT SINGLE bschl FROM bseg INTO p_key

VISEG P_KEY 0013 WHERE

VISEG P_KEY 0014 belnr = t_bkpf-belnr AND

VISEG P_KEY 0015 gjahr = t_bkpf-gjahr AND

VISEG P_KEY 0016 bukrs = t_bkpf-bukrs AND

VISEG P_KEY 0017 buzei = l_buzei.

VISEG P_KEY 0018 IF sy-subrc = 0.

VISEG P_KEY 0019 EXIT.

VISEG P_KEY 0020 ENDIF.

VISEG P_KEY 0021 ENDLOOP.

VISEG P_KEY 0022 ENDIF.

*DBZL

TDBPA

*DBPA

TDBWR

VISEG ISEG-DMBTR ISEG ISEG-WAERS 00038000390000005

VISEG ISEG-MENGE ISEG ISEG-MEINS 00024000250000003

VISEG ISEG-BUCHM ISEG ISEG-MEINS 00024000250000003

VISEG ISEG-ERFMG ISEG ISEG-ERFME 00050000510000003

VISEG DIFF_AMT ISEG ISEG-WAERS 00038000390000005

VISEG DIFF_QTY ISEG ISEG-MEINS 00024000250000003

*DBWR

TDBAR

VISEG ACC_NO FISEG ISEG-MBLNR X

VISEG ACC_NO FISEG ISEG-MJAHR X

VISEG ACC_NO FISEG ISEG-ZEILE X

VISEG ACC_NO FISEG ISEG-ZEILI X

VISEG DC_IND FISEG ISEG-MBLNR X

VISEG DC_IND FISEG ISEG-MJAHR X

VISEG DC_IND FISEG ISEG-ZEILE X

VISEG DC_IND FISEG ISEG-ZEILI X

VISEG DIFF_AMT FISEG ISEG-MBLNR X

VISEG DIFF_AMT FISEG ISEG-MJAHR X

VISEG DIFF_AMT FISEG ISEG-ZEILE X

VISEG DIFF_AMT FISEG ISEG-ZEILI X

VISEG DIFF_QTY FISEG ISEG-BUCHM X

VISEG DIFF_QTY FISEG ISEG-MENGE X

VISEG LIN_ITM FISEG ISEG-GJAHR X

VISEG LIN_ITM FISEG ISEG-MATNR X

VISEG LIN_ITM FISEG ISEG-MBLNR X

VISEG P_KEY FISEG ISEG-MBLNR X

VISEG P_KEY FISEG ISEG-MJAHR X

VISEG P_KEY FISEG ISEG-ZEILE X

VISEG P_KEY FISEG ISEG-ZEILI X

VISEG TEXT_IKPF_LGORT FIKPF IKPF-WERKS X

*DBAR

TDBFT

VDIFF_QTY 001090005300000

VLIN_ITM 001200013000000

*DBFT

NMAXSG_TINDX

V00132

TSGTEXT

VE000010000063Physical Inventory Reporting

VE000020000040Physical Inventory Document Items

VE000030000040Header: Physical Inventory Document

VE000040000080Physical Inventory Document Items

VE000050000080Header: Physical Inventory Document

VE000060000060Physical Inventory Document

VE000070000055PhysInvDoc

VE000080000060Material Number

VE000090000055Material

VE000100000060Text:Material Number

VE000110000060Material Number

VE000180000060Counted By

VE000190000055Name

VE000200000060Book quantity immediately prior to count

VE000210000055Book quantity

VE000220000060Quantity

VE000230000055Quantity

VE000240000060Base Unit of Measure

VE000250000055BUn

VE000260000060Text:Base Unit of Measure

VE000270000060Base Unit of Measure

VE000280000060Difference amount in local currency

VE000290000055Difference amount

VE000300000060Number of Material Document

VE000310000055Mat. Doc.

VE000320000060Date of last count

VE000330000055Count date

VE000340000060Difference posted

VE000350000055Dif

VE000360000060Text:Difference posted

VE000370000060Difference posted

VE000380000060Currency Key

VE000390000055Crcy

VE000400000060Plant

VE000410000055Plnt

VE000420000060Text:Plant

VE000430000060Plant

VE000440000060Storage Location

VE000450000055SLoc

VE000460000060Text:Storage Location

VE000470000060Storage Location

VE000480000060Quantity in unit of entry (physical inventory)

VE000490000055Quantity

VE000500000060Unit of entry (physical inventory)

VE000510000055UnE

VE000520001055Actual Difference in Quantity

VE000530000030Diff Qty

VE000540000080Diff Qty

VE000550000060Line Number

VE000560000055Item

VE001070000055Actual Difference in Amount

VE001080000030Difference in Amount

VE001090001060Difference Quantity

VE001100000060Vendor Account Number

VE001110000055Vendor

VE001120000060Text:Vendor Account Number

VE001130000060Vendor Account Number

VE001140000060G/L Account Number

VE001150000030Acc_No.

VE001200000055Line Item Number

VE001210001030Lin_Num

VE001220000060Posting Key

VE001230000030P_KEY

VE001240000060Debit/Credit Indicator

VE001250000030DC_IND

VE001300001055Lin_No

VE001310000060Item in Material Document

VE001320000055Item

*SGTEXT

TDBDP

*DBDP

ES ZMM_PHYS_INV R

<QUERY_OBJECT_FILE