Nov 06 2015

Tracking down reason why no approval queue

Published by under Uncategorized

You need rule class to get the rule group on the record. FTVRGRC is xwalk table.

You need document type number on record which you can get from forappl.lis

Look in table FORAQRC which has doc type and rule group

No responses yet

Jan 22 2015

Payroll encumbrance feed

Published by under Uncategorized

Got a call from SOU wanting to know why an encumbrance jv was stuck at Incomplete and wouldn’t post.  The was no seqno marked as an error in FGAJVCD so I had them complete it and then ran approvals.  No issues there so I ran posting. The posting list file gave the following error:

Document V0007872 , item 0 , sequence 872 – WARNING : Budget is exceeded
Document V0007872 , item 0 , sequence 873 attempted to post to an Encumbrance with a non-matching accounting Distribution.
Document V0007872 , item 0 , sequence 873 attempted to post to an Encumbrance with a non-matching accounting Distribution.
Error occurred on document, posting rolled back and document reset.

 

We tried to just delete that seqno but then just got another error.  Investigating we noticed it was an adjustment rule code but there was no initial rule code entry for that fopal, encd_item, encd_num combo.  We looked in the payroll feed table nhrdist and found the missing initial entry tied to a jv that existed in payroll but not in finance.  No reason why as far as we could determine.  We ran a script to post the missing jv to finance thinking the other would feed after the missing jv posted.  Instead we ran into the same error.  Investigating I compared nhrdist and fpbjvcd for that encumbrance number and looked for any jvs on nhrdist missing from fgbjvcd.  I found 6.

 

At this point we decided to clear payroll encumbrances for the year and re-do them.  We had a script to clear them on the payroll side but still had to clear them out of finance.  It turns out that the process is well documented by the HRIS Fiscal Year-End Processing document that OUS sends to the Payroll people before year-end.  Section A liquidates the encumbrances and Section B verifies that they have been removed.  Short version:

Section A: Run PWPEREM (in Update mode) with PR15 as the Encumbrance prefix.  Then run FWRLQPE. Then FGRTRNI, Then FGRTRNR.  Fix errors.  Post the file.

Section B: Run FWRPDIF.  Results should be zero.  Directions say to run either FGIENCB, query on PR15, or run FWRINZE, results should be zero.

 

We re-ran the encumbrance process and ended up with the same posting error but this time we were down to 3 different record pairs causing an issue.  They had the same foap, encd_num, encd_item, and encd_seqno but different account codes with the record above them.  Posting coulldn’t handle this.  The encd_seqno should not be the same for both records. We tracked down the cursor in nhpfin1 which set the account code and I found that for the ecls and bdca codes for the bad record was indeed returning the correct account code.  I was able to get nhrdist_posn and then the ecls from nbbposn.  The encd_seqno for the bad record in the pair had to be set up wrong in payroll.  Indeed the earnings code for the individual and labor distribution were mismatched.  Earnings was set up for classified but labor distribution was set up for faculty.  Once they fixed the 3 individuals, everything fed.

No responses yet

Oct 16 2014

Finance 8.10 Upgrade Notes

Published by under Uncategorized

FGRTRNI modified with 2 new parameters.  Feed scripts have to be modified to reflect the new parameters.  There is seed data that can be added to ftvsdat to bypass whatever choice is selected for the two parameters but you still need to put something in response.

 

OUS mods:

fakp_invoice_1

 

fabchkp.pc

fapcard.pc

fwpdird.pc

farbrec.pc

fupload.pc

 

faachks.fmb

faainve.fmb

faainvt.fmb

faichkh.fmb

faivndh.fmb

foauapp.fmb

fpapurr.fmb

ftmvend.fmb

No responses yet

Jul 10 2014

FPPPOBCA Issue

Published by under Uncategorized

WOU’s process to close POs failed leaving them unable to post the affected POs

I had to :

delete fpreoca where fpreoca_code like ‘%0002xxx’;

delete fpbeocd where fpbeocd_code like ‘%0002xxx’;

delete fpreocd where fpreocd_code like ‘%0002xxx’;

Then they were able to process POs individually.  About 160 affected.

 

 

No responses yet

Jul 08 2014

Bypassing approvals for problem Z docs

Published by under Uncategorized

Insert record into fobappd

Delete record from fobuapp

update fabinvh to set approval ind to Y

run posting

No responses yet

Jun 12 2014

AP Check Issue

Published by under ACH

FABCHKA was giving the error:

ORA-01400: cannot insert NULL into (“FIMSMGR”.”FOBAPPD”.”FOBAPPD_DOC_NUM”)
WRN-ORACERR: Error occurred in file “fabchka.pc” at line 1,040
WRN-ERRSTMT: Following statement was last statement parsed:
insert into fobappd (fobappd_seq_code,fobappd_doc_num,fobappd_bank_num
fabchka terminated with error.

I wrote some print statements so I could figure out which field was null.  Turned out to be the rpt_doc_code but rpt_inv_code was fine.   Figured out the check num field in fatckin and fatckdt was null when there should of been values in there.  I thought this got populated during fwpdird so I checked the output file for fwpdird and found it empty.  Figured they used the wrong date in the parameters. Had them run it again and this time the file looked good.  After that they were able to run FABCHKA without any error.

No responses yet

Jun 12 2014

Payroll check

Published by under Uncategorized

Got a call that shared services could not print two checks running pwpchkp.

Check print ind on table pwrchck for the two doc codes which I got from table pwrcnpb.  Print ind was set to Y so they had to use reprint parameter in pwpchkp along with the doc code range.

No responses yet

May 01 2014

DBEU Phase II

Published by under Uncategorized

Here is what is on the DBEU FAQ for Phase II:

Phase Two will be executed as part of the installation of the Banner XE applications such as Catalog, Schedule,  Faculty Grade Entry etc. This phase of the Database Extension Utility will ”enable” 2 of the columns added in Phase 1 (SURROGATE_ID and VERSION) and will only do this for tables that are associated with the Banner XE application being installed. Enabling the columns will consist of populating each column with a default value and updating each column to be NOT NULL. Triggers to populate these columns will also be added at this phase.

 

I was able to create a junk table with two not null columns temp1 and temp2.

I then created the following trigger:

 

CREATE OR REPLACE TRIGGER ft_junk

BEFORE INSERT ON JUNK

FOR EACH ROW

BEGIN

IF :NEW.temp2 IS NULL THEN

:NEW.temp2 := ‘test2’;

END IF;

END;

 

Then the following worked:

B8TST> insert into junk (temp1) values (‘test’);

 

1 row created.

 

B8TST> select * from junk;

 

TEMP1      TEMP2

———- ———-

test       test2

12 responses so far

Mar 14 2014

IITFEED Fun

Published by under IIJV FEED

The last 2 weeks I’ve had two deal with two instances where the daily ii jv feed didn’t balance.  Usually I get a call from controllers asking me to investigate.  Like almost always… both instances were due to an error in the original spreadsheet that was loaded into Banner.   For these two cases the datestamp for the particular VAxxxxxx jv was not the same across all seqno records.  These records all made it into the gurfeed table.  The first time this happened, fwpfedh.sql did not put header records into gurfeed.  These are the record type 1 records.  There should be one of these for each jv.  The second time this happened, fwpfedh.sql put header records into gurfeed but the total only went in for one of the timestamps.  So when FURFEED runs on the institution side it can’t handle it.

The first case left all the records in gurfeed and didn’t process anything.  To fix I added a header record manually with a correct total and then ran furfeed, fgrtrni, fgrtrnr,fgbactg in the institution instance.  I had to get the big schools to do it since I don’t have access to their databases.  The second case hit every institution and fed only the part of the jv that matched the header timestamp.  Since this posted, t required a new correcting jv referencing the old one to post the missing rows.  We deleted the orphan records in gurfeed.

This was a lot of work for everybody so I made a mod to iitfeed.pc to throw out any jvs that did not have a matching timestamp in all the seqno rows for a particular jv.  The jv should never load into owag to be split up.

No responses yet

Mar 14 2014

II JV Feed Notes (IITFEED)

Published by under IIJV FEED

II JV Feed

 

Overview:

The inter institutional jv feed is the most complex one we run.  JVs get input into owag via 2 methods: a form FWAIITD which institutions bring up in owag and spreadsheets from all the institutions and telecom.  Telecom, UO, and CO use the spreadsheets and the others use the form.  The spreadsheets can get up to 3100 lines of data.  Text that can go with a JV comes from the form FOATEXT in owag. The JV doc codes are typically VA######.  In the chps flat directory you will see files with J00xxxxxx which are not part of the iitfeed process.  These are related to chpsfeed which uses the same directory.  90% of issues with the ii jv feed come from the spreadsheets.  Anyway, there are 3 owag oracle scheduler jobs that run around 5 am.  Two are related to some pre and post feed reports.  The other, IITPROC_DAILY_JOB, runs the feeds.  The data from the form and spreadsheets get loaded into some custom tables in owag (fwbiitd, fwbiith, fwbfedh).  Then a process runs which splits them into several files for each institution and places them in the institution’s appropriate flat directory.  From there we handle feeding these files that came from the owag split into the 5th site databases.  The big 3 handle feeding these files into their databases themselves.  UO feeds theirs at 8 am, OSU at 3 pm, and PSU at 6 pm.  What you typically have is JVs moving from the schools to controller’s and jv’s from controller’s running to the schools(including chps).  The telecom stuff runs out to all the schools once a month.  There is a cutoff date set by Nick Miller a few days before period end which will not allow the II JVs to process if they are in the period but after the cutoff date.  This is in ftvsdax.  At year end there is also a setting to only allow controllers to send ii jvs.

 

Scripts in owag subversion:

iitproc.shl   – main one

iitchps.shl

iiteou.shl

iitoit.shl

iitsou.shl

iitwou.shl

Generated files in institution flat directories:

Ie.  /san/flat/wou/wouprd/fin/iitfeed/

fwbfdhe.dat

fwbtxte.dat

fwpfdhe1.lis

fwpfdhe2.lis

fwpfdhe4.lis

Issues:

Controller’s calls and can’t remember if they already uploaded a spreadsheet:

–          Go to /san/flat/ecs/chps/fin/iitfeed and look for it there and the arch directory.

Controller’s calls and ask if a certain VA####### loaded:

–          See if they ever uploaded the file if it’s from a spreadsheet.  Do a grep for it  in /san/flat/ecs/chps/fin/iitfeed/ and in the arch directory

–          Check the history table in owag, fwbfedh, for it.  You can get the date it loaded and whether or not it was placed in the institution files by the fwbfedh_transmitted_ind flag.

Controller’s calls and ask you to help them figure out why a JV didn’t load:

–          First see if it loaded.  Keep in mind the same JV can go to multiple institutions. If it was in a spreadsheet, make sure the file was there

–          If the file was there but you have no record in fwbfedh, go to the owag spool directory and do a ll *iit* and look through the files to see if you can find anything.  Also check fwpiitd.lis which will tell you what loaded into fwbfedh that morning and fwpfedh.lis which will tell yo what document went where that morning

–          Go to TreasOps/IITFEED folder in restricted share and look for fwpfeeed_error.lis file.  It will show any issues with loading the file into owag.

–          If it loaded and you see the flag on fwbfedh_transmitted_ind set to Y, go to the flat directory of an institution (say chps) and look for the furfeed and fgrtrnr files.  Ask controller’s if the JV ended up in suspense.

–          The spreadsheet consist of a type 1 header record and then 1 to many type 2 detail records for each JV.  A lot of times the total of the detail records doesn’t equal the total on the header record.  They sometimes forget a header record.  The sqlldr is position driven so sometimes they mess up a column width.  They may have tried to feed it before and forgot they did.  Sometimes they put in a blank line at the end of the file.

Controller’s calls and says they want to re-feed a file:

–          If it posted, you can’t re-feed the records that posted.  If it’s in suspense, controller’s has to remove it from all the banner instances that it went to before you re-feed.

–          Check the tables fwbiitd, fwbiith, and fwbfedh and remove records for the JV.

–          You want the morning job to take care of it and not run manually unless you have to.

Controller’s or UO calls you and says the feed bombed:

–          Review the iitproc log in the owag spool directory and figure out where it bombed.

–          Ask controller’s to send out an email to the campus business officers  informing them that the ii jv load failed that morning.  OSU does their institution load at 3 pm but the other two bigs do it in the morning so chances are they tried to load files from the previous day and had their process abort.

–          Chances are a spreadsheet caused a sqlld to abort

–          Remove file and run job manually on owag oracle scheduler unless controller’s wants to try and fix the file first

–          Ask controller’s to inform campuses when you’ve run it.

 

No responses yet

Next »