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

Good Information by Sanjay- Manufacturing Allocated or pending issue

Posted on 12/13/2013 by Sanjay Kumar

?

1

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

select 'Allocated - general or pending issue' as AllocType,         CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'             WHEN MANUFACTUREORDERST_I = '2' THEN 'open'             WHEN MANUFACTUREORDERST_I = '3' THEN 'released'             WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'             WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'             WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'             WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'             WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'         END AS MOstatus,  * from MOP1400 inner join dbo.WO010032 ON dbo.MOP1400 .MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I where ATYALLOC > 0   select 'Allocated - bin ' as AllocType,         CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'             WHEN MANUFACTUREORDERST_I = '2' THEN 'open'             WHEN MANUFACTUREORDERST_I = '3' THEN 'released'             WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'             WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'             WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'             WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'             WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'         END AS MOstatus,         * from MOP1900 inner join dbo.WO010032 ON dbo.MOP1900.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I where ATYALLOC > 0   select 'Allocated - lot or pending issue' as AllocType,         CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'             WHEN MANUFACTUREORDERST_I = '2' THEN 'open'             WHEN MANUFACTUREORDERST_I = '3' THEN 'released'             WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'             WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'             WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'             WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'             WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'         END AS MOstatus,         * from MOP1020 inner join dbo.WO010032 ON dbo.MOP1020.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I

Flowchart of SOP and Inventory

I always wanted to train the users using the flowchart for easy and better understanding.  So wanted to store this from the community for future use.  The following collection of flowchart of SOP / POP / Inventory / Manufacturing.  Thanks to Leslie and Mahmoud and Waqas.

Manufacturing Labor Code Definition

On GP 2013 radio button for fixed overhead and variable overhead in the Labor Code Definition window is not visible.  This is a bug of 2013  4 radio button groups in total for this window which got invisible because of improper Tab sequence order. This can modified on this Window to set the tab sequence for all the radio groups which you are not able to see.   This has been shared by Naga in the community which is useful information.

Thanks,

Babu Baskaran