Detail on location table

(0) Share

Report

Posted on by manuel vela

430

Hi All

Is there any way to join Location, LotSerT, INTran, TrnsfrDoc from query script??? i need to get the detail of items in the Location table.

I will appreciate your help thanks

All responses (5)

Answers (0)

Suggested answer

Ram Peru 2,830 on at

Like (0)

Report

RE: Detail on location table

Hi Manuel,

Could you please try this below SQL Script.

SELECT

TrnsfrDoc.BatNbr,

InvtID =RTRIM(INTran.InvtID)+' '+RTRIM(INTran.TranDesc),

--LotSerMst.LotSerNbr,

--INTran.InvtID,

--INTran.TranDesc,

INTran.TranAmt,

INTran.Qty,

Name=RTRIM(INTran.WhseLoc)+' '+RTRIM(LocTable.Descr),

INTran.WhseLoc,

--Nombre=LocTable.Descr,

TrnsfrDoc.RefNbr,

TrnsfrDoc.Comment,

TotalDays=DATEDIFF ( day, TrnsfrDoc.expecteddate , getdate() ),

Month=datepart(MM,TrnsfrDoc.TranDate),

Year=datepart(YYYY,TrnsfrDoc.TranDate),

TrnsfrDoc.TranDate,

TrnsfrDoc.expecteddate,

Loc1.QtyAvail,

Loc1.QtyOnHand

FROM

(

TrnsfrDoc INNER JOIN INTran ON TrnsfrDoc.BatNbr=INTran.BatNbr AND TrnsfrDoc.RefNbr=INTran.RefNbr

AND INTran.AcctDist=0

--AND TrnsfrDoc.BatNbr='093242'

AND TrnsfrDoc.ToSiteID like ('PR%')

--AND INTran.WhseLoc ='11VTAS-042'

AND INTran.InvtID ='422COR'

AND INTran.InvtMult=1

AND INTran.Rlsed=1

)

INNER JOIN

(

Select SUM(Location.QtyOnHand) 'QtyOnHand',SUM(Location.QtyAvail) 'QtyAvail',Location.InvtId,Location.SiteID,Location.WhseLoc

from Location Inner Join INTran ON Location.InvtID=INTran.InvtId

AND Location.SiteID=INTran.SiteID AND Location.WhseLoc=INTran.WhseLoc AND Location.QtyOnHand <>0

Group by Location.InvtId,Location.SiteID,Location.WhseLoc

) As Loc1 ON Loc1.InvtID=INTran.InvtId AND Loc1.SiteID=INTran.SiteID AND Loc1.WhseLoc=INTran.WhseLoc

INNER JOIN LocTable on Loc1.SiteID=LocTable.SiteID AND Loc1.WhseLoc=LocTable.WhseLoc

INNER JOIN LotSerMst on Loc1.InvtID = LotSerMst.InvtID and Loc1.SiteID=LotSerMst.SiteID AND Loc1.WhseLoc=LotSerMst.WhseLoc

AND Loc1.InvtID=LotSerMst.InvtID and LotSerMst.QtyOnHand <> 0

I believe this Script may solve you problem.

Thanks,

Perumalsamy.R
manuel vela 430 on at

Like (0)

Report

RE: Detail on location table

Hi, i really appreciate your time and your help, i ran the script but it duplicate "in" transactions

for example in location.whseloc a have 1 item with 2 diferent serial number and the script return me 4 lines

with duplicate serial number

any other option?

thanks a lot
Ram Peru 2,830 on at

Like (0)

Report

RE: Detail on location table

Hello Manuel,

Could you please try this below Query in your Environment.

SELECT

TrnsfrDoc.BatNbr,

InvtID =RTRIM(INTran.InvtID)+' '+RTRIM(INTran.TranDesc),

--LotSerMst.LotSerNbr,

--INTran.InvtID,

--INTran.TranDesc,

INTran.TranAmt,

INTran.Qty,

Name=RTRIM(INTran.WhseLoc)+' '+RTRIM(LocTable.Descr),

INTran.WhseLoc,

--Nombre=LocTable.Descr,

TrnsfrDoc.RefNbr,

TrnsfrDoc.Comment,

TotalDays=DATEDIFF ( day, TrnsfrDoc.expecteddate , getdate() ),

Month=datepart(MM,TrnsfrDoc.TranDate),

Year=datepart(YYYY,TrnsfrDoc.TranDate),

TrnsfrDoc.TranDate,

TrnsfrDoc.expecteddate,

Loc1.QtyAvail,

Loc1.QtyOnHand

FROM

(

TrnsfrDoc INNER JOIN INTran ON TrnsfrDoc.BatNbr=INTran.BatNbr AND TrnsfrDoc.RefNbr=INTran.RefNbr

AND INTran.AcctDist=0

--AND TrnsfrDoc.BatNbr='093242'

AND TrnsfrDoc.ToSiteID like ('PR%')

--AND INTran.WhseLoc ='11VTAS-042'

AND INTran.InvtID ='422COR'

AND INTran.InvtMult=1

AND INTran.Rlsed=1

)

INNER JOIN

(

Select SUM(Location.QtyOnHand) 'QtyOnHand',SUM(Location.QtyAvail) 'QtyAvail',Location.InvtId,Location.SiteID,Location.WhseLoc

from Location Inner Join INTran ON Location.InvtID=INTran.InvtId

AND Location.SiteID=INTran.SiteID AND Location.WhseLoc=INTran.WhseLoc AND Location.QtyOnHand <>0

Group by Location.InvtId,Location.SiteID,Location.WhseLoc

) As Loc1 ON Loc1.InvtID=INTran.InvtId AND Loc1.SiteID=INTran.SiteID AND Loc1.WhseLoc=INTran.WhseLoc

INNER JOIN LocTable on Loc1.SiteID=LocTable.SiteID AND Loc1.WhseLoc=LocTable.WhseLoc

INNER JOIN LotSerMst on Loc1.SiteID=LotSerMst.SiteID AND Loc1.WhseLoc=LotSerMst.WhseLoc

AND Loc1.InvtID=LotSerMst.InvtID and LotSerMst.QtyOnHand <> 0

This SQL Script may solve your problem.

Thanks,

Perumalsamy R
manuel vela 430 on at

Like (0)

Report

RE: Detail on location table

Hi Perumal,

I really apreciate your help, unfortunately the scritp does not help me too much, this is my script but someting is wrong. what i need is to know is witch transfers (10-040.00) where made to sum in the location.qtyonhand.

Sorry for my english

Thanks to all

SELECT

TrnsfrDoc.BatNbr,

InvtID =RTRIM(INTran.InvtID)+' '+RTRIM(INTran.TranDesc),

--LotSerMst.LotSerNbr,

--INTran.InvtID,

--INTran.TranDesc,

INTran.TranAmt,

INTran.Qty,

Name=RTRIM(INTran.WhseLoc)+' '+RTRIM(LocTable.Descr),

INTran.WhseLoc,

--Nombre=LocTable.Descr,

TrnsfrDoc.RefNbr,

TrnsfrDoc.Comment,

TotalDays=DATEDIFF ( day, TrnsfrDoc.expecteddate , getdate() ),

Month=datepart(MM,TrnsfrDoc.TranDate),

Year=datepart(YYYY,TrnsfrDoc.TranDate),

TrnsfrDoc.TranDate,

TrnsfrDoc.expecteddate,

Location.QtyAvail,

Location.QtyOnHand

FROM (TrnsfrDoc INNER JOIN INTran ON TrnsfrDoc.BatNbr=INTran.BatNbr AND TrnsfrDoc.RefNbr=INTran.RefNbr

AND INTran.AcctDist=0

--AND TrnsfrDoc.BatNbr='093242'

AND TrnsfrDoc.ToSiteID like ('PR%')

--AND INTran.WhseLoc ='11VTAS-042'

AND INTran.InvtID ='422COR'

AND INTran.InvtMult=1

AND INTran.Rlsed=1

)

INNER JOIN Location

ON Location.InvtID=INTran.InvtId

AND Location.SiteID=INTran.SiteID

AND Location.WhseLoc=INTran.WhseLoc

AND Location.QtyOnHand <>0

INNER JOIN LocTable on Location.SiteID=LocTable.SiteID AND Location.WhseLoc=LocTable.WhseLoc

--INNER JOIN LotSerMst on Location.SiteID=LotSerMst.SiteID AND Location.WhseLoc=LotSerMst.WhseLoc and Location.InvtID=LotSerMst.InvtID and LotSerMst.QtyOnHand<>0
Ram Peru 2,830 on at

Like (0)

Report

RE: Detail on location table

Hello Manuel,

Here is the SQL script to join the Location,LotSerT, InTran, TrnsfrDoc tables.

Select Location.* from Location join LotSerT on Location.InvtID = LotSerT.InvtID

Join INTran on Location.InvtID = INTran.InvtID

Join TrnsfrDoc on INTran.BatNbr = TrnsfrDoc.BatNbr and INTran.RefNbr = TrnsfrDoc.RefNbr

Where Location.InvtID = 'Inventory Id as your wish'

This SQL Script will help you to get the location table details.

Thanks,

Perumalsamy R