Information Systems:Important files on Bart

From uniWIKI
Jump to navigation Jump to search

Overview

This page is a list of important or commonly used files (physical or logical) in our DB2 database on Bart, categorized by area of relevance. To do your own digging, remember that IBM provides a file/table that lists and provides detailed info for all files in the system (all objects that are of type *FILE). This file is 'SYSTABLES' in QSYS2. Query it using SQL, filtering by table_schema and/or table_text

SQL and IBM i DB2 terminology

It is very important to be aware of the differences in the way database elements are referred to between the SQL and IBM worlds. Otherwise, it can be very confusing when you hear these terms, especially if you come from a PC background, where a file has always been something you can click on in Windows Explorer that has an extension, and can be opened with whatever associated program is specified for that extension. Take the time to understand the following table:

IBM SQL Layman's terms
Database Database We have 1 database: S1007B50. It's a DB2 database (for comparison, other types

include Microsoft SQL Server, MySQL, and Postgresql).

Library Schema A collection of files/tables. Similar to a PC folder.
Physical file Table Contains data. Object type is PF.
Logical file View An alternate organization of the data in a PF/table, according to an index. "Points" to the data,

does not contain the actual data. Modifying a logical file will thus affect the physical file.

Record Row When you open a file in DFU, you page through records. Those records appear as rows when viewed through SQL.
Field Column When making an edit in DFU, it applies to a single field in a single record. When using the SQL UPDATE

command, you must filter for a single record using a where clause. There are advantages to each method.

Important Libraries

UP1480BFVA UWDASWPRDD

Important Files

Accounting

File Library
SROLOGGL UP1480BFVA The G/L file. This file is massive.
SRODAT UP1480BFVA A/R transactions.
SROKBP UP1480BFVA Batch payments interface file.
Example Example Example
Example Example Example

Carrier Interface

File Library
CIWAYBILLP UWDASWPRDD More or less, the "master file" in Carrier Interface. This is the file that is

written to upon receiving invoices and waybill data from carriers. Lots of records and fields in this one.

Example Example Example
Example Example Example
Example Example Example
Example Example Example

Orders/IOP

File Library
IOPHDRP UWDASWPRDD Order header.
IOPDETP UWDASWPRDD Order lines.
IOPERRP UWDASWPRDD Contains IOP errors. Any time the IOP determines an error on an order line during the pre-check period of

order processing, it's logged here. Fun fact: This was used to query for invalid/blank pharmacist numbers.

OMSLINP XL_WEBSPT Contains lines in OMS messages.

Purchasing

File Library
SRBPOH UP1480BFVA PO header files
INUSERP WEBPRDD Infonet users. Includes Infonet authorities.
PW_SESSF XL_WEBSPT Contains valid session IDs and their expirations.
PW_SVARF XL_WEBSPT Contains smurfs associated with a valid smurf ID / session ID.

Distribution Centre

File Library
SROSRO UP1480BFVA Stockroom/warehouse/inventory balance
SROITR UP1480BFVA Stockroom/warehouse/inventory transactions.
WH1LOC UP1480BFVA Location balances.
WH1LOP UP1480BFVA Location transactions.

Refer also to these files in other areas (see other tables): SRBPOH

WebSmart: Infonet and Web Orders

File Library
WSUSERP WEBPRDD Web Orders users. Login credentials, pharmacist numbers, Web Orders authorities specified in thsi file.

This is the file read by the menu option '60 - Web Interface' in Extensions.

INUSERP WEBPRDD Infonet users. Includes Infonet authorities.
PW_SESSF XL_WEBSPT Contains valid session IDs and their expirations.
PW_SVARF XL_WEBSPT Contains smurfs associated with a valid smurf ID / session ID.
Retrieved from "https://owl.unipharm.com/mediawiki/index.php?title=Information_Systems:Important_files_on_Bart&oldid=6316"