[View] Real-time inventory [sskc]

Select a. pluno, A. pluname, A. qty,
Case when B. qty is null then 0 else B. qty end as xsqty,
Case when C. tgqty is null then 0 else C. tgqty end as tgqty,
Case when D. jhqty is null then 0 else D. jhqty end as jhqty,
Case when e. jhqty is null then 0 else D. jhqty end as tcqty,
A. qty + case when D. jhqty is null then 0 else D. jhqty end
-Case when B. qty is null then 0 else B. qty end
-Case when C. tgqty is null then 0 else C. tgqty end
-Case when e. jhqty is null then 0 else E. jhqty end
As 'sskc'
-- Inventory settlement
From (select pluno, pluname, sum (qty) qty from getstock ()
Group by pluno, pluname)
-- Unreviewed POS sales
Left join (select pluno = A. pluno, qty = sum (A. qty)
From
(Select pluno =. pluno, pluname =. pluname, spec =. SPEC, qty =. qty, net =. net, disamt =. disamt, slamt =. net +. disamt
From (select. pluno,. pluname,. SPEC, D. qty, D. net, D. disamt, D. pluid,. optid,. dptid,. bndid
From basplucrtc,
(
Select B. pluid, qty = sum (B. qty), net = sum (B. AMT-B .DISAMT-ISNULL. disc, 0), disamt = sum (B. disamt + isnull (B. disc, 0 ))
From possrlbf C (nolock), possrldt B (nolock)
Where C. uid = B. UID and C. Valid = 0 and (C. Flag = 1 or C. Flag = 5) and B. isdel = 0
Group by B. pluid
) D
Where a. pluid = D. pluid) A, basplucrtc B, basdept E
Where a. pluid = B. pluid and B. dptid = E. ID
Union all
Select pluno =. pluno, pluname = max (. pluname), spec = max (. SPEC), qty = sum (B. qty), net = sum (B. slnet), disamt = sum (B. disamt), slamt = sum (B. slamt)
From doc21bf C, doc21dt B, basplucrtc A, basdept E
Where C. docno = B. docno and B. pluid = A. pluid and A. dptid = E. ID
And C. ctyp = 1 and isnull (C. Respiratory, 0) = 0 and C. Valid = 0
Group by A. pluno
)
Group by A. pluno) B
On a. pluno = B. pluno
-- Unreviewed group buying
Left join (select C. pluno, C. pluname, sum (B. qty) tgqty
From doc21bf A, doc21dt B, basplumain C
Where a.docnow. B .doc no and B. pluid = C. pluid and A. Valid = '0'
Group by C. pluno, C. pluname) c

On a. pluno = C. pluno
-- Unreviewed incoming goods
Left join (select C. pluno, C. pluname, sum (B. qty) jhqty from doc01bf A, doc01dt B, basplumain C
Where a.docnow. B .doc no and B. pluid = C. pluid and A. Valid = '0' and (A. ctyp! = '6 ')
Group by C. pluno, C. pluname) d
On a. pluno = D. pluno
-- Unreviewed recall
Left join (select C. pluno, C. pluname, sum (B. qty) jhqty from doc01bf A, doc01dt B, basplumain C
Where a.docnow. B .doc no and B. pluid = C. pluid and A. Valid = '0' and (A. ctyp = '6 ')
Group by C. pluno, C. pluname) E
On a. pluno = E. pluno
Where a. pluname not like '+ %'