WIP Script Item Number N1000-N9999 Salespad Quick Report


SELECT *
from ( Select 

sli.Sales_Doc_Type,
im.xManufacture,
im.xMachinetype,
im.Item_Number,
im.xSeries,
im.Model_Number,
es.Serial_Num,
im.xOptionsOnMachine,
sli.Sales_Person_ID,
iqty.Avail,
iqty.Onhand,
iqty.Onorder,
im.Notes,
im.Port_Date,
im.xfactoryshipdate,
im.Standard_Cost,
im.Current_Cost,
im.List_Price,
sli.Customer_Name,
sli.Customer_Num,
sli.City,
sli.STATE,
Sli.Sales_Doc_Num, 
sli.Fulfillment_Date,
im.Item_Description,
sli.Source


FROM spvitemmaster as im


LEFT JOIN spvInventory as iqty ON im.Item_Number = iqty. Item_Number and im.Item_Number = iqty.Item_Number
LEFT JOIN spvEquipmentSearch as es on im.Item_number = es. Item_number and im.Item_Number = es.Item_Number
LEFT JOIN spv3SalesLineItem as sli on im.Item_number = sli. Item_number and im.Item_Number = sli.Item_Number

where im.Item_Number between 'N1000' and 'N9999' and (sli.Sales_Doc_Type = 'order' or sli.Sales_Doc_Type is null)

union

Select 

sli.Sales_Doc_Type,
im.xManufacture,
im.xMachinetype,
im.Item_Number,
im.xSeries,
im.Model_Number,
es.Serial_Num,
im.xOptionsOnMachine,
sli.Sales_Person_ID,
iqty.Avail,
iqty.Onhand,
iqty.Onorder,
im.Notes,
im.Port_Date,
im.xfactoryshipdate,
im.Standard_Cost,
im.Current_Cost,
im.List_Price,
sli.Customer_Name,
sli.Customer_Num,
sli.City,
sli.STATE,
Sli.Sales_Doc_Num,
sli.Fulfillment_Date,
im.Item_Description,
sli.Source

FROM spvitemmaster as im

LEFT JOIN spvInventory as iqty ON im.Item_Number = iqty. Item_Number and im.Item_Number = iqty.Item_Number
LEFT JOIN spvEquipmentSearch as es on im.Item_number = es. Item_number and im.Item_Number = es.Item_Number
LEFT JOIN spv3SalesLineItemHistory as sli on im.Item_number = sli. Item_Number and im.Item_Number = sli.Item_Number

where im.Item_Number between 'N1000' and 'N9999' and sli.Source <> 'Void' and (sli.Sales_Doc_Type = 'order' or sli.Sales_Doc_Type is null)
) as a 





Leave a Reply