Detail on location table

This question is not answered

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 Replies
  • 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

  • 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

  • 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

  • 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

  • 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