Ratakan

Informations :

Smartlist Assembly Entry Detail

This query used to create Smartlist Assembly Detail
Name Window : Assembly Entry
Location Window : 
Inventory >> Transactions >> Assembly Entry






create view ABC_Assembly_Detail

as

select 

a.TRX_ID as 'Document Number',

a.TRXSORCE as 'TRX Source',

a.TRXDATE as 'Document Date',

b.LOCNCODE as 'Site ID',

a.BACHNUMB as 'Batch ID',

b.ITEMNMBR as 'Bill Number (Produce Item)',

b.ITEMDESC as 'Description (Produce)',

b.[Stock Method (Produce)],

case a.BM_Trx_Status 

when 1 then 'New'

when 2 then 'Not Released'

when 3 then 'Released'

else ''

end [Status],

b.UOFM as 'UofM Produce',

b.Assemble_Quantity as 'Assembly QTY (Produce)',

--b.UNITCOST as 'Unit Cost (Produce)',

--b.EXTDCOST as 'Extended Cost (Produce)',


c.Component_ID as 'Line (Consume)',

c.ITEMNMBR as 'Component Item (Consume)',

c.ITEMDESC as 'Component Item Description (Consume)',

case c.BM_Stock_Method

when 1 then 'Build If Necessary'

when 2 then 'Stock'

when 3 then 'Build'

else ''

end 'Stock Method (Consume)',

c.UOFM as 'UofM (Consume)',

c.Stock_Quantity as 'Stock QTY (Consume)',

c.Assemble_Quantity as 'Assembly QTY (Consume)',

c.Extended_Standard_Quantity as 'Ext Standard QTY (Consume)',

--c.UNITCOST as 'Unit Cost (Consume)',

--c.EXTDCOST as 'Extended Cost (Consume)',


case c.Cost_Type 

when 1 then 'Per Unit'

when 2 then 'Setup'

else ''

end 'Cost Type',

a.BCHSOURC as 'Batch Source',

a.BM_Start_Date as 'Start Date',

a.USER2ENT as 'User to Enter',

'Unposted' as 'Posting Status',

a.PTDUSRID as 'User Posted',

a.PSTGDATE as 'Posting Date'

from BM10200   a


left join (select 

   a.TRX_ID,

   a.TRXSORCE,

   a.LOCNCODE,

   a.ITEMNMBR,

   a.ITEMDESC,

   a.UOFM,

   a.Assemble_Quantity,

   --b.UNITCOST,

   --b.EXTDCOST,

   case a.BM_Stock_Method

   when 1 then 'Build If Necessary'

   when 2 then 'Stock'

   when 3 then 'Build'

   else ''

   end 'Stock Method (Produce)'


    from 

   BM10300  a 

   left join 

   (select * from IV30300 

   where DOCTYPE = 7

   and LNSEQNBR = 0) b

   on a.TRX_ID = b.DOCNUMBR 

   and a.ITEMNMBR = b.ITEMNMBR 

   and a.TRXSORCE = b.TRXSORCE

   where a.Component_ID = 0) b on a.TRX_ID = b.TRX_ID


left join (select 

   a.TRX_ID,

   a.TRXSORCE,

   a.LOCNCODE,

   a.Component_ID,

   a.ITEMNMBR,

   a.ITEMDESC,

   a.UOFM,

   a.Assemble_Quantity,

   a.Stock_Quantity,

   a.Extended_Standard_Quantity,

   a.BM_Stock_Method,

   a.Cost_Type,

   b.UNITCOST,

   b.EXTDCOST,

   coalesce (b.LNSEQNBR,0) LNSEQNBR

   from 

   BM10300  a 

   left join 

   (select * from IV30300 

   where DOCTYPE = 7) b

   on a.TRX_ID = b.DOCNUMBR 

   and a.ITEMNMBR = b.ITEMNMBR 

   and a.TRXSORCE = b.TRXSORCE

   and a.Component_ID = b.LNSEQNBR

   where Parent_Component_ID = 0) c on a.TRX_ID = c.TRX_ID


--where a.TRX_ID = 'ASM00262'



union


select 

a.TRX_ID as 'Document Number',

a.TRXSORCE as 'TRX Source',

a.TRXDATE as 'Document Date',

b.LOCNCODE as 'Site ID',

a.BACHNUMB as 'Batch ID',

b.ITEMNMBR as 'Bill Number (Produce Item)',

b.ITEMDESC as 'Description (Produce)',

b.[Stock Method (Produce)],

case a.BM_Trx_Status 

when 1 then 'New'

when 2 then 'Not Released'

when 3 then 'Released'

else ''

end [Status],

b.UOFM as 'UofM Produce',

b.Assemble_Quantity as 'Assembly QTY (Produce)',

--b.UNITCOST as 'Unit Cost (Produce)',

--b.EXTDCOST as 'Extended Cost (Produce)',


c.Component_ID as 'Line (Consume)',

c.ITEMNMBR as 'Component Item (Consume)',

c.ITEMDESC as 'Component Item Description (Consume)',

case c.BM_Stock_Method

when 1 then 'Build If Necessary'

when 2 then 'Stock'

when 3 then 'Build'

else ''

end 'Stock Method (Consume)',

c.UOFM as 'UofM (Consume)',

c.Stock_Quantity as 'Stock QTY (Consume)',

c.Assemble_Quantity as 'Assembly QTY (Consume)',

c.Extended_Standard_Quantity as 'Ext Standard QTY (Consume)',

--c.UNITCOST as 'Unit Cost (Consume)',

--c.EXTDCOST as 'Extended Cost (Consume)',


case c.Cost_Type 

when 1 then 'Per Unit'

when 2 then 'Setup'

else ''

end 'Cost Type',

a.BCHSOURC as 'Batch Source',

a.BM_Start_Date as 'Start Date',

a.USER2ENT as 'User to Enter',

'Posted' as 'Posting Status',

a.PTDUSRID as 'User Posted',

a.PSTGDATE as 'Posting Date'

from BM30200  a


left join (select 

   a.TRX_ID,

   a.TRXSORCE,

   a.LOCNCODE,

   a.ITEMNMBR,

   a.ITEMDESC,

   a.UOFM,

   a.Assemble_Quantity,

   --b.UNITCOST,

   --b.EXTDCOST

   case a.BM_Stock_Method

   when 1 then 'Build If Necessary'

   when 2 then 'Stock'

   when 3 then 'Build'

   else ''

   end 'Stock Method (Produce)'

   

   from 

   BM30300 a 

   left join 

   (select * from IV30300 

   where DOCTYPE = 7

   and LNSEQNBR = 0) b

   on a.TRX_ID = b.DOCNUMBR 

   and a.ITEMNMBR = b.ITEMNMBR 

   and a.TRXSORCE = b.TRXSORCE

   where a.Component_ID = 0) b on a.TRX_ID = b.TRX_ID


left join (select 

   a.TRX_ID,

   a.TRXSORCE,

   a.LOCNCODE,

   a.Component_ID,

   a.ITEMNMBR,

   a.ITEMDESC,

   a.UOFM,

   a.Assemble_Quantity,

   a.Stock_Quantity,

   a.Extended_Standard_Quantity,

   a.BM_Stock_Method,

   a.Cost_Type,

   b.UNITCOST,

   b.EXTDCOST,

   coalesce (b.LNSEQNBR,0) LNSEQNBR

   from 

   BM30300 a 

   left join 

   (select * from IV30300 

   where DOCTYPE = 7) b

   on a.TRX_ID = b.DOCNUMBR 

   and a.ITEMNMBR = b.ITEMNMBR 

   and a.TRXSORCE = b.TRXSORCE

   and a.Component_ID = b.LNSEQNBR

   where Parent_Component_ID = 0) c on a.TRX_ID = c.TRX_ID


--where a.TRX_ID = 'ASM00262'


go 

grant select on ABC_Assembly_Detail to DYNGRP








No comments