Incode Systems, Inc.

Enterprise System Standard Reports

(Last modified: Friday, November 24, 2006 12:54pm)

Reports by Category

General Accounting Reports
Accounts Payable Reports
Accounts Receivable Reports
Excel Reports
Inventory Reports
Order Reports
OutNBack Reports
Payroll Reports
Purchasing Reports
Pricing Reports
Sales Reports
System Reports
Utility Reports

Reports by Name

AllPrice
APApprv Report
APApprv Process
APApprv1 Process
APCheck
APClear
APUnApr1 Process
APUnAprv Process
ARAGE
ARAGED
ARCustNo
ARHist
AuditCnt
BelowMin
BOI
BOMChg
BOMCopy
CheckBal
CheckReg
ChkAmt
ChkNonH
CntComp1
CntCompG
COA
CrossBr
CstOrder
CstSls
CstSlsSm
CustBal
CustLbls
CustSale
CustSTax
CustUnit
DeadInvn
DelPOLin
DepSlip
Discrep
DSO
EarlyIn
EarlyOut
EMail Customer Invoices
EMail Past Due Invoices
Excel Templates
ExclInvG
ExclInvn
ExclSldG
ExclSold
GLAcct
GLEOM
GLFracs
GLFracsFix
GLPeriodvsTranDate
GLTrans
Inactive Customers
Invn_AP
InvnHLvl
InvnLow
Invoice
Invoices Hidden
InvPerDy
ItemSale
JobBOM
JobDetl
JobList
JobOpenOrders
LastSale
LateIn
LateOut
MaxTime
MaxUnit
MfgSale
MfgSales
MinMax
MultUnit
NewCust
NewUnit
OpenPO
POSTAR
POReceiv
POUnpaid
PrchHist
Reconcil
RefPrice
Reprice
SalesByCustomer
Sls3yrMf
SlsmnHst
STaxCmp
TaxSum
TopSold
TrialBal
UnPstInv
WhoAmI
XOrdAval
Zip_Plus


General Accounting Reports

[top]

COA.rpt
Chart of Accounts Report

GLAcct.rpt
G/L Account Report
Prompts for First Account, Last Account, First Period, and Last Period. Returns all GL table columns ordered by Acct, Period, TranDate, and TranID in a grid.

GLEOM.bat, GLEOM.sql
G/L End of Month Processing
Prompts for First date of the period to close. Sets GLPeriod.Status = 'C', summarizes all G/L activity to GLBal for the period, then adds a new row to GLPeriod for the next period.

GLFracs.sql
Returns TranID and Amount for all GL.Amount that have more than 2 decimal places.

GLFracsFix.sql
Rounds GL.Amount to 2 decimal places for all rows with more than 2 decimal places.

GLPeriodvsTranDate.sql
Returns rows from GL table where TranDate is outside range of dates for a given GL.Period and Acct. For example, if 201208 is given as the Period, and 1001 as the Acct, rows with Period = 201208 and Acct = 1001, but TranDate not in the range 8/1/2012 through 8/31/2012 are returned.

GLTrans.rpt
G/L Transaction Report
Prompts for First Date and Last Date. Returns all GL table columns ordered by TranDate, and TranID in a grid.

GLTrial.sql
G/L Trial Balance Report
Returns a GLTrial.prn (column aligned text) file with AcctNum, Description, Balance for all accounting in COA. Balance column is the GLBal.Amount + SUM(GL.Amount).

Reconcil.sql
Reconcil Check Register Report
Returns a tab output with bank account reconciliation.

TrialBal.bat
G/L Trial Balance Report
Returns a TrialBal.txt (tab-delimited text) file with Description, AcctNum, Debit, Credit for all accounts in COA.
Accounts with no activity have nothing in the Debit or Credit columns.


Accounts Payable Reports

[top]

APApprv.rpt
Approval report for running A/P Checks.
APApprv.rpt returns a listing of the items that are approved
(AP.ApprovedBy) and have not already been paid.
Includes subtotals by vendor and a grand total.
If this report reveals items or vendors that should not
be paid, the items may be manually unapproved and the
report rerun.

APApprv.sql
This process automatically approves all vendor invoices by
It prompts for a cutoff date and sets AP.ApprovedBy to USER_NAME()
for all invoices not previously paid (AP.CheckNo IS NULL) and
AP.DueDate <= the cutoff date.
This process provides a quick way to approve all the vendor
invoices which are due. APUnAprv.sql should be run first to
clear all previous approvals. APApprv.rpt may then be run to
determine any you wish to unapprove. APApprv1.sql may be used
if you want to process a check for a single vendor.

APCheck.zip
Contains APCheck (PAGE routine), APCheck.rpt, APCheck.bat.
APCheck.bat runs all approved checks where CheckNo IS NULL
Output is APCheck.gdm and APCheck.sql. APCheck.sql imports
the check information back into the AP table when checks are
printed.

APClear.sql
Clears check information from the AP table for a given range of CheckNo

APUnApr1.sql
This process automatically unapproves vendor invoices for
a single vendor by setting AP.ApprovedBy to USER_NAME() for
all invoices not previously paid (AP.CheckNo IS NULL).
This does the opposite of the APApprv1.sql.

APUnAprv.sql
This process automatically unapproves all vendor invoices by
setting AP.ApprovedBy to USER_NAME() for all invoices
not previously paid (AP.CheckNo IS NULL). This does the
opposite of the APApprv.sql.

CheckBal.sql
Returns CheckReg data for a given AcctNum with a running balance, like a check book register.

CheckReg.sql
Returns CheckReg data for a given AcctNum with a Skip column indicating if the check number represents a skip in the sequence.
Note: This report assumes that all CheckNum values may be CONVERTed to integers.

Invn_AP.sql
Input: Cutoff Date
Output: INVN_AP.TXT
Output type: Tab delimited
Output format: QtyRecd, PONum, POLineNum, Mfg, StkNum, Unit, Price, QtyPaid, AvgPricePaid, QtyPayable, ExtPayable
Returns record above for items received before Cutoff Date
This analysis provides information about Accounts Payable as of a certain date, like the end of an accounting period.
For example, a Cutoff Date of 1/1/2000 would return Payables level up to 12/31/1999


Accounts Receivable Reports

[top]

ARAGE
Summary level A/R Aging Report.
Given a Cutoff date and minimum balance, customers with a balance exceeding the minimum are shown, one line per customer, with columns for Total, Current, 30, 60, 90, Future.
Reports is ordered by customer.

ARAGED
Detailed A/R Aging Report.
Given a Cutoff date and minimum balance, customers with a balance exceeding the minimum are shown, one line per invoice with columns for Total, Current, 30, 60, 90, Future for each customer. Uses the same data file as ARAGE so the two reports may be run in synch.
Reports is ordered by customer.

ARCustNo.sql
returns InvoiceNo, TranNum and CustNo from AR where the AR.CustNo does not match Invoice.CustNo

ARHist.sql
(A/R History)
Query that returns CustNo, BillName, InvoiceCount, MinDays, MaxDays, AvgDays
These are the count, low, high, and average number of days to pay invoices dated within a given date range. Also prompted for Min_AvgDays and CustNo. Min_AvgDays limits output to customer with AvgDay >= Min_AvgDays (enter 0 to see all). CustNo limits output to a single CustNo (enter 0 to see all).

ChkAmt.sql
(Check Amount)
Query that returns the sum of all A/R payment transactions for a given CustNo and Check Number.

CustBal.rpt
(Customer Balance)
Report that returns the open balance for a given Customer Number (CustNo).

DelPOLin.bat
(Del PO Line Routine)
Reads a tab=delimited file name DelPOLin.txt containing the columns PONum LineNum and deletes all the matching rows in the POLines table. Some rules in POLines that a significant amount of time to process; each row may take a minute or more to delete. This routine is helpful when you have a large number of lines to delete.

DepSlip.rpt
(Deposit Slip Report)
Returns a deposit slip for all payments entered on a given date.

DSO.sql
(Days Sales Outstanding Report)
DSO.sql returns CustNo, A/R, Sales, Days, and DSO (Receivables / (Sales / Days)) for a given FirstDate, LastDate, and CustNo.
(note: if CustNo is NULL, it returns for all customers)

POSTAR
Posts invoices with a given date to A/R.

POReceiv.sql
Returns receipts for a given PO Number.

UnPstInv
(Unpost Invoice)
A routine consisting of an Access eXpert report and PAGE script which generates a SQL script which unposts invoices for a given days invoicing.


Excel Templates

[top]

Excel templates are WorkBooks containing Excel code which provide "drill down" capability to Enterprise System applications or other programs or documents.

A template for a particular report may be created from the master template named

DrillDn.xls
by loading the master, adding DrillDown specs, and saving the WorkBook under another name. A new version of the particular report may be created by simply pasting data into this template.


Inventory Reports

[top]

AuditCnt.sql
This script returns the most recent InvnCnts record for a given item prior to a given date, plus a PastAuditCnt column which is the calculated AuditCnt from Inventry on the given date.
This is an example of how to determine what the Inventry.AuditCnt was at some time in the past.

BelowMin.sql
Report on low inventory levels for a particular customer.
Returns inventory information from Inventry for a given CutoffDate and CustNo for items ordered by CustNo since CutoffDate and the OnHandQty <= MinQty. All quantities are aggregated for all units.

DeadInvn.sql
(Dead Inventory Report)
Output: DEADINVN.TXT
Output type: Tab delimited
Output format:
Mfg,StkNum,Unit,ItemNum,LastSoldDate,OnHandCnt,InvoiceNo,CustNo,BillName,Description
Returns record above for each item from Inventory where the OnHandCnt > 0 (inventory is on hand) and LastSoldDate <= :dtOldestDate (the last time we sold any was prior to a given date)

Discrep.sql
(Physical Inventory Discrepancy Report)
Output format: Br,Dept,Mfg,StkNum,Unit,Cost,BinLoc,ItemNum,OnHandCnt,AuditCnt,Discrepancy,Description
(where Discrepancy is AuditCnt - OnHandCnt).
Returns record above for each item from Inventory where the Physical.OnHandCnt <> 0 or Inventry.AuditCnt <> 0 and Discrepancy <> 0. This report is designed to run after Physical has been loaded with the counts from a physical inventory and will report the discrepancy between the counts entered and the previous OnHandCnt (before physical inventory was taken), even after business has resumed, so long as all AuditCnts are inventory related.

ExclInvG.sql
(Exclusive Inventory by Customer Group)
Input: CustGrp.GrpID
Output: EXCLINVG.TXT
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,ItemNum,SoldCount1
Returns record above for each item from Inventory where the OnHandCnt > 0 and exclusively bought by customers in CustGrp.GrpID

ExclInvn.sql
(Exclusive Inventory)
Input: CustNo
Output: EXCLINVN.TXT
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,ItemNum,SoldCount1
Returns record above for each item from
Inventory where the OnHandCnt > 0 and
exclusively bought by a given CustNo

ExclSldG.sql
(Exclusive Sold by Group)
Input: CustGrp.GrpID
Output: EXCLSLDG.TXT
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,SoldCount1
Returns record above for each item from
ItemUnit exclusively bought by customer
in GrpID

ExclSold.sql
(Exclusive Sold)
Input: Customer.CustNo
Output: EXCLSLDG.TXT
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,SoldCount1
Returns record above for each item from
ItemUnit exclusively bought by customer
CustNo

InvnHLvl.sql
(Inventory Historical Level)
Query returns all the inventory items with the current cost, OnHandCnt on a given date, and the extended cost.

InvnLow.sql
(Inventory Low)
Input: (None)
Output: INVNLOW.TXT
Output type: Tab delimited
Output format: Mfg, Stknum, Unit,
QtySold, OnHandCnt, OnOrderCnt,
LastPONum, VendNo, OurAcct, Vendor.Phone
Returns record above for all items where OnHandCnt
is less than half the sum of the QtySold (from Invoice)
for the past 40 days.

InvnRtrn.sql
(Inventory Returns)
Input: Mfg, Days1, Days2
Returns inventory item information with Qty OnHand, Available, ExtendedCost for items that are nearing the deadline for return to the supplier.
Mfg is the supplier, Days1 and Days2 are used to calculate a FirstDate and LastDate to compare to the PO.PODate. FirstDate is calculated as date today minus Days1. LastDate is calculated as FirstDate plus Days2

MinMax.sql
Input: Mfg
Output: minmax.txt
Output type: Tab delimited
Output format: Mfg,StkNum,Unit, Mfg, StkNum, Unit, Current, Month - 1, Month - 2, Month - 3 Month - 4, Month - 5, Month - 6, MinQtyNow, MaxQtyNow, NewMinQty, NewMaxQty

MinMax.sql returns:
Mfg, StkNum, Unit, VendorMin, CustReq, Current, Month - 1, Month - 2, Month - 3, Month - 4, Month - 5, Month - 6, MinQtyNow, MaxQtyNow, NewMinQty, NewMaxQty

NewMinQty and NewMaxQty are proposed new MinQty and MaxQty values. They are the lowest and highest Quantities sold (plus artificial demand from XDemand) for any of the previous 6 whole months. Current Qty is shown for reference only.

XDemand is used to store Demand data that is not based on actual sales. For example, a customer may request that the company stock a certain level of inventory in anticipation of future sales. Normal XDemand data is coded to indicate the purpose of the data and how it will be processed by MinMax.sql. XDemand.Code='C' indicates normal XDemand data, treated exactly like Invoice data. The XDemand.QtyShip is added to sales using XDemand.BeginDate. An example of where this would be appropriate is when you have an unusual one time sale that you don't want to influence future MinMax calculations. Say you normally sell 25 of a particular item per month. A single customer buys 50 in one month resulting in a total of 75 sold for the month. That will result in a new peak of 75 for that item for the next 6 months. To offset that unusual one-time sale, we add a row to XDemand where XDemand.Code='C' (customer) and XDemand.QtyShip=-75 and XDemand.BeginDate=(same date at the invoice), XDemand.EndDate is ignored.

Another entirely different scenario would be a customer that requests that you stock 25 of an item for them. In this case we add a row to XDemand where XDemand.Code='S' (stock request). If XDemand.Code='S', MinMax compares the sum of rows in XDemand where XDemand.Code='S' with the sales from Invoice and takes the higher of the two. This avoid double counting when figuring demand. If we would code demand like this with XDemand.Code='C', it would be added to whatever the customer actually bought resulting in a peak that might be double what it should be. XDemand.EndDate specifies the date after which this demand will no longer be considered.

Another code that works similarly is XDemand.Code='M' which indicates that the demand is required by a vendor minimum stock level agreement. In this case, XDemand.Ref is a vendor number. XDemand.EndDate specifies the date after which this demand will no longer be considered.

MinQtyNow and MaxQtyNow are the sum of MinQty and MaxQty for all rows in Inventry with matching Mfg, StkNum, and Unit. Rows where MinQtyNow IS NULL are rows where there exists no such item in Inventry. Rows are added to the output for items in Inventry for the same Mfg where MinQty > 0 or MaxQty > 0. These rows have NULL Month - 1 columns. This allows review of the MinQty and MaxQty for items that have not sold in the last 6 months.

This routine may be used in conjunction with MinMaxIn.sql to process the output with SQLEx to update the MinQty and MaxQty in Inventry.

LeadDays.sql
Input: Mfg
Output: LeadDays.txt
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,ItemNum,LeadDays,NewLeadDays, AvgLeadDays,NewAvgLeadDays

LeadDays produces updated LeadDays and AvgLeadDays for a given Mfg.
LeadDays are defined as the number of days from the PODate to the InvnRecv.TimeCounted for the most recent receiver.
AvgLeadDays are the mean of all Lead days.
Only receipts within the last year (365 days) are considered.


Order Reports

[top]

BOI.rpt
(Backordered Inventory Report)
returns pertinent information about lines from orders which are backordered from inventory from a given branch.

ChkNonH.sql
(Check Non Hidden Lines)
returns OrdNum's of orders where non-hidden lines do not sum to Orders.SaleTotal

Contig
Contig is a PAGE routine which reads a tab file assumed to have a header row and 2 (or more) columns. The output returned is a list of the row columns where the second column value was not contiguous. For example, if the tab file contained OrdLine.OrdNum and OrdLine.LineNum sorted by both columns, the output would contain orders where the line numbers were not contiguous. If an order had the second of three lines deleted, that OrdNum would be returned along with the first non-contiguous LineNum.

CrossBr.rpt
(Cross Branch Sales Report)
Returns item information about lines in orders which contain inventory items from a different branch than the order.


OutNBack Reports

[top]

EarlyIn.rpt
EarlyIn.rpt returns: LastName,FirstName,LateMinutes,Login,DateTimeOut,DateTimeBack
from ONBHist. Contains a list of everyone who clocked in early on a given date. EarlyIn is coded for 8:00AM, but you may change that to conform to your normal start time.

EarlyOut.rpt
EarlyOut.rpt returns: LastName,FirstName,LateMinutes,Login,DateTimeOut,DateTimeBack
from ONBHist. Contains a list of everyone who clocked out early on a given date. EarlyOut is coded for 05:00PM, but you may change that to conform to your normal end time.

EMail Customer Invoices
Asks for a CustNo or CustGrp.GrpID, StartDate and EndDate. Returns a list in EditTab of all the PDF files of matching invoices. SendSMTP is opened with already filled in to send the PDF files as attachments. EditTab may be used to delete lines you don't want to send or to open (Shift-Ctrl-X) the PDF files for viewing. The users login is used to find an email address in Contacts to fill in BCC so the user gets a copy of the email.
EMail Past Due Invoices

EMail Past Due Invoices
Asks for a CustNo or CustGrp.GrpID and Cutoff_Date. Returns a list in EditTab of all the PDF files of unpaid invoices dated prior to Cutoff_Date. SendSMTP is opened with already filled in to send the PDF files as attachments. EditTab may be used to delete lines you don't want to send or to open (Shift-Ctrl-X) the PDF files for viewing. The users login is used to find an email address in Contacts to fill in BCC so the user gets a copy of the email.

LateIn.rpt
LateIn.rpt returns: LastName,FirstName,LateMinutes,Login,DateTimeOut,DateTimeBack
from ONBHist. Contains a list of everyone who clocked in late on a given date. LateIn is coded for 8:15AM, but you may change that to conform to your normal start time.

LateOut.rpt
LateOut.rpt returns: LastName,FirstName,LateMinutes,Login,DateTimeOut,DateTimeBack
from ONBHist. Contains a list of everyone who clocked out late on a given date. LateOut is coded for 05:00PM, but you may change that to conform to your normal end time. LateOut is useful when You want to monitor overtime associated with leaving later than the normal end of the day.

MaxTime.rpt
MaxTime.rpt returns: LastName,FirstName,Minutes,Login,DateTimeOut,DateTimeBack
from ONBHist for a given date and a given Reason where the (DateTimeBack - DateTimeOut) exceeds a given number of minutes. Here's an example. Suppose you want to see a list of people who clocked back in from break late. Your normal break time is 15 minutes, so you enter today's date, give "Break" as the Reason, and enter 15 as the minutes. The list will be people who clocked out on break today but clocked back in more than 15 minutes after they clocked out.


Payroll Reports

[top]

NoShow.sql
NoShow returns a list of employees employed on a given date who had no timeclock punches on that date


Purchasing Reports

[top]

MaxUnit.sql
MaxUnit.sql is a query that returns all items for a given Mfg in ItemUnit where Unit has the highest UnitCount of all items with the same Mfg and StkNum. Output is written to MaxUnit.txt as a tab-delimited file.
Returns:
dbo.ItemUnit.Mfg,
dbo.ItemUnit.StkNum,
dbo.ItemUnit.Unit,
dbo.ItemUnit.UnitCount,

MultUnit.sql
MultUnit.sql is a query that returns all items contained in ItemUnit where there exists more than one unit for the same Mfg and StkNum.
Returns:
dbo.ItemUnit.Mfg,
dbo.ItemUnit.StkNum,
dbo.ItemUnit.Unit,
dbo.ItemUnit.UnitCount,
dbo.ItemUnit.Cost,
dbo.InvnView.ItemNum (NULL if not in Inventry)

OpenPO.rpt
OpenPO.sql returns information on open lines from POLines. Open lines are lines where the quantity received (QtyRecd) <> the quantity ordered (QtyOrd)

POUnpaid.sql
POUnpaid.sql returns information on open lines from POLines, including amount not yet invoiced by vendor.

PrchHist.sql
(Purchasing History)
PrchHist.sql returns: PONum, PODate, QtyOrd, Mfg, StkNum, Unit, VendNo, VendName, Price, Unit Price, Date Received, Days (Days from PODate to RecvDate)
Sorted by Unit Price.

PrchHist returns items for a given Mfg and StkNum purchased within the last year.
PrchHist is useful to determine where to purchase a given item by showing where you have purchased it in the past, how much you paid, and how long the vendor took to deliver the product.


Pricing Reports

[top]

AllPrice.sql
AllPrice.sql returns all the prices available to CustNo for Mfg,StkNum,Unit
If PriceDate = NULL, PriceDate = today
Output: ALLPRICE.TXT
Output type: Tab delimited
Input format: CustNo,Mfg,StkNum,Unit,PriceDate
Output format: CustNo,Mfg,StkNum,Unit,PriceDate,Contract,Price

CstContr.sql
CstContr.sql returns all the prices available to CustNo for on a given date
Output: CstContr.txt
Output type: Tab delimited
Input format: CustNo,EffectiveDate
Output format: Custno,Billname,ContractEndDate,MfgName,Mfg,Contract,GPOContract,GrpCust,Category,Note,Updates

CntComp1.sql
Output: CNTCOMP1.TXT
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,Qty,PriceA,PriceB,IsContract
Provides the following analysis:
You have a customer currently eligible for ContractA. You want to analyze what customer could have saved if he had been eligible for ContractB.
Compares two contacts for a given ContAcct and date range Returns record above for each item from Contract table. Items in ContractB not contained in ContractA are not included.
Qty contains sum of all quantities from InvLine between FirstDate and LastDate.
PriceA and PriceB are determined by multiplying Contract.Cost by Factor. Factor is from CustCont for ContractA, if it exists for ContAcct. If not, it is Customer.PriceFactor.

CntCompG.sql
Output: CNTCOMPG.TXT Output type: Tab delimited Output format: Mfg,StkNum,Unit,Qty,PriceA,PriceB,IsContract Provides the following analysis: You have a customer currently eligible for ContractA. You want to analyze what customer could have saved if he had been eligible for ContractB. Compares two contacts for a given ContAcct and date range Returns record above for each item from Contract table. Items in ContractB not contained in ContractA are not included. Qty contains sum of all quantities from InvLine between FirstDate and LastDate. PriceA and PriceB are determined by multiplying Contract.Cost by Factor. Factor is from CustCont for ContractA, if it exists for ContAcct. If not, it is Customer.PriceFactor.

NewUnit.sql
Input: Tab file containing Mfg,StkNum,Unit,UnitCount
Output: (tab file)
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,UnitCount,Cost
NewUnit.sql is designed for use with SQL Exec
The Parameter file must have columns with the headings: Mfg, StkNum, Unit, and UnitCount
NewUnit produces an output file with:
Mfg, StkNum, Unit, UnitCount, and Cost
where Cost is prorated from the item from ItemUnit matching Mfg and StkNum from the parameter file which has the greatest UnitCount.
If there is more than one item with the same high UnitCount, the one with the lowest Unit name is used.
Suggested use:
You decide to start offering a large number of items in each (EA) quantities which you previously only sold by the box and case.

RefPrice.sql
(Reference Price)
Input: Tab file containing Mfg, StkNum, Unit, Cost
Output: (tab file)
Output type: Tab delimited
Output format: Mfg,StkNum,Unit,Cost

RefPrice.sql is designed for use with SQL Exec The Parameter file must have columns with the headings:
Mfg, StkNum, Unit, and Cost
RefPrice produces an output file with:
Mfg, StkNum, Unit, Cost
where Mfg, StkNum, and Unit are all the units from ItemUnit matching Mfg and StkNum from the parameter file. Cost is the prorata cost based on the unit from the parameter file.

Suggested use:
A vendor sends you a price file containing a single unit for each Mfg and StkNum. Processing the file with RefPrice will produce a tab file which may be imported to ItemUnit to update all the unit costs based on the unit contained in the price file.


Sales Reports

[top]

CstOrder.sql
CstOrder.sql returns count purchased for each
Mfg, StkNum, and Unit for given CustNo for
a given BaseDate month plus the previous 6 months.

CstSls.sql
Produces an Excel Spreadsheet that compares sales for all customers for one department from one period to another. Parameters are Dept, FirstDate1, LastDate1, FirstDate2, LastDate2
The report output is:


                       Customer Sales Comparison

                  Current Period               Previous Period			
          ______________________________ ____________________________

Bill Name   ExtCost     ExtSell   MgnPct  ExtCost    ExtSell   MgnPct SalesPct
_________ ___________ ___________ ______ __________ __________ ______ ________

AAA, Inc.   $1,750.00   $1,963.00  10.9%      $0.00      $0.00   0.0%   100.0%

CstSlsSm.bat
Produces an Excel Spreadsheet that summarizes sales by CustNo and Salesman combinations for all customers for one department within a given date range. If a customer had the same Salesman for sales in the date range, there is only one line for the customer. If the customer had two different salesmen, there will be two lines, etc.
Parameters are Dept, FirstDate, LastDate
The report output is:

Customer Name CustNo Salesman TotalSales
_____________ ______ ________ __________

CustLbls.bat
CustLbls.bat runs CustLbls.rpt and prints labels for all Customers who had 1 or more invoices within a given date range.
CustLbls.bat uses
AccessEx.exe, MsgBox.exe, and MkLabels.exe

CustSale.sql
returns a list of items purchased by a given CustNo with the count sold for the current period plus the previous 6 months.

CustSTax.rpt
returns Customer info correlated with CustShip info and SaleTax info to show a list of customer and the default tax codes and along with their descriptions and rates for all customers.

CustUnit.sql
returns a list of items purchased by a given CustNo with the average sold per month for the previous 6 months. In addition, all other units contained in Inventry for each item are included. This report provides information to a customer to analyze the appropriate unit to buy.

Inactive Customers.sql
Inactive Customers
Prompts for "First Date" and returns
a list of CustNo and BillName of Customers
who have not ordered anything since that date

Invoice.zip
Standard Invoice
This ZIP contains Invoice.rpt, Invoice (PAGE routine), and Invoice.bat.
The standard invoices are selected by InvDate, which assumes invoices are run only once per day. This could easily be customized to select by a range of invoice numbers.
When run, produces Invoice.gdm containing multiple invoices.

Invoices Hidden.sql
Invoices Hidden
Exception report returns a list of invoice
numbers where all lines are hidden and the
SaleTotal <> 0

InvPerDy.sql
Invoices Per Day
Query returns count of number of invoices for
a given day.

ItemSale
Item Sales History Report
Given a First_Date, Last_Date, Mfg, and Stknum, returns the sales for the item grouped by customer with subtotals by month.

JobBOM
(Job Bill of Materials)
List of Ordline records for all open jobs with ExtCost and subtotals by Job.

JobDetl
(Job Detail)
List of InvLine records for all open jobs with ExtCost and subtotals by Job.

JobList
List of jobs opened from a given date.
Returns JobNo, CustNo, BillName, Note, OpenDate, CloseDate

Job Open Orders
Job Open Orders writes an XLS file containing:
JobNo,JobName,OrdNum,LineNum,QtyOrd,QtyPS,Backorder,Price,BOExtPrice
for all open orders for all jobs. Returns JobNo, CustNo, BillName, Note, OpenDate, CloseDate

LastSale
Returns all customers name and address with Salesman, PriceFactor, and the date of the most recent sale (Invoice.InvDate).

MfgSale
MfgSale.sql returns count sold for each StkNum and Unit for a given group of Mfg codes for the current period plus the previous 6 months. The group of Mfg codes is identified by the MfgGrp.GrpID

MfgSales
Returns an Excel Spreadsheet summarizing current and year to date sales for all Mfg codes which had any sales year to date.
Asks for Fiscal_Year_Date, which is the date of the first day of the current fiscal year, and First_Date and Last_Date which are the first and last dates of the current period to summarize. For example, the first and last date of the current month, or the first and last date of the current quarter.

NewCust.sql
NewCust returns a list of new customers, by salesman. A new customer is defined as a customer who had no sales from the Oldest_Date to the CutOff_Date, but had Min_Sales from the CutOff_Date to the Newest_Date
-------
Output: NewCust.txt
Output type: Tab delimited
Output format: BillName,CustNo,Salesman,NewSales
Returns record above for each item from Customer from Invoice where the OldSales IS NULL AND NewSales > :flMin_Sales.
OldSales are sales between Oldest_Date and CutOff_Date. NewSales are sales between CutOff_Date and Newest_Date.

Reprice
Reprice is a routine that asks for a First and Last date, and forces repricing of all order lines for the date range where PriceClass <> M.
Files include: Reprice.rpt (Access eXpert report), Reprice (PAGE routine).
Reprice.rpt returns all the data from Ordline for the rows to reprice. This file is kept as a backup. Reprice PAGE routine generates Reprice.sql which sets all the OrdLine prices to zero to force the system to reprice the line.
SQLEx is used to run Reprice.sql, if operator confirms.

SalesByCustomer
Page32 routine that creates a SalesByCustomer.xls file containing CustNo,BillName,City,State,Zip,TotalSales
TotalSales is the sum of Invoice.SalesTotal by customer for a given date range.
Dependencies: GetParms.exe, SQLx.exe, XLImport.exe

Sls3yrMf.sql
Sls3yrMf (Salesman 3 year Sales History by Mfg Report)
Sales by Mfg 3 year comparison for a single salesman.
Fiscal year is 6/1 - 5/31
Returns tab-delimited text file: Sls3yrMf.txt in the form:
Mfg,MfgName,Y1ExtPrice,Y1ExtCost,Y2ExtPrice,Y2ExtCost,Y3ExtPrice,Y3ExtCost

SlsmnHst.rpt
SlsmnHst (Salesman History Report)
The Salesman History Report consists of an Access eXpert report (SlsmnHst.rpt), and a PAGE routine which summarizes and formats the data. SlsmnHst.rpt returns a tab file containing Salesman, Billname, Custno,Invdate,SaleTotal from Invoice, for a range of dates ordered by Salesman, Custno, and Invdate. The PAGE routine has a cutoff date parameter which is used to differentiate "Previous Year" from "Year to date" numbers. Invoices with dates less than the cutoff are accumulated to the Previous Year. So, the cutof date should be the first day of the fiscal year. The report output is:


Salesman Sales History Report (by Customer)
1/14/2003 11:54am

SLS Bill Name                           Cust No  Previous Yr  Year to date
___ ___________________________________ ________ ____________ ____________

AAA Customer 1                            100001     6,006.21     3,003.24
AAA Customer 2                            100002     6,012.21     3,006.24
AAA Customer 3                            100003     6,018.21     3,009.24
                                                 ____________ ____________

Salesman Total .................................    18,036.63     9,018.72

STaxCmp.sql
STaxCmp (Sales Tax Compare)
Output type: Tab delimited
Output format: InvoiceNo, TaxTotal, Tax, Difference
Input: First_InvoiceNo, Last_InvoiceNo, Maximum_Difference
Where: Difference >= Maximum_Difference
(Use Maximum_Difference = 0 to return all)
STaxCmp was designed to find discrepancies between
the sum of the InvLine.Tax for an invoice and the
amount of Invoice.TaxTotal.

TaxSum.sql
Output type: Tab delimited
Returns InvoiceNo and Tax for a range of invoice numbers given. Tax is calculated by summing InvLine.Tax for all records matching each InvoiceNo.
This report is useful for validating the Invoice.TaxTotal field.

TopSold.sql
Output type: Tab delimited
Input: FirstDate, LastDate, TopCount
Returns the top selling items from Invoice from FirstDate to LastDate. TopCount is the number to return. For example, TopCount 500 returns the top 500 sales items with the top selling item on top.

RankDescriptionMfgStkNumUnitUnitCountSalesPCT
1REFRIGERATORG-ETPX24PBBWWEA1191797.7

XOrdAval.rpt
Output type: On Screen Grid
Returns all lines from a given XOrdNum where InvnView.AvailableCnt < 0. This report may be used as a tool in the XOrders application to find lines in the order with a negative AvailableCnt.
A negative AvailableCnt should be unusual, but is possible if, for example, someone does a bin audit that reduces the Qty onhand. If an XOrder is release for an item with a negative AvailableCnt, the release will fail with the error:
QtyOrd and QtyShip signs must match
This error results because the release code creates an OrdLine record with the XOrdLine.QtyOrd and InvnView.AvailableCnt as QtyShip.
The XOrdAval report will show the line(s) from the given XOrdNum that produced the error.


System Reports

[top]

WhoAmI.rpt
Output type: On Screen Grid
Returns USER_NAME() from database. Useful for determining how a user is logged into the database.


Utility Reports

[top]

Zip_Plus
Output type: Tab file
PAGE routine that automatically converts a given ZipCode column in a tab delimited text file to the format #####-####.
Usage: PAGE Zip_Plus OutputFile; InputFile ColumnName
Column is converted only if the length > 5 and the column does not already contain a dash.
BOMChg

BOMChg.sql
BOMChg.sql (Bill of Material Changed Today) Returns BOMNum, JobNo, CustNo, BillName of Bills of Material changed today.

BOMCopy
BOMCopy.sql (Bill of Material Copy) copies a given BOMNum to a New BOMNum with a given date.>
EnteredBy, TimeEntered, and ApprovedBy are not INSERTed, so they default to what they would be if the user running this routine had manually entered the BOM.
SaleTotal and TaxTotal are INSERTed as 0 so when the BillMatL rows are added, the totals will be correct.
Status is INSERTed as N BillMatL rows are copied as they are with the same LineNum, but QtyReq is INSERTed as 0.