Ratakan

Informations :

Smartlist Item Class Setup

This query used to create Smartlist Item Class Setup
Name Window : Item Class Setup
Location Window : 
Inventory >> Setup >> Item Class



SP View : 



Sintax 
create view ABC_Item_Class_Setup

as

select 

a.ITMCLSCD as 'Item Class',

a.ITMCLSDC as 'Class Description',

a.ITMGEDSC as 'Generic Description',

case a.ITEMTYPE 

when 1 then  'Sales Inventory'

when 2 then  'Discontinued'

when 3 then  'Kit'

when 4 then  'Misc Charges'

when 5 then  'Services'

when 6 then  'Flat Fee'

else ''

end 'Item Type',

case a.VCTNMTHD 

when 1 then  'FIFO Perpetual'

when 2 then  'LIFO Perpetual'

when 3 then  'Average Perpetual'

when 4 then  'FIFO Periodic'

when 5 then  'LIFO Periodic'

else ''

end 'Valuation Method',

case ITMTRKOP 

when 1 then 'None'

when 2 then 'Serial Numbers'

when 3 then 'Lot Numbers'

else ''

end 'Item Track Options',

case a.TAXOPTNS 

when 1 then 'Taxable'

when 2 then 'Nontaxable'

when 3 then 'Base on customers'

else ''

end 'Sales Tax Option',

a.ITMTSHID as 'Sales Tax Schedule ID',

case a.Purchase_Tax_Options 

when 1 then 'Taxable'

when 2 then 'Nontaxable'

when 3 then 'Base on customers'

else ''

end 'Purchase Tax Option',

a.Purchase_Item_Tax_Schedu as 'Purchase Tax Schedule ID',

case a.ALWBKORD 

when 0 then 'Not Allow'

when 1 then 'Allow'

else ''

end 'Allow Back Order',

case a.DECPLQTY 

when 1 then 0

when 2 then 1

when 3 then 2

when 4 then 3

when 5 then 4

when 6 then 5

else ''

end 'Quantity Decimals',

a.UOMSCHDL as 'U of M Schedule',

a.PriceGroup as 'Price Group',

a.PRCLEVEL as 'Default Price Level',

case a.PRICMTHD 

when 1 then 'Currency Amount'

when 2 then '% of List Price'

when 3 then '% Markup–Current Cost'

when 4 then '% Markup–Standard Cost'

when 5 then '% Margin–Current Cost'

when 6 then '% Margin–Standard Cost'

else ''

end 'Price Method',

case a.UseQtyShortageTolerance

when 0 then 'No'

when 1 then 'Shortage'

else ''

end 'Shortage',

QtyShortTolerancePercent as 'Percent Shortage',

case UseQtyOverageTolerance 

when 0 then 'No'

when 1 then 'Overage'

else ''

end 'Overage',

QtyOverTolerancePercent as 'Percent Overage',

case a.KPCALHST when 0 then 'No Maintain' else 'Maintain' end 'Calender Year',

case a.KPERHIST when 0 then 'No Maintain' else 'Maintain' end 'Fiscal Year',

case a.KPTRXHST when 0 then 'No Maintain' else 'Maintain' end 'Transaction',

case a.KPDSTHST when 0 then 'No Maintain' else 'Maintain' end 'Distribution',

b.Account as 'Inventory',

c.Account as 'Inventory Offset',

d.Account as 'Cost of Good Sold',

e.Account as 'Sales',

f.Account as 'Markdown',

g.Account as 'Sales Return',

h.Account as 'In Use',

i.Account as 'In Service',

j.Account as 'Damaged',

k.Account as 'Variance',

l.Account as 'Drop Ship Items',

m.Account as 'Purchase Price Variance',

n.Account as 'Unrealized Purchase Price Variance',

o.Account as 'Inventory Return',

p.Account as 'Assembly Variance',

q.Account as 'Standard Cost Revaluation'

from IV40400 a 

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) b on a.IVIVINDX = b.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) c on a.IVIVOFIX = c.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) d on a.IVCOGSIX = d.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) e on a.IVSLSIDX = e.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) f on a.IVSLDSIX = f.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) g on a.IVSLRNIX = g.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) h on a.IVINUSIX = h.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) i on a.IVINSVIX = i.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) j on a.IVDMGIDX = j.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) k on a.IVVARIDX = k.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) l on a.DPSHPIDX = l.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) m on a.PURPVIDX = m.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) n on a.UPPVIDX = n.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) o on a.IVRETIDX = o.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) p on a.ASMVRIDX = p.ACTINDX

left join (select a.ACTINDX,a.ACTNUMST,b.ACTDESCR,rTrim(a.ACTNUMST)+' : '+rtrim(b.ACTDESCR) as 'Account' from GL00105 a left join GL00100 b on a.ACTINDX= b.ACTINDX) q on a.IVSCRVIX = q.ACTINDX

go

grant select on ABC_Item_Class_Setup to DYNGRP
Hasilnya :








No comments