SM Query List

Aged Purchase Orders With Links To PO's

SELECT icpoheaders.sshipcode as 'SHIP TO CODE',

CONCAT('<A HREF=\*LINKBASE*smic.ICEditPOEdit?lid=',

CAST(icpoheaders.lid AS CHAR), '&CallingClass=smic.ICEditPOSelection&SESSIONTAG=*SESSIONTAG*>', CAST(icpoheaders.lid AS CHAR), '</A>') AS 'PO #', date_format(icpoheaders.datassigned, '%c%/%e%/%Y') as 'DATE ASSIGNED', icpoheaders.sassignedtoname as 'ASSIGNED TO', IF (icpoheaders.lstatus = 0, 'ENTERED', if(icpoheaders.lstatus = 1, 'PARTIAL RECPT', if(icpoheaders.lstatus = 2, 'COMPLETE', 'DELETED'))) AS 'STATUS', SUM(icpolines.bdextendedordercost) AS 'AMOUNT ORDERED', icpoheaders.sreference as REFERENCE, icpoheaders.svendorname AS VENDOR from icpoheaders LEFT JOIN icpolines ON icpoheaders.lid = icpolines.lpoheaderid WHERE ((icpoheaders.lstatus < 2)) GROUP BY (icpoheaders.lid) ORDER BY icpoheaders.sshipcode, icpoheaders.lid

Find Items In Purchase Orders By Part Of The Item Description

SELECT date_format(icpoheaders.datassigned, '%c%/%e%/%Y') as 'PO DATE', CONCAT('<A HREF=\*LINKBASE*smic.ICPrintPOGenerate?StartingPOID=', CAST(icpoheaders.lid AS CHAR), '&EndingPOID=&SESSIONTAG=*SESSIONTAG*>', CAST(icpoheaders.lid AS CHAR), '</A>') AS 'PO #', icpoheaders.sassignedtoname as 'ASSIGNED TO', IF (icpoheaders.lstatus = 0, 'ENTERED', if(icpoheaders.lstatus = 1, 'PARTIAL RECPT', if(icpoheaders.lstatus = 2, 'COMPLETE', 'DELETED'))) AS 'STATUS', icpoheaders.sreference as REFERENCE, icpoheaders.svendorname AS VENDOR, icpolines.sitemdescription AS 'DESCRIPTION', icpolines.bdextendedordercost AS 'EXT COST' from icpoheaders LEFT JOIN icpolines ON icpoheaders.lid = icpolines.lpoheaderid WHERE (icpolines.sitemdescription LIKE '%[[Enter a part of the item description]]%') ORDER BY icpoheaders.datpodate, icpoheaders.lid

Search Phrases In Order Header Ticket Comments

SELECT CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=', TRIM(sOrderNumber), '&SESSIONTAG=*SESSIONTAG*>', TRIM(orderheaders.sOrderNumber), '</A>') as 'ORDER #', date_format(datOrderDate, '%c%/%e%/%Y') AS 'ORDER DATE', sBillToName AS 'BILL TO', sShipToName AS 'SHIP TO', mTicketComments AS 'TICKET COMMENTS' from orderheaders WHERE ((orderheaders.iOrderType != 4) AND (mTicketComments LIKE '%[[Enter a phrase to search for in the ticket comments]]%') AND (orderheaders.iOrderType != 4)

) ORDER BY datOrderDate DESC

Search INVOICE Detail Item Descriptions

SELECT CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMPrintInvoice?InvoiceNumberFrom=', TRIM(invoiceheaders.sInvoiceNumber), '&SESSIONTAG=*SESSIONTAG*>', TRIM(invoiceheaders.sInvoiceNumber), '</A>') as 'INV #', CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=', TRIM(invoiceheaders.sOrderNumber), '&SESSIONTAG=*SESSIONTAG*>', TRIM(invoiceheaders.sOrderNumber), '</A>') as 'ORDER #', date_format(datInvoiceDate, '%c%/%e%/%Y') as 'INV DATE', sBillToName AS 'BILL TO', sShipToName AS 'SHIP TO', invoicedetails.iLineNumber as 'LINE #', CONCAT('<A HREF=\*LINKBASE*smic.ICDisplayItemInformation?ItemNumber=', invoicedetails.sItemNumber, '&SESSIONTAG=*SESSIONTAG*>', invoicedetails.sItemNumber, '</A>') as 'ITEM', invoicedetails.sDesc AS 'DESCRIPTION' from invoicedetails LEFT JOIN invoiceheaders ON invoicedetails.sInvoiceNumber = invoiceheaders.sInvoiceNumber WHERE (invoicedetails.sDesc LIKE '%[[Enter a phrase to search from the item descriptions on the invoice]]%') ORDER BY datInvoiceDate DESC

List SMCP Report Usage

select datLoggingDate, sLoggingUser, sLoggingSQL from systemlog where (

(sLoggingDescription = 'REPORT')

AND (datLoggingDate >= '[[Enter the starting date in YYYY-MM-DD format (for example, 2011-01-01)]]')

AND (datLoggingDate <= '[[Enter the ending date in YYYY-MM-DD format (for example, 2011-01-01)]] 23:59:59')

) ORDER BY datLoggingDate DESC

List SMCP Report Usage For A Selected User And Starting From A Selected Date

SELECT

sLoggingSQL AS REPORT,

datLoggingDate AS DATE,

sLoggingUser AS USER

from systemlog

where (

(sLoggingDescription = 'REPORT')

AND (sLoggingUser = '[[Enter the user's login name]]')

AND (datLoggingDate >= '[[Enter the starting date in YYYY-MM-DD format (for example, 2011-01-01)]]')

) ORDER BY sLoggingSQL, datLoggingDate DESC

List Customers Using Electronic Deposits

select sCustomerNumber, sCustomerName, sAddressLine1, sAddressLine2, sAddressLine3, sAddressLine4, sCity, sState, sPostalCode from arcustomers where (iuseselectronicdeposit = 1)

'Stock' Inventory List (MADG Companies Only)

(select ServMgr1.icitems.sitemnumber as ITEM, ServMgr1.icitems.sitemdescription as DESCRIPTION, "WASH" as COMPANY, ServMgr1.icitemlocations.slocation as LOCATION, ServMgr1.icitemlocations.bdqtyonhand as QTY, ServMgr1.icitems.scomment1 as COMMENT1, ServMgr1.icitems.scomment2 as COMMENT2, ServMgr1.icitems.sreportgroup4 as REWARD from ServMgr1.icitemlocations LEFT JOIN ServMgr1.icitems on ServMgr1.icitemlocations.sitemnumber = ServMgr1.icitems.sitemnumber where((ServMgr1.icitems.sreportgroup4 != '') AND (ServMgr1.icitemlocations.bdqtyonhand != 0.00))) UNION ALL (select ServMgr2.icitems.sitemnumber as ITEM, ServMgr2.icitems.sitemdescription as DESCRIPTION, "HARR" as COMPANY, ServMgr2.icitemlocations.slocation as LOCATION, ServMgr2.icitemlocations.bdqtyonhand as QTY, ServMgr2.icitems.scomment1 as COMMENT1, ServMgr2.icitems.scomment2 as COMMENT2, ServMgr2.icitems.sreportgroup4 as REWARD from ServMgr2.icitemlocations LEFT JOIN ServMgr2.icitems on ServMgr2.icitemlocations.sitemnumber = ServMgr2.icitems.sitemnumber where((ServMgr2.icitems.sreportgroup4 != '') AND (ServMgr2.icitemlocations.bdqtyonhand != 0.00))) UNION ALL (select ServMgr3.icitems.sitemnumber as ITEM, ServMgr3.icitems.sitemdescription as DESCRIPTION, "CVA" as COMPANY, ServMgr3.icitemlocations.slocation as LOCATION, ServMgr3.icitemlocations.bdqtyonhand as QTY, ServMgr3.icitems.scomment1 as COMMENT1, ServMgr3.icitems.scomment2 as COMMENT2, ServMgr3.icitems.sreportgroup4 as REWARD from ServMgr3.icitemlocations LEFT JOIN ServMgr3.icitems on ServMgr3.icitemlocations.sitemnumber = ServMgr3.icitems.sitemnumber where((ServMgr3.icitems.sreportgroup4 != '') AND (ServMgr3.icitemlocations.bdqtyonhand != 0.00))) ORDER BY COMPANY, ITEM

List PO Receipt Deletion Attempts

select datLoggingDate as 'DATE DELETED', sLoggingUser as 'DELETED BY', if(sLoggingDescription = 'Receipt #: -1', 'Delete SUCCESSFUL', if(sLoggingDescription = 'Receipt #: 0', 'Delete UNSUCCESSFUL', sLoggingDescription)) AS 'DESCRIPTION' from systemlog WHERE (sLoggingOperation = 'DELETERECEIPT') ORDER BY datLoggingDate

List Any PO Receipts Which Had Invoices Applied Against Them, But Still Don't Have The Invoice Number Recorded On The Receipt

select icporeceiptheaders.lid as 'RECEIPT #', icporeceiptheaders.sinvoicenumber AS 'INV # ON RECEIPT', icpoinvoiceheaders.sinvoicenumber AS 'INV # ON INVOICE', icpoinvoiceheaders.datinvoice AS 'INVOICE DATE', icpoinvoiceheaders.lid AS 'INVOICE ID' from icpoinvoiceheaders LEFT JOIN icporeceiptheaders ON icpoinvoiceheaders.lreceiptid = icporeceiptheaders.lid where (icporeceiptheaders.sinvoicenumber = '')

List Open COD Transactions

This lists open transactions which have 'COD' types of terms, but still carry open amounts - (may have to change the terms codes for different companies)

SELECT invoiceheaders.sSalesperson, invoiceheaders.sServiceTypeCodeDescription, artransactions.datdocdate, arcustomers.sCustomerNumber, CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=', TRIM(invoiceheaders.sOrderNumber), '&SESSIONTAG=*SESSIONTAG*>', TRIM(invoiceheaders.sOrderNumber), '</A>') as 'ORDER #', invoiceheaders.sBillToName, invoiceheaders.sShipToName, artransactions.sterms, arcustomers.iOnHold, arcustomers.dCreditLimit, artransactions.sdocnumber, artransactions.dcurrentamt FROM (artransactions INNER JOIN arcustomers ON artransactions.spayeepayor = arcustomers.sCustomerNumber) LEFT JOIN invoiceheaders ON LPAD(artransactions.sdocnumber, 8, ' ') = invoiceheaders.sInvoiceNumber WHERE (((artransactions.sterms)="CASH" Or (artransactions.sterms)="COD" Or (artransactions.sterms)="50Z50" Or (artransactions.sterms)="HOLD") AND ((artransactions.dcurrentamt)>0)) ORDER BY invoiceheaders.sSalesperson, invoiceheaders.sServiceTypeCodeDescription, arcustomers.sCustomerNumber

Total Open AR Transactions With 'COD' Type Terms

(may have to change the terms codes for different companies)

SELECT SUM(artransactions.dcurrentamt) AS 'COD TOTAL' FROM (artransactions INNER JOIN arcustomers ON artransactions.spayeepayor = arcustomers.sCustomerNumber) LEFT JOIN invoiceheaders ON LPAD(artransactions.sdocnumber, 8, ' ') = invoiceheaders.sInvoiceNumber WHERE (((artransactions.sterms)="CASH" Or (artransactions.sterms)="COD" Or (artransactions.sterms)="50Z50" Or (artransactions.sterms)="HOLD") AND ((artransactions.dcurrentamt)>0))

Commercial Parts List Sorted By Item

(MADG Companies Only)

SELECT icitems.sitemnumber as ITEM, icitems.sitemdescription AS DESCRIPTION, icitems.scostunitofmeasure as UNIT, icitemprices.bdbaseprice AS PRICE from icitems LEFT JOIN icitemprices on icitems.sitemnumber=icitemprices.sitemnumber where (

(icitems.iactive = 1)

AND (icitems.ilaboritem = 0)

AND (icitems.sdedicatedtoordernumber = '')

AND (icitems.sitemnumber >= 'A')

AND (icitemprices.spricelistcode = '1')

AND (LEFT(icitems.sitemnumber, 3) != 'LDE')

AND (LEFT(icitems.sitemnumber, 3) != 'OPE')

AND (LEFT(icitems.sitemnumber, 3) != 'RDD')

AND (LEFT(icitems.sitemnumber, 3) != 'RSD')

AND (LEFT(icitems.sitemnumber, 3) != 'SEC')

AND (LEFT(icitems.sitemnumber, 3) != 'SED')

AND (LEFT(icitems.sitemnumber, 3) != 'SPR')

) ORDER BY icitems.sitemnumber

Commercial Parts List Sorted By Description

(MADG Companies Only)

SELECT icitems.sitemdescription AS DESCRIPTION, icitems.sitemnumber as ITEM, icitems.scostunitofmeasure as UNIT, icitemprices.bdbaseprice AS PRICE from icitems LEFT JOIN icitemprices on icitems.sitemnumber=icitemprices.sitemnumber where (

(icitems.iactive = 1)

AND (icitems.ilaboritem = 0)

AND (icitems.sdedicatedtoordernumber = '')

AND (icitems.sitemnumber >= 'A')

AND (icitemprices.spricelistcode = '1')

AND (LEFT(icitems.sitemnumber, 3) != 'LDE')

AND (LEFT(icitems.sitemnumber, 3) != 'OPE')

AND (LEFT(icitems.sitemnumber, 3) != 'RDD')

AND (LEFT(icitems.sitemnumber, 3) != 'RSD')

AND (LEFT(icitems.sitemnumber, 3) != 'SEC')

AND (LEFT(icitems.sitemnumber, 3) != 'SED')

AND (LEFT(icitems.sitemnumber, 3) != 'SPR')

) ORDER BY icitems.sitemdescription

Commercial Parts List Sorted By Prefix, Then Description

(MADG Companies Only - sorts by the three letter 'prefix' first, then by the item description)

(icitems.iactive = 1)

AND (icitems.ilaboritem = 0)

AND (icitems.sdedicatedtoordernumber = '')

AND (icitems.sitemnumber >= 'A')

AND (icitemprices.spricelistcode = '1')

AND (LEFT(icitems.sitemnumber, 3) != 'LDE')

AND (LEFT(icitems.sitemnumber, 3) != 'OPE')

AND (LEFT(icitems.sitemnumber, 3) != 'RDD')

AND (LEFT(icitems.sitemnumber, 3) != 'RSD')

AND (LEFT(icitems.sitemnumber, 3) != 'SEC')

AND (LEFT(icitems.sitemnumber, 3) != 'SED')

AND (LEFT(icitems.sitemnumber, 3) != 'SPR')

) ORDER BY LEFT(icitems.sitemnumber, 3), icitems.sitemdescription

Residential Parts List Sorted By Item

(MADG Companies Only)

SELECT icitems.sitemnumber as ITEM, icitems.sitemdescription AS DESCRIPTION, icitems.scostunitofmeasure as UNIT, icitemprices.bdlevel5price AS PRICE from icitems LEFT JOIN icitemprices on icitems.sitemnumber=icitemprices.sitemnumber where (

(icitems.iactive = 1)

AND (icitems.ilaboritem = 0)

AND (icitems.sdedicatedtoordernumber = '')

AND (icitems.sitemnumber >= 'A')

AND (icitemprices.spricelistcode = '1')

AND (LEFT(icitems.sitemnumber, 3) != 'LDE')

AND (LEFT(icitems.sitemnumber, 3) != 'OPE')

AND (LEFT(icitems.sitemnumber, 3) != 'RDD')

AND (LEFT(icitems.sitemnumber, 3) != 'RSD')

AND (LEFT(icitems.sitemnumber, 3) != 'SEC')

AND (LEFT(icitems.sitemnumber, 3) != 'SED')

AND (LEFT(icitems.sitemnumber, 3) != 'LDP')

AND (LEFT(icitems.sitemnumber, 3) != 'RDP')

) ORDER BY icitems.sitemnumber

Residential Parts List Sorted By Description

(MADG Companies Only)

SELECT icitems.sitemdescription AS DESCRIPTION, icitems.sitemnumber as ITEM, icitems.scostunitofmeasure as UNIT, icitemprices.bdlevel5price AS PRICE from icitems LEFT JOIN icitemprices on icitems.sitemnumber=icitemprices.sitemnumber where (

(icitems.iactive = 1)

AND (icitems.ilaboritem = 0)

AND (icitems.sdedicatedtoordernumber = '')

AND (icitems.sitemnumber >= 'A')

AND (icitemprices.spricelistcode = '1')

AND (LEFT(icitems.sitemnumber, 3) != 'LDE')

AND (LEFT(icitems.sitemnumber, 3) != 'OPE')

AND (LEFT(icitems.sitemnumber, 3) != 'RDD')

AND (LEFT(icitems.sitemnumber, 3) != 'RSD')

AND (LEFT(icitems.sitemnumber, 3) != 'SEC')

AND (LEFT(icitems.sitemnumber, 3) != 'SED')

AND (LEFT(icitems.sitemnumber, 3) != 'LDP')

AND (LEFT(icitems.sitemnumber, 3) != 'RDP')

) ORDER BY icitems.sitemdescription

Residential Parts List Sorted By Prefix, Then Description

(MADG Companies Only - sorts by the three-letter 'prefix', then by the item description)

(icitems.iactive = 1)

AND (icitems.ilaboritem = 0)

AND (icitems.sdedicatedtoordernumber = '')

AND (icitems.sitemnumber >= 'A')

AND (icitemprices.spricelistcode = '1')

AND (LEFT(icitems.sitemnumber, 3) != 'LDE')

AND (LEFT(icitems.sitemnumber, 3) != 'OPE')

AND (LEFT(icitems.sitemnumber, 3) != 'RDD')

AND (LEFT(icitems.sitemnumber, 3) != 'RSD')

AND (LEFT(icitems.sitemnumber, 3) != 'SEC')

AND (LEFT(icitems.sitemnumber, 3) != 'SED')

AND (LEFT(icitems.sitemnumber, 3) != 'LDP')

AND (LEFT(icitems.sitemnumber, 3) != 'RDP')

) ORDER BY LEFT(icitems.sitemnumber, 3), icitems.sitemdescription

Parts List Cross-referenced By Company Showing Most Recent Costs

(MADG Companies Only)

SELECT ServMgr1.icitems.sitemnumber as 'WASH ITEM', ServMgr1.icitems.sitemdescription as 'WASH DESC', ServMgr1.icitems.bdmostrecentcost as 'WASH COST',

ServMgr2.icitems.sitemnumber as 'HARR ITEM', ServMgr2.icitems.bdmostrecentcost as 'HARR COST',

ServMgr3.icitems.sitemnumber as 'CVA ITEM', ServMgr3.icitems.bdmostrecentcost as 'CVA COST'

from ServMgr1.icitems LEFT JOIN ServMgr2.icitems on ServMgr1.icitems.sreportgroup5 = ServMgr2.icitems.sreportgroup5

LEFT JOIN ServMgr3.icitems on ServMgr1.icitems.sreportgroup5 = ServMgr3.icitems.sreportgroup5

where (

(ServMgr1.icitems.sreportgroup5 != '')

)

order by ServMgr1.icitems.sitemnumber

Purchase Order Invoice Header Listing

select lid as `Invoice ID`, lreceiptid as `Receipt Number`, sinvoicenumber as `Invoice Number` from icpoinvoiceheaders order by lid

List of Uninvoiced PO Receipts

select

datreceived as `Received Date`

, lid as `Receipt Number`

, lpoheaderid as `PO Number`

, sinvoicenumber as `Invoice Number`

, if(lpostedtoic = 1,'Y','N') as `Posted to IC?`

from icporeceiptheaders

where (

(lpostedtoic = 1)

AND (sinvoicenumber = '')

)

order by datreceived

List of SM Query Usage

select datLoggingDate as Date, sLoggingUser as User, sLoggingSQL as Query from systemlog where sLoggingOperation = 'SMQUERY' order by datLoggingDate

List Customer Transactions Moving Into Past Due Columns

(This query will list all AR transactions with POSITIVE current amounts (typically invoices) that are between a minimum and maximum number of days past due.)

SELECT CONCAT('<A HREF=\*LINKBASE*smar.ARDisplayCustomerInformation?CustomerNumber=', spayeepayor, '&SESSIONTAG=*SESSIONTAG*>', spayeepayor, '</A>') as 'CUST #', CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMPrintInvoice?InvoiceNumberFrom=', sdocnumber, '&SESSIONTAG=*SESSIONTAG*>', sdocnumber, '</A>') as 'DOC #', CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=', sordernumber, '&SESSIONTAG=*SESSIONTAG*>', sordernumber, '</A>') as 'ORDER #', date_format(datdocdate, '%c%/%e%/%Y') as 'DOC DATE', date_format(datduedate, '%c%/%e%/%Y') as 'DUE DATE', datediff(NOW(), datduedate) AS 'DAYS PAST DUE', doriginalamt as 'ORIG. AMT', dcurrentamt as 'CURRENT AMT' from artransactions where ( (dcurrentamt > 0.00) AND (datediff(NOW(), datduedate) > [[Enter the MINIMUM days past due you want to see (for example enter 88 to include items AT LEAST 89 days past due)]]) AND (datediff(NOW(), datduedate) <= [[Enter the MAXIMUM days past due you want to see (for example enter 90 to include items UP TO 90 days past due)]])) ORDER BY spayeepayor, datduedate

List Product Type Bid Amounts SUBTOTALLED BY PRODUCT, Grouped By Salesperson And Product Type For A Selected Period

select bids.ssalespersoncode as SALESCODE, bidproducttypes.sproducttype AS TYPE, bidproductamounts.lbidproducttypeid as TYPEID, SUM(bidproductamounts.bdamount) AS TOTALAMT from bidproductamounts LEFT JOIN bids ON bidproductamounts.lbidid = bids.id LEFT JOIN bidproducttypes ON bidproductamounts.lbidproducttypeid = bidproducttypes.lid WHERE ((bids.dattimeoriginationdate >= '[[Enter the starting bid origination date in YYYY-MM-DD format (for example 2011-07-01)]]') AND (bids.dattimeoriginationdate <= '[[Enter the ending bid origination date in YYYY-MM-DD format (for example 2011-08-01)]] 23:59:59')) GROUP BY bids.ssalespersoncode, bidproductamounts.lbidproducttypeid

List Individual Product Bid Amounts By Bid, Sorted By Salesperson, Then Product Type For A Selected Period

(change the starting and ending dates as needed)

select CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMEditBidEntry?id=', bids.id, '&SESSIONTAG=*SESSIONTAG*>', bids.id, '</A>') as 'BID #', date_format(bids.dattimeoriginationdate, '%c%/%e%/%Y') as 'ORIG.DATE', bids.ssalespersoncode as SALESCODE, bids.scustomername as CUSTOMER, bids.sprojectname as PROJECT, bidproducttypes.sproducttype AS TYPE, bidproductamounts.lbidproducttypeid as TYPEID, bidproductamounts.bdamount AS TOTALAMT from bidproductamounts LEFT JOIN bids ON bidproductamounts.lbidid = bids.id LEFT JOIN bidproducttypes ON bidproductamounts.lbidproducttypeid = bidproducttypes.lid WHERE ( (bids.dattimeoriginationdate >= '[[Enter the starting bid origination date in YYYY-MM-DD format (for example 2011-07-01)]]') AND (bids.dattimeoriginationdate <= '[[Enter the ending bid origination date in YYYY-MM-DD format (for example 2011-08-01)]] 23:59:59') AND (bidproductamounts.bdamount > 0.00) ) ORDER BY bids.ssalespersoncode, bidproductamounts.lbidproducttypeid

List Individual Product Bid Amounts By Bid, Sorted By Product Type, Then Salesperson For A Selected Period

select CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMEditBidEntry?id=', bids.id, '&SESSIONTAG=*SESSIONTAG*>', bids.id, '</A>') as 'BID #', date_format(bids.dattimeoriginationdate, '%c%/%e%/%Y') as 'ORIG.DATE', bids.ssalespersoncode as SALESCODE, bids.scustomername as CUSTOMER, bids.sprojectname as PROJECT, bidproducttypes.sproducttype AS TYPE, bidproductamounts.lbidproducttypeid as TYPEID, bidproductamounts.bdamount AS TOTALAMT from bidproductamounts LEFT JOIN bids ON bidproductamounts.lbidid = bids.id LEFT JOIN bidproducttypes ON bidproductamounts.lbidproducttypeid = bidproducttypes.lid WHERE ( (bids.dattimeoriginationdate >= '[[Enter the starting bid origination date in YYYY-MM-DD format (for example 2011-07-01)]]') AND (bids.dattimeoriginationdate <= '[[Enter the ending bid origination date in YYYY-MM-DD format (for example 2011-08-01)]] 23:59:59') AND (bidproductamounts.bdamount > 0.00) ) ORDER BY bidproductamounts.lbidproducttypeid, bids.ssalespersoncode

List Databases and Sizes in MySQL Database Server

SELECT table_schema AS 'Db Name', Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)', Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)' FROM information_schema.tables GROUP BY table_schema

List AP Invoice Export Sequences

select lid AS 'Export Sequence ID', suser as 'USER', datexported as 'Date Exported', scomment as 'COMMENT' from icinvoiceexportsequences order by lid DESC

List Invoice Totals By Customer For A Selected Date Range

(This is listed from the highest total sales amount to the lowest.)

SELECT SALESBYDATE.Sales, arcustomers.sCustomerNumber, arcustomers.sCustomerName, arcustomers.sAddressLine1, arcustomers.sAddressLine2, arcustomers.sAddressLine3, arcustomers.sAddressLine4, arcustomers.sCity, arcustomers.sState, arcustomers.sPostalCode, arcustomers.sContactName, arcustomers.sPhoneNumber, arcustomers.sFaxNumber, arcustomers.sEmailAddress FROM (SELECT invoiceheaders.sCustomerCode, SUM(invoicedetails.dExtendedPrice) AS Sales FROM invoiceheaders INNER JOIN invoicedetails ON invoiceheaders.sInvoiceNumber = invoicedetails.sInvoiceNumber WHERE ((invoiceheaders.datInvoiceDate >= '[[Enter a starting date in YYYY-MM-DD format (for example: 2011-01-01]]') And (invoiceheaders.datInvoiceDate <= '[[Enter an ending date in YYYY-MM-DD format (for example: 2011-01-01]] 23:59:59')) GROUP BY invoiceheaders.sCustomerCode) AS SALESBYDATE LEFT JOIN arcustomers ON SALESBYDATE.sCustomerCode = arcustomers.sCustomerNumber ORDER BY SALESBYDATE.Sales DESC

List SM Invoices Showing AR and IC Batch Numbers

(Use this to reconcile which invoices went to which batches in AR and IC.)

select

date_format(datInvoiceDate, '%c%/%e%/%Y') as 'Inv Date',

sInvoiceNumber as 'Inv #',

sBillToName as 'Bill To',

sShipToName as 'Ship To',

if(iExportedToAR = 1, 'Y', 'N') as 'Exp To AR?',

if(iExportedToIC = 1, 'Y', 'N') as 'Exp To IC?',

ibatchnumber as 'AR Batch',

if(iDayEndNumber > 0, CAST(iDayEndNumber AS CHAR), 'N/A') as 'IC Batch',

sCreatedBy as 'Created by', CONCAT(date_format(datInvoiceCreationDate, '%c%/%e%/%Y'), ' ', MID(sInvoiceCreationTime, 1, 2), ':', MID(sInvoiceCreationTime, 3, 2), ':', MID(sInvoiceCreationTime, 5, 2)) as 'Created'

from invoiceheaders LEFT JOIN entries on ((TRIM(invoiceheaders.sInvoiceNumber) = entries.sdocnumber) AND (invoiceheaders.sCustomerCode = entries.spayeepayor))

where (

(datInvoiceDate >= '[[Enter a starting date in YYYY-MM-DD format (for example: 2011-01-01]]')

AND (datInvoiceDate <= '[[Enter an ending date in YYYY-MM-DD format (for example: 2011-12-01]] 23:23:59')

) order by datInvoiceDate desc

List of SM Invoices with no Lines Linked to Them

(This query shows any invoices with no invoice lines linked to them - it also shows the number of invoice lines that SHOULD be on the invoice, but are now missing because of manual deletion or data corruption.)

select date_format(invoiceheaders.datInvoiceDate, '%c%/%e%/%Y') as 'Inv. Date', invoiceheaders.sInvoiceNumber as 'Inv #', iNumberOfLinesOnInvoice as 'No. Of Lines Originally On Invoice' from invoiceheaders LEFT JOIN invoicedetails on invoiceheaders.sInvoiceNumber = invoicedetails.sInvoiceNumber where (invoicedetails.sInvoiceNumber IS NULL)

Show Number of SM Invoices

This query simply shows the total count of Service Manager invoices.

select count(*) as 'Total Number Of Invoices' from invoiceheaders

Show Number of Orders

This query simply shows the total count of Service Manager orders.

select count(*) as 'Total Number Of Orders' from orderheaders WHERE (orderheaders.iOrderType != 4)

List Invoices And Amounts For One Customer In A Selected Date Range, Grouped By Order Type

SELECT invoiceheaders.sServiceTypeCodeDescription AS 'ORDER TYPE', date_format(invoiceheaders.datInvoiceDate, '%c%/%e%/%Y') as 'INV. DATE', invoiceheaders.sCustomerCode AS 'CUST. ACCT', invoiceheaders.sInvoiceNumber AS 'INV #', invoiceheaders.sBillToName AS 'CUSTOMER', invoiceheaders.sShipToName AS 'SHIP-TO', SUM(invoicedetails.dExtendedPrice) AS 'INV. AMT.' FROM invoiceheaders INNER JOIN invoicedetails ON invoiceheaders.sInvoiceNumber = invoicedetails.sInvoiceNumber WHERE ((invoiceheaders.datInvoiceDate >= '[[Enter a starting invoice date in YYYY-MM-DD format (for example: 2011-01-01]]') And (invoiceheaders.datInvoiceDate <= '[[Enter an ending invoice date in YYYY-MM-DD format (for example: 2011-01-01]] 23:59:59') AND (invoiceheaders.sCustomerCode = '[[Enter the customer account code]]')) GROUP BY invoiceheaders.sInvoiceNumber ORDER BY invoiceheaders.sServiceTypeCode, invoiceheaders.datInvoiceDate

Current Month's Sales Grouped By Category

This query summarizes the sales and cost of goods sold for the current month, grouped into the company's IC Categories

select iccategories.sdescription AS 'SALES CATEGORY',

COUNT(dExtendedCost) AS 'NUMBER OF TRANSACTIONS',

CAST(REPLACE(FORMAT(SUM(dExtendedPriceAfterDiscount),2),',','') AS

DECIMAL(17,2)) AS REVENUE,

CAST(REPLACE(FORMAT(SUM(dExtendedCost), 2), ',', '') AS DECIMAL(17,2)) AS COGS,

CAST(REPLACE(FORMAT(((SUM(dExtendedPriceAfterDiscount)- (SUM(dExtendedCost)*.5)))/(SUM(dExtendedCost))

* 100, 2), ',', '') AS DECIMAL(17,2)) AS 'GP%'

from iccategories LEFT JOIN

invoicedetails on iccategories.scategorycode =

invoicedetails.sItemCategory LEFT JOIN invoiceheaders on

invoicedetails.sInvoiceNumber = invoiceheaders.sInvoiceNumber

where (

(YEAR(datInvoiceDate) = YEAR(NOW()))

AND (MONTH(datInvoiceDate) = MONTH(NOW()))

)

GROUP BY iccategories.sdescription

List All Items Received From A Selected Vendor Sorted By Receipt Date

SELECT

icpoheaders.svendor as 'VENDOR',

icporeceiptheaders.datreceived AS 'RECVD',

icporeceiptlines.sitemnumber AS ITEM,

icporeceiptlines.lreceiptheaderid AS 'RECEIPT #',

IF(icporeceiptheaders.lpostedtoic = 0, 'N', 'Y') AS 'POSTED?',

icporeceiptlines.bdqtyreceived AS 'QTY RCVD.',

icporeceiptlines.sunitofmeasure AS UOM,

icporeceiptlines.bdextendedcost AS 'EXT.COST',

IF (icporeceiptlines.bdqtyreceived != 0.00,icporeceiptlines.bdextendedcost / icporeceiptlines.bdqtyreceived, 0.00)

AS'UNIT COST',

icporeceiptlines.sitemdescription AS DESCRIPTION,

icporeceiptlines.slocation AS 'LOC.',

IF(icporeceiptlines.lnoninventoryitem = 1, 'N', 'Y') AS 'INV?',

icporeceiptheaders.lpoheaderid AS 'PO#',

icporeceiptheaders.sinvoicenumber AS 'INV #'

from icporeceiptlines LEFT JOIN icporeceiptheaders ON icporeceiptlines.lreceiptheaderid =

icporeceiptheaders.lid

LEFT JOIN icpoheaders on icporeceiptheaders.lpoheaderid = icpoheaders.lid

WHERE (

(icpoheaders.svendor = '[[Enter the vendor's account code]]')

AND (icporeceiptheaders.lstatus = 0)

)

ORDER BY icporeceiptheaders.datreceived

List All Items Received From A Selected Vendor Sorted By Item Number

SELECT

icpoheaders.svendor as 'VENDOR',

icporeceiptheaders.datreceived AS 'RECVD',

icporeceiptlines.sitemnumber AS ITEM,

icporeceiptlines.lreceiptheaderid AS 'RECEIPT #',

IF(icporeceiptheaders.lpostedtoic = 0, 'N', 'Y') AS 'POSTED?',

icporeceiptlines.bdqtyreceived AS 'QTY RCVD.',

icporeceiptlines.sunitofmeasure AS UOM,

icporeceiptlines.bdextendedcost AS 'EXT.COST',

IF (icporeceiptlines.bdqtyreceived != 0.00,icporeceiptlines.bdextendedcost / icporeceiptlines.bdqtyreceived, 0.00)

AS'UNIT COST',

icporeceiptlines.sitemdescription AS DESCRIPTION,

icporeceiptlines.slocation AS 'LOC.',

IF(icporeceiptlines.lnoninventoryitem = 1, 'N', 'Y') AS 'INV?',

icporeceiptheaders.lpoheaderid AS 'PO#',

icporeceiptheaders.sinvoicenumber AS 'INV #'

from icporeceiptlines LEFT JOIN icporeceiptheaders ON icporeceiptlines.lreceiptheaderid =

icporeceiptheaders.lid

LEFT JOIN icpoheaders on icporeceiptheaders.lpoheaderid = icpoheaders.lid

WHERE (

(icpoheaders.svendor = '[[Enter the vendor's account code]]')

AND (icporeceiptheaders.lstatus = 0)

)

ORDER BY icporeceiptlines.sitemnumber,

icporeceiptheaders.datreceived

Show 'Turnaround' Time For A Particular Range Of Items

(This query shows the number of days from the time the customer placed the order until it was shipped, effectively telling you how quick the 'turnaround' is for a given range of items. It is sorted by turnaround time, listing the items from the shortest turnaround time to the longest.)

select

invoicedetails.sLocationCode as 'LOCATION',

date_format(orderheaders.datOrderDate, '%c%/%e%/%Y') AS 'ORDER DATE',

date_format(invoiceheaders.datInvoiceDate, '%c%/%e%/%Y') AS 'INV DATE',

DATEDIFF(invoiceheaders.datInvoiceDate, orderheaders.datOrderDate) AS

'TURNOVER DAYS',

CONCAT('<A HREF=\*LINKBASE*smic.ICDisplayItemInformation?ItemNumber=',

invoicedetails.sItemNumber, '&SESSIONTAG=*SESSIONTAG*>',

invoiceDetails.sItemNumber, '</A>') as 'ITEM #',

invoicedetails.sDesc AS 'DESCRIPTION',

CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMPrintInvoice?InvoiceNumberFrom=',

TRIM(invoiceheaders.sInvoiceNumber), '&SESSIONTAG=*SESSIONTAG*>',

TRIM(invoiceheaders.sInvoiceNumber), '</A>') as 'INV #',

CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=',

invoiceheaders.strimmedordernumber, '&SESSIONTAG=*SESSIONTAG*>',

invoiceheaders.strimmedordernumber, '</A>') as 'ORDER #'

from invoicedetails LEFT JOIN invoiceheaders ON

invoicedetails.sInvoiceNumber = invoiceheaders.sInvoiceNumber

LEFT JOIN orderheaders on invoiceheaders.strimmedordernumber =

orderheaders.strimmedordernumber

WHERE (

(LEFT(invoicedetails.sItemNumber, 3) = '[[Enter the first three characters of the item number range you are looking for (for example enter RSD for residential doors)]]')

AND (invoiceheaders.datInvoiceDate > '[[Enter a starting invoice date in YYYY-MM-DD format (for example 2011-01-01))]]') AND (orderheaders.iOrderType != 4)

) ORDER BY invoicedetails.sLocationCode, `TURNOVER DAYS`

Show Gross Profit For A Selected Salesperson's Orders

SELECT

CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=', InvoiceQuery.strimmedordernumber, '&SESSIONTAG=*SESSIONTAG*>', InvoiceQuery.strimmedordernumber, '</A>') as 'ORDER #',

`SALESPERSON`,

FORMAT(`TOTAL MATERIAL COST`,2) AS `TOTAL MATERIAL COST`,

`TOTAL HOURS`,

`TOTAL EST. LABOR COST`,

`TOTAL BILLED`,

FORMAT((`TOTAL BILLED` - (`TOTAL MATERIAL COST` + `TOTAL EST. LABOR COST`)),2) AS 'GROSS PROFIT',

FORMAT(((`TOTAL BILLED` - (`TOTAL MATERIAL COST` + `TOTAL EST. LABOR COST`))/(`TOTAL BILLED`) * 100),2) AS 'GP %'

FROM (

SELECT

invoiceheaders.strimmedordernumber,

invoiceheaders.sSalesperson as 'SALESPERSON',

SUM(invoicedetails.dExtendedCost) AS 'TOTAL MATERIAL COST',

SUM(invoicedetails.dExtendedPriceAfterDiscount) AS 'TOTAL BILLED'

FROM invoiceheaders LEFT JOIN invoicedetails

ON invoiceheaders.sInvoiceNumber = invoicedetails.sInvoiceNumber

GROUP BY invoiceheaders.sOrderNumber

HAVING invoiceheaders.sSalesperson = '[[Enter the salesperson's number:]]'

) as InvoiceQuery,

(

SELECT SUM(bdqtyofhours) AS 'TOTAL HOURS',

SUM(bdqtyofhours) * [[Enter an estimated hourly rate (for example 40.00):]] AS 'TOTAL EST. LABOR COST',

strimmedordernumber FROM workorders

GROUP BY workorders.strimmedordernumber

) AS JobCostQuery

WHERE InvoiceQuery.strimmedordernumber = JobCostQuery.strimmedordernumber

List Invoices Over 90 Days Sorted By Salesperson

select

CONCAT(TRIM(Salesperson.sSalespersonFirstName), ' ', TRIM(Salesperson.sSalespersonLastName)) AS SALESPERSON,

artransactions.sdocnumber as 'INV#',

artransactions.sordernumber as 'ORD#',

DATE_FORMAT(artransactions.datdocdate, "%m/%d/%Y") as 'INV DATE',

DATE_FORMAT(artransactions.datduedate, "%m/%d/%Y") as 'DUE DATE',

DATEDIFF(NOW(), artransactions.datduedate) AS 'PAST DUE',

artransactions.doriginalamt as 'INV AMT',

artransactions.dcurrentamt as 'REMAINING',

IF(artransactions.iretainage = 1, 'Y', 'N') AS 'RETAINAGE?',

orderheaders.sBillToName as 'BILL TO'

from artransactions LEFT JOIN orderheaders

ON artransactions.sordernumber = orderheaders.strimmedordernumber

LEFT JOIN Salesperson ON orderheaders.sSalesperson = Salesperson.sSalespersonCode

WHERE (

(idoctype = 0)

AND (dcurrentamt != 0.00)

AND (DATEDIFF(NOW(), artransactions.datduedate) > 90)

AND (orderheaders.iOrderType != 4))

ORDER BY orderheaders.sSalesperson

Planned Maintenance Mailing Selected By Warranty Expiration Date (ODC Washington)

select

CONCAT(

sBillToName

, ' '

, IF (TRIM(sBillToAddressLine1) != '', CONCAT(sBillToAddressLine1, ' '), '')

, IF (TRIM(sBillToAddressLine2) != '', CONCAT(sBillToAddressLine2, ' '), '')

, IF (TRIM(sBillToAddressLine3) != '', CONCAT(sBillToAddressLine3, ' '), '')

, IF (TRIM(sBillToAddressLine4) != '', CONCAT(sBillToAddressLine4, ' '), '')

, CONCAT (

IF (TRIM(sBillToCity) != '', CONCAT(sBillToCity, ", "), ''),

IF (TRIM(sBillToState) != '', CONCAT(sBillToState, " "), ''),

IF (TRIM(sBillToZip) != '', CONCAT(sBillToZip, " "), '')

)

, ' '

, 'Attn: '

, TRIM(sBillToContact)

, ' '

, 'Re: Warranty Expiration Notice - '

, sShipToName

, ' '

, '     (Our order number: '

, CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=', strimmedordernumber, '&SESSIONTAG=*SESSIONTAG*>', strimmedordernumber, '</A>) ')

, 'Thank you for purchasing '

, '*** PRODUCT NAME HERE ***'

, ' from the Overhead Door Company of Washington, DC, Inc.'

, ' Our records indicate that the warranty of the above listed '

, 'equipment expired on '

, DATE_FORMAT(datwarrantyexpiration, '%m/%d/%Y') , '.'

, ' '

, 'As a property owner/manager you are sensitive to maintenance costs '

, 'and the need for reliability and security provided '

, 'by the this equipment at your facility. '

, 'We are pleased to introduce for your consideration '

, 'our Planned Maintenance Program.'

, ' '

, 'The purpose of a Planned Maintenance Program is to establish a consistent routine'

, ' for maintenance to your overhead sectional doors,'

, ' rolling doors, door operators, and loading dock equipment.'

, ' Early detection of operational malfunctions is the objective'

, ' of our Planned Maintenance Program.'

, ' Experience has shown us that a periodic Planned Maintenance Program'

, ' can reduce downtime and repair expenses, while extending'

, ' useful life and operating efficiency of your equipment.'

, ' '

, 'We would be happy to provide you with additional information'

, ' and a price proposal for a Planned Maintenance Program.'

, ' Please contact me at 301 937-1800 or email me at'

, ' RonHenry@doorsnow.com.'

, ' '

, 'At Overhead Door Company of Washington, DC Emergency Service'

, ' is available on a 24-7 basis.'

, ' Our Service Department is committed to meeting your needs'

, ' and exceeding your expectations.'

, ' We appreciate your business and the opportunity to serve you.'

, ' '

, 'Ronald L. Henry'

, ' '

, 'Service Manager'

, ' '

) AS 'WARRANTY EXPIRATION FOLLOW-UP LETTERS'

FROM orderheaders

WHERE (

(sServiceTypeCodeDescription = 'Commercial Installation')

AND (datwarrantyexpiration >= '[[Enter the starting expiration date in YYYY-MM-DD format, for example 2011-01-01:]]')

AND (datwarrantyexpiration <= '[[Enter the ending expiration date in YYYY-MM-DD format, for example 2011-12-31:]]')

AND (orderheaders.iOrderType != 4)

)

List Tax Totals For A Selected Invoice Date Range

SELECT

invoiceheaders.sTaxGroup as 'TAX GROUP',

tax.sTaxTypeDesc as 'TAX CLASS',

FORMAT(SUM(dExtendedCost),2) AS COST,

FORMAT(SUM(dExtendedPriceAfterDiscount),2) AS PRICE,

FORMAT(SUM(dLineTaxAmount),2) AS TAX

FROM invoicedetails LEFT JOIN invoiceheaders ON invoicedetails.sInvoiceNumber = invoiceheaders.sInvoiceNumber

LEFT JOIN tax ON (

(invoiceheaders.sTaxGroup = tax.sTaxJurisdiction)

AND (invoiceheaders.iTaxClass = tax.iTaxType)

)

WHERE (

(datInvoiceDate >= '[[Enter the starting date in YYYY-MM-DD format:]]')

AND (datInvoiceDate <= '[[Enter the ending date in YYYY-MM-DD format:]] 23:59:59')

)

GROUP BY invoiceheaders.sTaxGroup, invoiceheaders.iTaxClass

PO Invoice Costs VS. Receipt and PO Costs

select

icpoinvoiceheaders.datinvoice as 'INV. DATE'

, CONCAT('<A HREF=\*LINKBASE*smic.ICEditPOEdit?lid=',

CAST(icpolines.lpoheaderid AS CHAR), '&CallingClass=smic.ICEditPOSelection&SESSIONTAG=*SESSIONTAG*>', CAST(icpolines.lpoheaderid AS CHAR), '</A>') as 'PO #'

, icpoinvoicelines.bdqtyreceived as 'QTY RCVD'

, icpoinvoicelines.sitemnumber AS 'ITEM'

, icporeceiptlines.sitemdescription as 'DESCRIPTION'

, icpoinvoicelines.sunitofmeasure as 'UOM'

, if (icpoinvoicelines.lnoninventoryitem = 0, "Y", "N") AS 'INV?'

, icpolines.bdextendedordercost AS 'PO COST'

, icpoinvoicelines.bdreceivedcost as 'REC COST'

, icpoinvoicelines.bdinvoicedcost as 'INV COST'

, (icpoinvoicelines.bdreceivedcost - icpolines.bdextendedordercost) AS 'REC > PO'

, (icpoinvoicelines.bdinvoicedcost - icpoinvoicelines.bdreceivedcost) AS 'INV > REC'

, icpoinvoicelines.lporeceiptid AS 'REC #'

FROM icpoinvoicelines LEFT JOIN icpoinvoiceheaders on icpoinvoiceheaders.lid = icpoinvoicelines.lpoinvoiceheaderid

LEFT JOIN icporeceiptlines on icporeceiptlines.lid = icpoinvoicelines.lporeceiptlineid

LEFT JOIN icpolines on icporeceiptlines.lpolineid = icpolines.lid

WHERE (

(icpoinvoiceheaders.lexportsequencenumber > 0)

AND (

NOT (

(icpoinvoicelines.bdreceivedcost = icpoinvoicelines.bdinvoicedcost)

AND (icpoinvoicelines.bdreceivedcost = icpolines.bdextendedordercost)

)

AND (icpoinvoicelines.lporeceiptid != -1)

AND (icporeceiptlines.sitemdescription != '')

)

ORDER BY icpoinvoiceheaders.datinvoice DESC

List Costs On Hand In Inventory For Items Not On Open Orders

SELECT

CONCAT('<A HREF=\*LINKBASE*smic.ICDisplayItemInformation?ItemNumber=', icitemlocations.sitemnumber, '&SESSIONTAG=*SESSIONTAG*>',

icitemlocations.sitemnumber, '</A>') as 'ITEM'

, icitems.sitemdescription AS DESCRIPTION

, icitemlocations.slocation AS LOCATION

, icitemlocations.bdqtyonhand AS 'QTY ON HAND'

, icitemlocations.bdtotalcost AS 'TOTAL COST'

FROM icitemlocations

LEFT JOIN icitems on icitemlocations.sitemnumber = icitems.sitemnumber

LEFT JOIN

(select

DISTINCT

orderdetails.sItemNumber as ITEMONORDER

FROM orderdetails LEFT JOIN orderheaders on orderdetails.strimmedordernumber = orderheaders.strimmedordernumber

WHERE (

((orderheaders.datOrderCanceledDate IS NULL) OR (orderheaders.datOrderCanceledDate < '1901-01-01'))

AND (orderheaders.iOrderType != 4)

AND (orderdetails.dQtyOrdered > 0.0000)

)

) AS ITEMONORDERQUERY

ON icitemlocations.sitemnumber = ITEMONORDERQUERY.ITEMONORDER

WHERE (

(icitemlocations.bdtotalcost > [[Enter a minimum cost to look for with no commas:]])

AND (ITEMONORDERQUERY.ITEMONORDER IS NULL)

) ORDER BY icitemlocations.bdtotalcost DESC, icitemlocations.sitemnumber, icitemlocations.slocation

Commercial Installation Backlog By Expected Ship Date

SELECT

ORDHEADERS.sSalesperson AS SP

, date_format(ORDHEADERS.datExpectedShipDate, '%c%/%e%/%Y') as 'EXP. SHIP DATE'

, CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMDisplayOrderInformation?OrderNumber=', ORDHEADERS.strimmedordernumber, '&SESSIONTAG=*SESSIONTAG*>', ORDHEADERS.strimmedordernumber, '</A>') as 'ORDER #'

, ORDHEADERS.sBillToName as 'BILL TO'

, ORDHEADERS.sShipToName as 'SHIP TO'

, ORDERTOTALQUERY.ORDERTOTAL AS 'ORDER VALUE'

, INVOICETOTALQUERY.INVOICETOTAL AS 'INVOICED AMT'

FROM

(SELECT

orderheaders.sSalesperson

, orderheaders.datExpectedShipDate

, orderheaders.strimmedordernumber

, orderheaders.sBillToName

, orderheaders.sShipToName

FROM orderheaders

where

(

(orderheaders.datExpectedShipDate >= '[[Enter the starting expected ship date as YYYY-MM-DD:]]')

AND (orderheaders.datExpectedShipDate <= '[[Enter the ending expected ship date as YYYY-MM-DD:]] 00:00:00')

AND (

(orderheaders.datOrderCanceledDate IS NULL) OR (orderheaders.datOrderCanceledDate < '1901-01-01')

AND (orderheaders.iOrderType != 4)

)

AND (orderheaders.sServiceTypeCode = 'SH0004')

)

) AS ORDHEADERS

LEFT JOIN

(SELECT

orderdetails.strimmedordernumber AS TRIMMEDORDERNUMBER

, SUM((orderdetails.dqtyOrdered + orderdetails.dQtyShippedToDate) * orderdetails.dOrderUnitPrice) AS 'ORDERTOTAL'

from orderdetails

GROUP BY strimmedordernumber)

AS ORDERTOTALQUERY

ON ORDHEADERS.strimmedordernumber = ORDERTOTALQUERY.TRIMMEDORDERNUMBER

LEFT JOIN

(SELECT

invoiceheaders.strimmedordernumber AS TRIMMEDORDERNUMBER

, SUM(invoicedetails.dExtendedPrice) AS 'INVOICETOTAL'

from invoiceheaders LEFT JOIN invoicedetails ON invoiceheaders.sInvoiceNumber = invoicedetails.sInvoiceNumber

GROUP BY strimmedordernumber)

AS INVOICETOTALQUERY

ON ORDHEADERS.strimmedordernumber = INVOICETOTALQUERY.TRIMMEDORDERNUMBER

ORDER BY ORDHEADERS.sSalesperson, ORDHEADERS.datExpectedShipDate

List Of Items Received On Purchase Orders By Received Date

Prompts for a range of received dates, locations, and starting and ending range of vendors - lists items received with links to the receipt, the purchase order, and the item information.

SELECT CONCAT('<A HREF=\*LINKBASE*smic.ICEditReceiptEdit?lpoheaderid=', CAST(icporeceiptheaders.lpoheaderid AS CHAR),'&lid=',CAST(icporeceiptheaders.lid AS CHAR),'&CallingClass=smic.ICEditPOEdit&SESSIONTAG=*SESSIONTAG*>',CAST(icporeceiptheaders.lid AS CHAR), '</A>') AS 'Receipt #'

,CONCAT('<A HREF=\*LINKBASE*smic.ICEditPOEdit?lid=',CAST(icporeceiptheaders.lpoheaderid AS CHAR),'&CallingClass=smic.ICEditPOSelection&SESSIONTAG=*SESSIONTAG*>',CAST(icporeceiptheaders.lpoheaderid AS CHAR),'</A>') AS 'PO #'

,icporeceiptlines.slocation AS 'Location'

,icporeceiptlines.bdqtyreceived AS 'Qty Received'

,CONCAT('<A HREF=\*LINKBASE*smic.ICDisplayItemInformation?SubmitEdit=Y&ItemNumber=',icporeceiptlines.sitemnumber,'&SESSIONTAG=*SESSIONTAG*>',icporeceiptlines.sitemnumber, '</A>') AS 'Item #'

,icporeceiptlines.sitemdescription AS 'Description'

,icporeceiptheaders.datreceived AS 'Date Received'

,icpoheaders.svendorname AS 'Vendor'

,icitems.sComment1 AS 'Comment 1'

FROM icporeceiptlines

LEFT JOIN icporeceiptheaders ON icporeceiptlines.lreceiptheaderid = icporeceiptheaders.lid

LEFT JOIN icpoheaders ON icporeceiptheaders.lpoheaderid = icpoheaders.lid

LEFT JOIN icitems ON icporeceiptlines.sitemnumber = icitems.sItemNumber

WHERE (

(icporeceiptheaders.datreceived >= str_to_date('[[Enter Starting Arrival Date in m/d/yyyy Format]] 00:00:00','%c/%e/%Y %T'))

AND (icporeceiptheaders.datreceived <= str_to_date('[[Enter Ending Arrival Date in m/d/yyyy Format]] 23:59:59','%c/%e/%Y %T'))

AND (INSTR('[[Enter Location Codes separated by commas:]]', icporeceiptlines.slocation) > 0)

AND (icpoheaders.svendor >= '[[Enter Lower Limit for Vendor Name]]')

AND (icpoheaders.svendor <= '[[Enter Upper Limit for Vendor Name]]')

) ORDER BY icpoheaders.svendor

,icporeceiptheaders.lid

,icporeceiptlines.llinenumber

List Physical Inventory Variances Showing Largest Variances First

SELECT icinventoryworksheet.sitemnumber AS 'Item #'

, icitems.sitemdescription AS 'Item Desc.'

, icinventoryworksheet.sinvacct AS 'Inv. Acct.'

, icinventoryworksheet.swriteoffacct AS 'Write-Off Acct.'

, icitems.scostunitofmeasure AS 'UOM'

, ROUND(icinventoryworksheet.bdqtyonhand,4) AS 'Qty on Hand'

, COUNTQUERY.countedqty as 'Qty Counted'

, ROUND(icitems.bdmostrecentcost,2) AS 'Most Recent Cost'

, ROUND(IF (COUNTQUERY.countedqty IS NULL, -1 * icinventoryworksheet.bdqtyonhand, COUNTQUERY.countedqty - icinventoryworksheet.bdqtyonhand), 4) as 'Qty Variance'

, ROUND(IF (COUNTQUERY.countedqty IS NULL, -1 * icinventoryworksheet.bdqtyonhand * icitems.bdmostrecentcost,

(COUNTQUERY.countedqty - icinventoryworksheet.bdqtyonhand) * icitems.bdmostrecentcost), 2) AS 'Cost Variance'

FROM

icinventoryworksheet

LEFT JOIN

(SELECT

icphysicalcountlines.sitemnumber

, icphysicalcountlines.lphysicalinventoryid

, SUM(icphysicalcountlines.bdqty) as countedqty

FROM icphysicalcountlines

GROUP BY icphysicalcountlines.lphysicalinventoryid, icphysicalcountlines.sitemnumber

) AS COUNTQUERY

ON (icinventoryworksheet.sitemnumber = COUNTQUERY.sitemnumber)

AND (icinventoryworksheet.lphysicalinventoryid = COUNTQUERY.lphysicalinventoryid)

LEFT JOIN icitems on icitems.sItemNumber = icinventoryworksheet.sitemnumber

WHERE (

(icinventoryworksheet.lphysicalinventoryid = [[Enter the physical inventory ID:]])

)

ORDER BY

ROUND(IF (COUNTQUERY.countedqty IS NULL, -1 * icinventoryworksheet.bdqtyonhand * icitems.bdmostrecentcost,

(COUNTQUERY.countedqty - icinventoryworksheet.bdqtyonhand) * icitems.bdmostrecentcost), 2) DESC

, icinventoryworksheet.sitemnumber

Page updated

Google Sites

Report abuse