These are the queries you may find most helpful, and are a subset of all the queries available to your team. Please be aware that queries are updated from time to time, with new fields added or removed. Completely new queries may also be added.
Queries are categorized by the type of data involved. < Designates queries recently added.
Category: Sales and Marketing
Active Connections - Accounting
Lists each AC supplier, their retailer connections, and the billing account driving subscriptions.
Fields: Supplier, Supplier BUID, Retailer, Retailer BUID, Sync?, SBT?, Coors dist?, Billing Account.
Active Retailer List
Run this to display Retailer BUIDs. Lists all retailers actively engaged with PCG Advanced Commerce. Corp Name / BU Name shows "banners" or divisions within a parent corporation. Sync is AC Repository. SBT includes any advanced service, such as SBT or DBT.
Fields: Corp Name, BU Name, BU ID, Sync?, SBT?, City, State, Zip, Address
Active Supplier List
Run this to display Supplier BUIDs. Lists all suppliers actively engaged with PCG Advanced Commerce. Includes supplier's general Product Line. Sync is AC Repository. SBT includes any advanced service, such as SBT or DBT. AZ means AutoZone supplier. Coors Dist shows if supplier is a MillerCoors distributor.
Fields: Product Line, Name, BU ID, Sync?, SBT?, AZ?, Coors Dist?, City, State, Zip, Address
Active items (selling) for a Supplier (Past month)
Lists the number of distinct items (SKUs, not quantity) sold for a supplier within the last 31 days.
Fields: Active Items
Note: You must know the supplier's BUID.
Agreements for a specific Customer
Lists all trading partnerships that include a specific client. You must enter the client's Business Unit ID (BUID). This can be obtained from the Active Retailer List or Active Supplier List query under Sales and Marketing.
Fields: Customer, BUID, Service, Service Name, With, ID, City, State, Zip, Status
Note: You must know the customer's BUID.
Authorized Item Count by Supplier for AC Retailer
Lists the total number of items (SKUs), by supplier, that are authorized to a specific retailer. This is helpful for V&A Server planning and other estimation needs. Note that these are items offered to and accepted by the retailer only, and may not include all possible items manufactured or distributed by each supplier.
Fields: Supplier, Suplr ID, Retailer, Rtlr ID, Service, No. Auth Items
Note: You must know the retailer's BUID.
> Average days to post store delivery
Provides a year-to-date, month-by-month breakdown of the average days it takes suppliers to post delivery information after the delivery business day has passed.
Fields: Supplier, Retailer, Month, Avg Days to Post, Nbr of Deliveries
Billing Configuration by Account
Lists the fee schedules associated with a customer's monthly, quarterly, or annual subscription.
Fields: Multiple fields specific to Accounting. Jim Scanlan can assist.
Note: You must know the customer's Billing Account ID. In most cases this is the BUID. Jim Scanlan can assist if the BUID does not provide the desired results.
Contacts for a particular Customer
Lists all online users and report recipients associated with a specific client. You must enter the client's Business Unit ID (BU ID). This can be obtained from the Active Retailer List or Active Supplier List query.
Fields: Company, Address, City, State, Zip, User, Email, Phone
Note: You must know the customer's BUID.
> Cost last change date for retailer by supplier
Lists the latest change date for any cost in a supplier's catalog for each supplier associated with the retailer selected. Use to view who is keeping up their price books. MAX in the report contents means the "last".
Fields: Supplier Name, Supplier ID, Max Cost Last Change Date,
Note: You must know the retailer's BUID.
Sales Volume By Retailer for a Supplier (Last 12 Months)
Supplier sales volume per retailer listed by year and month. Includes the supplier's store count for each retailer.
Fields: Supplier, Retailer. Year, Month, Sales Volume, Store Count
Note: You must know the supplier's BUID.
SBT Monthly Stats - Compare This Year to Last Year
Comparison of this years/last years partnership performance for a specific month. Enter the numeric Month to compare. For example, enter 3 for March.
Act Stores (active) are stores set up in PCG. Inv. Stores are fully deployed and trading through SBT or other service. Amt. Invoiced is the value of sales items invoiced. No. Deliveries are the physical deliveries to a store location. Amt. Invoiced Dlvry is the value of delivery items invoiced.
Fields: Supplier, Retailer, Service, Act. Stores, Last Yr, Inv. Store, Last Yr, Amt. Invoiced, Last Yr, No. Deliveries, Last Yr, Amt. Invoiced Dlvry, Last Yr
Sales Volume By Retailer for a Supplier (Last 12 Months)
Supplier sales volume per retailer listed by year and month. Includes the supplier's store count for each retailer.
Fields: Supplier, Retailer. Year, Month, Sales Volume, Store Count
Note: You must know the supplier's BUID.
> Store count last dates for retailer by supplier
Lists the latest count and settlement dates for each supplier associated with the retailer selected. MAX in the report contents means "last".
Fields: Supplier Name, Supplier ID, Max Count Date, Max Settlement Date
Note: You must know the retailer's BUID.
> Store delivery last business date for retailer by supplier
Lists the latest delivery business date for each supplier associated with the retailer selected. MAX in the report contents means the "last".
Fields: Supplier name, Supplier ID, Max Delivery Bus Date
Note: You must know the retailer's BUID.
> Supplier item last change date for retailer by supplier
Lists the latest change date for any item in a supplier's catalog for each supplier associated with the retailer selected. MAX in the report contents means the "last" SI equals Supplier Item..
Fields: Supplier Name, Supplier ID, Max SI Last Change Date
Note: You must know the retailer's BUID.
Supplier/Retailers having > 500 Items with SBT/SIM
All partnerships performing SBT (actively invoicing or undergoing simulation) in which more than 500 specific items are trading.
Fields: Supplier, Suplr ID, Retailer, Rtlr ID, No. Items
Suppliers located in State - XX
Lists all suppliers within a state. Enter the State code in upper case. For example, NY not ny. Query does NOT list MillerCoors Distributorship subordinates. (Not possible from a database perspective.)
Supplier is the supplier company name in PCG. Coors? designates whether the supplier is a MillerCoors distributor. Coors No is the distributorship number. Sync Sts is the agreement status with the retailer -- AC=Active, HL= Hold. Since is the date the status was set. With is the retailer trading partner or form.
Fields: Supplier, Coors?, Coors No, Sync Sts, Since, With
Suppliers/Retailers having > 50 stores with SBT/SIM
All partnerships performing SBT (actively invoicing or undergoing simulation) in more than 50 stores.
Fields: Supplier, Suplr ID, Retailer, Rtlr ID, No. Stores
YTD Sales and Delivery Summary by Service
Shows statistics for the queried year, by month, for each advanced service such as SBT, DBT, or DBTS (DBT with sales). Enter the Year as yyyy. For example, 2007.
Act. Store Connections are stores trading through the service. No. Invoicing is the number stores for which the client is billed. $Inv. from Sales and Dlvrys is the total invoice-able movement value. $All Sales and Dlvrys are the total value of sales / deliveries processed through the service, regardless of invoicing status. Units are the quantities of items sold / delivered in each category. No. Deliveries are the number of physical deliveries made to store locations.
Fields: Service, Month, Act. Store Connections, No Invoicing, $Inv. from Sales, Units, $All Sales, Units, No. Deliveries, $Inv. from Dlvrys, Units, $Net All Dlvrys, Units
Category: Directory
Active Advanced Commerce Agreements - All
Lists all trading partnerships actively engaged in SBT, DBT, or other advanced service. List does not include clients doing sync (AC Repository) service only.
Fields: Service, Retailer, Rtlr ID, Supplier, Splr ID
Agreements for a specific Customer
Lists all trading partnerships that include a specific client. You must enter the client's Business Unit ID (BUID). This can be obtained from the Active Retailer List or Active Supplier List query under Sales and Marketing.
Fields: Customer, BUID, Service, Service Name, With, ID, City, State, Zip, Status
Note: You must know the customer's BUID.
Retailer (ALL Active) - User List
All users whose company type is "retailer". Good for mailing lists. Please note that some clients have asked not to be mailed. These are NOT marked in this list.
UserId is yes/no client has a login ID. Last Login is date they last logged into AC. Nbr Dlists is the number of AC report/document distribution lists to which they are assigned.
Fields: Retailer, ID, Type, First, Last, Email, Name (full), UserId, Last Login, Days since Login, Nbr Dlists
Supplier (ALL Active) - User list
All users whose company type is "supplier". Good for mailing lists. Please note that some clients have asked not to be mailed. These are NOT marked in this list. Coors field shows if supplier is a Coors distributor, and ineligible for advanced services such as SBT.
UserId is yes/no client has a login ID. Last Login is date they last logged into AC. Nbr Dlists is the number of AC report/document distribution lists to which they are assigned.
Fields: Supplier, ID, Type, Coors?, First, Last, Email, Name (full), UserId, Last Login, Days since Login, Nbr Dlists
Sync Connections (Held, Active), and Their Current AC Service/Status
All trading partnerships engaged in either sync only (AC Repository) or sync with SBT, DBT, etc. Includes Sync Sts (status) field to show if service between partners is active or on hold due to implementation or other issues. Coors field shows if supplier is a Coors distributor, and ineligible for advanced services such as SBT. Service Sts field shows active advanced agreements. Field is blank if no agreement exists.
Fields: Retailer, Rtlr ID, Supplier, Splr ID, Coors?, Sync Sts, AC Service, Service Sts
Updated 5/25/2012