atdservicebot commented 1 year ago

What application are you using?

Other / Not sure

Describe the problem.

I think that CityBase is not updating properly perhaps? I emailed VisitAustin which is our highest volume customer about several unpaid invoices and got the response that they were taken care of recently. I’ve attached the emails for both the outstanding invoices and linking them along with her response containing some numbers that I assume are confirmation numbers. Is this a different bug for CityBase or am I viewing the transactions tab incorrectly?

How soon do you need this?

Soon — Within a week

Is there anything else we should know?

So that I can follow up with customers regarding payment.

Requested By Nicholas M.

Request ID: DTS23-108030

patrickm02L commented 1 year ago

@dianamartin Nick sent me an email last week with links to the vendors who were having trouble with payments. I'm forwarding it to you for viewing.

dianamartin commented 1 year ago

@chiaberry FYI, I'm gonna look at it now

chiaberry commented 1 year ago

@dianamartin keep me posted. I remember we had the issue where invoices werent being marked as paid, but i thought I had fixed it.

dianamartin commented 1 year ago

@patrickm02L @chiaberry

This was a table of records that had no "Transaction Status" - there were 9 records

3 records actually have not been paid
6 records are listed as not paid, but in the Rev Mgmt system they are actually "Paid"
the last record wasn't part of the filtered query, but mentioned by a Convention Center staff person that said they paid recently, but when I looked it up it showed a January paid date.

INVOICE ID	CB TXN ID	Knack TXN DATE	RevMgmt	Rev Mgmt Status	Notes
INV2023-100485 - Number of Banners: 14 - Locations: Convention Center	114296592	IS BLANK	05/01/23 - 07:28:05 am	Transaction Successful
INV2023-100490 - Number of Banners: 15 - Locations: Convention Center	115095107	IS BLANK	05/24/23 - 12:33:34 pm	Transaction Successful
INV2023-100500 - Number of Banners: 22 - Locations: "SoCo" (South Congress Avenue)		IS BLANK		UNPAID
INV2023-100665 - Number of Banners: 15 - Locations: Convention Center	114887629	IS BLANK	05/17/23 - 02:56:13 pm	Transaction Successful
INV2023-100670 - Number of Banners: 15 - Locations: Convention Center	115074587	IS BLANK	05/23/23 - 05:25:47 pm	Transaction Successful
INV2023-100675 - Number of Banners: 15 - Locations: Congress Avenue	114920184	IS BLANK	05/18/23 - 01:57:54 pm	Transaction Successful
INV2023-100680 - Number of Banners: 1 - Locations: # 8: 4199 1/2 N LAMAR BLVD	115012573	IS BLANK	05/22/23 - 09:05:30 am	Transaction Successful
INV2023-100685 - Number of Banners: 12 - Locations: E Sixth Street		IS BLANK		UNPAID
INV2023-100740 - Number of Banners: 2 - Locations: # 5: 201 1/2 E MLK BLVD, # 12: 1101 1/2 W 24th ST		IS BLANK		UNPAID

INV2023-100235 - Number of Banners: 15 - Locations: Convention Center	114887982	1/26/2023	05/17/23 - 03:02:47 pm	Transaction Successful	<-- this one is weird bc it was created in 1/26/23 but paid in May and the TXN Date is 1/26/2023

chiaberry commented 1 year ago

Yesterday it was discovered the flask app wasn't running, I restarted it and will set it so it will aways restart if it ever goes down for any reason. I am still debugging to see if there were other reasons why the invoices werent marked as PAID

patrickm02L commented 1 year ago

@dianamartin to go through CityBase and update the transactions that were PAID. We'll meet with Nick 6/8/23 to go over bug fix and payments update, as well as instructions to send to customers about paid transactions.

dianamartin commented 1 year ago

6/7

Invoice Fields

Updating only "Transaction Status" and "Transaction Paid" fields on transaction_invoice object

Steps

[x] Updated 7 invoice transactions to "Transaction Status" to "PAID" and set "Transaction Paid" Date to actual RevMgmt paid
[x] Created 7 citybase_messages records to have related CityBase ID
[x] Added a field Admin Note to all 7 citybase message records
- 6/7/23, Manually added by Diana, so that the CB TXN ID can be related

Reference Table

INVOICE ID	CB TXN ID	TXN STATUS	Knack TXN DATE	RevMgmt	Rev Mgmt Status	Notes	Action
INV2023-100485 - Number of Banners: 14 - Locations: Convention Center	114296592	Set to `PAID`	IS BLANK - Set to `5/1/23`	05/01/23 - 07:28:05 am	Transaction Successful	Added TXN Status `PAID` and TXN Date: `5/1/23`	COMPLETED
INV2023-100490 - Number of Banners: 15 - Locations: Convention Center	115095107	Set to `PAID`	IS BLANK - - Set to `5/24/23`	05/24/23 - 12:33:34 pm	Transaction Successful	Added TXN Status `PAID` and TXN Date: `5/24/23`	COMPLETED
INV2023-100500 - Number of Banners: 22 - Locations: "SoCo" (South Congress Avenue)			IS BLANK		UNPAID
INV2023-100665 - Number of Banners: 15 - Locations: Convention Center	114887629	Set to `PAID`	IS BLANK - Set to `5/17/23`	05/17/23 - 02:56:13 pm	Transaction Successful	Added TXN Status `PAID` and TXN Date: `5/17/23`	COMPLETED
INV2023-100670 - Number of Banners: 15 - Locations: Convention Center	115074587	Set to `PAID`	IS BLANK - Set to `5/23/23`	05/23/23 - 05:25:47 pm	Transaction Successful	Added TXN Status `PAID` and TXN Date: `5/23/23`	COMPLETED
INV2023-100675 - Number of Banners: 15 - Locations: Congress Avenue	114920184	Set to `PAID`	IS BLANK - Set to `5/18`23`\| 05/18/23 - 01:57:54 pm \| Transaction Successful \| Added TXN Status`PAID`and TXN Date:`5/18/23`	COMPLETED
INV2023-100680 - Number of Banners: 1 - Locations: # 8: 4199 1/2 N LAMAR BLVD	115012573	Set to `PAID`	IS BLANK - Set to `5/22/23`	05/22/23 - 09:05:30 am	Transaction Successful	Added TXN Status `PAID` and TXN Date: `5/22/23`	COMPLETED
INV2023-100685 - Number of Banners: 12 - Locations: E Sixth Street			IS BLANK		UNPAID
INV2023-100740 - Number of Banners: 2 - Locations: # 5: 201 1/2 E MLK BLVD, # 12: 1101 1/2 W 24th ST			IS BLANK		UNPAID

INV2023-100235 - Number of Banners: 15 - Locations: Convention Center	114887982	Set to `PAID`	1/26/2023 - Set to `5/17/23`	05/17/23 - 03:02:47 pm	Transaction Successful	<-- this one is weird bc it was created in 1/26/23 but paid in May and the TXN Date is 1/26/2023 ------ Added TXN Status `PAID` and TXN Date: `5/17/23`	COMPLETED

Unpaid Invoices

INV2023-100500 - $2200 (EDD)
INV2023-100685 - $2400 (UT)
INV2023-100740 - $900 (ARR)

dianamartin commented 1 year ago

6/8

During our meeting with SMO "Banner Payments", we noticed that some older invoices had "Transaction Dates", but no "Transaction Statuses"
Filter of those 13 records
- "Transaction Status" is blank
- "Transaction Paid" is not blank

Double checking that these transactions were actually paid

Order	LPB	OTS	Total Amount	Event Name	Invoice ID	Transaction Paid
1		993	$1,800.00	MotoGP at Circuit of The Americas	INV2023-100450	3/24/2023
2	516		$1,800.00	2023 City of Austin Career Expo	INV2023-100420	3/24/2023
3	523		$25,920.00	MotoGP	INV2023-100415	3/24/2023
4			$600.00	Summer Camp Hiring	INV2023-100395	3/23/2023
5		979	$600.00	Summer Camp Hiring	INV2023-100390	3/22/2023
6		970	$1,800.00	HAAM Day 2023	INV2023-100385	3/21/2023
7	508		$3,200.00	Air Quality Awareness Week 2023	INV2023-100360	3/8/2023
8	510		$7,200.00	Leave No Trace	INV2023-100300	3/6/2023
9		963	$600.00	Summer Camp Staff Hiring	INV2023-100295	3/2/2023
10		959	$1,200.00	Lifeguard Hiring	INV2023-100285	3/2/2023
11		954	$1,200.00	Lifeguard Hiring	INV2023-100270	2/17/2023
12	503		$2,100.00	SXSW - Shuttle Stop Banners	INV2023-100250	2/7/2023
13		923	$1,200.00	Austin Film Festival	INV2023-100205	1/26/2023

dianamartin commented 1 year ago

6/13

Nick messaged

Hi there, I just saw that there are 7 different transactions for refunds for one particular event, NetVu 2023, for $6000. Is this an error? Can someone let me know what happened? Also, The citybase site is not letting me reset password or login.

I replied

I'm looking at the transactions list and now I'm seeing the weird transactions you've mentioned. 7 records are showing but are not connected to any reservation record and all happened on 6/2/23.

Ran a query on the backend, invoice_transactions

There are 9 records that are orphaned
- INV2023-100365
- INV2023-100395
- INV2023-100760
- INV2023-100765
- INV2023-100770
- INV2023-100775
- INV2023-100780
- INV2023-100785
- INV2023-100790

Reference Table

C. Date	C. By	Invoice ID	Type	Name	Event	Total Amount	Fee Description	Sub Description	Transaction Status	Transaction Paid	REVENUE
3/14/2023		INV2023-100365	Over-the-Street		2023 Austin Train Show	($600.00)	Over-the-Street - 2023 Austin Train Show - - George Z.	INV2023-100365 - Number of Banners: - Locations:	REFUND	3/14/2023	4477
3/23/2023	MG	INV2023-100395	Over-the-Street	Raquel	Summer Camp Hiring	$600.00	Over-the-Street - Summer Camp Hiring - - Raquel D.	INV2023-100395 - Number of Banners: - Locations:		3/23/2023	4477
6/2/2023		INV2023-100760	Lamppost		NetVu 2023 Annual Conference	($6,000.00)	Lamppost - NetVu 2023 Annual Conference - - Jenn S.	INV2023-100760 - Number of Banners: - Locations:	REFUND	6/2/2023	4477
6/2/2023		INV2023-100765	Lamppost		NetVu 2023 Annual Conference	($6,000.00)	Lamppost - NetVu 2023 Annual Conference - - Jenn S.	INV2023-100765 - Number of Banners: - Locations:	REFUND	6/2/2023	4477
6/2/2023		INV2023-100770	Lamppost		NetVu 2023 Annual Conference	($6,000.00)	Lamppost - NetVu 2023 Annual Conference - - Jenn S.	INV2023-100770 - Number of Banners: - Locations:	REFUND	6/2/2023	4477
6/2/2023		INV2023-100775	Lamppost		NetVu 2023 Annual Conference	($6,000.00)	Lamppost - NetVu 2023 Annual Conference - - Jenn S.	INV2023-100775 - Number of Banners: - Locations:	REFUND	6/2/2023	4477
6/2/2023		INV2023-100780	Lamppost		NetVu 2023 Annual Conference	($6,000.00)	Lamppost - NetVu 2023 Annual Conference - - Jenn S.	INV2023-100780 - Number of Banners: - Locations:	REFUND	6/2/2023	4477
6/2/2023		INV2023-100785	Lamppost		NetVu 2023 Annual Conference	($6,000.00)	Lamppost - NetVu 2023 Annual Conference - - Jenn S.	INV2023-100785 - Number of Banners: - Locations:	REFUND	6/2/2023	4477
6/2/2023		INV2023-100790	Lamppost		NetVu 2023 Annual Conference	($6,000.00)	Lamppost - NetVu 2023 Annual Conference - - Jenn S.	INV2023-100790 - Number of Banners: - Locations:	REFUND	6/2/2023	4477

dianamartin commented 1 year ago

6/13

In depth dive on these 7 records
67 records transactions total

Before the 7 records

The transaction invoice INV2023-100755, before to the 7 records was created by NM on 6/2/23
- OTS RSVP 1023 - Austin Against Hate - We All Belong Community Campaign Kick Off

7 records

The 7 records are all LPB type

After the 7 records

The transaction invoice INV2023-100795. after the 7 records was created by NM on 6/6/23
- OTS RSVP 1023 - Austin Museum Day

patrickm02L commented 1 year ago

6/14/23 - Continue to research why the invoices have been orphaned. We did in the meeting and can't figure out why NetVu has duplicates, our recommendation is to ask Nick to delete the transactions or give him the option for us to do it.

patrickm02L commented 1 year ago

Pinged Nick on Teams to let him know the duplicates were errant records and that they were not connected to Knack. Offered the option for him to delete. He said he did not have that capability, so we're going to delete them.

cc: @dianamartin

dianamartin commented 1 year ago

6/14

bulk delete the 7 records that are phantom on 6/2 and contains "netvu"
This is completed ✅

patrickm02L commented 1 year ago

Resolved the scope of the original SR, broke off further investigation of older invoices into separate Issue.

cityofaustin / atd-data-tech

CityBase: Invoices Not Updating Correctly for VisitAustin #12422

6/7

Invoice Fields

Steps

Reference Table

Unpaid Invoices

6/8

Double checking that these transactions were actually paid

6/13

Reference Table

6/13

Before the 7 records

7 records

After the 7 records

6/14