| Ecomedate Analysis Suite - Query Listing and Descriptions | ||||||||
| BQY File | Description | Tables Used | Queue | Drilldown columns available | Date Delivered/Updated | Current Version - SQL Server | Current Version - Oracle | |
| 1 | Aged orders in the warehouse.bqy | Generates bar charts showing aged orders & dollars in the warehouse by pay method group and by ship method. Also produces pivot tables by age and by ship method, showing orders, dollars, cost of goods, and units. | SV_MACORD_PROCESS_ORDER SV_MACORD_ORDER_HEADER SV_MACORD_SALES_ORD_LINES SV_MACORD_CUSTOMERS SV_MACORD_CUSTOMER_ADDL SV_MACORD_CUST_TEL SV_MACORD_CUST_EMAIL SV_MACORD_OA_ORD_OTH SV_MACITM_ITEM_MAST |
ORD, CUST, ORDO, ITEM, CSTX | Company, Division, Full Order Number, Sales Order Entry Date, Pay Method, Pay Method Group, Customer Name and Address, Ship Method, Date to Warehouse, Order Quantity, Sale Amount, Cost of Sale, Days in Warehouse, In-Warehouse Age Category, Item Number, Description, Status, Tracking Number, E-Mail Address | 4/7/2005 | 9/1/2006 | 9/1/2006 |
| 2 | Avg orders items and dollars by source.bqy | Generates a report and two pivot tables of orders, units, total dollars, units per order, and dollars per order, by source code. One pivot table is by offer and use; the other is by portions of the structured offer codes. | SV_MACITM_SOURCES SV_MACITM_OFFERS SV_MACITM_SOURCES_FOP |
OFFER | Offer Code, Use Code, Source, Offer Description, Source Description, Date Mailed, Quantity Mailed, Total Orders, Total Units, Total Order Amount, Offer Mode Code, Offer Period Code, Offer User Type Code, Offer Version Code | 4/7/2005 | 9/28/2006 | 9/28/2006 |
| 3 | Back orders by vendor and buyer.bqy | Generates two pivot tables of current back order information. One pivot table shows data by vendor, the other shows data by buyer. Each pivot table shows units, total product dollars, and cost of goods for orders on back-order. Selection is based on Processing records with Action-Search of PA, PB, or WB. | SV_MACORD_PROCESS_ORDER SV_MACORD_SALES_ORD_LINES SV_MACORD_BACK_ORDER SV_MACORD_CUSTOMERS SV_MACITM_ITEM_MAST SV_MACITM_VENDOR_ITEMS |
ORD, CUST, ORDO, ITEM | Company, Division, Sales Order Number, Sales Order Ship-To Number, Sales Order Line Number, Customer Name and Address, Backorder Date, Line Item Type Code, Line Item Status Code, Order Quantity, Sale Amount, Cost of Sale, Item Number, Item Description, Item Status, Vendor Code, Buyer Code, Days on Backorder | 6/8/2006 | 7/13/2006 | 7/31/2006 |
| 4 | Back orders.bqy | Generates three pivot tables current backorder and open purchase order information. One pivot table shows data by Item Number and Order Status, another by Category and Buyer and the other by Item Number, Item Status and Buyer. It creates a detail report of back-orders, including order and customer data, by item; also creates summary report by item, including open PO qty and next PO due date. | SV_MACITM_PO_DETAILS SV_MACORD_BACK_ORDERS SV_MACORD_ORDER_HEADER SV_MACORD_ITEM_MAST SV_MACORD_SALES_ORD_LINES SV_MACORD_COMP_DIV_MASTER SV_MACORD_CUSTOMERS |
PO, ORDO, ORD, ITEM, CUST, SETUP | Company, Division, Order Number, Customer Name and Address, Order Date, Category, Major / Sub Major / Minor / Sub Minor Category Type Code, Order Item Status Codes and Hold Til Complete Code. | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 5 | Future orders.bqy | Produces a series of bar charts and pivot tables showing information regarding future orders. Bar charts show shipments, units, and dollars by both shipment week and release week, based on Monday thru Sunday week. (For many clients, shipment and release dates will be the same. But Ecometry functionality allows for calculation of a release date based on fulfillment and delivery durations.) Pivot tables show shipments, units and dollars by item category, by ship week/ship date, by release week/release date, and by offer and use. | SV_MACORD_PROCESS_ORDER SV_MACORD_ORDER_HEADER SV_MACORD_SALES_ORD_LINES SV_MACORD_CUSTOMERS SV_MACORD_CUSTOMER_ADDL SV_MACORD_OA_ORD_OTH SV_MACITM_ITEM_MAST |
ORD, CUST, OA, ITEM | Sales Order Number, Sales Order Ship-To Number, Company, Division, Offer Code, Use Code, Sales Order Mail Date, Sales Order Entry Date, Future Release Date, Future Ship Date, Item Number, Item Description, Item Category, Item Status, Order Quantity, Sale Amount, Customer Number, Customer Name and Address, Release Week, Ship Week | 4/7/2005 | 7/14/2006 | 8/3/2006 |
| 6 | Item Listing.bqy |
Generates analysis of basic item data including item number, status, style, description, sell price, default vendor, buyer, assigned warehouse location, customization code, and last change date. There are three pivot tables and reports: the first pivot/report is sorted by Product Number, Style, Status, Category, the second by Item Status and Product Number and the last by Buyer, Item Status, Product Number, Style, Category. | SV_MACITM_ITEM_MAST SV_MACITM_VENDOR_ITEMS SV_MACITM_COMP_DIV_MASTER SV_MACITM_ITEM_MAST_WH |
ITEM, SETUP | Company, Division, Major / Sub Major / Minor / Sub Minor Category, Vendor Product Number, Vendor Preference Number, Merchandiser, Royalty Code, Minimum Order Quantity, Maximum Order Quantity, Reorder Point, Stop Shopping Date, Season Code, Item Classification Code, Custom Code, Primary Warehouse Location | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 7 | Items changed to discontinued.bqy | Generates a list of item data for items that have been changed to "C1", "C2", "C3", "KC", or "S1" from something other than one of these same codes. Note: requires use of before image fields and history of change functionality. | SV_MACITM_ITEM_MAST | ITEM | No pivot tables - no drill-down | 4/7/2005 | 7/24/2006 | 8/3/2006 |
| 8 | Obsolete Inventory.bqy | Identifies and reports items that have inventory not newer than 3/31/04, along with the most recent offer date, most recent order date, units on hand, and total inventory value. Also generates pivot tables of the same data by category code. | SV_MACITM_INV_DETAILS SV_MACITM_ITEM_MAST SV_MACITM_OFFER_ITEMS SV_MACITM_OFFERS SV_MACITM_ORDER_HEADER SV_MACITM_SALES_ORD_LINES |
INV, ITEM OFFER, ORD | Item Number, Item Description, Item Status, Item Category, Available Inventory, Selling Price, Inventory Sort Date, Most Recent Offer Start Date, Most Recent Order Date, Company, Division, Total Inventory Cost, Major Category, Sub Major Category, Minor Category, Sub Minor Category | 4/7/2005 | 7/14/2006 | 8/3/2006 |
| 9 | Open orders by item.bqy | Generates a pie chart and a pivot table of orders by open category, showing orders, units, and total dollar value. Also generates a report by item, which also includes total on-hand units of each item. | REF_PROCESS_ACTION_SEARCH SV_MACORD_PROCESS_ORDER SV_MACORD_ORDER_HEADER SV_MACORD_SALES_ORD_LINES SV_MACORD_CUSTOMERS SV_MACITM_ITEM_MAST |
ORD, CUST, ITEM | Open Category, Action Search Code, Sales Order Number, Item Number, Item Status, Item Description, Available Inventory, Company, Division, Sales Order Entry Date, Pay Method, Order Quantity, Sale Amount, Customer Name and Address | 4/7/2005 | 7/14/2006 | 8/3/2006 |
| 10 | Open purchase orders.bqy | Generates a pivot table and two reports of purchase orders. One report is by vendor; the other is by buyer. Pivot table shows buyer, vendor, PO number, item number, due date, and open quantity and cost. Reports show detail data, including item number, vendor item number, item description, vendor item description, status, purchase reason, original purchase quantity, open quantity. Dashboard allows specification of status criteria, buyer, and vendor. | SV_MACITM_PO_DETAILS SV_MACITM_PO_HEADER SV_MACITM_VEND_ORD_FROM SV_MACITM_ITEM_MAST SV_MACITM_VENDOR_ITEMS |
PO, SETUP, ITEM | Buyer, Vendor, Purchase Order Number, PO Line Number, Vendor Item Number, Item Number, Item Status, Item Description, Vendor Item Description, Purchase Quantity, Open Quantity, Unit Cost, Total Cost, Original Due Date, Due Date, Due Week, Purchase Order Status, Vendor Name, Purchase Reason, First Receipt Date | 4/7/2005 | 7/14/2006 | 8/3/2006 |
| 11 | Orders at the dropshipper.bqy | Generates a series of bar charts, pivot tables, and reports. Bar charts show aged orders & dollars at the drop shipper by pay method group and by ship method. Pivot tables show orders, units, dollars, and cost of goods by vendor, buyer, item number, and age category. One report is a detail report of order and customer data, by item. The other report is a summary report by item of orders, units, and dollars. | SV_MACORD_PROCESS_ORDER SV_MACORD_ORDER_HEADER SV_MACORD_SALES_ORD_LINES SV_MACORD_CUSTOMERS SV_MACITM_ITEM_MAST |
ORD, CUST, ITEM | Company, Division, Full Order Number, Sales Order Entry Date, Pay Method, Pay Method Group, Customer Name and Address, Ship Method, Date to Drop-Shipper, Days at Drop-Shipper, Line Number, Item Number, Item Status, Item Description, Order Quantity, Sale Amount, Cost of Sale, Vendor Number, Buyer Code | 4/7/2005 | 7/14/2006 | 8/3/2006 |
| 12 | Orders in the warehouse.bqy | Generates pie charts of orders and dollars in the warehouse by warehouse and by ship method; creates a pivot table of orders in the warehouse by company, division, and ship method; creates a detail report of order and customer data for orders in the warehouse. | SV_MACORD_PROCESS_ORDER SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACORD_CUSTOMERS |
ORD, CUST | Full Order Number, Company, Division, Sale Amount, Date to Warehouse, Customer Name and Address, Warehouse, Ship Method | 4/7/2005 | 7/14/2006 | 8/3/2006 |
| 13 | Price Overrides.bqy | Generates a pivot table and a detail report of sales order price overrides for a specified date range. Pivot table shows offer unit price, order unit price, and variance, by date and override reason. Report shows order and line item details of line items where price was overridden. Data includes entry date, order number, override reason, item number & description, order quantity, item master price, offer price, order unit price, unit price variance, and extended price variance. | REF_PERIOD_TABLE SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACORD_CUSTOMERS SV_MACITM_ITEM_MAST SV_MACITM_OFFER_ITEMS |
ORD, CUST, ITEM, OFFER | Sales Order Number, Sales Order Entry Date, User Code, Offer Code, Item Number, Item Description, Item Price, Order Quantity, Sales Unit Price, Customer Name, Price Override Reason, Offer Unit Price, Unit Price Variance, Extended Sale Price, Extended Offer Price, Extended Price Variance | 6/13/06 | 7/17/2006 | 7/24/2006 |
| 14 | Sales revenue per catalog mailed.bqy | Generates two reports of total orders, total demand dollars, circulation quantity, and revenue per catalog mailed. One report is by the 8-character offer code; the other is by the pieces of the structured offer codes. Note: only offers with non-zero actual circulation quantity are selected. | SV_MACITM_OFFERS | OFFER | Offer Code, Company, Division, Offer Description, Offer Start Date, Total Orders, Gross Sales, Gross Cost, Actual Circulation Quantity, Revenue per Catalog Mailed, Structured Offer Fields | 4/7/2005 | 7/24/2006 | 8/3/2006 |
| 15 | Sales revenue per page.bqy | Generates two reports of total orders, total demand dollars, total catalog pages, and revenue per catalog page. One report is by the 8-character offer code; the other is by the pieces of the structured offer codes. Note: only offers with non-zero actual page count are selected. | SV_MACITM_OFFERS | OFFER | Offer Code, Company, Division, Offer Description, Offer Start Date, Total Orders, Gross Sales, Gross Cost, Total Pages, Structured Offer Fields | 4/7/2005 | 7/24/2006 | 8/3/2006 |
| 16 | Expected Weekly PO Receipts.bqy | Generates two pivot tables of expected receipts. One shows total units and costs with Year, Month, and Week Due as dimensions; the other shows units and total cost with Item, Year, Month, Due Day, and Purchase Order Number as dimensions. Also generates a detail report of item and PO information, sorted by item number within week due. | SV_MACITM_ITEM_MAST SV_MACITM_PO_DETAILS SV_MACITM_VENDOR_ITEMS SV_MACITM_VEND_ORD_FROM |
ITEM, PO, SETUP | Item Number, Item Description, Item Status, Category, Reorder Point, Item Price, Available Inventory, Style Code, Purchase Order Number, P.O. Line Number, Purchase Quantity, Original Due Date, Due Date, Purchase Reason, Unit of Measure, Vendor Unit Factor, Landed Unit Cost, P.O. Status, Vendor Item Number, Vendor Number, Vendor Item Description, Vendor Style Code, Vendor Name and Address, Due Week, Due Month, Due Year, Due Day, Total Cost | 4/7/2005 | 7/13/2006 | 8/3/2006 |
| 17 | Receiving Staffing Requirements.bqy | Generates a pivot table of showing units due in and hours required for receiving. Dimensions include Year, Month, and "Week Of", along with days of the week and totals across the top dimension. The user is prompted for the number of units per hour to be used in the calculation. This value can be changed and the results recalculated. The resulting pivot table can then be printed. | SV_MACITM_PO_HEADER SV_MACITM_PO_DETAILS SV_MACITM_PO_REC SV_MACITM_ITEM_MAST SV_MACITM_VENDOR_ITEMS SV_MACITM_ITEM_MAST_WH SV_MACITM_WHSE_LOCS |
PO, ITEM, SETUP | Purchase Order Number, P.O. Line Number, Vendor Number, Company, Division, Ship-To Warehouse, Ship Method, Issue Date, Purchase Quantity, Oriignal Due Date, Due Date, Purchase Reason, Open Quantity, Unit of Measure, Vendor Unit Factor, Cost, Landed Cost, P.O. Status, First Receipt Date, First Receipt Quantity, Total Receipt Quantity, Last Receipt Date, Item Category, Item Number, Reorder Point, Item Price, Available Inventory, Customization Code, Drop-Ship Code, Future Order Date Code, Style Code, Cubic Volume, Box Codes, Expiration Code, Merchandiser, Item Status, Warehouse Code, Warehouse Location, Warehouse Name, Warehouse Location Dimensions, Capacity, and Restock Point, Location Type Code, Usable Code, Due Week, Due Date, Due Day, Due Month, Due Year | 4/7/2005 | 7/17/2006 | 8/3/2006 |
| 18 | Shipping charge analysis.bqy | Generates two pivot tables and three pie charts summarizing postage and handling income versus shipping charges, by date, by postal zone, by package weight. One pivot table lists: packages, P&H income, carrier charges, and net P&H income by month, for each ship method. The second pivot table lists packages, orders, total carrier charge, total P&H income, average P&H income per package, average carrier charge per package, and net P&H income, by date, ship method, postal zone, weight, and ship method. The pie charts summarize: 1) packages by ship method; 2) P&H income by ship method; and 3) carrier charges by ship method. | SV_MACMAN_MANIFEST_ENTRIES SV_MACMAN_MANI_PKG_SHIP SV_MACMAN_FEDEX_ENTRIES SV_MACMAN_FED_PKG_SHIP SV_MACMAN_AIRBORNE_ENTRIES SV_MACMAN_AIRBORNE_PKG_SHIP SV_MACORD_FT_SHIP SV_MACORD_FT_SHIP_AMT |
ORDO, FIN | Sales Order Number, Sales Order Ship-To Number, Sales Order Shipment Number, Ship Method, Postal Zone, Warehouse, Rounded Weight, Actual Weight, Carrier Charge, Ship Date, Company, Company Group, Division, Recipient Name and Address, Zip Code, Tracking Number, Sectional Center, Package Count, P&H Amount, Ship Month, Ship Quarter, Ship Year | 4/7/2005 | 7/24/2006 | 8/3/2006 |
| 19 | Vendor expenditures by year and category.b | Generates a pivot table of summarized purchase order receipts and inventory costs by vendor and product category. Also generates a detail report showing product and purchase order detail information for each receipt, by item within category within year within vendor. | SV_MACITM_INVENTORY_TRANS SV_MACITM_INV_TRANS_POR SV_MACITM_PO_HEADER SV_MACITM_ITEM_MAST SV_MACITM_VEND_ORD_FROM |
INV,PO, ITEM, SETUP | Item Number, Description, Status, Category, Company, Division, Season Code, Major Category, Sub-Major Category, Minor Category, Sub-Minor Category, Selling Price, Style, Transaction Number, Purchase Order Number, Warehouse Location, Transaction Status, User, Receipt Quantity, Unit Cost, Total Cost, Vendor Number, Vendor Name, Receipt Month, Receipt Year | 4/7/2005 | 7/24/2006 | 8/3/2006 |
| 20 | Vendor performance analysis.bqy | Generates 3 pivot tables showing statistics related to receipt date versus due date of purchase order receipts for vendors. First pivot table lists total number of receipts and percentage of total receipts for each vendor, categorized as "Early", "On-Time", or "Late". Second pivot table lists total receipts and percentage of total receipts for each item, categorized as "Early", "On-Time", or "Late". Third pivot table shows total receipts by vendor by week, relative to due date -- 0 = week of due date; negative numbers represent weeks early; positive numbers represent weeks late. | SV_MACITM_PO_DETAILS SV_MACITM_PO_HEADER SV_MACITM_PO_REC SV_MACITM_ITEM_MAST SV_MACITM_VENDOR_ITEMS SV_MACITM_VEND_ORD_FROM SV_MACITM_VEND_ORD_TEL |
PO, ITEM, SETUP | Vendor Number, Vendor Name and Address, FOB City, Company, Division, Ship-To Warehouse, Ship Method, Terms Code, Buyer Code, Issue Date, PO Status, PO Line Number, Purchase Quantity, Original Due Date, Due Date, Purchase Reason, Open Quantity, Unit of Measure, Unit Factor, Unit Cost, Landed Unit Cost, PO Item Status, Vendor Phone Number, First Receipt Date, First Receipt Quantity, Total Received Quantity, Last Receipt Date, Item Number, Description, Category, Status, Price, Vendor Item Number, Vendor Item Description, Lead Time, Minimum Purchase Quantity, Master Carton Quantity, Master Pack Quantity, Vendor Item Status, Vendor Preference Code, Early Receipts, On-Time Receipts, Late Receipts | 4/7/2005 | 7/24/2006 | 8/3/2006 |
| 21 | Vendor returns analysis.bqy | Generates a pivot table, 3 bar charts, and a detail report of customer return data - number & percent of times returned, number & percent of units returned, and return value and percent of return dollars - by primary vendor. One bar chart shows total number of returns by vendor and by return reason; one bar chart shows total units returned by vendor and by return reason; one bar chart shows total dollars returned by vendor and by return reason. The report lists item detail information, along with returns, units returned, return dollars, and return cost of goods, for each item for each vendor. | SV_MACORD_RETURNS SV_MACORD_PROC_RETURN SV_MACITM_ITEM_MAST SV_MACITM_VENDOR_ITEMS SV_MACITM_VEND_ORD_FROM |
ORDO, ITEM, SETUP | Sales Order Number, Sales Order Line Number, Offer Code, Use Code, Return Amount, Return P&H Amount, Return Tax Amount, Other Return Amount, Tax Code, Return Reason, No Product Indicator, Inventory Disposition Code, Return Item Type, Return Cost of Goods, Tax Jurisdiction Code, Tax Rate, Company, Division, Return Quantity, Return Date, User Code, Return Status, Item Number, Item Description, Item Category, Season Code, Item Sell Price, Customization Code, Warranty Days, Style, Item Status, Vendor Item Number, Vendor Item Description, Buyer Code, Lead Time, Minimum Purchase Quantity, Vendor Item Unit Cost, Vendor Item Freight, Vendor Style, Vendor Preference Code, Vendor Name and Address, Vendor E-Mail Address | 4/7/2005 | 7/24/2006 | 8/3/2006 |
| 22 | Inventory Adjustments.bqy | Shows inventory adjustments by reason code, ticket, user or vendor. Analysis provides by item, by date range information about cost and units by selected criteria (reason, ticket, or user) | SV_MACITM_ITEM_MAST SV_MACITM_INVENTORY_TRANS SV_MACITM_VENDOR_ITEMS |
ITEM, INV | Item Description, Category, Item Status, Transaction #, Transaction Date, Unit Cost, Warehouse, Warehouse Location, Style, Vendor | 4/7/2005 | 7/14/2006 | 8/3/2006 |
| 23 | Inventory Receiving Analysis.bqy | Shows receipts by date by vendor by warehouse units and cost of inventory received. Reports available showing detail by vendor or purchase order or item or ticket. | SV_MACITM_ITEM_MAST SV_MACITM_INVENTORY_TRANS SV_MACITM_PO_HEADER SV_MACITM_VEND_ORD_FROM SV_MACITM_VEND_ORD_TEL |
ITEM, INV, PO, SETUP | Item Description, Category, Item Status, Transaction #, Transaction Date, Unit Cost, Warehouse, Warehouse Location, Style, Vendor, Available Inventory, Sell Price, Vendor Name, City, State, Phone Number | 4/7/2005 | 7/14/2006 | 8/4/2006 |
| 24 | P and H Overrides.bqy | Shows postage and handling overrides by user, reason and date. Charts available and pivot tables. | SV_MACORD_OA_ORD_OTH SV_MACORD_FT_SALES_ORD SV_MACORD_FT_SALES_ORD_AMT |
OA, FIN | Sales Order Number, Company, Division, Override Date | 4/7/2005 | 7/17/2006 | 8/4/2006 |
| 25 | Pick Velocity.bqy | Shows pick velocity by warehouse and/or date. Details by item/warehouse location pick lots, capacity, units, orders, and pick percentage. Analysis available as pivot table or report. | SV_MACORD_PICK_LOT_HEADER SV_MACORD_PICK_LOT_DETAIL SV_MACORD_PICK_LOT_DET_WH SV_MACITM_ITEM_MAST SV_MACITM_WHSE_LOCS |
ORDO, ITEM, SETUP | Item Description, Sales Order Number, Sales Order Line Number, Warehouse, Pick Lot Date, Location Dimensions, Capacity, and Restock Point, Ship Method, Line Item Status | 4/7/2005 | 7/17/2006 | 8/4/2006 |
| 26 | Returns Analysis.bqy | Generates analysis for returns by date/reason code. Details items and provides counts for number of times returned, numbered returns, return amount, return cost, gross margin, % of total GM, and overall ranking. Number of pivot tables available include by reason, category, style. All of listed are also charted. | SV_MACORD_RETURNS SV_MACORD_PROC_RETURN SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACORD_CUSTOMERS SV_MACITM_ITEM_MAST |
ORDO, ORD, CUST, ITEM | Sales Order Number, Sales Order Ship-To Number, Sales Order Shipment Number, Sales Order line Number, Order Quantity, Sale Amount, Cost of Sale, Ship Date, Return P&H, Return Tax, Returm Other Amount, Tax Code, Return Reason, No Product Return Indicator, Line Item Type, Line Item Status, Inventory Disposition Code, Tax Jurisdiction, Tax Rate, Return Date, Return Status, User Code, Offer Code, Use Code, Customer Number, Sales Order Mail Date, Sales Order Entry Date, Customer Name and Address, Customer Type, Item Status, Item Description, Item Category, Style Code | 4/7/2005 | 7/24/2006 | 8/4/2006 |
| 27 | Sales by Product ID.bqy | This analysis provides 17 different views into the items were sold. Basic selection includes item range, date range, company and division range, offer and/or use subsetting, category range, customer type subsetting, item status subsetting, customer status subsetting. All analysis can be shown as either demand or shipped. Perspective can be item, by state, by category, by style, sec center, customer type, offer, or date range. Most analysis provide pivot tables, charts and reports. The data can be selected by calendar date or fiscal date. | SV_MACITM_COMP_DIV_MASTER SV_MACORD_ORDER_HEADER SV_MACORD_SALES_ORD_LINES SV_MACITM_ITEM_MAST SV_MACORD_CUSTOMERS SV_MACITM_STYLE_ITEM_DATA SV_MACITM_STYLE_HEADER REF_PERIOD_TABLE |
SETUP, ORD, ITEM, CUST | Item Number, Item Description, Item Status, Style Code, Style Name, Customer Number, Customer Name and Address, Sales Order Number, Company, Division, Offer Code, Use Code, User Code, Pay Method Code, Pay Method Group Code, Order Header Status, Line Item Quantity, Sale Amount, Cost of Sale, Sub-Head Level Status, Line Item Status, Ship Method, Month, Quarter, Year, Zip Code, Secitonal Center, Major Category, Sub Major Category, Minor Category, Sub Minor Category, Customer Type, Customer Status, Customer Aciton, L3-Code, L2-Code, L1-Code, Ship Date, Sales Order Mail Date | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 28 | Sales versus Returns Analysis.bqy | This analysis area provides analysis data for sales versus returns. Subsets are available by date, item, category, company and division combinations. Return qty, Return orders, Percent of orders, sales dollars, and ranking are shown. Pivots are available by category, style, item, | SV_MACORD_SALES_ORD_LINES SV_MACORD_RETURNS SV_MACITM_ITEM_MAST SV_MACORD_ORDER_HEADER SV_MACORD_PROC_RETURN SV_MACORD_CUSTOMERS SV_MACITM_STYLE_HEADER SV_MACITM_COMP_DIV_MASTER |
ORD, ORDO, ITEM, CUST, SETUP | Item Number, Item Description, Item Status, Return Code, Sales Order Number, Sales Order Ship-To Number, Sales Order Shipment Number, Sales Order Line Number, Order Quantity, Sale Amount, Cost of Sale, Line Item Type Code, Line Item Status, Ship Date, Return Amount, Return P&H Amount, Return Tax Amount, Return Other Amount, Tax Code, Return Reason, No Product Return Indicator, Inventory Disposition Code, Tax Jurisdiction, Tax Rate, Return Quantity, Return Cost, Return Date, User Code, Return Control Number, Return Status, Sales Order Mail Date, Sales Order Entry Date, Offer Code, Use Code, Customer Name and Address, Customer Type, Style Code, Major Category, Sub Major Category, Minor Category Sub Minor Category | 11/29/2006 | 11/29/2006 | 8/4/2006 |
| 29 | Top X Customers.bqy | Provides an analysis of the top customers for a date range. Generates 2 pivot tables: one shows the customers and total order and amount statistics; the other shows the items purchased by these customers and the respective statistics for the items. Both pivot tables can be sorted by various columns and printed. In addition, a detail report of the customer information is generated. Selection criteria includes: Company, Division, Date Range, Original Offer, Original Use, Customer Type, and State. The query can be run for either demand or for shipments. It allows the specification of the number of top customers to be selected. | SV_MACITM_COMP_DIV_MASTER SV_MACORD_ORDER_HEADER SV_MACORD_SALES_ORD_LINES SV_MACITM_ITEM_MAST SV_MACORD_CUSTOMERS SV_MACORD_CUSTOMER_COMP SV_MACORD_CUSTOMER_ADDL |
SETUP, ORD, ITEM, CUST | Customer Number, Name and Address, Order Number, Customer Type, Customer Status, Customer Action, Original Offer, Original Use, Last Offer, Last Use, Offer Code, Use Code, Sales Order Mail Date, Sales Order Entry Date, Pay Method, First Time Indicator, Inquiry Conversion Indicator, Order Quantity, Sale Amount, Cost of Sale, Line Item Type, Ship Date, Item Number, Description, Category, Status, Royalty Code, Style Code | 4/7/2005 | 7/24/2006 | 8/4/2006 |
| 30 | Repeat Buyer Analysis.bqy | Produces a pivot table showing the number of customers who first purchased within the specified date range (based on Original Date from Customer-Comp), along with the total orders, dollars, and average order value for the first orders, and for subsequent repeat orders within the same date range. These statistics are grouped into 7 categories: 1x Buyers, 2x Buyers, 3x Buyers, 4x Buyers, 5x Buyers, 6-10x Buyers, and 10+ Buyers. Also produces a detail customer report of a selected subset of these customers. And, provides the ability to export name and address information of this same selected subset of customers. | SV_MACORD_CUSTOMER_COMP SV_MACORD_ORDER_HEADER SV_MACORD_SALES_ORD_LINES SV_MACORD_CUSTOMERS SV_MACORD_CUSTOMER_ADDL SV_MACORD_CUST_TEL SV_MACORD_CUST_EMAIL SV_MACITM_COMP_DIV_MASTER |
CUST, ORD, SETUP | Order Number, First-Time Indicator, Company, Division, Offer Code, Use Code, Sales Order Mail Date, Sales Order Entry Date, Total Order Quantity, Total Sale Amount, Total Cost of Sale, Customer Number, Customer Name and Address, Customer Telephone, E-Mail Address | 4/7/2005 | 7/17/2006 | 8/4/2006 |
| 31 | RFM Analysis.bqy | Produces a pivot table showing the number of customers, orders, total sales, and average order value, by Recency, Frequency, and Monetary breakpoints. Selection is by date range, company, and division. Six breakpoints may be specified for each of the RFM dimensions. Recency is in months; Frequency is in number of orders; Monetary is in total order value. Once the query has been processed, the RFM criteria can be changed, reclassifying the selected customers based on the new criteria. | SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACORD_CUSTOMERS SV_MACITM_ITEM_MAST SV_MACITM_OFFER_ITEMS SV_MACITM_OFFERS SV_MACITM_SOURCES SV_MACITM_COMP_DIV_MASTER |
ORD, CUST, ITEM, OFFERS, SETUP | Customer Number, Name and Address, Customer Type, Customer Status, Customer Action, Order Number, Company, Division, Offer Code, Use Code, Sales Order Mail Date, Pay Method, Item Unit Price, Item Number, Item Description, Source Description, Offer Description, Structured Offer Codes, Offer Start Date, Offer Finish Date, Offer Final Date, Order Quantity, Sale Amount, Cost Of Sale, Line Item Status, Order Level Status, Demand Month, Demand Year, Demand Quarter, Source Cross-Reference | 4/7/2005 | 7/24/2006 | 8/4/2006 |
| 32 | Forecast to Buy.bqy | Generates a pivot table of future purchase requirements, based on past demand, on hand inventory, and open purchase orders. Selection criteria includes: Weeks of past demand to analyze; weeks forward to project; item number range; product category; offer (including wild cards); and, buyer. | SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACITM_ITEM_MAST SV_MACITM_VENDOR_ITEMS SV_MACITM_INV_DETAILS SV_MACITM_PO_DETAILS SV_MACITM_OFFER_ITEMS |
ORD, ITEM, INV, PO, OFFER | Product Category, Description, Selling Price, Page Percent, Page Number, Page Position, Order Number, Company, Division, Offer Code, Use Code, Sales Order Mail Date, Order Quantity, Sale Amount, Cost of Sale, Price Override Code, Open PO Quantity, On-Hand Inventory, Vendor, Vendor Item Number, Vendor Item Description, Buyer, Lead Time, Minimumu Purchase Quantity, Master Carton Quantity, Master Pack Quantity, Vendor Unit Cost, Unit of Measure, Unit Factor, Vendor Unit Freight Cost | 11/30/2006 | 11/29/2006 | 11/29/2006 |
| 33 | Ecomedate Menu Dashboard.bqy | Does not generate any output. Is just the menu for selecting all other queries | NA | NA | NA | 1/24/2007 | 1/24/2007 | 1/27/2007 |
| 34 | Aged Open AR.bqy | Generates multiple pivot tables and charts showing aged open accounts receivable, aged by categories: current, 31-60, 61-90, 91-120, and 120+ days. Also produces statements. Selection criteria includes: company, division, customer type, customer status, customer action. | SV_MACORD_OPEN_AR, SV_MACORD_CREDIT_APPROVAL, SV_MACORD_CUSTOMERS, SV_MACORD_CUST_TEL, SV_MACORD_CUSTOMER_ADDL, SV_MACORD_CUST_EMAIL, SV_MACORD_CUSTOMER_COMP, SV_MACORD_AR_COMMENTS, SV_MACITM_COMP_DIV_MASTER |
ORD, CUST, CSTX, ORDS, SETUP | Customer #, name & address, A/R status, A/R transaction type, due date, posted-to reference, originating reference, transaction amount, transaction date, transaction amount, credit limiit, terms days, potential A/R, territory code, sales rep code, tax ID, A/R comments, customer type, customer status, customer action, phone number, e-mail address | 8/18/2006 | 8/18/2006 | 8/18/2006 |
| 35 | Merchandise Performance Analysis.bqy | Provides analysis of product demand information by offer. Generates 3 pivot tables, each of which provide gross demand units and dollars, demand dollars net of returns and cancels, average retail dollars, gross margin percent, item response rate ranking, and projected demand per million circ. One pivot table presents this information by SKU, one presents the information by style, and one presents the information by category. Provides the ability to exclude "page zero" System-Created offer products. Also gives the ability to define a range of page numbers that would be considered "remote pages" and to exclude that page range if desired. | SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACORD_RETURNS SV_MACORD_PROC_RETURN SV_MACORD_CUSTOMERS SV_MACORD_CUSTOMER_ADDL SV_MACORD_CUST_EMAIL SV_MACORD_CUST_TEL SV_MACITM_ITEM_MAST SV_MACITM_STYLE_HEADER SV_MACITM_OFFER_ITEMS SV_MACITM_OFFERS SV_MACITM_COMP_DIV_MASTER REF_PERIOD_TABLE REF_MAJ_CAT_LGCY_CD REF_SUB_MAJ_CAT_LGCY_CD REF_MIN_CAT_LGCY_CD REF_SUB_MIN_CAT_LGCY_CD |
SETUP, ORD, ORDO, ITEM, CUST, CSTX, OFFER | Customer #, name & address, order details (demand, cancel, return units & dollars & COGs), offer, use, company, division, item details (item #, description, category, category descriptions, style code, style name), page number, page percent, page position, advertised price, offer circ qty, offer page count, offer page cost, offer name, offer start/finish/final date, order date, square inches, fiscal date details. | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 36 | Price Range Analysis.bqy | Provides analysis of product demand information by product category and by price range. Generates a pivot table which provide gross demand dollars, number of SKUs, percentage of demand within category, and percentage of total demand for each price range for each category. Separate sections are generated for advertised items, non-advertised items, and items included in "remote pages." Also generates a chart showing total demand for each price range. Provides the ability to exclude "page zero" System-Created offer products. Also gives the ability to define a range of page numbers that would be considered "remote pages" and to exclude that page range if desired. | SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACORD_CUSTOMERS SV_MACORD_CUSTOMER_ADDL SV_MACORD_CUST_EMAIL SV_MACORD_CUST_TEL SV_MACITM_ITEM_MAST SV_MACITM_STYLE_HEADER SV_MACITM_OFFER_ITEMS SV_MACITM_OFFERS SV_MACITM_COMP_DIV_MASTER REF_PERIOD_TABLE REF_MAJ_CAT_LGCY_CD REF_SUB_MAJ_CAT_LGCY_CD REF_MIN_CAT_LGCY_CD REF_SUB_MIN_CAT_LGCY_CD |
SETUP, ORD, ITEM, CUST, CSTX, OFFER | Customer #, name & address, order details (demand, cancel, return units & dollars & COGs), offer, use, company, division, item details (item #, description, category, category descriptions, style code, style name), page number, page percent, page position, advertised price, offer circ qty, offer page count, offer page cost, offer name, offer start/finish/final date, order date, square inches, fiscal date details. | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 37 | Spread Performance Analysis.bqy | Provides analysis of product demand information by page number and item for selected offers. Generates 3 pivot tables, each of which include demand units and dollars, return units and dollars, cancel units and dollars, net units and dollars, and percent of page. One pivot table presents this information by SKU within each page, one presents the information by style within each page, and one presents the information by category within each page. Provides the ability to exclude "page zero" System-Created offer products. Also gives the ability to define a range of page numbers that would be considered "remote pages" and to exclude that page range if desired. | SV_MACORD_SALES_ORD_LINES SV_MACORD_ORDER_HEADER SV_MACORD_RETURNS SV_MACORD_PROC_RETURN SV_MACORD_CUSTOMERS SV_MACORD_CUSTOMER_ADDL SV_MACORD_CUST_EMAIL SV_MACORD_CUST_TEL SV_MACITM_ITEM_MAST SV_MACITM_STYLE_HEADER SV_MACITM_OFFER_ITEMS SV_MACITM_OFFERS SV_MACITM_COMP_DIV_MASTER REF_PERIOD_TABLE REF_MAJ_CAT_LGCY_CD REF_SUB_MAJ_CAT_LGCY_CD REF_MIN_CAT_LGCY_CD REF_SUB_MIN_CAT_LGCY_CD |
SETUP, ORD, ORDO, ITEM, CUST, CSTX, OFFER | Customer #, name & address, order details (demand, cancel, return units & dollars & COGs), offer, use, company, division, item details (item #, description, category, category descriptions, style code, style name), page number, page percent, page position, advertised price, offer circ qty, offer page count, offer page cost, offer name, offer start/finish/final date, order date, square inches, fiscal date details. | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 38 | Inventory Value.bqy | Provides analysis of inventory including available quantity (useable and unusable), "good" and "bad" quantity (based on warehouse location) and inventory value for each category. Generates four pivot tables: first by Major Category and Item Code, second by Style, third by Buyer and Major Category and lastly by Item Status and Item Code. | SV_MACITM_ITEM_MAST SV_MACITM_INV_DETAILS SV_MACITM_VENDOR_ITEMS SV_MACITM_COMP_DIV_MASTER |
SETUP, ITEM, INV | Company, Division, Buyer, Category, Sub Major / Minor / Sub Minor Category, Style, Status, Item, Item Status Code, Warehouse, Warehouse Location, Usuable Type Code, | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 39 | Offer PNL.bqy | Provides P&L by offer including circulation, orders, sales, margin, advertising, fulfillment costs and net contribution. Includes one report at the offer level and another at the offer/use level. | SV_MACITM_COMP_DIV_MASTER,
SV_MACITM_OFFERS, SV_MACITM_SOURCES, SV_MACITM_SOURCES_FOP_TOT, SV_MACORD_ORDER_HEADER |
SETUP, OFFER, ORD | None | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 40 | Pending Refunds.bqy | Generates multiple pivot table and chart showing pending refund amount by refund reason, customer and document number. Also produces a report with additional information: customer code, document type and refund date. Selection criteria includes: company, division, customer code. | SV_MACORD_PROCESS_REFUND, SV_MACORD_CUSTOMERS, SV_MACORD_CUST_TEL, SV_MACORD_CUSTOMER_ADDL, SV_MACORD_CUST_EMAIL, SV_MACITM_COMP_DIV_MASTER, REF_CUST_TYPE_LGCY_CD, REF_CUST_STA_LGCY_CD, REF_PROCESS_ACTION_SRCH, REF_CUST_ACTION_LGCY_CD |
ORD, CUST, CSTX, ORDS, SETUP | Company, Division, Refund Date, Document Number, Document Type, Customer Number, Customer EDP ID, Customer Type Code, Customer Status Code, Customer Action Code, Action Search Code, Sales Order Number, Sales Order Ship To, Sales Order Ship To Number, Refund Reason, User Code | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 41 | Open RAs.bqy | Generates multiple pivot tables and charts showing open returns by Return Action Description, Item Number, Return Authorization Expiration Date and Category. | SV_MACORD_REFUND, SV_MACORD_RETRUN_AUTH, SV_MACORD_CUSTOMERS, SV_MACORD_CUSTOMER_ADDL, SV_MACORD_ORDER_HEADER, SV_MACITM_ITEM_MAST, REF_ORD_ITM_STA_LGCY_CD, REF_RTN_REASON_LGCY_CD |
ORDO, CUST, ORD, ORDS, ITEM, SETUP | Company, Division, Return Authorizaiton Number, Return Authorization Date, Authorization Expiration Date, Return Status Code, Sales Order Number, Sales Order Line, Offer, Use, Item Number, Return Code, Return Reason, Required Action Code, Complaint Code, Inventory Disposal Code, Return Item Type Code, Cust Edp, Category, User Code, Sales Order Entry Date, Sales Order Mail Date | 12/30/2006 | 12/30/2006 | 12/30/2006 |
| 42 | Closed AR History.bqy | Generates multiple pivot tables and charts showing closed accounts receivable, aged by categories: current, 31-60, 61-90, 91-120, and 120+ days. Also produces statements. Selection criteria includes: company, division, customer number, customer edp, trans type. | SV_MACORD_CLOSED_AR, SV_MACORD_CREDIT_APPROVAL, SV_MACORD_CUSTOMERS, SV_MACORD_CUST_TEL, SV_MACORD_CUSTOMER_ADDL, SV_MACORD_CUST_EMAIL, SV_MACORD_AR_COMMENTS, SV_MACITM_COMP_DIV_MASTER |
ORD, CUST, CSTX, ORDS, SETUP | Customer #, name & address, A/R transaction type, due date, posted-to reference, originating reference, transaction amount, transaction date, transaction amount, credit limiit, terms days, territory code, sales rep code, A/R comments, customer type, customer status, customer action, phone number, e-mail address | 12/30/2006 | 12/30/2006 | 12/30/2006 |