440
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
*This post is locked for comments
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
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
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 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,
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
Share
Report
All responses (
Answers (
