SQL Query by Sanjay - Allocated Quantities

select  'Allocated Returns in Purchase Order Processing' as AllocType, A.POPRCTNM as document,A.ITEMNMBR, QTYRESERVED as allocatedqty

from POP10500 A join POP10310 B

on A.POPRCTNM=B.POPRCTNM AND

A.RCPTLNNM=B.RCPTLNNM AND

A.ITEMNMBR=B.ITEMNMBR

where  A.QTYRESERVED > 0

 

union all

 

select  'Allocated Returns in Purchase Order Processing' as AllocType,A.POPRCTNM as document,A.ITEMNMBR, QTYRESERVED as allocatedqty

 from POP10500 A join POP10310 B

on B.POPRCTNM=B.POPRCTNM AND

A.RCPTLNNM=B.RCPTLNNM AND A.ITEMNMBR=B.ITEMNMBR

where  A.QTYRESERVED > 0

 

union all

 

select 'Allocated Fulfillment Orders in Sales Order Processing' as AllocType, SOPNUMBE as document,ITEMNMBR,ATYALLOC as allocatedqty

from SOP10200

where  ATYALLOC > 0 and  SOPTYPE = 6

 

union all

 

select 'Allocated Invoices in Sales Order Processing' as AllocType, SOPNUMBE as document,ITEMNMBR,ATYALLOC as allocatedqty

from SOP10200

where ATYALLOC > 0 and  SOPTYPE = 3

 

union all

 

select  'Allocated Orders in Sales Order Processing' as AllocType ,

        SOPNUMBE as document,

        ITEMNMBR,

        ATYALLOC as allocatedqty

from    SOP10200

where   ATYALLOC > 0 and  SOPTYPE = 2

 

union all

 

select 'Allocated documents in Inventory' as AllocType ,

IVDOCNBR as Document,

ITEMNMBR,

TRXQTY as AllocatedQTY

from IV10001 where TRXQTY < 0

 

union all

 

select 'Allocated documents in Inventory' as AllocType ,

IVDOCNBR as Documnet,

ITEMNMBR,

TRXQTY as allocatedQTY

from IV10001 where  TRXQTY > 0 and IVDOCTYP = 3

 

union all

 

select 'Allocated Invoices in Invoicing' as AllocType ,

INVCNMBR as Document,

ITEMNMBR,

QUANTITY as allocatedQTY

from IVC10101 where  DOCTYPE = 1

 

union all

 

select 'Allocated Assembly documents in Bill of Materials' as AllocType,

TRX_ID as Document,

ITEMNMBR,

ATYALLOC as Allocated

from BM10300 where Component_ID <> 0

and  ATYALLOC > 0