11-11-2005 1:30 AM
Hi,
I have select-options: s_matnr for mchb-matnr &
s_pradh for mara-pradh & s_wdatu for lqua-wdatu.
I want to select records from mchb(matnr,wekrs,lgort)
, mara(matnr,pradh) and lqua(matnr,wdatu)
can any one tell me how do i achieve this?
points guaranteed..
cheers
kaki
11-11-2005 1:49 AM
Hi Kaki,
select mchb~matnr
mchb~werks
mchb~lgort
mara~pradh
lqua~wdatu
into table itab
from mchb
inner join mara on maramatnr = mchbmatnr
inner join lqua on lquamatnr = mchbmatnr and
lquawerks = mchbwerks
where mchb~matnr in s_matnr and
mara~pradh in s_pradh and
lqua~wdatu in s_wdatu.
Thanks.
Nethaji.
11-11-2005 1:34 AM
select maramatnr, marapradh, mchbwerks, mchblgort,lqua~wdatu into table itab
from mara
inner join on mchbmatnr = maramatnr
inner join on lquamatnr = maramatnr
where mara~matnr in s_matnr and
mara~pradh in s_pradh and
lqua~wdatu in s_wdatu.
Cheers,
Satya
11-11-2005 2:42 AM
Hi Satya,
I want to make selections based on material receiving date(lqua-wdatu)along with mchb & mara.
cheers
kaki
11-11-2005 3:09 AM
The following table join also looks into LQUA-WDATU.
This join first select material and prod. Hierarchy from MARA and check table MCHB to retrive WERKS and LGORT and
check against LQUA for WDATU.
Hi use Table joins:
select a~matnr
b~werks
b~lgort
a~pradh
c~wdatu
into table internal tab
from ( mara as a
join mchb as b on amatnr = bmatnr )
join lqua as c on cmatnr = amatnr and
cwerks = bwerks
where a~matnr in S_MATNR AND
a~PRADHA in S_PRADH AND
b~wdatu in S_wdatu.
Try to use material type it will retrieve data faster.
11-11-2005 1:49 AM
Hi Kaki,
select mchb~matnr
mchb~werks
mchb~lgort
mara~pradh
lqua~wdatu
into table itab
from mchb
inner join mara on maramatnr = mchbmatnr
inner join lqua on lquamatnr = mchbmatnr and
lquawerks = mchbwerks
where mchb~matnr in s_matnr and
mara~pradh in s_pradh and
lqua~wdatu in s_wdatu.
Thanks.
Nethaji.
11-11-2005 1:58 AM
Hi use Table joins:
<i>select a~matnr
b~werks
b~lgort
a~pradh
c~wdatu
into table internal tab
from ( mara as a
join mchb as b on amatnr = bmatnr )
join lqua as c on cmatnr = amatnr and
cwerks = bwerks
where amatnr in/FOR s_matnr/ p_PRADHA (if Parameter)and apradh in/for S_PRADH /P_PRADH(if parameter)
b~wdatu in/FOR S_wdatu.</i></i>
Try to use <u>material type</u> it will retrive data faster.
11-11-2005 3:17 AM
Looks like you are extending you select statement that you built earier. Here is some modify code from the previous post. Its pretty much like what has already been posted here.
report zrich_0001 .
tables: mchb, mara, lqua.
data: begin of itab occurs 0,
matnr type mchb-matnr,
werks type mchb-werks,
lgort type mchb-lgort,
pradh type mara-prdha,
wdatu type lqua-wdatu,
end of itab.
select-options: s_matnr for mchb-matnr,
s_prdha for mara-prdha,
s_wdatu for lqua-wdatu.
start-of-selection.
select * into corresponding fields of table itab
from mchb
inner join mara
on mchb~matnr = mara~matnr
inner join lqua
on mchb~matnr = lqua~matnr
and mchb~werks = lqua~werks
where mchb~matnr in s_matnr
and mara~prdha in s_prdha
and lqua~wdatu in s_wdatu.
Regards,
Rich Heilman
11-11-2005 3:37 AM
Select a~matnr a~werks a~lgort b~pradh c~wdatu
into corresponding fields of table itab
from mchb as a join mara as b
on a~matnr = b~matnr
lqua as join c
on a~matnr = c~matnr
where a~matnr IN s_matnr
and b~pradh IN s_pradh
and c~WDATU IN s_wdatu.
Hope this helps u.
11-11-2005 4:03 AM
select mara~matnr
mara~pradha
mchb~werks
mchb~lgort
lqua~wdatu
into table itab
from mara
inner join on mchbmatnr = maramatnr
inner join on lquamatnr = maramatnr
where mara~matnr in s_matnr and
mara~pradha in s_pradh and
lqua~wdatu in s_wdatu.
11-11-2005 4:13 AM
Hi,
Since there is no common key fields in LQUA and other tables,I prefer to use for all entries for that.Here is the complete program.Kindly reward points if it helps you.
types : begin of ty1,
matnr type mara-matnr,
pradh type mara-pradh,
werks type mchb-werks,
lgort type mchb-lgort,
end of ty1.
types : begin of ty2,
matnr type lqua-matnr,
wdatu type lqua-wdatu,
end of ty2.
types : begin of ty,
matnr type mara-matnr,
pradh type mara-pradh,
werks type mchb-werks,
lgort type mchb-lgort,
wdatu type lqua-wdatu,
end of ty.
data : itab type standard table of ty,
itab1 type standard table of ty1,
itab2 type standard table of ty2,
wa type ty,
wa1 type ty1,
wa2 type ty2.
select maramatnr marapradh mchbwerks mchblgort
into table itab1
from mara inner join mchb
on maramatnr = mchbmatnr
where mara~matnr in S_matnr
and mara~pradh in s_pradh.
select matnr wdatu from lqua into table itab2
from lqua
for all entries in itab1
where matnr = itab1-matnr
and wdatu in s_wdatu.
loop at itab1 into wa1.
move-corresponding wa1 to wa.
loop at itab2 into wa2 where matnr = wa1-matnr.
wa-wdatu = wa1-wdatu.
append wa to itab.
endloop.
endloop.
sort itab.
delete adjacent duplicates comparing all fields.
loop at itab into wa.
write wa.
endloop.
11-11-2005 5:36 AM
Hi,
Here is my program...
I have five select-options...
s_matnr,s_werks,s_lgort,s_wdatu,s_prdha.
So end user can select any of this options.Iam trying to write select statements based on this.
REPORT YMINVAGE no standard page heading
line-size 145
line-count 65
message-id Z1.
tables : mchb, "Batch Stocks
mara, "Material Master
mseg,
lqua, "Storage bin location
mvke.
data: begin of t_mchb occurs 0,
matnr like zpartcnv-zzpartfj,
matnr like mara-matnr,
werks like mchb-werks,
lgort like mchb-lgort,
ersda like mchb-ersda, " creation date
charg like mchb-charg, " Batch Number
clabs like mchb-clabs, " unrestricted quantity
cspem like mchb-cspem, " Blocked stock
prdha like mara-prdha, " product hierarchy
wdatu like lqua-wdatu, " gr date
qty type i,
price(8) type p decimals 2,
amount(8) type p decimals 2,
end of t_mchb.
data: begin of t_qty occurs 0,
matnr like mchb-matnr,
werks like mchb-werks,
lgort like mchb-lgort,
wdatu like lqua-wdatu,
clabs like mchb-clabs,
cspem like mchb-cspem,
charg like mchb-charg,
price(8) type p decimals 2,
qty30 like mchb-clabs,
qty60 like mchb-clabs,
qty90 like mchb-clabs,
qtyful like mchb-clabs,
amt30 like mseg-dmbtr,
amt60 like mseg-dmbtr,
amt90 like mseg-dmbtr,
amtful like mseg-dmbtr,
end of t_qty.
data: begin of t_coll occurs 0,
matnr like mchb-matnr,
werks like mchb-werks,
lgort like mchb-lgort,
clabs like mchb-clabs,
cspem like mchb-cspem,
charg like mchb-charg,
amount(8) type p decimals 2,
date30 type d,
date60 type d,
date90 type d,
fdate type d,
end of t_coll.
data: begin of t_mara occurs 0,
matnr like mara-matnr,
prdha like mara-prdha,
end of t_mara.
data : zukurs type p decimals 3,
zffact like tcurr-ffact,
ztfact like tcurr-tfact.
selection-screen begin of block sel with frame title text-001.
select-options : s_matnr for mchb-matnr.
select-options : s_prdha for mara-prdha.
select-options : s_werks for mchb-werks.
select-options : s_lgort for mchb-lgort.
select-options : s_wdatu for lqua-wdatu.
selection-screen end of block sel.
selection-screen begin of block sel1 with frame title text-002.
parameters: p_actstk radiobutton group g1 default 'X',
p_blkstk radiobutton group g1.
selection-screen end of block sel1.
call function 'Z_FILL_MATNR_SELTAB'
TABLES
s_matnr = s_matnr.
start-of-selection.
perform get_data.
perform process_data.
perform display_data.
top-of-page.
perform set_page_header.
&----
*& Form process_data
&----
text
----
form get_data.
select matnr prodh from mvke into table t_mara where
matnr in s_matnr and
prodh in s_prdha.
*
select * into corresponding fields of table t_mchb
from mchb
inner join mara
on mchbmatnr = maramatnr
inner join lqua
on mchbmatnr = lquamatnr
and mchbwerks = lquawerks
where mchb~matnr in s_matnr
and mara~prdha in s_prdha
and lqua~wdatu in s_wdatu.
*
loop at t_mara.
CALL FUNCTION 'CONVERSION_EXIT_MATN1_OUTPUT'
EXPORTING
INPUT = t_mara-matnr
IMPORTING
OUTPUT = t_mara-matnr.
modify t_mara.
endloop.
CHECK NOT t_mara[] IS INITIAL.
if p_actstk = 'X'.
select matnr werks lgort ersda charg clabs
into corresponding fields of table t_mchb from mchb
FOR ALL ENTRIES IN t_mara
where matnr = t_mara-matnr and
werks in s_werks and
lgort in s_lgort and
clabs > 0.
else.
select matnr werks lgort ersda charg clabs
into corresponding fields of table t_mchb from mchb
FOR ALL ENTRIES IN t_mara
where matnr = t_mara-matnr and
werks in s_werks and
lgort in s_lgort and
cspem > 0.
endif.
loop at t_mchb.
for date of goods receipt
select single * from lqua where matnr = t_mchb-matnr and
werks = t_mchb-werks and
charg = t_mchb-charg and
wdatu in s_wdatu.
t_mchb-wdatu = lqua-wdatu.
if sy-subrc = 0.
modify t_mchb.
clear t_mchb.
endif.
endloop.
loop at t_mchb.
for unit price
select single * from mseg where matnr = t_mchb-matnr and
charg = t_mchb-charg and
werks = t_mchb-werks.
clear zukurs.
perform get_usd.
if zukurs <> 0.
t_mchb-price = ( mseg-dmbtr / mseg-menge ) / ( zukurs / 100 ).
else.
t_mchb-price = ( mseg-dmbtr / mseg-menge ) / '1.6'.
endif.
if sy-subrc = 0.
modify t_mchb.
clear t_mchb.
endif.
endloop.
if sy-subrc ne 0.
message i016.
leave list-processing.
endif.
loop at t_mchb.
t_coll-matnr = t_mchb-matnr.
t_coll-werks = t_mchb-werks.
t_coll-lgort = t_mchb-lgort.
append t_coll.
clear t_coll.
endloop.
sort t_coll.
delete adjacent duplicates from t_coll.
endform. "get_data
&----
*& Form process_data
&----
text
----
form process_data.
data:age type d.
data: g_qty30 type i,
g_qty60 type i,
g_qty90 type i,
g_qtyful type i,
g_amt30 like t_qty-amt30,
g_amt60 like t_qty-amt30,
g_amt90 like t_qty-amt30,
g_amtful like t_qty-amt30.
loop at t_coll.
loop at t_mchb where matnr = t_coll-matnr
and werks = t_coll-werks
and lgort = t_coll-lgort.
age = t_mchb-wdatu.
if age lt 31.
if p_actstk = 'X'.
g_qty30 = g_qty30 + t_mchb-clabs.
else.
g_qty30 = g_qty30 + t_mchb-cspem.
endif.
elseif age gt 30 and age lt 61.
if p_actstk = 'X'.
g_qty60 = g_qty60 + t_mchb-clabs.
else.
g_qty60 = g_qty60 + t_mchb-cspem.
endif.
elseif age gt 60 and age lt 91.
if p_actstk = 'X'.
g_qty90 = g_qty90 + t_mchb-clabs.
else.
g_qty90 = g_qty90 + t_mchb-cspem.
endif.
else.
if p_actstk = 'X'.
g_qtyful = g_qtyful + t_mchb-clabs.
else.
g_qtyful = g_qtyful + t_mchb-cspem.
endif.
endif.
endloop.
for amount calculation
if t_mchb-price = '0.00'.
t_mchb-price = '1'.
endif.
g_amt30 = g_qty30 * t_mchb-price.
g_amt60 = g_qty60 * t_mchb-price.
g_amt90 = g_qty90 * t_mchb-price.
g_amtful = g_qtyful * t_mchb-price.
t_qty-amt30 = g_amt30.
t_qty-amt60 = g_amt60.
t_qty-amt90 = g_amt90.
t_qty-amtful = g_amtful.
t_qty-matnr = t_mchb-matnr.
t_qty-werks = t_mchb-werks.
t_qty-lgort = t_mchb-lgort.
t_qty-wdatu = t_mchb-wdatu.
t_qty-clabs = t_mchb-clabs.
t_qty-cspem = t_mchb-cspem.
t_qty-price = t_mchb-price.
*for quantities
t_qty-qty30 = g_qty30.
t_qty-qty60 = g_qty60.
t_qty-qty90 = g_qty90.
t_qty-qtyful = g_qtyful.
if g_qty30 ne 0 or g_qty60 ne 0
or g_qty90 ne 0 or g_qtyful ne 0.
append t_qty.
endif.
clear t_qty.
clear: g_qty30, g_qty60, g_qty90, g_qtyful,
g_amt30, g_amt60, g_amt90, g_amtful.
endloop.
sort t_qty by wdatu.
endform. "process_data
&----
*& Form display_data
&----
text
----
form display_data.
data: count type i.
data: l_ind value '1'.
loop at t_qty.
count = count + 1.
if l_ind = '1'.
l_ind = '0'.
format color col_total intensified off .
else.
l_ind = 1.
format color col_normal.
endif.
write:/1(24) t_qty-matnr color 2,
24(4) t_qty-werks,
32(4) t_qty-lgort.
WRITE:41(1) SY-VLINE NO-GAP.
WRITE:68(1) SY-VLINE NO-GAP.
WRITE:95(1) SY-VLINE NO-GAP.
WRITE:119(1) SY-VLINE NO-GAP.
if age < 31.
write: 42(13) t_qty-qty30,
55(13) t_qty-amt30.
elseif age < 61.
write: 69(11) t_qty-qty60,
84(11) t_qty-amt60.
elseif age < 91.
write: 96(11) t_qty-qty90,
108(11) t_qty-amt90.
else.
write: 120(11) t_qty-qtyful,
131(12) t_qty-amtful.
endif.
endloop.
uline.
write count.
endform. "display_data
&----
*& Form GET_USD
&----
text
----
form get_usd.
call function 'READ_EXCHANGE_RATE'
exporting
date = lqua-wdatu
foreign_currency = 'USD'
local_currency = 'SGD'
type_of_rate = 'M'
importing
exchange_rate = zukurs
foreign_factor = zffact
local_factor = ztfact
valid_from_date =
exceptions
no_rate_found = 1
no_factors_found = 2
no_spread_found = 3
others = 4.
endform. " GET_USD
&----
*& Form set_page_header
&----
text
----
FORM set_page_header.
call function 'Z_REPORT_TITLE'
EXPORTING
line_size = sy-linsz
sy_title = 'Inventory Aging Report'
uline = 'X'
first_page_only = ' '.
write:/1(145) space color 1.
write :1(20) 'Material' color col_heading,
23(9) 'Plant' color col_heading,
31(15) 'Str.loc' color col_heading,
51(18) '0 - 30 days' color col_heading,
76(18) ' 31 - 60 days ' color col_heading,
100(15) ' 61 - 90 days ' color col_heading,
125(14) ' > 91 days ' color col_heading.
write:/1(145) space color 1.
write:48(11) 'Qty' color col_heading,
58(10) 'Amnt(USD)' color col_heading.
write:73(10) 'Qty' color col_heading,
82(10) 'Amnt(USD)' color col_heading.
write:98(10) 'Qty' color col_heading,
108(10) 'Amnt(USD)' color col_heading.
write:122(10) 'Qty' color col_heading,
132(10) 'Amnt(USD)' color col_heading.
uline.
endform. "set_page_header
11-11-2005 5:41 AM
Dont use Selct inside the loop
loop at t_mchb.
* for date of goods receipt
select single * from lqua where matnr = t_mchb-matnr and
werks = t_mchb-werks and
charg = t_mchb-charg and
wdatu in s_wdatu.
In the previous post i have mentioned how to retrieve the data right?
Refer that.