Performing Queries/Analysis—Exercises

IMPORTANT
If you are a student, it is extremely important that you set up and work within your own site to maintain your data integrity. If you work within any other site, you will compromise your own exercise data as well as the data of other students. Predictable exercise results require that your data be isolated in your own site.

Basic Data Setup

Purpose: The purpose of this exercise is to create data in order to use it in the main exercises.

Enter Customer

Windows:
Customer

  1. Enter C.S.C. Oslo as customer with XX-1001 as Customer No.
  2. Enter C.S.C. Paris as customer with XX-1002 as Customer No.
  3. Enter C.S.C. New York as customer with XX-1000 as Customer No.

General exercise for Enter Customer

Create Stored Sales Parts

Windows:
Sales Part
Inventory Locations
Inventory Part
Receive Inventory Part

  1. Enter Street Car as Sales Part and with XX18-300 as Sales Part No.
  2. Enter Race Car as Sales Part and with XX18-100 as Sales Part No.
  3. Enter Ferrari Car as Sales Part and with XX18-350 as Sales Part No.
  4. Create relevant Inventory Locations.
  5. Create corresponding Inventory Parts.
  6. Receive a sufficient number of parts of each into stock.

General exercise for Sales Part
General exercise for Inventory Locations
General exercise for Minimal Inventory Part
General exercise for Direct Receipt

Set Up Output Channels for Group, Sum and Export to Output Channel

Windows:
Configure Output Channels

  1. Click Output in the context pane.
  2. Click Add. Enter Transfer to Excel in the Name field.
  3. Under Generate File of Type, click Text (TXT), select the Use Other Extension check box, and enter xls in the Use Other Extension field.
  4. Under Application to Start, click Default. Click Create to save your changes.

Required Data

Entering Customer Order for Group and Sum

Purpose: The purpose of this exercise is to set up the basic data required for running this particular functionality.

Windows:
Customer Order

  1. Enter a customer order with C.S.C. Oslo (XX-1001) as customer and wanted delivery date in a month's time. Select NO as order type. Use your User ID as the coordinator. Note the order number: __________.
  2. Enter an order line with 20 pcs of the sales part Race Car (XX-18-100).
  3. Enter an order line with 15 pcs of the sales part Street Car (XX-18-300).
  4. Enter an order line with 10 pcs of the sales part Ferrari Car (XX-18-350).
  5. Release the customer order.
  6. Reserve the customer order.
  7. Enter a customer order with C.S.C. Paris (XX-1002) as customer and wanted delivery date in a month's time. Select NO as order type. Use your User ID as the coordinator. Note the order number: __________.
  8. Enter an order line with 20 pcs of the sales part Race Car (XX-18-100).
  9. Enter an order line with 15 pcs of the sales part Street Car (XX-18-300).
  10. Enter an order line with 10 pcs of the sales part Ferrari Car (XX-18-350).
  11. Reserve the parts manually.
  12. Release the customer order.
  1. Enter a customer order with C.S.C. New York (XX-1000) as customer and wanted delivery date in a month's time. Select NO as order type. Use your User ID as the coordinator. Note the order number: __________.
  2. Enter an order line with 20 pcs of the sales part Race Car (XX-18-100).
  3. Enter an order line with 15 pcs of the sales part Street Car (XX-18-300).
  4. Enter an order line with 10 pcs of the sales part Ferrari Car (XX-18-350).
  5. Reserve the parts manually.
  6. Release the customer order.

General exercise for Entering Customer Order
General exercise for Reservations

Entering Customer Order for Group, Sum and Export to Output Channel

Purpose: The purpose of this exercise is to set up the basic data required for running this particular functionality.

Windows:
Customer Order

  1. Enter a customer order with C.S.C. New York (XX-1000) as customer and wanted delivery date in a month's time. Select NO as order type. Use your User ID as the coordinator. Note the order number: __________.
  2. Enter an order line with 10 pcs of the sales part Street Car (XX-18-300).
  3. Reserve the parts manually.
  4. Release the customer order.

General exercise for Entering Customer Order
General exercise for Reservations

Entering Customer Orders for Clear Out Procedure

Purpose: The purpose of this exercise is to enter order lines with Delivered, Invoiced/Closed, and Cancelled statuses in the system.

Windows:
Customer Order

Student 1 Header history—in Invoiced/Closed or Cancelled statuses
Student 2 Line history—in statuses Invoiced/Closed or Cancelled
Student 3 Lines in Cancelled status—including history
Student 4 Lines with Invoiced/Closed status—including history
Student 5 Reservations—in pick lists that are picked
Student 6 Deliveries—lines that are Invoiced/Closed
  1. Create three customer orders that correspond to the conditions that you have been assigned. For example, orders or order lines with Invoiced/Closed status and Cancelled status.

General exercise for Entering Customer Order

Group and Sum

Group

Purpose: The purpose of this exercise is to show you how to analyze the sales parts that each customer has ordered.

Windows:
Customer Order Lines

  1. Press F3 to open the Search window.
  2. Search for all your customers by entering XX-% in the Customer No field.
  3. Click Advanced. In the Advanced tab, in the # column, enter 1 in the Customer No field, and 2 in the Sales Part No field. Click Group in the Group/Sum column for both fields. Click OK.
  4. What is displayed in the overview window? How is the data grouped?

Group and Count

Purpose: The purpose of this exercise is to show you how to analyze the number of orders each customer has placed.

Windows:
Customer Order Lines

  1. Press F3 to open the Search window.
  2. Search for all your customers by entering XX-% in the Customer No field.
  3. Click Advanced. In the Advanced tab, in the # column, enter 1 in the Customer No field and click Group in the Group/Sum column. In the Order No field, click Count in the Group/Sum column. Click OK.
  4. How is the data grouped? What does the total stand for?

Sum

Purpose: The purpose of this exercise is to show you how to analyze the number of sales parts that are reserved.

Windows:
Customer Order Lines

  1. Open the Customer Order Lines window. Press F3 to open the Search window.
  2. Search for all your customers by entering XX-% in the Customer No field and Reserved in the Status field.
  3. Click Advanced. In the Advanced tab, in the Group/Sum column, enter Summarize in the Quantity Reserved field. Click OK.
  4. How many parts are reserved?

Group and Sum

Purpose: The purpose of this exercise is to show you how to analyze how many parts are reserved per sales part.

Windows:
Customer Order Lines

Note: Skip steps 1 and 2 below if you already have completed the Sum exercise.

  1. Open the Customer Order Lines window. Press F3 to open the Search window.
  2. Search for all your customers by entering XX-% in the Customer No field and Reserved in the Status field.
  3. Click Advanced. In the Advanced tab, in the # column, enter 1 in the Sales Part No field and click Group in the Group/Sum column. In the Quantity Reserved field, click Summarize in the Group/Sum column. Click OK.
  4. How many parts are reserved? How are they grouped?

Group, Sum and Export to Output Channel

Purpose: The purpose of this exercise is to show you how to analyze the highest quantity ordered per customer and sales part, and how to export the data to Microsoft Excel.

Calculation

Windows:
Customer Order Lines

C.S.C. New York usually places an order for 5 street cars each month. However, this month the customer placed an order for 10 street cars.

  1. Open the Customer Order Lines window. Press F3 to open the Search window.
  2. Search for your customer by entering XX-% in the Customer No field and XX-% in the Sales Part field.
  3. Click Advanced. In the Advanced tab, in the # column, enter 1 in the Customer No field and 2 in Sales Part No field. Click Group in the Group/Sum column for both fields. In the Sales Qty field, click Highest in the Group/Sum field. Click OK.
  4. What is the highest quantity order?
  5. Click the Excel icon on the toolbar to export the results to Microsoft Excel.
  6. Open Excel, and view the spreadsheet that you just created. What is displayed in it?

Clear Out Procedure

Purpose: The purpose of this exercise is to show you how to remove old data from the database.

Windows:
Cleanup of Customer Orders
Background Jobs

Note: Before you perform this exercise, order lines with Delivered, Invoiced/Closed, and Cancelled statuses must be in the system.

Procedure

  1. Open the Cleanup of Customer Orders dialog box.
  2. Perform the task that you were assigned by selecting the Correspondent check box.
  3. Important: In the Correspondent field, use the List of Values to select the site where your customer orders have been registered. In the Older Than field, enter 0 (zero) days. Click OK.
  4. Open the Background Jobs window. Verify that the background job has finished successfully.
  5. Return to your order headers/lines, and verify that they have been cleared.