Information Systems:ASW Query Manager
Overview
Query Manager is an ASW function to organize queries, and provide users with an interface to enter record selection parameters.
Access it through ASW / System Management / Query Manager, or GO ASWQRY
Instructions
Build Query Run Controls
Access through ASW / System Management / Query Manager, or GO ASWQRY
Use QUERY to build the queries to fulfill the user's requirement. For example 'QRYBUY01' to extract all regular stock items by a requested buyer, and 'QRYBUY02' to print a list of them, along with the stock on hand.
Select first menu option; 'Work with query run controls', and change 'User ID' to *ALL.
*VA/480B* Work with query run controls 12/16/15 10:49:32 ASTD43001
-------------------------------------------------------------------------------
1=Add 2=Change 3=Copy 4=Delete 5=Display 7=Rename 8=Menu item 9=Run
_ User ID Request Description
_ *ALL ASSET01 Create CSV file of Asset Inventory
_ *ALL BRANDPHARM Branded Pharmie Sales by Customer
_ *ALL BUYINVLIST Supplier Inventory List
_ *ALL BUYINVLISV Supplier Inventory List with VEBA
_ *ALL BUYINVLISW Supplier Inv List with VEBA for both whs
_ *ALL BUYINVLISX Secondary Suppliers Inventory List
_ *ALL BUYREP001 onhand=0, planning meth=0, activ in whse
_ *ALL BUYREP002 items s/b ordering but aren't
_ *ALL BUYREP003 stat 20 nonpromo orders with stck onhand
_ *ALL BUYREP004 Print Items with line codes
_ *ALL BUYREP005 stat 20 promo orders with stck onhand
More...
User ID Request Description
*ALL ________ ___________________
F7=WRKQRY F8=WRKSBMJOB F9=WRKSPLF F10=Work with menus
Press F6 to add.
*VA/480B* Add query run control 12/16/15 10:59:11 ASTD43004
-------------------------------------------------------------------------------
User ID............. *ALL
Request............. BUYREP012
Description......... List regular stock items for a vendor
Normally, key in *ALL for user. It is possible to restrict a query to a particular user or users; but you must be signed on as FINADMIN to have authority to any user other than yourself or *ALL.
Press enter to continue.
*VA/480B* Query run control record details 12/16/15 11:01:54 ASTD43101
Selection field controls
-------------------------------------------------------------------------------
User ID............. *ALL
Request name........ BUYREP012 Desc List regular stock items for a vendor
Field label Entry length Window
Date field 1........ _____________________
Date field 2........ _____________________
Alpha/num field 1... Vendor Number 11
Alpha/num field 2... _____________________ 0
Alpha/num field 3... _____________________ 0
Alpha/num field 4... _____________________ 0
Alpha/num field 5... _____________________ 0
Alpha/num field 6... _____________________ 0
Numeric field 1..... _____________________ 0 0
Numeric field 2..... _____________________ 0 0
Numeric field 3..... _____________________ 0 0
Numeric field 4..... _____________________ 0 0
Numeric field 5..... _____________________ 0 0
Numeric field 6..... _____________________ 0 0
F7=WRKQRY F8=WRKSBMJOB F9=WRKSPLF F10=Command entry
If the user is to key in any selection parameters, indicate what they are. When the user is to key a date, the date format should be included in 'Field label'; ie MMDDYY. This is the format defined within ASW.
If the user will not be entering any parameters, just press enter.
*PL/480B* Query run control record details 12/16/15 13:30:37 ASTD43102
Run controls
-------------------------------------------------------------------------------
User ID......... *ALL
Request name.... BUYREP012 List regular stock items for a vendor
Run command 1... RUNQRY QRY(QRYBUY01) QRYFILE((*LIBL/XXITEMP) (*LIBL/SROQYIFC))
Run command 2... RUNQRY QRY(QRYBUY02) QRYFILE((*LIBL/ITEMFILE) (*LIBL/SROSRO))
Output controls:
Output type..... *RUNOPT
Output form..... *RUNOPT
Printer......... PRT01
Copies.......... 1
F7=WRKQRY F8=WRKSBMJOB F9=WRKSPLF F10=Command entry
There is space to enter two commands. If the process will be more than that, then the first command can be a call to a CL program. However, if the final result will be a query printing a report, do not include it in the CL; call it in the second command line. The reason for this, is that when the user submits this request they will be prompted to enter the name of the printer to use.
Make sure that *LIBL is shown for the data files. This causes query to use the files from the environment you are in - production or development - instead of the library defined in the query.
Notice the file SROQYIFC (Query interface file). A temporary copy of this file is made when the user keys in any parameters, containing those parameters. If the library for this file does not say '*LIBL', or even 'QTEMP', the file used will be in library UP1480BFVA - which will have no records.
This file will be linked to all records in the primary file, so the value(s) read from it will always be available. You can go back into the query you created, add this file, and change the record selection from -
ITSUPP EQ '22478'
to -
ITSUPP EQ QYSLV1
Run Query Request
All the ASW queries that have a 'User ID' that is either *ALL, or the user's, will show.
*PL/480B* Select a query to run 12/16/15 15:23:50 ASTD44001 ------------------------------------------------------------------------------ Request Description _ ASSET01 Create CSV file of Asset Inventory _ BRANDPHARM Branded Pharmie Sales by Customer _ BUYINVLIST Supplier Inventory List _ BUYINVLISV Supplier Inventory List with VEBA _ BUYINVLISW Supplier Inv List with VEBA for both whs _ BUYINVLISX Secondary Suppliers Inventory List _ BUYREP001 onhand=0, planning meth=0, activ in whse _ BUYREP002 items s/b ordering but aren't _ BUYREP003 future & special orders with stck onhand _ BUYREP004 Print Items with line codes _ BUYREP005 stat 20 promo orders with stck onhand _ BUYREP006 # of lines; status 20 promos with stock _ BUYREP007 items with onhand, and on stop buy _ BUYREP008 Warehouse Inventory Older Than 2 Years + ------------------------------------------------------------------------------ Request Description __________ ______________________________________
Select the one you want, and press enter. If the run control has defined a selection field, you will see -
*PL/480B* Run query request 12/16/15 15:23:03 ASTD45001
------------------------------------------------------------------------------
Request name........ BUYREP004 Desc Print Items with line codes
Record selection values: Entry
Length Window
For Vendor Number 22518 11
*PL/480B* Run query request 12/16/15 15:23:30 ASTD45002
Print Items with line codes
------------------------------------------------------------------------------
Output controls:
Output type......... *RUNOPT
Output form......... *RUNOPT
Printer............. ELAHO
Copies.............. 1