University of Calgary

...Year End Processing

Example Korn Scripts

  • Database maintenance: problemmonorders.ksh

    • Sample output:

      
                Listing of Monograph Orders which are problems in 1999.
                =======================================================
               ...produced on Wed Mar 29 12:12:36 MST 2000

                Order_ID| ...information about problem detected...

**************************************************************************
CANCEL errors where no copies are cancelled yet date to cancel is past ...

        264252|  Copies o/o: 2|  Copies rcvd: 1|   Date to cancel: 20000123|
***************************************************************************
CLAIM errors where the date to claim is NEVER and there are outstanding unrcvd copies

        261137|FIRM|  Line copies o/o: 1|  Line copies rcvd: 0|  Date to claim: NEVER
        261964|FREE-MONO|  Line copies o/o: 1|  Line copies rcvd: 0|  Date to claim: NEVER
        263303|FIRM|  Line copies o/o: 1|  Line copies rcvd: 0|  Date to claim: NEVER
        263973|FIRM|  Line copies o/o: 1|  Line copies rcvd: 0|  Date to claim: NEVER
        263975|FIRM|  Line copies o/o: 1|  Line copies rcvd: 0|  Date to claim: NEVER
        264257|FIRM|  Line copies o/o: 1|  Line copies rcvd: 0|  Date to claim: NEVER
***************************************************************************
ORDER error where the date ready is NEVER and number of copies on order is more than zero

        261863|APPROVAL|   # copies on order: 1
        262354|APPROVAL|   # copies on order: 1
        262600|APPROVAL|   # copies on order: 1
        263303|FIRM|   # copies on order: 1
        264257|FIRM|   # copies on order: 1
***************************************************************************
        260764|   # copies rcvd: 0|   # copies paid: 1
        261064|   # copies rcvd: 0|   # copies paid: 1
        261191|   # copies rcvd: 0|   # copies paid: 2
        261338|   # copies rcvd: 0|   # copies paid: 1
        261532|   # copies rcvd: 0|   # copies paid: 1
        261582|   # copies rcvd: 0|   # copies paid: 1
        262417|   # copies rcvd: 0|   # copies paid: 1
        263547|   # copies rcvd: 0|   # copies paid: 1
***************************************************************************
Accounting type error when more copies of the order are rcvd than paid.

        260123|   # copies rcvd: 1|   # copies paid: 0
        260143|   # copies rcvd: 2|   # copies paid: 1
        260205|   # copies rcvd: 1|   # copies paid: 0
        260329|   # copies rcvd: 1|   # copies paid: 0
        260337|   # copies rcvd: 1|   # copies paid: 0
        260353|   # copies rcvd: 1|   # copies paid: 0
        260363|   # copies rcvd: 1|   # copies paid: 0
  • Similar script for serial orders but specific errors for our site since we don't pay or receive serial orders on Unicorn yet.


    YRVENDORLST and FUNDSUMMARY

  • YEVENDORLST is a year end report we use to get statistics for our acquisitions department.

      .prn output tells where to find the Excel file:

Look for final COMBINED VENDOR Excel file (no subtotals) in
/s/sirsi/Unicorn/Rptcustom/Run/Yevendorlst/20000403_over_1997_less_than_2000_ALLvendors.final

Look for final SERIAL VENDOR Excel file in
/s/sirsi/Unicorn/Rptcustom/Run/Yevendorlst/20000403_over_1997_less_than_2000_serial.final

Look for final MONOGRAPH VENDOR Excel file in
/s/sirsi/Unicorn/Rptcustom/Run/Yevendorlst/20000403_over_1997_less_than_2000_monograph.final

Look for final DOUCETTE VENDOR Excel file in
/s/sirsi/Unicorn/Rptcustom/Run/Yevendorlst/20000403_over_1997_less_than_2000_docette.final


      
-------------------SAMPLE EXCEL FILE-----------------------------------
Fiscal_cyc|VendorID|TotalOrders|#CopiesOrd.|#Copiesrcvd|#copiespaid|TotalPaid|Avgdaystorcvd|
#items_claimed|Claims_sent|#cop.cancelled|Avgpaidprice|Avgdiscnt|
1998|1243|2|2|2|2|$20.00|5|0|0|0|$10.00|0.00|
1998|30|2|2|1|1|$1155.60|19|0|0|0|$1155.60|22.44|
1998|307|2|3|1|3|$31.25|74|0|0|0|$10.42|6.83|
1998|AABC|12|10|10|10|$149.27|50|0|0|0|$14.93|3.63|
1998|AAFRD|5|5|1|5|$132.68|30|0|0|0|$26.54|6.79|
1998|AB|13|13|13|13|$902.01|11|0|0|0|$69.39|6.99|
1998|ABA|1|1|0|0|$0.00|0|0|0|0|$0.00|0|
1998|ABC|2976|2955|2129|2150|$161500.75|74|0|0|0|$75.12|8.20|
1998|ABCAPP|728|734|403|407|$27240.64|0|0|0|0|$66.93|11.87|
1998|ABCENTWELL|1|1|0|0|$0.00|0|0|0|0|$0.00|0|
1998|ABSHOP|1|1|0|0|$0.00|0|0|0|0|$0.00|0|
1998|ADIS|1|1|1|1|$136.39|49|0|0|0|$136.39|0.40|
1998|AFER|1|1|0|0|$0.00|0|0|0|0|$0.00|0|
1998|AFN|29|29|0|0|$0.00|0|0|0|0|$0.00|0|
1998|AGS|6|6|0|0|$0.00|0|0|0|0|$0.00|0|
1998|AHMA|1|1|0|0|$0.00|0|0|0|0|$0.00|0|
1998|AIDC|1|2|0|0|$0.00|0|0|0|0|$0.00|0|
1998|AIP|1|1|1|1|$189.24|60|0|0|0|$189.24|7.91|
  • FUNDSUMMARY 
      
    MONOGRAPH level1, level4 NOT DOUCETTE fund summary for pre-1999 cycles     
                        PRODUCED 1999-04-05 at 12:42:15                        
                             FOR 1996 FISCAL CYCLE                             
                                                                               
                                                                               
ACCOUNT          AMOUNT NO.OF #COPY #COPY #COPY      AMOUNT AVG PAID  AVERAGE
FUND ID        BUDGETED ORDER ORD'D REC'D PAID        PAID    PRICE  DISCOUNT
 
 
DEFERRED           0.00     0     0     0     0        0.00     0.00     0.00
MINOR TOTAL        0.00     0     0     0     0        0.00     0.00     0.00
 
51-4710-420
00427-51         427.69     7     7     7     7      427.69    61.10    13.78
90509-51         418.73     1     1     1     1      418.73   418.73     3.12
90510-51         616.18    18    11     8     8      475.10    59.39   -31.41
92100              0.00     0     0     0     0        0.00     0.00     0.00
92907-51          92.56     1     1     1     1       92.56    92.56     9.67
95202             50.00     1     1     1     1       62.50    62.50   -25.00
95702           2234.45    33    33    33    33     2234.45    67.71     9.64
MINOR TOTAL     3839.61    61    54    51    51     3711.03    72.77     2.99
        ........................................................ 
69-5087-420
00000-69         622.80     2     2     2     2      622.80   311.40     0.18
MINOR TOTAL      622.80     2     2     2     2      622.80   311.40     0.18

81001              0.00     0     0     0     0        0.00     0.00     0.00
82001             70.96     1     1     1     1       70.96    70.96    22.61
89001              0.00     0     0     0     0        0.00     0.00     0.00
90001             54.47     1     1     1     1       54.47    54.47     0.86
MINOR TOTAL   625943.43 12417 12169 12122 11668   618599.38    53.02     2.18
 
CYCLE TOTAL   653040.88 12785 12517 12467 12012   645536.00    53.74     2.45


      ------------------------SAMPLE EXCEL FILE OUTOUT-----------------------------

1997,57091,80-4709-420-57091,4082.09,55,49.000000,48,47,3503.96,0.00,3983.61,0,73.03,
1997,57101,80-4709-420-57101,1863.95,30,29.000000,29,29,1820.25,0.00,1917.03,0,0.00,
1997,57111,80-4709-420-57111,2993.82,58,54.000000,52,43,2650.85,0.00,2979.29,0,174.92,
1997,57121,80-4709-420-57121,2247.88,32,32.000000,32,32,2224.03,0.00,2451.82,0,0.00,
1997,57131,80-4709-420-57131,348.71,7,7.000000,7,7,348.71,0.00,414.60,0,0.00,
1997,57137,80-4709-420-57137,0.00,0,0.000000,0,0,0.00,0.00,0.00,0,0.00,
1997,57141,80-4709-420-57141,1863.61,22,22.000000,22,23,1865.53,0.00,2044.49,0,0.00,
1997,57147,80-4709-420-57147,147.01,4,4.000000,4,4,147.01,0.00,168.61,0,0.00,
1997,57151,80-4709-420-57151,10250.64,83,80.000000,79,79,9716.11,0.00,9921.88,0,30.00,
1997,61001,80-4709-420-61001,184.73,4,4.000000,4,4,184.73,0.00,187.62,0,0.00,
1997,61011,80-4709-420-61011,0.00,0,0.000000,0,0,0.00,0.00,0.00,0,0.00,
1997,61031,80-4709-420-61031,8865.00,153,152.000000,148,149,8667.81,0.00,9820.35,0,235.00,

    YEZEROFREEBAL

  • set the balance of the ALLOCATION to ENCUMBERED amount. 
                                                                                     
                      Zero Free Balance Report and Update                      
                        PRODUCED 2000-04-04 at 11:31:39                        
                             FOR 1999 FISCAL CYCLE                             
                                                                               
                                                                               
                                          OLD           FREE        NEW    
ACCOUNT NUMBER     FUND ID             ALLOCATION      BALANCE   ALLOCATION
80-4709-420-61143  61143                $20978.39        $0.00
80-4709-420-61151  61151                 $1200.00     $1200.00        $0.00
80-4709-420-61161  61161                $10000.00        $0.06     $9999.94
80-4709-420-61171  61171                  $500.00      $455.97       $44.03
80-4709-420-61271  61271                  $500.00      $390.20      $109.80

    Flag Flipping Reports:

    • FUNDENCUMFLAG-"Fundcyc block over encumbrance flag will be changed"

    • and FUNDINACTIVE-"Fundcyc ordering active flag will be changed"

    YEDATEREADYSER

  • -post rolloverorder to set date ready as desired

    ROLLOVRSERCTL

  • -active status controls to NEW fiscal cycle

    YEZEROCOPDIST

  • -allows us to keep our previous fiscal cycle serial order in the database for historical reference without the Public Access view of the record collecting additional number of copies on order with each year.

    Statistical Korn Scripts

  • Statistics: collection.ksh and insurespace.ksh are two sample scripts which allow us to review our entire non-shadowed database

    

                Final report for VM_ITEMS Collection Analysis
                ============================================
         ...produced on osiris.lib.ucalgary.ca on Fri Mar 31 08:59:22 MST 2000
         ...file extraction date and name: Mar 31 08:59 /s/sirsi/Unicorn/Work/NDL/Carl/Ndltemp/vmfin.ndl



        MACKIMMIE items:        276.
        DOUCETTE  items:        5174.
        GALLAGHER items:        0.
        LAW       items:        62.
        MANAGEMENT items:       1.
        MEDICAL    items:       81.
        CYBERSPACE items:       1.

9 EXTERNAL branch items are broken down as follows:
                LEARN-COMM      items:  9.

TOTAL VM_ITEMS items in the collection: 5604


    
--------------Sample Output from insurespace.ksh-----------------------------

                  MONOGRAPH ITEM COUNT
                  ====================

        ...produced on osiris.lib.ucalgary.ca Fri Mar 31 14:20:06 MST 2000
         ...file extraction date and name: Mar 31 14:20 /s/sirsi/Unicorn/Work/NDL/Carl/Ndltemp/monoitemsfin.ndl

Number of MONOGRAPH ITEMS in the catalogue:   1613356


NOTE: criteria avoids IN-PROCESS home locn and shadow locations of:
   CANCELLED,RESDISCARD,WITHDR98,SERORDER,WITHDR99,DISCARD,
   LOST,MISSING,WITHDRAWN,PERSCOPY,SELECTION,BOARDOFGOV,
   WITHDR00,SHADOWED
   .

If IN-PROCESS items SHOULD be included in the above number, refer to your
IN-PROCESS report to get the required number.
    --------------------------------------------------

    Back to: Main API presentation page

    Back to: TOP of page

    --------------------------------------------------


    Nella Lall
    University of Calgary Library, ITS