Stored procedures & user-defined functions in WebDocs - iSeries Edition
From Support
Contents |
Stored Procedures
Stored procedures are like program calls that can be used in an SQL environment. They can be called only using ODBC, JDBC, or CLI (call level interface) on the iSeries. They can be tested using the iSeries Navigator SQL Script runner, which uses JDBC. I recommend that the library name be used when calling these stored procedures.
There are 3 stored procedures - all 3 have the same parameters:
1. WebDocs user
- NOTE: Leave the user blank (or empty) to retrieve all valid folders, types, or keys
2. Sort by description
- Put 'Y' or 'y' into the sort by parameter to sort the results first by the description, then by the regular sort
- The sort is case-insensitive for the description
- This is intended to provide a nice alphabetical listing by description
- NOTE: Leave the sort by blank (or empty) to leave the description out of the sort
3. Program library
- This should almost always be 'RJSIMAGE'
- This will be added to the library list when the stored procedure is called
4. Data library
- This will usually be blank (or empty), for data that is in RJSIMAGE
- If not blank and not the same as the program library, this be added to the library list when the stored procedure is called
GetFolderList
1. Specify the user to retrieve valid folders (no gaps, no missing parents) to which the user has access
- The results include the folder permissions for the user
2. The level 1 description, if specified, is used first in the sort, then the 5 levels
Examples
Get a list of folders to which 'abrown' (parameter 1) has access, both by itself and through any groups of which 'abrown' is a member. The sort order of the list will include the text description of level 1 (parameter 2). The program library (parameter 3) is 'RJSIMAGE', and the data library (parameter 4) is also 'RJSIMAGE'.
call rjsimage/getfolderlist ('abrown', 'Y', 'RJSIMAGE', 'RJSIMAGE')
Get a list of all folders (parameter 1 is empty) without the text description of level 1 in the sort order (parameter 2 is empty). The program library is 'RJSIMAGE' (parameter 3), and the data library (parameter 4) is empty, as it is also 'RJSIMAGE'.
call rjsimage/getfolderlist (, , 'RJSIMAGE', )
Folder 1 Folder 2 Folder 3 Folder 4 Folder 5 Text description ChkOut ChkIn Delete Notes Move Email LEVEL 1 TEXT ACCOUNTING Accounting ACCOUNTING AP A/P ACCOUNTING AP SERVICES Services ACCOUNTING AR A/R ACCOUNTING GL G/L AP WA Accounts Payable AP INVOICES WA AP Invoices A1 a1 A1 A2 a2 BAKERAR Baker AR BAKERAR BAKERINVOICE Baker Sales Orders BJSREPORTLEVEL1 L1 Desc etc. BJSREPORTLEVEL1 BJSREPORTLEVEL2 L2 Desc CERTTRANS Transportation and Physical Development
GetTypeList
Specify the user in order to retrieve the document types to which the user has access Calling application determines whether to specify the user, based on value in data area DOCTYPESEC The results include the flag that indicates whether the title uses a lookup list (DOCL00) The document type description, if specified, is used first in the sort, then the type itself
Examples Get a list of document types to which 'abrown' (parameter 1) has access, both by itself and through any groups of which 'abrown' is a member. The sort order of the list will include the text description of the document type (parameter 2). The program library is 'RJSIMAGE' (parameter 3), and the data library (parameter 4) is empty, as it is also 'RJSIMAGE'.
call rjsimage/gettypelist ('abrown', 'y', 'RJSIMAGE',)
Document type Text Use lookup TEXT ASDADSFSA AP Accounts Payable ACCOUNTS PAYABLE AR Accounts Receivable ACCOUNTS RECEIVABLE LINEAGE Choctaw Lineage Y CHOCTAW LINEAGE CLAIMS Claims CLAIMS
Get a list of all document types (parameter 1 is empty) without the text description in the sort order (parameter 2 is empty). The program library is 'RJSIMAGE' (parameter 3), and the data library (parameter 4) is empty, as it is also 'RJSIMAGE'.
call rjsimage/gettypelist (, , 'RJSIMAGE', ) Document type Text Use lookup TEXT A A test AP Accounts Payable APINVOICE AP Invoice/Florida Northeast AR Accounts Receivable ASDADSFSA ASPX aspc BAKERINV Baker Sales Order CAPITAL ASSETS Capital Assets Invoice CERTLIABINS Certificate of Liability Insurance CLAIMS Claims DCS Contracts EASY PRINT Easy Print/Hand Checks GREG DOC TYPE 2 Test 2 GREG DOC TYPES Gregs Test ILO Impaclab Documentation INVOICE Outstanding Invoices ISO ISO Documents LINEAGE Choctaw Lineage Y LOTTICKET Lot Ticket MB INVOICE Major Brands Invoice MEDREC Medical Record MISC Miscellaneous NEWONE2 Another new one Y OPERATIONS Operations Invoice PURCHASING Purchasing Invoice RETAIN01YEAR Retain document 1 year RETAIN07DAYS Retain document 7 days RETAIN15DAYS Retain document 15 days RJSINV RJS Invoice TESTOCRDOC Testing OCR Document Option TIME Time Entry Documents TIMESHEET Employee timesheet VERNLEVEL3B Vern Level 3b WAAPINVOICE Washinton County AP Invoice WACTRECEIPT WACT Expense Tracking WEEKLYEDIT Weekly Timesheet Edit
GetKeyList
Specify the user in order to retrieve the keys for the document types to which the user has access Calling application determines whether to specify the user, based on value in data area DOCTYPESEC The results include the flags that indicate whether the key
1) is displayed,
2) uses a lookup list (DOCL00), 3) is read-only, 4) is required, and 5) is part of shared data as join or data
The document type description, if specified, is used first in the sort, then the type itself, the the true numeric value of the key
Examples
Get a list of keys for document types to which 'abrown' (parameter 1) has access, both by itself and through any groups of which 'abrown' is a member. The sort order of the list will include the text description of the document type (parameter 2). The program library is 'RJSIMAGE' (parameter 3), and the data library (parameter 4) is empty, as it is also 'RJSIMAGE'.
call rjsimage/getkeylist (, 'y', 'RJSIMAGE',)
Document type Key Text Display Read-only Required Use lookup Shared Sequence TYPE TEXT AP USERKEY1 Vendor Number Y 1 ACCOUNTS PAYABLE AP USERKEY2 Vendor Name Y 2 ACCOUNTS PAYABLE AP USERKEY3 PO N 3 ACCOUNTS PAYABLE AP USERKEY4 Invoice Number Y 4 ACCOUNTS PAYABLE AP USERKEY5 AP Key 5 N 5 ACCOUNTS PAYABLE AP USERKEY6 AP Key 6 N 6 ACCOUNTS PAYABLE AP USERKEY7 AP Key 7 N 7 ACCOUNTS PAYABLE AP USERKEY8 AP Key 8 N 8 ACCOUNTS PAYABLE AP USERKEY9 AP Key 9 N 9 ACCOUNTS PAYABLE AP USERKEY10 AP Key 10 N 10 ACCOUNTS PAYABLE AR USERKEY1 Customer# Y 1 ACCOUNTS RECEIVABLE AR USERKEY1 Invoice# 1 ACCOUNTS RECEIVABLE AR USERKEY2 Customer# 2 ACCOUNTS RECEIVABLE AR USERKEY2 Name Y 2 ACCOUNTS RECEIVABLE AR USERKEY3 AR Key 3 N 3 ACCOUNTS RECEIVABLE AR USERKEY3 P.M.# 3 ACCOUNTS RECEIVABLE AR USERKEY4 AR Key 4 N 4 ACCOUNTS RECEIVABLE AR USERKEY4 Project# 4 ACCOUNTS RECEIVABLE AR USERKEY5 AR Key 5 N 5 ACCOUNTS RECEIVABLE AR USERKEY6 AR Key 6 N 6 ACCOUNTS RECEIVABLE AR USERKEY7 AR Key 7 N 7 ACCOUNTS RECEIVABLE AR USERKEY8 AR Key 8 N 8 ACCOUNTS RECEIVABLE AR USERKEY9 AR Key 9 N 9 ACCOUNTS RECEIVABLE AR USERKEY10 AR Key 10 N 10 ACCOUNTS RECEIVABLE LINEAGE USERKEY1 ID Number Y Y J 1 CHOCTAW LINEAGE LINEAGE USERKEY2 Last Name Y Y Y D 2 CHOCTAW LINEAGE LINEAGE USERKEY3 First Name Y Y D 3 CHOCTAW LINEAGE LINEAGE USERKEY4 Middle Name Y D 4 CHOCTAW LINEAGE LINEAGE USERKEY5 Married Name Y D 5 CHOCTAW LINEAGE LINEAGE USERKEY6 DOB Y D 6 CHOCTAW LINEAGE LINEAGE USERKEY7 SSN Y D 7 CHOCTAW LINEAGE LINEAGE USERKEY8 Dawes Roll Number Y 8 CHOCTAW LINEAGE LINEAGE USERKEY9 Legato Document ID Y 9 CHOCTAW LINEAGE CLAIMS USERKEY1 Enter Social Security Number - numerals only, no dashes Y 1 CLAIMS CLAIMS USERKEY2 Enter Patient Life ID Y 2 CLAIMS CLAIMS USERKEY3 Enter Provider Tax ID - numerals only, no dashes Y 3 CLAIMS CLAIMS USERKEY4 Enter Provider ID Y 4 CLAIMS CLAIMS USERKEY5 Enter Claim Date - YYYYMMDD Y 5 CLAIMS CLAIMS USERKEY6 Select Claims Adjuster from list Y 6 CLAIMS CLAIMS USERKEY7 Enter Scan Date - YYYYMMDD Y 7 CLAIMS CLAIMS USERKEY8 Enter Claim ID Y 8 CLAIMS CLAIMS USERKEY9 Document ID Y 9 CLAIMS CLAIMS USERKEY10 Document Link Y 10 CLAIMS
Get a list of keys for all document types (parameter 1 is empty) without the text description in the sort order (parameter 2 is empty). The program library is 'RJSIMAGE' (parameter 3), and the data library (parameter 4) is empty, as it is also 'RJSIMAGE'.
call rjsimage/getkeylist (, , 'RJSIMAGE',)
Document type Key Text Display Read-only Required Use lookup Shared Sequence TYPE TEXT AP USERKEY1 Vendor Number Y 1 AP USERKEY2 Vendor Name Y 2 AP USERKEY3 PO N 3 AP USERKEY4 Invoice Number Y 4 AP USERKEY5 AP Key 5 N 5 AP USERKEY6 AP Key 6 N 6 AP USERKEY7 AP Key 7 N 7 AP USERKEY8 AP Key 8 N 8 AP USERKEY9 AP Key 9 N 9 AP USERKEY10 AP Key 10 N 10 APINVOICE USERKEY1 Vendor Y 1 APINVOICE USERKEY2 P.O. Number Y 2 APINVOICE USERKEY3 Invoice Number Y 3 APINVOICE USERKEY4 Invoice Date Y 4 APINVOICE USERKEY5 Invoice Type Y 5 APINVOICE USERKEY6 Project Y 6 AR USERKEY1 Customer# Y 1 AR USERKEY1 Invoice# 1 AR USERKEY2 Name Y 2 AR USERKEY2 Customer# 2 AR USERKEY3 AR Key 3 N 3 AR USERKEY3 P.M.# 3 AR USERKEY4 AR Key 4 N 4 AR USERKEY4 Project# 4 AR USERKEY5 AR Key 5 N 5 AR USERKEY6 AR Key 6 N 6 AR USERKEY7 AR Key 7 N 7 AR USERKEY8 AR Key 8 N 8 AR USERKEY9 AR Key 9 N 9 AR USERKEY10 AR Key 10 N 10 BAKERINV USERKEY1 Customer Number Y 1 BAKERINV USERKEY2 Order Number Y 2 BAKERINV USERKEY3 Customer Purchase Order Y 3 BAKERINV USERKEY4 Amount Y 4 BAKERINV USERKEY5 Paid? Y 5 etc.
User-defined Functions
User-defined functions are similar to the built-in functions of SQL and can be used in the same way as those built-ins are used. If using system naming, RJSIMAGE has to be in the library list, because you cannot qualify functions with the library when using sytem naming. If using SQL naming, you can qualify the function with the library name, as in
RJSIMAGE.function_name
There is one user-defined function at this time.
FolderIsValid
This function returns the validity status of a folder, that is, whether there are gaps in the levels (a higher level has a blank code) or the level has missing parent levels. It has the following parameters:
1. Folder level 1 (DOCFLR1)
NOTE: This should never be blank, due to editing when folders are created
2. Folder level 2
3. Folder level 3
4. Folder level 4
5. Folder level 5
6. Program library
- This should almost always be 'RJSIMAGE'
- This will be added to the library list when the stored procedure is called
7. Data library
- This will usually be blank (or empty), for data that is in RJSIMAGE
- If not blank and not the same as the program library, this be added to the library list when the stored procedure is called
Examples
Return the folders that are not valid
select * from docflr00 where folderisvalid(docflr1,docflr2,docflr3,docflr4,docflr5,'RJSIMAGE',) <> 0 Folder 1 Folder 2 Folder 3 Folder 4 Folder 5 Text NATHAN NATHAN NATHAN NATHAN /RJSIMAGEDOC VERN1 VERN2 Vern 2 /RJSIMAGEDOC VERN1 VERN2 VERN4 Vern 4 /RJSIMAGEDOC VERN1 VERN2 VERN3 Vern 3 /RJSIMAGEDOC
The first 2 and the last are invalid because they do not have all the parents. The 3rd one is invalid because one of the levels is skipped - there is a gap.
Return the folders that are valid
select * from docflr00 where folderisvalid(docflr1,docflr2,docflr3,docflr4,docflr5,'RJSIMAGE',) = 0
Folder 1 Folder 2 Folder 3 Folder 4 Folder 5 Text ACCOUNTING Accounting ACCOUNTING AP A/P ACCOUNTING AP SERVICES Services ACCOUNTING AR A/R ACCOUNTING GL G/L AP WA Accounts Payable AP INVOICES WA AP Invoices A1 a1 A1 A2 a2 BAKERAR Baker AR BAKERAR BAKERINVOICE Baker Sales Orders BJSREPORTLEVEL1 L1 Desc BJSREPORTLEVEL1 BJSREPORTLEVEL2 L2 Desc CERTTRANS Transportation and Physical Development etc.
