"Invoice Number did not feed to TEM from BANNER ERP - replicating transaction" (I believe this is the Banner error message)

Tammy's notes:


Issue: PCard transactions replicating in Banner

Description: Multiple invoices (S documents) being created for a single Pcard transaction. For unknown reasons, PCard credit was being fed from TEM into Banner multiple times creating additional Invoices (S****** docs).

Problem: On Tables FABINVT and FARRPCT feed indicator, status and doc codes are not being updated by the jobs run.  

Corrective Action Taken:

1. Annotated the TXN number (aka Reference Number) from TEM. This number used to search tables.  (TXN00012202)

2. Noted the Invoice Number (S doc code) from Banner. We used the first one that posted successfully (S0003029)

3. Searched table FABINVT for  TXN#

a. Changed FABINVT_FEED_DOC_CODE to S0003029

b. Changed FABINVT_FEED_IND to “F”

c. Changed FABINVT_STATUS_CDE to “V”

d. Noted the FABINVT_SURRIGATE_ID

4. Searched Table FARRPCT for FARRPCT_FABINVT_ ID identified on FABINVT (surrogate ID above)

a. Changed FARRPCT _FEED_DOC_CODE to S0003029

b. Changed FARRPCT _STATUS_CDE to “V”

c. Changed FARRPCT_FEED_IND to “F”

5. Verified that there was now the correct S# on the TXN# in Credit Card Manager in TEM

6. Reran the FAPINVT job again and confirmed that it did not replicate again.

7. In this instance there were 3 replicated S****** that were incomplete in Banner that were deleted and a FY16 journal entry will be written to correct the three extra postings (S docs) that were posted in FY 15 (S0003258, S0003533, S0003754) as prior year correction.

Issue: Transactions duplicated in TEM, but not in BANNER

Problem: Charge assigned to traveler, then returned to cardholder, then assigned to another traveler. Resulting in a duplication of the same charge. Not a reissuance of the same charge.

Corrective Action:

1. Confirm that has not posted twice to Banner

2. Delete extra lines from FARRPCT. 



Code:

--replicating PCARD fix

--PCARD credits; info not feeding back to TEM...

SELECT * FROM fabinvt

WHERE fabinvt_feed_doc_code IS NULL;

--find surrogate ID for step 2 update

SELECT * FROM farrpct

where farrpct_fabinvt_id = '10777'; --surrogate_id from FABINVT

SELECT * FROM fabinvt

WHERE FABINVT_TRANS_REF_NUM = 'TXN00020306';

--0. back up FABINVT

CREATE TABLE FABINVT_20170907_BAK AS SELECT * FROM FABINVT;

COMMIT;

--1.

UPDATE fabinvt

SET fabinvt_feed_ind = 'F',

fabinvt_feed_doc_code = 'S0023362',

FABINVT_STATUS_CDE = 'V'

WHERE FABINVT_TRANS_REF_NUM = 'TXN00020306';

commit;

--2.

UPDATE farrpct

SET farrpct_feed_doc_code = 'S0023362',

farrpct_feed_ind = 'F'

WHERE farrpct_fabinvt_id = '10777';

commit;

--3. remove extra record (in PENDING, not necessarily in table); record remove from Banner

SELECT * FROM fabinvt

--where fabinvt_feed_doc_code = 'S0023432'

where FABINVT_TRANS_REF_NUM = 'TXN00020306';