Can eProcurement reach Banner?

13 Aug 2022 🔖 databases integration minimum viable build
💬 EN

Accountants never work directly with servers running Ellucian’s Integration for eProcurement (IFEP) “middleware” software (syncing Ellucian Banner with Jaggaer a.k.a. SciQuest), but engineers do.

Here are tricks engineers can try to ensure that communications are running smoothly from the eProcurement middleware to the Banner ERP database.

Engineers, I know that you built the Banner database long before you built the eProcurement server, because the eProcurement server borrows a little bit of storage space in the Banner database as a place to keep its configuration settings.

So presuming that Banner is a healthy, running system (if it weren’t you’d know about it from a lot more than just your accountants) and simply testing whether eProcurement can reach it properly is the first place I imagine you’ll start troubleshooting from.

Let’s get into how.

Network test

The moment you have a raw Linux server up and running for eProcurement, SSH into its command line.

Unit test

Let’s verify that the eProcurement server can initiate TCP network connections to the Banner database server over an appropriate port number.

Execute the following Linux command, substituting an appropriate domain name where I’ve put your_banner_database_domain_name and the port you actually have the Banner database listening for incoming ODBC/JDBC connections on where I’ve put 1521 (although 1521 is the default in Oracle, so yours might be 1521):

nc -vz your_banner_database_domain_name 1521

If the connection passes, you should see a message similar to the following:

Connected to your_banner_database_domain_name:1521.

If the connection fails, you should see a message similar to the following:

Connection timed out.

If you made a domain name typo, you might see this:

Could not resolve hostname "your_banner_database_domain_name": Name or service not known. QUITTING.

Resolution

Work with network engineers, datacenter / cloud engineers, other sysadmins, etc. to inspect firewall rules. Also inspect firewall-like rules inside the eProcurement and Banner servers’ operating systems. Also inspect firewall-like rules attached to the context of the two servers’ cloud infrastructure provisionment.

Also double-check with the Banner DBA that you didn’t misunderstand which port number to try.

While troubleshooting, you might find it handy to test whether any computer anywhere on the internet can reach the Banner database. If you need to try from some sort of Windows machine, the equivalent command is structured this way:

Test-NetConnection -ComputerName "your_banner_database_domain_name" -Port "1521"

With Windows Test-NetConnection, you’d be lookng for the TcpTestSucceeded result to be True instead of False.

Do not proceed with the rest of this article until this unit test passes.

Oracle username and password test

Let’s make sure you have proper credentials into the Banner database – after all, eProcurement will need them.

Unit test

You probably didn’t install Oracle’s sqlplus command-line tool on your eProcurement machine (it’s not necessary for day-to-day operations), but if you or another engineer happens to have access to a computer where sqlplus is installed and that’s allowed to connect to the Banner database (perhaps even the command line of the Banner database server itself), here’s a great way to validate that the Bannerdatabase username and password you plan to type into eProcurement’s configuration actually works.

You don’t have to use Oracle’s sqlplus command-line tool to execute this test, by the way – you could also put the following 5 pieces of information into a point-and-click database development productivity tool like Oracle SQL Developer:

1. Banner database server domain name
2. ODBC/JDBC port number for querying the Banner database
3. Actual name of the Banner database (and whether it goes by “service name” or “SID”)
4. Username by which eProcurement will log into the Banner database
5. Password by which eProcurement will log into the Banner database

Just make sure that you’ve already done nc (Linux) or Test-NetConnection (Windows) from the command line of whatever computer you’re testing Oracle with, lest you mistake a networking error for an Oracle error.

Using sqlplus, the way to check the database, substituting an appropriate domain name where I’ve put your_banner_database_domain_name and the port you actually have the Banner database listening for incoming ODBC/JDBC connections on where I’ve put 1521 (although 1521 is the default in Oracle, so yours might be 1521), an appropriate database name where I’ve put your_banner_database_internal_name (although check with your DBA – you might need to change SERVICE_NAME to SID depending on how Oracle is configured), and an appropriate database user name where I’ve put eprocs_username_within_banner, would be:

sqlplus 'eprocs_username_within_banner@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=your_banner_database_domain_name)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=your_banner_database_internal_name)))'

When prompted to enter a password, do so.

If your password is correct, you should get a SQL> prompt.

To validate that you’re really logged in well, run this SQL:

SELECT 0 FROM dual;

The data output should be a 1-row, 1-column table where the header and value are 0:

You won’t get far if your database user isn’t allowed to do anything in the database, so you might also want to try another query that will help you make sure that the username you’ve logged in with can do things an eProcurement server ought to do.

Try this query:

SELECT table_name, tablespace_name, status, num_rows FROM user_tables ORDER BY table_name;

The data output should be a 4-column table with about 2 dozen rows of data in it.

If the dataset has no rows, maybe there are actually 2 users that have to do with connecting

Ask the Banner DBA if there’s a different user being used just for owning table schemas pertaining to Ellucian’s Integration for eProcurement (IFEP)** software. Once you have that username (in my code below, it’d be the_other_database_username), try this:

select table_name, tablespace_name, status, num_rows from all_tables where owner = 'the_other_database_username' order by table_name;

There’s a good chance that now the data output will be a 4-column table with about 2 dozen rows of data in it, depending on exactly what permissions the Banner DBA gave to your_banner_database_internal_name.

Anyway, when you’re all done playing around, at the SQL> prompt, here’s how you log out of the database in Oracle’s sqlplus prompt and return to the Linux / Windows command-line prompt:

exit();

Look for green lights

Log into the web-based administration portal that eProcurement servers run as a convenience for their sysadmins.

Unit test

Under Registered Applications and Services (the Home tab):

1. Is every service you thought you installed present?
2. Is the circle under each service’s “Registered” green rather than red?
   • Tip: If you’re colorblind, right-click each circle, click “Open image in new tab,” and validate that the filename of the icon is greenlight.gif.

Resolution

The most common way to get the icons green is to restart various services running on the eProcurement server.

Don’t move on until all of the status indicator icons (you might think of them as “lights”) are green.

Proofread connection information

Remain logged into the web-based administration portal that eProcurement servers run as a convenience for their sysadmins, click the Connections tab toward the top of the screen.

Unit test

Under Gateway Connection / Configuration, under Banner, click “Connection”.

1. JDBC URL (Banner) – after the “@”, does it look like a reliable way to connect to your Banner database? e.g. something like “...@your_banner_database_domain_name:1521your_banner_database_internal_name”?
2. Username (Banner) – is it the database username you tested back in “Oracle username and password test”? If not, maybe you want to go back to that section, repeat the directions, and update your notes for next time. (Presumably, when you set up the eProcurement server, you knew what to type here.)
3. Password (Banner) – don’t worry about this, just know it’s here and that having a proper value here is how eProcurement knows how to log into the Banner database. Presumably, when you set up the eProcurement server, you knew what to type here.

Be careful if you have a password management tool like LastPass set up and doing autofill – if your eProcurement web portal is hosted at your university’s .edu domain, you might see your own e-mail username autofilled here, rather than the actual value.

Turn off your password manager before entering this page, because not only will they fake you out about what data you’re reading, but they’re a great way to accidentally overwrite the correct username and password if you click Save while trying to tidy up a field like JDBC URL.

Resolution

Note that if you’re working in a test environment and you recently copied data from your production Banner database into your test Banner database, the value of JDBC URL in your test eProcurement system could be showing Banner-production’s connection information instead of Banner-test’s connection information, and you’ll need to fix it (hopefully you took good notes when you set up eProcurement-test; if not, contact a Banner DBA for the correct Banner-test connection information).

Log out

Click the Logout button in the upper-right corner of the screen of the web-based administration portal that eProcurement servers run as a convenience for their sysadmins

Force eProcurement to request data from Banner

The Banner database’s baninst1.fokutil.p_is_foap_postable() procedure is a routine that validates whether a budget-code is valid and has sufficient available balance for a given transaction to be posted to Banner’s transaction ledgers.

Test Banner in isolation

Before we force eProcurement to ask Banner for a budget authorization check, we should make sure that p_is_foap_postable() is up and running correctly. It should be, but who knows! Always best to be thorough.

Unit test

Have a Banner DBA log into the Banner database as some sort of database user authorized to execute p_is_foap_postable() and run the following PL/SQL script – only be sure to read this script’s comments and change values accordingly.

DECLARE
    foap_was_valid VARCHAR2(32767);
    message_out_was VARCHAR2(32767);
BEGIN
    baninst1.fokutil.p_is_foap_postable(
        doc_type => '1',
        doc_code => null,
        item_num => 1,
        seq_num => 1,
        coas => 'T', -- THIS IS PROBABLY "T" FOR "Total," BUT DOUBLE-CHECK WITH YOUR SCHOOL'S FINANCE SPECIALISTS
        acci => '54321', -- SET THIS TO SOMETHING THAT THERE SHOULD ACTUALLY BE BUDGET FOR
        fund => null, -- SET THIS IF CRUCIAL TO BUDGET-CHECKS AT YOUR SCHOOL
        orgn => null, -- SET THIS IF CRUCIAL TO BUDGET-CHECKS AT YOUR SCHOOL
        acct => '98765', -- SET THIS TO SOMETHING THAT THERE SHOULD ACTUALLY BE BUDGET FOR
        prog => null, -- SET THIS IF CRUCIAL TO BUDGET-CHECKS AT YOUR SCHOOL
        actv => null, -- SET THIS IF CRUCIAL TO BUDGET-CHECKS AT YOUR SCHOOL
        locn => null, -- SET THIS IF CRUCIAL TO BUDGET-CHECKS AT YOUR SCHOOL
        transdate => to_date('2020-01-01', 'YYYY-MM-DD'), -- SET THIS TO SOME DATE WITHIN THE FISCAL YEAR YOU'D LIKE TO TEST, I THINK
        trans_amt => 12345, -- SET THIS TO A NUMBER REPRESENTING DOLLARS THAT THERE SHOULD/SHOULDN'T BE AMPLE BUDGET FOR
        userid => 'nonsensical_banner_database_username', -- PLAY WITH SETTING THIS TO A FAKE USERNAME, THEN TO A REAL USER WHO DOESN'T WORK IN FINANCE, AND FINALLY TO A REAL USER WHO DOES WORK IN FINANCE
        is_foap_valid => foap_was_valid, 
        message_out => message_out_was
    );
    DBMS_OUTPUT.PUT_LINE(foap_was_valid);
    DBMS_OUTPUT.PUT_LINE(message_out_was); 
END;
/

What you’re looking for:

1. Get to know how the various inputs translate to a first output-parameter of “Y” or “N”
2. Get to know how the various inputs translate to various error messages (one possible value of which, often with “Y” for the first, is null).

Resolution

Keep playing with this until you really know how to use it properly.

Document what you learn so you won’t forget.

Grab eProcurement’s HTTP listener URL

Log into the web-based administration portal that eProcurement servers run as a convenience for their sysadmins. (Oops, sorry, I shouldn’t have had you log out just yet.)

Click the Connections tab.

Under eProc Connection, copy the URL seen in HTTP Request from Vendor and paste it into Notepad.

Later in these instructions, I’ll refer to it as the_eproc_http_input_url.

Now you can log back out of the eProcurement administration portal.

Grab the inbound password to eProcurement

You can ask the eProcurement sysadmin to tell you the username and password for eProcurement’s HTTP portal (I haven’t found a place to find it in the web-based administration portal, though).

Alternatively, a Jaggaer admin from your accountancy team can show you around the Jaggaer administration website to figure out what the username and password is.

An accountant with administrative access to Jaggaer can look in Jaggaer under Setup -> General Site Settings -> Document Export Settings. Next to the dashboard tab in the connection mgt tab, they can click through the various links listed under a box labeled Connections until they find one where, at right under Selected Connection, has the “Supports Banner” checkbox checked and the URL matches the_eproc_http_input_url.

Have them securely send you the values from User Name and Password on this screen (e.g. through a password management tool like Lastpass).

Query Banner via eProcurement

Once you’ve confirmed that the procedure works nicely inside of Banner and you’re familiar with its output, head over to your favorite HTTP-request-testing software, like Postman or cURL, to see if your eProcurement server can be forced to call Banner’s baninst1.fokutil.p_is_foap_postable() procedure, and if eProcurement can a proper value, through an HTTP request and response.

Essentially, you’re going to pretend to be Jaggaer, and it’s going to be safe to do so because the impact of a budget authorization check upon Banner is read-only (no data gets written to the database).

Unit test

Make a “GET”-typed HTTP request to the_eproc_http_input_url and use “basic authentication” with the username and password you obtained from your eProcurement sysadmin or your Jaggaer admin.

Important: Be sure not to save the username and password anywhere that it could later be scraped by someone malicious who got access to your computer with Postman installed or to your cloud Postman account.

The HTTP request should have a Content-Type header of application/xml, and a request body of something like:

<?xml version="1.0" encoding="ISO-8859-1"?>
<com.sct.Accounting.BudgetAuthorization.Query>
    <DataArea>
        <PurchaseTransaction type="PurchaseRequisition">
            <UserId>nonsensical_banner_database_username</UserId> <!-- PLAY WITH SETTING THIS TO A FAKE USERNAME, A REAL USER WHO DOESN'T WORK IN FINANCE, AND ONE WHO DOES -->
            <TransactionDate>
                <Year>2020</Year> <!-- SET THIS TO SOME DATE WITHIN THE FISCAL YEAR YOU'D LIKE TO TEST, I THINK -->
                <Month>1</Month> <!-- SET THIS TO SOME DATE WITHIN THE FISCAL YEAR YOU'D LIKE TO TEST, I THINK -->
                <Day>1</Day> <!-- SET THIS TO SOME DATE WITHIN THE FISCAL YEAR YOU'D LIKE TO TEST, I THINK -->
            </TransactionDate>
            <!-- THIS IS ITEM NUMBER 1 OF 2 ($12,344 of $12,345) -->
            <AccountingDistribution>
                <ItemNumber>1</ItemNumber>
                <SequenceNumber>1</SequenceNumber>
                <TransactionAmount>12344.00</TransactionAmount> <!-- SET THIS TO A NUMBER THAT, WHEN ADDED WITH OTHERS, THERE SHOULD/SHOULDN'T BE AMPLE BUDGET FOR -->
                <AdditionalAmount>0.00</AdditionalAmount>
                <!-- YOU WILL NEED ADDITONAL ACCOUNTINGSEGMENT BLOCKS IF BUDGET-CHECKS AT YOUR SCHOOL INVOLVE FUND, ORGN, PROG, ACTV, or LOCN -->
                <AccountingSegment>
                    <AccountingElementType>
                        <ElementType>Chart</ElementType>
                        <Order>1</Order>
                    </AccountingElementType>
                    <ElementCode>T</ElementCode> <!-- THIS IS PROBABLY "T" FOR "Total," BUT DOUBLE-CHECK WITH YOUR SCHOOL'S FINANCE SPECIALISTS -->
                </AccountingSegment>
                <AccountingSegment>
                    <AccountingElementType>
                        <ElementType>Index</ElementType>
                        <Order>2</Order>
                    </AccountingElementType>
                    <ElementCode>54321</ElementCode> <!-- SET THIS TO SOMETHING THAT THERE SHOULD ACTUALLY BE BUDGET FOR -->
                </AccountingSegment>
                <AccountingSegment>
                    <AccountingElementType>
                        <ElementType>Account</ElementType>
                        <Order>5</Order>
                    </AccountingElementType>
                    <ElementCode>98765</ElementCode> <!-- SET THIS TO SOMETHING THAT THERE SHOULD ACTUALLY BE BUDGET FOR -->
                </AccountingSegment>
            </AccountingDistribution>
            <!-- THIS IS ITEM NUMBER 2 OF 2 ($1 of $12,345) -->
            <AccountingDistribution>
                <ItemNumber>2</ItemNumber>
                <SequenceNumber>1</SequenceNumber>
                <TransactionAmount>1.00</TransactionAmount> <!-- SET THIS TO A NUMBER THAT, WHEN ADDED WITH OTHERS, THERE SHOULD/SHOULDN'T BE AMPLE BUDGET FOR -->
                <AdditionalAmount>0.00</AdditionalAmount>
                <!-- YOU WILL NEED ADDITONAL ACCOUNTINGSEGMENT BLOCKS IF BUDGET-CHECKS AT YOUR SCHOOL INVOLVE FUND, ORGN, PROG, ACTV, or LOCN -->
                <AccountingSegment>
                    <AccountingElementType>
                        <ElementType>Chart</ElementType>
                        <Order>1</Order>
                    </AccountingElementType>
                    <ElementCode>T</ElementCode> <!-- THIS IS PROBABLY "T" FOR "Total," BUT DOUBLE-CHECK WITH YOUR SCHOOL'S FINANCE SPECIALISTS -->
                </AccountingSegment>
                <AccountingSegment>
                    <AccountingElementType>
                        <ElementType>Index</ElementType>
                        <Order>2</Order>
                    </AccountingElementType>
                    <ElementCode>54321</ElementCode> <!-- SET THIS TO SOMETHING THAT THERE SHOULD ACTUALLY BE BUDGET FOR -->
                </AccountingSegment>
                <AccountingSegment>
                    <AccountingElementType>
                        <ElementType>Account</ElementType>
                        <Order>5</Order>
                    </AccountingElementType>
                    <ElementCode>98765</ElementCode> <!-- SET THIS TO SOMETHING THAT THERE SHOULD ACTUALLY BE BUDGET FOR -->
                </AccountingSegment>
            </AccountingDistribution>
        </PurchaseTransaction>
    </DataArea>
</com.sct.Accounting.BudgetAuthorization.Query>

Tip: To figure out exactly what a typical budget-authorization request body looks like at your university when it’s sent to eProcurement from the real Jaggaer, have a Jaggaer user use Jaggaer to buy something. The accountants will know what to do – maybe they’ll use some fake budget codes in a test environment that no one actually issues bank payments out of. Or maybe they’ll just order $1 worth of paperclips they needed for their office anyway. Once they’ve placed the order in Jaggaer, presuming that eProcurement application logging settings aren’t set too narrowly (they can be controlled in the application panel – ask Ellucian’s Action Line support desk for help), the eProcurement sysadmin should be able to look through its underlying Tomcat server’s catalina.out file and get you a copy of the corresponding <com.sct.Accounting.BudgetAuthorization.Query> XML object that came through to the server over HTTP when Jaggaer talked to eProcurement.

If your eProcurement sysadmin set up catalina.out to be copied to a log-management tool, you might even be able to inspect eProcurement’s logs through that tool without having to bother the sysadmin.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE com.sct.Accounting.BudgetAuthorization.Provide SYSTEM "http://YOUR_EPROCUREMENT_SERVER_URL:A_PORT_NUMBER/ldimessaging//messagesets/com/sct/Accounting/BudgetAuthorization/1.0/dtd/Provide-Reply.dtd">
<com.sct.Accounting.BudgetAuthorization.Provide>
    <ControlAreaReply messageCategory="com.sct.Accounting" messageObject="BudgetAuthorization" messageAction="Provide" messageRelease="1.0" messagePriority="0" messageType="Reply">
        <Sender>
            <MessageId>
                <SenderAppId>Banner</SenderAppId>
                <ProducerId>(A BIG LONG HYPHENATED HEXIDECIMAL NUMBER)</ProducerId>
                <MessageSeq>9</MessageSeq>
            </MessageId>
            <Authentication>
                <AuthUserId>Banner</AuthUserId>
                <AuthUserSignature>Banner</AuthUserSignature>
            </Authentication>
        </Sender>
        <Datetime>
            <Year>2020</Year>
            <Month>1</Month>
            <Day>1</Day>
            <Hour>1</Hour>
            <Minute>22</Minute>
            <Second>33</Second>
            <SubSecond>110</SubSecond>
            <Timezone>Mountain Standard Time</Timezone>
        </Datetime>
        <Result action="Query" status="success">
            <ProcessedMessageId>
                <SenderAppId>Point2Point Producer v1.0</SenderAppId>
                <ProducerId>(A BIG LONG HYPHENATED HEXIDECIMAL NUMBER)</ProducerId>
                <MessageSeq>9</MessageSeq>
            </ProcessedMessageId>
        </Result>
    </ControlAreaReply>
    <DataArea>
        <BudgetAuthorization status="Unauthorized">
            <AccountingDistributionMessage>
                <ItemNumber>1</ItemNumber>
                <SequenceNumber>1</SequenceNumber>
                <BudgetAvailable>No</BudgetAvailable><!-- VALIDATE THAT THIS "Yes" / "No" MATCHES THE Y/N FROM PL/SQL -->
                <MessageText>THIS SHOULD MATCH THE SECOND OUTPUT FROM PL/SQL</MessageText> <!-- VALIDATE THAT THIS MATCHES WHAT THE OUTPUT MESSAGE WOULD BE IN PL/SQL -->
            </AccountingDistributionMessage>
        </BudgetAuthorization>
    </DataArea>
</com.sct.Accounting.BudgetAuthorization.Provide>

Congratulations! You now know that, while maybe not everything in eProcurement is able to talk to Banner correctly (eProcurement has a lot of sub-services that manage different business processes pertaining to “buying things”), at least one thing in eProcurement is talking to Banner correctly in a meaningful way.

Resolution

If things don’t go right, make sure that you’ve done a good job building an HTTP request that corresponds correctly to proper usage of Banner’s p_is_foap_postable() procedure, because that’s what eProcurement is building a call to each time it receives one of your HTTP requests.

If you’re sure you’ve coded it right (e.g. because you’re copying & pasting from documentation that included a well-formatted HTTP request you built in the past), then go back into the Banner database and make sure that there isn’t something wrong with Banner or its p_is_foap_postable() procedure.

If nothing’s wrong with Banner, then you probably have something wrong with eProcurement.

Look again at the status indicator icons (are they green?), look through catalina.out for errors in the vicinity of logs that show your incoming HTTP request, etc.

Have the eProcurement sysadmin restart the various services and then try again – eProcurement is notoriously arbitrary and needs to be stopped and started again mysteriously. (Just be careful if it’s a production system and you’re in the middle of the workday and people are actively using Jaggaer and Banner!)

If all of that fails, schedule a conference call with Ellucian Action Line Support, Jaggaer Support, your eProcurement sysadmin, you (if you’re not the eProcurement sysadmin), someone with appropriate Banner permissions (if not you), and, because it’s hard to get Ellucian and Jaggaer on the phone at the same time, an accountant who can do real/realistic end-to-end testing between Jaggaer and Banner (you don’t want to waste the call only accomplishing IT-side unit testing but have no idea if actual business operations work).

Posts In This Series