1 / 24

QUERY

LANGUAGE. QUERY. Option A Select A - for Amend in desired process Enter a fictitious code Press <enter> File name associated with this file will be displayed at the bottom of the screen. SUPMNT Rev/x.x Company Time Date

sarah
Télécharger la présentation

QUERY

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LANGUAGE QUERY

  2. Option A Select A - for Amend in desired process Enter a fictitious code Press <enter> File name associated with this file will be displayed at the bottom of the screen SUPMNT Rev/x.x Company Time Date Screen : 1 Supplier File Maintenance x User Name Please Select : CRE/AME/REV/DEL/END A *1) Supplier Code [XXXX ] 2) Date Opened Last Changed 3) Supplier Name 5) Address line 1 4) Alpha Key 6) Address line 2 7) Address line 3 8) Post Code 9) Pay to Supplier 10) Our Debtor ID 11) Telephone No 14) Fax No 12) Contact 13) Title 15) Mobile No 16) A.B.N 17) Postal line 1 18) Postal line 2 20) Post Code 19) Postal line 3 record not on file SUPPLIER Locate File Names

  3. Option B Select A to Amend - in desired process Press Enter In the first field of the process Press F1 HELP function key ARDOCTXT Rev/x.x Company Time Date Screen : 1 Maintain Document Text x User Name Please Select : CRE/AME/REV/DEL/END A Text Code [ ] Text Transfer P1 Locate File Names

  4. Option B (continued) You will then view the Help text associated with that field. This will display the file that stores the information from this process. ARDOCTXT Rev/x.x Company Time Date HELP Processor Maintain Document Text x User Name Text Code This is an alphanumeric field of up to 5 characters A minimum entry of 1 character is required The FND function key can be used with this field Lowercase entries are converted to uppercase The BCK key can be used from this field, but FWD is not allowed. The entry must already exist on the DOC.TEXT file which is maintained by the ARDOCTXT process This text will act as a default description for this billing code when it is referenced in "ARDOCENT", and of course acts as a description for thebilling code itself. Locate File Names

  5. Types of Files • MasterFiles • Transaction • Files • Index Files • Balance Files

  6. File Structure What is a dictionary? Data Portion FILE Dictionary Portion

  7. File and Dictionary Terms ID/Key Attributes Values

  8. Sorting Information ReportsVerbs - LIST and SORT ListsVerbs - SELECT, QSELECTSSELECT

  9. QLMNT 1. A code that you give to this procedure 2. A descriptive name that help describe this procedure. 3. A valid Pick Verb as maintained by PICKVERB. 4. Only asked if verb is SELECT or SSELECT 5. The file name you wish to extract information from. P1…etc.. Will contain a list of query commands. QLMNT Rev/x.x Company Time Date Screen : 1 Maintain Stored Procedures x User Name Please Select : CRE/AME/REV/DEL/END [ ] *1) Procedure Name 2) Description 3) Verb Name 4) List Name 5) File Name ----------------------Query Language Commands-------------------------- P1 P2 P3

  10. XQL 1. The procedure code as entered in QLMNT 2. Only asked for select lists in ‘change’ mode 3. Enter a list name if you want to use an existing select list, as a pre-list to this procedure. 4. ‘N’ or a number between 1 and 9 5. ‘S’ to send report to Screen ‘P’ to send report to printer XQL Rev/x.x Company Time Date Screen : 1 Execute a User Defined Procedure x User Name 1) Procedure Name [ ] Purpose Verb Name File Name 2) Save List Name 3) Pre-Start List 4) Via Batch 5) Screen/Printer

  11. Selection Criteria Selection criteria is necessary in order to limit the items that are being processed. If no selection criteria is entered all items in the file will be processed WITH is used in conjunction with a dictionary item, a relational operator and a value. Multiple selections can be joined together with AND and OR

  12. Relational Operators Symbol = orEQ # or NE,NO,NOT > or GT,AFTER < or LT,BEFORE >= or GE <= or LE Meaning Equal to Not Equal to Greater than Less than Greater than or equal to Less than or equal to

  13. Wildcard characters [ Left Square bracket ] Right Square bracket ^ Carat

  14. LANGUAGE QUERY How to turn your QL procedure into a Menu item.

  15. Screen : 1 Maintain Stored Procedures X User Name Please Select : CRE/AME/REV/DEL/END A *1) Procedure Name CUSDISC 2) Description Listing of customers and discount amounts 3) Verb Name SORT Generate a sorted report 4) List Name 5) File Name CUSTOMERS Customer Master file ----------------- Query Language Commands------------------------------- P1 BY CUSTOMER.CODE P2 WITH CUSTOMER.CODE >= "AA]” P3 AND WITH CUSTOMER.CODE <=“Z]” P3 AND WITH DISCOUNT.TERMS = “COD” P4 CUSTOMER.CODE P5 TELEPHONE P6 CONTACT P7 DISCOUNT.TERMS 1. QLMNT Create your procedure in QLMNT How to turn a QL procedure into a Menu item

  16. 2. XQL Run the report in XQL Cust.... Telephone........... Contact......................... Discount Terms Code AA001 58 5765 STEPHEN COOMBES COD AA002 54 7848 Bill Brown COD AA003 69 2768 Joe Smith COD AA006 63 1312 RON & PETER COD AA201 69 2965 Smith’s Hardware COD AA202 65 2222 Jack Brown COD AAMI01 42-2660 Steve Connor COD AATK01 9666-3899 Gary King COD [4051] 8 items listed. How to turn a QL procedure into a Menu item

  17. 3. DEFQLPRC The process code will be the name assigned to your report when it has been copied to the menu. The name will commence with a Z….. The QL procedure is the name of the report you developed in QLMNT. (Step 1) Is the description that will be associated with your process when it is copied to the menu. If your report uses a pre-select list, enter the name here. If not leave this field blank. DEFQLPRC Rev/x.x Testing Company Time Date Screen : 1 Define QL user interface process x User Name Please Select : CRE/AME/REV/DEL/END [C] *1) Process Code 2) QL Procedure 3) Process Name 4) Pre-Select Procedure Constant Tag --------------------------------------------------------------------------------- How to turn a QL procedure into a Menu item

  18. EXAMPLE How to turn a QL procedure into a Menu item DEFQLPRC - Screen 1 DEFQLPRC Rev/x.x Company Time Date Screen : 1Define QL user interface process x User Name Please Select : CRE/AME/REV/DEL/END C *1) Process Code CUSDISC 2) QL Procedure CUSDISC Listing of customers 3) Process Name Customers/Discounts Report 4) Pre-Select Procedure Constant Tag ---------------------------------------------------------------------------------- P1 AA] [FIRST CUSTOMER ] 1. When turned into a menu item our report code will be ZCUSDISC 2. Is the existing QL report we created in QLMNT 3. Is the description of the report name to display on the Menu. Is the prompt/tag that will appear on the Menu after the report has been created.

  19. How to turn a QL procedure into a Menu item DEFQLPRC - Screen 2 1. What is the minimum length of characters that you want the user to enter? If you want the user to always enter something set this field to 1 at least. 2. What is the maximum length of characters that you want the user to enter? The maximum length of Customers Codes is 8. If you only have customer codes of 4 characters, enter 4 here. 3. Is this field type letters or letters and numbers or only numbers. Enter A if your customer codes are alpha characters only, Enter N if your customer codes are alpha and numbers. DEFQLPRC Rev/x.x Company Time Date Screen : 2 Define QL user interface process x User Name Field Definition for First Customer 1) Minimum Length [ ] 2) Maximum Length 3) Field Type 4) Validation Min Max Filename Optional 5) Help Code 6) Find Code 7) Default 8) Include in Heading

  20. EXAMPLE How to turn a QL procedure into a Menu item DEFQLPRC - Screen 2 4. Do you want the input to be checked against a valid file to make sure the customer code does exist? If your answer is Yes. Enter F in this field. If your answer is No leave this field blank. Enter a valid file name to validate the input against. Is input optional? Yes or No 5. Can be used to specify your own help text for this field. Leave blank if not required. 6. Is the name of the file that will be used if the user hits the Find Function key. 7. You can specify a default that will always display in this field. If not, leave this field blank. 8. Allows automatic insertion of the Customer Code (in this example) in the heading. Yes or No response is required. DEFQLPRC Rev/x.x Company Time Date Screen : 2 Define QL user interface process x User Name Field Definition for First Customer 1) Minimum Length 1 2) Maximum Length 8 3) Field Type N 4) Validation F Min Max Filename CUSTOMERS Customers Master File Optional N 5) Help Code 6) Find Code FIND.CUSTOMERS 7) Default 8) Include in Heading N

  21. EXAMPLE How to turn a QL procedure into a Menu item DEFQLPRC - Screen 1 • You will then be returned to Screen 1 of DEFQLPRC if you have other lines in your QLMNT procedure that asks for input Eg.Last Customer, Discount Terms • Follow the same steps from above to define this extra input data validation. DEFQLPRC Rev/x.x Company Time Date Screen : 1Define QL user interface process x User Name Please Select : CRE/AME/REV/DEL/END C *1) Process Code CUSDISC 2) QL Procedure CUSDISC Listing of customers 3) Process Name Customers/Discounts Report 4) Pre-Select Procedure Constant Tag ---------------------------------------------------------------------------------- P1 AA] FIRSTCUSTOMER P2 Z] LAST CUSTOMER P2 COD DISCOUNT TERMS

  22. EXAMPLE How to turn a QL procedure into a Menu item DEFQLPRC - Screen 2 • Set up valid lengths for the Field Discount Terms now - data input, Field types, validation details if required as you did for Customer Code. • For each line in your QLMNT procedure where you have a selection line eg customer.code = “AA]”, discount.terms = “COD]” etc..etc. you will be prompted to set up the details. • When you have set up all your selection criteria and pressed <enter> the procedure will be generated and inserted into the appropriate user menu. DEFQLPRC Rev/x.x Company Time Date Screen : 2 Define QL user interface process x User Name Field Definition for Discount Terms 1) Minimum Length 1 2) Maximum Length 4 3) Field Type N 4) Validation F Min Max Filename TERMS.CODE Maintain Payment Terms Optional N 5) Help Code 6) Find Code FIND.TERMS 7) Default 8) Include in Heading

  23. LANGUAGE QUERY Using your DEFQLPRC procedure from the Menu.

  24. Using your DEFQLPRC procedure from the Menu. • Access your report by typing ZCUSDISC. • You may need to change security profiles that are in place before you are able to access the generated process. ZCUSDISC Rev/x.x Company Time Date Screen : 1 Customers/Discounts Report x User Name First Customer [ ] Last Customer Discount Terms Via Batch Screen/Printer

More Related