ALTER PROCEDURE [dbo].[Inquiry]
(
@t nvarchar(15)
)
AS
Begin SET NOCOUNT ON
Select Distinct([Product].Barcode), [Product].ItemCode, Tone.Tonality , Sized.Size, [AFS], [SOH], [TotalAdvance], [UndelPO], [Unserved], [TotalAFS], [TotalSOH] From Tone Left Join [Product] on Tone.Barcode = [Product].Barcode
Left Join Sized on Sized.SizeID = Product.SizeID
Left Join ( Select TxnDetails.TID, ( [po] + [adjadd] + [sorr] - [so] - [porr] - [break] ) as AFS From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'po' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 1 and TxnHead.Deleted is Null Group by TID) poall on poall.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'so' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 2 and TxnHead.Deleted is Null Group by TID) soall on soall.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'porr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 6 and Track = 1 and TxnHead.Deleted is Null Group by TID) powrr on powrr.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'sorr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 6 and Track = 2 and TxnHead.Deleted is Null Group by TID) sowrr on sowrr.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 3 and TxnHead.Deleted is Null Group by TID) addj on addj.TID = TxnDetails.TID
Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 5 and TxnHead.Deleted is Null Group by TID) brek on brek.TID = TxnDetails.TID Where TxnHead.Deleted is Null) af on af.TID = Tone.TID
Left Join (Select TxnDetails.TID, ([RR] - [DR] - [powrr] - [break] + [sowrr] + [adjadd]) as SOH From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TDID, ISNULL(Sum(Quantity),0) as 'RR' From RDetails Group by TDID) porr on porr.TDID = TxnDetails.TDID Left Join (Select TDID, ISNULL(Sum(Quantity),0) as 'DR' From DDetails Group by TDID) sorr on sorr.TDID = TxnDetails.TDID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'sowrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 6 and Track= 2 and Stat= 1 and TxnHead.Deleted is Null Group by TID) soret on soret.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'powrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 6 and Track= 1 and Stat= 1 and TxnHead.Deleted is Null Group by TID) poret on poret.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 3 and TxnHead.Deleted is Null Group by TID) addj on addj.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 5 and TxnHead.Deleted is Null Group by TID) brek on brek.TID = TxnDetails.TID Where TxnHead.Deleted is Null) so on so.TID = Tone.TID
Left Join (Select [Product].Barcode, ([po] + [adjadd] + [sorr] - [so] - [porr] - [break]) as TotalAFS From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join [Product] on Tone.Barcode = [Product].Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'po' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 1 and TxnHead.Deleted is Null Group by Barcode) poall on poall.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'so' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 2 and TxnHead.Deleted is Null Group by Barcode) soall on soall.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'porr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track = 1 and TxnHead.Deleted is Null Group by Barcode) powrr on powrr.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'sorr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track = 2 and TxnHead.Deleted is Null Group by Barcode) sowrr on sowrr.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 3 and TxnHead.Deleted is Null Group by Barcode) addj on addj.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 5 and TxnHead.Deleted is Null Group by Barcode) brek on brek.Barcode = Tone.Barcode Where TxnHead.Deleted is Null) toaf on toaf.Barcode = Tone.Barcode
Left Join (Select [Product].Barcode, ([RR] - [DR] - [powrr] - [break] + [sowrr] + [adjadd]) as TotalSOH
From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join Product on Product.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(RDetails.Quantity),0) as 'RR' From RDetails Left Join TxnDetails on TxnDetails.TDID = RDetails.TDID Left Join Tone on Tone.TID = TxnDetails.TID Group by Barcode) porr on porr.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(DDetails.Quantity),0) as 'DR' From DDetails Left Join TxnDetails on TxnDetails.TDID = DDetails.TDID Left Join Tone on Tone.TID = TxnDetails.TID Group by Barcode) sorr on sorr.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'sowrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track= 2 and Stat= 1 and TxnHead.Deleted is Null Group by Barcode) soret on soret.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'powrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track= 1 and Stat= 1 and TxnHead.Deleted is Null Group by Barcode) poret on poret.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 3 and TxnHead.Deleted is Null Group by Barcode) addj on addj.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 5 and TxnHead.Deleted is Null Group by Barcode) brek on brek.Barcode = Tone.Barcode Where TxnHead.Deleted is Null) toso on toso.Barcode = Tone.Barcode
Left Join (Select TxnDetails.TID, ([TotalOrder] + [Supp] - [Total] - [RetNoRR]) as UndelPO From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TxnDetails.TID as id, Sum(Quantity) as 'TotalOrder' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 1 and Ttype = 1 and TxnHead.Deleted is NULL Group by TID) pod on pod.id = Tone.TID Left Join (Select TDID as tx, ISNULL(Sum(Quantity),0) as 'Total' From RDetails Group by TDID) po on po.tx = TxnDetails.TDID Left Join (Select TxnDetails.TID as id, ISNULL(Sum(Quantity),0) as 'Supp' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 2 and Ttype = 1 Group by TID) sup on sup.id = Tone.TID Left Join (Select TxnDetails.TID, Sum(Quantity) as 'RetNoRR' From TxnDetails Left Join TxnHead on TxnHead.TxnID = TxnDetails.TxnID where Ttype = 6 and Track= 1 and Stat = 2 and TxnHead.Deleted is Null Group by TxnDetails.TID) norr on norr.TID = Tone.TID where TxnHead.Deleted is null) undel on undel.TID = Tone.TID
Left Join (Select TxnDetails.TID, ([TotalOrder] + [Adv] - [Total] - [RetNoRR]) as Unserved From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TxnDetails.TID as id, Sum(Quantity) as 'TotalOrder' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 1 and Ttype = 2 and TxnHead.Deleted is NULL Group by TID) pod on pod.id = Tone.TID
Left Join (Select TDID as tx, ISNULL(Sum(Quantity),0) as 'Total' From DDetails Group by TDID) so on so.tx = TxnDetails.TDID Left Join (Select TxnDetails.TID as id, ISNULL(Sum(Quantity),0) as 'Adv' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 2 and Ttype = 2 Group by TID) sup on sup.id = Tone.TID Left Join (Select TxnDetails.TID, ISNULL(Sum(Quantity),0) as 'RetNoRR' From TxnDetails Left Join TxnHead on TxnHead.TxnID = TxnDetails.TxnID where Ttype = 6 and Track= 2 and Stat = 2 and TxnHead.Deleted is Null Group by TxnDetails.TID) norr on norr.TID = Tone.TID where TxnHead.Deleted is null) unserv on unserv.TID = Tone.TID
Left Join (Select TxnDetails.TID, [TotalAdvance] From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TxnDetails.TID as id, ISNULL(Sum(Quantity),0) as 'TotalAdvance' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 2 and Ttype = 2 and TxnHead.Deleted is null Group by TID) pod on pod.id = Tone.TID where TxnHead.Deleted is null) adv on adv.TID = Tone.TID
where Tone.TID = @t
order by 2