Payment does not show in BCOL for Registration in Modernized System

lbergero819 commented 3 months ago

Sharon has reported that there was no fee debited in BCOL for the registrations she performed in the new system for RBC condos acct 274888 (fee code MHR400 in BCOL $50 when filing done in legacy system). The registration numbers are MHR108186-7-8) and the fees were not debited from their BCOL acct 274888.

Note: In the modernized system Initial Registration uses MHR Fee Code NTOWN.

lbergero819 commented 3 months ago

I will check the account this morning to see if something was posted since yesterday. I will attach the acct debits since yesterday.

mstanton1 commented 3 months ago

@lbergero819 just to confirm, the issue here is that Sharon completed a transaction for new Registration in the modernized system and then did the Transport Permit in the legacy system. Are you stating neither the new registration or the transport permit from legacy are showing in BCOL? Or is just one transaction missing in BCOL?

doug-lovett commented 3 months ago

Modernized registrations do not set the legacy DB2 document.bcolacct value. Maybe this is causing a problem for legacy change registration payments?

lbergero819 commented 3 months ago

Should we ask John McColl to check

From: Doug Lovett @.> Sent: Friday, January 26, 2024 10:28 AM To: bcgov/entity @.> Cc: Bergeron, Louise CITZ:EX @.>; Mention @.> Subject: Re: [bcgov/entity] Payment does not show in BCOL for Registration in Modernized System (Issue #19532)

[EXTERNAL] This email came from an external source. Only open attachments or links that you are expecting from a known sender.

Modernized registrations do not set the legacy DB2 document.bcolacct value. Maybe this is causing a problem for legacy change registration payments?

— Reply to this email directly, view it on GitHubhttps://github.com/bcgov/entity/issues/19532#issuecomment-1912502042, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AT3A4LZX6Q5WEAGFDRWEX23YQPYRTAVCNFSM6AAAAABCMNY2DSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMJSGUYDEMBUGI. You are receiving this because you were mentioned.Message ID: @.**@.>>

mstanton1 commented 3 months ago

@lbergero819 @doug-lovett do modernized search's set the legacy DB2 document.bcolacct value? Search is correctly writing back to BC Online. If we aren't setting the legacy document.bcolacct value for search we can assume that isn't the issue.

@lbergero819 have you checked with Patty on whether she could take a look?

cc @arlentees

doug-lovett commented 3 months ago

After meeting with Louise and John M, here is the issue: Staff is creating a modernized registration to be charged to a BCOL account. The payment is not showing up in the BCOL legacy interface. The registration may be showing up incorrectly as a no fee payment in the modernized account transactions.

As an example, a new MH registration for MHR 108186 was created with a timestamp of 2024-01-25 22:07:58 UTC (2:07 PM). From the MHR API log, the Pay API request data is: { "filingInfo": { "filingTypes": [ { "filingTypeCode": "INREG", "priority": false, "futureEffective": false, "quantity": 1 }, { "filingTypeCode": "PRIMH", "priority": true, "futureEffective": false, "quantity": 1 } ] }, "businessInfo": {"corpType": "MHR"}, "details": [ { "label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION" } ], "accountInfo": { "bcolAccountNumber": "274888", "datNumber": "C1038657" } }

And the Pay API response is: { "_links": { "collection": "/api/v1/payment-requests?invoice_id=4640717", "self": "/api/v1/payment-requests/4640717" }, "bcolAccount": "274888", "corpTypeCode": "MHR", "createdBy": "IDIR\SMERLINO", "createdName": "Merlino, Sharon CITZ:EX", "createdOn": "2024-01-25T22:07:57+00:00", "datNumber": "C1038657", "details": [ { "label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION" } ], "id": 4640717, "isPaymentActionRequired": false, "lineItems": [ { "description": "Initial Registration", "filingFees": 0.0, "futureEffectiveFees": 0.0, "gst": 0.0, "id": 4647564, "priorityFees": 0.0, "pst": 0.0, "quantity": 1, "serviceFees": 0.0, "statusCode": "ACTIVE", "total": 0.0, "waivedBy": null, "waivedFees": 0.0 }, { "description": "Priority Fee for MHR", "filingFees": 0.0, "futureEffectiveFees": 0.0, "gst": 0.0, "id": 4647565, "priorityFees": 0.0, "pst": 0.0, "quantity": 1, "serviceFees": 0.0, "statusCode": "ACTIVE", "total": 0.0, "waivedBy": null, "waivedFees": 0.0 } ], "overdueDate": "2024-02-01T22:07:57+00:00", "paid": 0.0, "paymentAccount": { "accountId": "14752", "accountName": "Ministry of Citizens' Services-BC Registries and Online Services Staff", "billable": true }, "paymentDate": "2024-01-25T22:07:57+00:00", "paymentMethod": "INTERNAL", "receipts": [ { "id": 4634226, "receiptAmount": 0.0, "receiptDate": "2024-01-25T22:07:57.393038", "receiptNumber": "REG04640717" } ], "references": [ { "id": 4746995, "invoiceNumber": "REG04640717", "statusCode": "COMPLETED" } ], "refund": 0.0, "serviceFees": 0.0, "statusCode": "COMPLETED", "total": 0.0, "updatedBy": "IDIR\SMERLINO", "updatedName": "Merlino, Sharon CITZ:EX", "updatedOn": "2024-01-25T22:07:57+00:00" }

@mstanton1 from the response, it looks like no fee was charged even though the BCOL account info was submitted and payment was not waived (no fee). Can you verify the PROD fee is set up correctly for INREG?

lbergero819 commented 3 months ago

Patty is on flex today, Darci does not know anything about how it would be set up for the new system. I can try John

From: mstanton1 @.> Sent: Friday, January 26, 2024 11:51 AM To: bcgov/entity @.> Cc: Bergeron, Louise CITZ:EX @.>; Mention @.> Subject: Re: [bcgov/entity] Payment does not show in BCOL for Registration in Modernized System (Issue #19532)

[EXTERNAL] This email came from an external source. Only open attachments or links that you are expecting from a known sender.

@lbergero819https://github.com/lbergero819 @doug-lovetthttps://github.com/doug-lovett do modernized search's set the legacy DB2 document.bcolacct value? Search is correctly writing back to BC Online. If we aren't setting the legacy document.bcolacct value for search we can assume that isn't the issue.

@lbergero819https://github.com/lbergero819 have you checked with Patty on whether she could take a look?

cc @arlenteeshttps://github.com/arlentees

— Reply to this email directly, view it on GitHubhttps://github.com/bcgov/entity/issues/19532#issuecomment-1912606777, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AT3A4L7VXABHCY7L4STFRNDYQQCI3AVCNFSM6AAAAABCMNY2DSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMJSGYYDMNZXG4. You are receiving this because you were mentioned.Message ID: @.**@.>>

mstanton1 commented 3 months ago

@doug-lovett to you question on whether the INREG fee code is set up properly in PROD I can see it is set up with at 50.00 fee as expected. When I go into the edit screen I can also see it is mapped to the right distribution code (GL coding for modernized MHR system - Registrations).

Looking at our fee requirements though, I notice that when policy did a deeper review of fee codes we had some duplication. INREG - Initial Registration was noted as being the same as Notice of Ownership (NTOWN) and since Notice of Ownership as a fee code better aligned with regulations that was recommended as the code we retained. I recall work with relationships, maybe @seeker25 to try to remove some fee codes which weren't needed (EXTTM, CORDS, AFFDV, INREG) and recall some issues with doing that because we were using / had used some of those codes. @seeker25 do you recall that? Was any change made to inactivate the fee code 'INREG'?

doug-lovett commented 3 months ago

@mstanton1 @seeker25 if we can retroactively apply a charge manually, the 4 INREG payment transactions identified in this ticket are invoice numbers: 4640717 4640885 4640959 4641008

mstanton1 commented 3 months ago

@doug-lovett @seeker25 is there a way to provide us with the MHR Registration number for those four invoices?

lbergero819 commented 3 months ago

MHR108186-7-8

doug-lovett commented 3 months ago

@mstanton1 @lbergero819 Louise I think you identified 4 MHR registrations in the meeting today. Here they are with the registration timestamps: Timestamp.....................MHR.........Invoice 2024-01-25 22:07:58 108186 4640717 2024-01-25 22:22:59 108187 4640885 2024-01-25 22:28:27 108188 4640959 2024-01-25 22:31:52 108189 4641008

lbergero819 commented 3 months ago

Sharon is asking if she should debit their BCOL account manually since they have not been charged for these registrations below. I just wanted to make sure with you both Doug and Melissa, that you have not processed any payments behind the scene, I want to make sure they are not charged twice.

I will wait for your response and let Sharon know on Monday morning Thanks Louise

From: Doug Lovett @.> Sent: Friday, January 26, 2024 4:00 PM To: bcgov/entity @.> Cc: Bergeron, Louise CITZ:EX @.>; Mention @.> Subject: Re: [bcgov/entity] Payment does not show in BCOL for Registration in Modernized System (Issue #19532)

[EXTERNAL] This email came from an external source. Only open attachments or links that you are expecting from a known sender.

@mstanton1https://github.com/mstanton1 @lbergero819https://github.com/lbergero819 Louise I think you identified 4 MHR registrations in the meeting today. Here they are with the registration timestamps: MHR Invoice 2024-01-25 22:07:58 108186 4640717 2024-01-25 22:22:59 108187 4640885 2024-01-25 22:28:27 108188 4640959 2024-01-25 22:31:52 108189 4641008

— Reply to this email directly, view it on GitHubhttps://github.com/bcgov/entity/issues/19532#issuecomment-1912846537, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AT3A4L7DDOITLZFRD7CIX23YQQ7RNAVCNFSM6AAAAABCMNY2DSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMJSHA2DMNJTG4. You are receiving this because you were mentioned.Message ID: @.**@.>>

mstanton1 commented 3 months ago

Adding @arlentees . Arlen, Doug also included timestamps and these are just from yesterday so we could ask the business re-enter the payment given it failed. It would be best to reach out to Jeff Zukowski and Sharon Merlino to request this is completed.

The only other outstanding questions I would have are about how we ensure this failure doesn't keep happening. If this was from INREG being used as the code there would be a need to switch to NTOWN. If that takes place we need to verify the UI still shows the right wording in the fee code summary box, and would want to watch to make sure the first registration using that code worked properly. There may be an additional request needed to Patty Stemkens for support in making sure that maps to a BCOL fee code and shows in the legacy system. Let me know how I can assist when I'm back next week:)

arlentees commented 3 months ago

@mstanton1 Update on some conversations:

Sharon will be able to go in and manually re-enter the payment. After discussing the issue with @doug-lovett the NTOWN code is associated with the description "Change of Ownership" and it would be best if the app could continue to use the INREG code and receive the correct charge amount from the Pay API. We will be reaching out today to Relationships to discuss activating the INREG code.

seeker25 commented 3 months ago

INREG, is set to $50 in the pay-admin,

doug-lovett commented 3 months ago

For tracking, from Louise's new registration in TEST, here is the pay api response with the correct $50 charge from the MHR API log: { "_links": { "collection": "/api/v1/payment-requests?invoice_id=716179", "self": "/api/v1/payment-requests/716179" }, "bcolAccount": "424727", "corpTypeCode": "MHR", "createdBy": "IDIR\LBERGERO", "createdName": "Bergeron, Louise CITZ:EX", "createdOn": "2024-01-29T20:38:46+00:00", "datNumber": "C2233112", "details": [ { "label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION" } ], "id": 716179, "isPaymentActionRequired": false, "lineItems": [ { "description": "Initial Registration", "filingFees": 50.0, "futureEffectiveFees": 0.0, "gst": 0.0, "id": 718771, "priorityFees": 0.0, "pst": 0.0, "quantity": 1, "serviceFees": 0.0, "statusCode": "ACTIVE", "total": 50.0, "waivedBy": null, "waivedFees": 0.0 } ], "overdueDate": "2024-02-01T20:38:46+00:00", "paid": 50.0, "paymentAccount": { "accountId": "1999", "accountName": "Ministry of Citizens' Services-BC Registries and Online Services Staff", "billable": true }, "paymentDate": "2024-01-29T20:38:47+00:00", "paymentMethod": "DRAWDOWN", "receipts": [ { "id": 3552642, "receiptAmount": 50.0, "receiptDate": "2024-01-29T20:38:47.013212", "receiptNumber": "REGT00716179" } ], "references": [ { "id": 714240, "invoiceNumber": "REGT00716179", "referenceNumber": "0001", "statusCode": "COMPLETED" } ], "refund": 0.0, "serviceFees": 0.0, "statusCode": "COMPLETED", "total": 50.0, "updatedBy": "IDIR\LBERGERO", "updatedName": "Bergeron, Louise CITZ:EX", "updatedOn": "2024-01-29T20:38:47+00:00" }

seeker25 commented 3 months ago

apply_filing_fees = 'f' for MHR, set by Melissa

seeker25 commented 3 months ago

Requested by Doug to set apply_filing_fees = 't' for MHR

pay-db=# select * from account_fees where account_id = 21509
pay-db-# ;
  id  | account_id | apply_filing_fees | service_fee_code |     product     |         created_on         | updated_on |  created_by   |  created_name   | updated_by | updated_name
------+------------+-------------------+------------------+-----------------+----------------------------+------------+---------------+-----------------+------------+--------------
  555 |      21509 | t                 | TRF01            | BUSINESS        | 2022-10-19 21:22:18.905427 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  556 |      21509 | t                 | TRF01            | BUSINESS_SEARCH | 2022-10-19 21:22:18.91904  |            | IDIR\TRSEMPLE | Travis Semple   |            |
  557 |      21509 | t                 | TRF01            | BCA             | 2022-10-19 21:22:18.929663 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  558 |      21509 | t                 | TRF01            | CSO             | 2022-10-19 21:22:18.940587 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  559 |      21509 | t                 | TRF01            | PPR             | 2022-10-19 21:22:18.951749 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  560 |      21509 | t                 | TRF01            | RPT             | 2022-10-19 21:22:18.962469 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  561 |      21509 | t                 | TRF03            | ESRA            | 2022-10-19 21:22:19.021944 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  562 |      21509 | t                 | TRF01            | VS              | 2022-10-19 21:22:19.033567 |            | IDIR\TRSEMPLE | Travis Semple   |            |
 1171 |      21509 | f                 | TRF02            | MHR             | 2023-12-22 18:31:05.93348  |            | IDIR\MSTANTON | Melissa Stanton |            |
(9 rows)

pay-db=# begin; update account_fees set apply_filing_fees = 't' where id = 1171;
BEGIN
UPDATE 1
pay-db=*# commit;
COMMIT
pay-db=# select * from account_fees where account_id = 21509
;
  id  | account_id | apply_filing_fees | service_fee_code |     product     |         created_on         | updated_on |  created_by   |  created_name   | updated_by | updated_name
------+------------+-------------------+------------------+-----------------+----------------------------+------------+---------------+-----------------+------------+--------------
  555 |      21509 | t                 | TRF01            | BUSINESS        | 2022-10-19 21:22:18.905427 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  556 |      21509 | t                 | TRF01            | BUSINESS_SEARCH | 2022-10-19 21:22:18.91904  |            | IDIR\TRSEMPLE | Travis Semple   |            |
  557 |      21509 | t                 | TRF01            | BCA             | 2022-10-19 21:22:18.929663 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  558 |      21509 | t                 | TRF01            | CSO             | 2022-10-19 21:22:18.940587 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  559 |      21509 | t                 | TRF01            | PPR             | 2022-10-19 21:22:18.951749 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  560 |      21509 | t                 | TRF01            | RPT             | 2022-10-19 21:22:18.962469 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  561 |      21509 | t                 | TRF03            | ESRA            | 2022-10-19 21:22:19.021944 |            | IDIR\TRSEMPLE | Travis Semple   |            |
  562 |      21509 | t                 | TRF01            | VS              | 2022-10-19 21:22:19.033567 |            | IDIR\TRSEMPLE | Travis Semple   |            |
 1171 |      21509 | t                 | TRF02            | MHR             | 2023-12-22 18:31:05.93348  |            | IDIR\MSTANTON | Melissa Stanton |            |
(9 rows)

lbergero819 commented 3 months ago

@doug-lovett @arlentees @mstanton1 There is a comment above from Doug to set apply_filing_fees = 't' for MHR, is it something done by Melissa, it is it by a developer? If Melissa, Arlen and I should get a demo for setting that.

seeker25 commented 3 months ago

Can be done in the interface:

mstanton1 commented 3 months ago

@lbergero819 I checked in both TEST and PROD and the statutory fee is set to 'True'. The Service Fee in PROD was incorrectly set to $1.00 and has been updated in PROD to $1.50. That shouldn't have any impact though because the only time a Service Fee will apply with MHR is in the case where a client is completing their own MHR Search or MHR Combo Search. When staff complete a MHR or Combo search on a client's behalf different fee codes are used and no service fee applies.

@seeker25 with the notes, I saw that you said the INREG was tied to a $50.00 fee (which I'd seen in the admin) yet we had a handful of cases where it didn't result in a charge. Did you take any action to edit that fee code to make it available for use again?

@doug-lovett @seeker25 the four MHR registrations that were listed where a 0$ fee was charged are very recent. Was anything done to look further back and verify that other initial registrations performed by staff or pilot users are incurring fees? I could look at our pilot documents to pull the pilot users accounts?

cc @arlentees

doug-lovett commented 3 months ago

@mstanton1 as Travis noted above, he flipped the PROD apply_filing_fees flag from false to true yesterday afternoon for the INREG filing type. I have not reviewed other PROD staff MH registrations to see if a fee was missed. We do not save payment details in the MHR database, only the invoice ID, so the only way for assets to research if staff applied a payment is to review the API logs.

mstanton1 commented 3 months ago

@doug-lovett thank you. Given we don't save payment in the MHR database, would it make more sense for you to just query all the MHR registrations done in the new system and we could then seek relationships support in looking those up in auth to confirm if fees were taken? Ultimately, we will need to be able to confirm the full set of impacted MHR's and then @arlentees and I can work with the business and report up on the issue.

seeker25 commented 3 months ago

@doug-lovett I've taken the payload above and recharged, could you re-associate to this invoice?

request:

{
    "filingInfo": {
        "filingTypes": [
            {
                "filingTypeCode": "INREG",
                "priority": false,
                "futureEffective": false,
                "quantity": 1
            },
            {
                "filingTypeCode": "PRIMH",
                "priority": true,
                "futureEffective": false,
                "quantity": 1
            }
        ]
    },
    "businessInfo": {
        "corpType": "MHR"
    },
    "details": [
        {
            "label": "MH Registration Type:",
            "value": "MANUFACTURED HOME REGISTRATION"
        }
    ],
    "accountInfo": {
        "bcolAccountNumber": "274888",
        "datNumber": "C1038657"
    }
}

Response:

{
    "_links": {
        "collection": "/api/v1/payment-requests?invoice_id=4672900",
        "self": "/api/v1/payment-requests/4672900"
    },
    "bcolAccount": "274888",
    "corpTypeCode": "MHR",
    "createdBy": "IDIR\\xx",
    "createdName": "Travis Semple",
    "createdOn": "2024-01-30T22:21:52+00:00",
    "datNumber": "C1038657",
    "details": [
        {
            "label": "MH Registration Type:",
            "value": "MANUFACTURED HOME REGISTRATION"
        }
    ],
    "id": 4672900,
    "isPaymentActionRequired": false,
    "lineItems": [
        {
            "description": "Initial Registration",
            "filingFees": 50.0,
            "futureEffectiveFees": 0.0,
            "gst": 0.0,
            "id": 4679903,
            "priorityFees": 0.0,
            "pst": 0.0,
            "quantity": 1,
            "serviceFees": 0.0,
            "statusCode": "ACTIVE",
            "total": 50.0,
            "waivedBy": null,
            "waivedFees": 0.0
        },
        {
            "description": "Priority Fee for MHR",
            "filingFees": 100.0,
            "futureEffectiveFees": 0.0,
            "gst": 0.0,
            "id": 4679904,
            "priorityFees": 0.0,
            "pst": 0.0,
            "quantity": 1,
            "serviceFees": 0.0,
            "statusCode": "ACTIVE",
            "total": 100.0,
            "waivedBy": null,
            "waivedFees": 0.0
        }
    ],
    "overdueDate": "2024-02-01T22:21:52+00:00",
    "paid": 150.0,
    "paymentAccount": {
        "accountId": "14752",
        "accountName": "Ministry of Citizens' Services-BC Registries and Online Services Staff",
        "billable": true
    },
    "paymentDate": "2024-01-30T22:21:52+00:00",
    "paymentMethod": "DRAWDOWN",
    "receipts": [
        {
            "id": 4665997,
            "receiptAmount": 150.0,
            "receiptDate": "2024-01-30T22:21:52.907616",
            "receiptNumber": "REG04672900"
        }
    ],
    "references": [
        {
            "id": 4778745,
            "invoiceNumber": "REG04672900",
            "referenceNumber": "0001",
            "statusCode": "COMPLETED"
        }
    ],
    "refund": 0.0,
    "serviceFees": 0.0,
    "statusCode": "COMPLETED",
    "total": 150.0,
    "updatedBy": "IDIR\\xx",
    "updatedName": "Travis Semple",
    "updatedOn": "2024-01-30T22:21:52+00:00"
}

seeker25 commented 3 months ago

Here are the invoices after the filing fee update December 22nd/23rd?:

pay-db=# select created_on, total, paid, details, payment_method_code from invoices where payment_account_id = 21509 and corp_type_code = 'MHR'order by id desc limit 500;
         created_on         | total  |  paid  |                                        details                                         | payment_method_code
----------------------------+--------+--------+----------------------------------------------------------------------------------------+---------------------
 2024-01-30 22:21:52.473266 | 150.00 | 150.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | DRAWDOWN
 2024-01-30 15:39:54.590916 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "014222"}]                                      | INTERNAL
 2024-01-30 00:02:13.301572 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "Burge, Tracey"}]                                    | INTERNAL
 2024-01-29 22:49:13.065632 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "033999"}]                                           | INTERNAL
 2024-01-29 17:20:13.499257 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "045524"}]                                           | INTERNAL
 2024-01-26 19:35:58.886249 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "108186"}]                                           | INTERNAL
 2024-01-26 18:27:33.316381 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "065712"}]                                           | INTERNAL
 2024-01-26 17:56:31.620456 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "010627"}]                                           | INTERNAL
 2024-01-26 16:42:32.029721 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "015964"}]                                           | INTERNAL
 2024-01-26 00:07:39.090061 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-25 22:46:00.855641 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "108151"}]                                           | INTERNAL
 2024-01-25 22:40:18.936218 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "smith, john"}]                                      | INTERNAL
 2024-01-25 22:31:51.186117 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-25 22:28:27.028523 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-25 22:22:58.532295 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-25 22:07:57.331728 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-25 21:52:10.167308 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "034672"}]                                      | INTERNAL
 2024-01-25 18:48:38.090385 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "016517"}]                                      | INTERNAL
 2024-01-25 18:26:40.873196 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "009643"}]                                      | INTERNAL
 2024-01-25 00:18:01.891759 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-24 23:28:37.358649 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-24 19:48:05.445446 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-24 17:39:51.565682 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "052234"}]                                      | INTERNAL
 2024-01-23 21:23:46.684157 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "044166"}]                                      | INTERNAL
 2024-01-23 18:53:13.490703 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "044856"}]                                      | INTERNAL
 2024-01-22 23:50:55.280935 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "054657"}]                                      | INTERNAL
 2024-01-22 16:30:11.219937 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "011950"}]                                      | INTERNAL
 2024-01-19 18:14:29.172837 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "044856"}]                                      | INTERNAL
 2024-01-19 17:38:16.73307  |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-19 17:36:01.711654 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-19 17:34:14.584846 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-18 23:07:55.789582 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "Smith, John"}]                                 | INTERNAL
 2024-01-18 22:51:42.88038  |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-18 22:29:49.607046 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "011950"}]                                      | INTERNAL
 2024-01-18 21:13:02.051324 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "017330"}]                                      | INTERNAL
 2024-01-18 19:07:22.88558  |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-17 18:10:07.075335 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-16 20:03:45.73908  |   0.00 |   0.00 | [{"label": "MHR Search", "value": "105335"}]                                           | INTERNAL
 2024-01-16 16:55:13.128193 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "105335"}]                                      | INTERNAL
 2024-01-15 23:32:45.662419 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "075577"}]                                           | INTERNAL
 2024-01-15 23:14:34.92422  |   0.00 |   0.00 | [{"label": "MHR Search", "value": "008659"}]                                           | INTERNAL
 2024-01-15 23:09:58.691565 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "021887"}]                                           | INTERNAL
 2024-01-15 22:54:51.179089 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "021887"}]                                           | INTERNAL
 2024-01-15 22:25:43.420481 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "021887"}]                                           | INTERNAL
 2024-01-15 21:08:50.027026 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "Luypen, Josephus"}]                                 | INTERNAL
 2024-01-15 17:52:03.923059 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "001821"}]                                      | INTERNAL
 2024-01-14 22:57:40.8244   |   0.00 |   0.00 | [{"label": "MHR Search", "value": "056033"}]                                           | INTERNAL
 2024-01-14 22:33:19.835459 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "004991"}]                                      | INTERNAL
 2024-01-14 21:57:14.853977 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "078306"}]                                      | INTERNAL
 2024-01-14 21:11:58.777388 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "008787"}]                                      | INTERNAL
 2024-01-12 23:53:08.715931 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "047454"}]                                           | INTERNAL
 2024-01-12 23:40:02.353864 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "107603"}]                                      | INTERNAL
 2024-01-12 23:05:51.723609 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "077480"}]                                      | INTERNAL
 2024-01-12 22:02:08.041591 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "033405"}]                                      | INTERNAL
 2024-01-12 20:25:28.156705 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "a1h1822271"}]                                       | INTERNAL
 2024-01-12 20:15:24.669119 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "021167"}]                                           | INTERNAL
 2024-01-12 19:08:15.806942 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "045602"}]                                      | INTERNAL
 2024-01-10 20:26:09.533848 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "072824"}]                                           | INTERNAL
 2024-01-10 19:58:18.698055 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "013472"}]                                           | INTERNAL
 2024-01-10 19:56:10.56134  |   0.00 |   0.00 | [{"label": "MHR Search", "value": "080372"}]                                           | INTERNAL
 2024-01-10 00:10:43.013769 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "020752"}]                                           | INTERNAL
 2024-01-10 00:08:33.333279 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "081655"}]                                           | INTERNAL
 2024-01-10 00:05:30.147326 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "032254"}]                                           | INTERNAL
 2024-01-10 00:02:51.212601 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "002196"}]                                           | INTERNAL
 2024-01-09 23:58:59.542586 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "080372"}]                                           | INTERNAL
 2024-01-09 23:58:12.316052 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "080372"}]                                           | INTERNAL
 2024-01-09 23:47:35.157343 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "013472"}]                                           | INTERNAL
 2024-01-09 01:00:28.619156 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "017243"}]                                      | INTERNAL
 2024-01-04 21:51:07.887249 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-04 21:49:58.745023 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-04 21:48:11.177667 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-04 21:46:56.433233 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-04 21:45:25.703583 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-04 00:08:02.378183 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-03 22:36:22.513956 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "028057"}]                                      | INTERNAL
 2024-01-03 19:28:35.07818  |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-03 19:26:33.678863 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-03 19:24:13.370219 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2024-01-03 18:55:34.981282 |   0.00 |   0.00 | [{"label": "Combined Search", "value": "073208"}]                                      | INTERNAL
 2023-12-29 21:55:38.508899 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2023-12-29 17:50:47.54989  |   0.00 |   0.00 | [{"label": "Combined Search", "value": "010933"}]                                      | INTERNAL
 2023-12-29 00:24:58.787348 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2023-12-29 00:22:01.174597 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2023-12-28 23:59:57.721651 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2023-12-28 18:15:43.968683 |   0.00 |   0.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL
 2023-12-27 19:01:25.121464 |   0.00 |   0.00 | [{"label": "MHR Search", "value": "072824"}]                                           | INTERNAL
 2023-12-21 21:37:14.045008 |  50.00 |  50.00 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]        | INTERNAL

seeker25 commented 3 months ago

@mstanton1 @doug-lovett let me know which other fields you need in this query, I see filingId, but it's only populated some of the time

mstanton1 commented 3 months ago

@doug-lovett @seeker25 the list above shows the Manufactured Home Registrations that have a 0$ charge, but doesn't identify the Manufactured Home Registration numbers. It sounds like we might need to look these up from the Assets side. @doug-lovett are you able to run a query to list all MHR Registration Numbers from December 22nd to current for New Registrations in the modernized system?

doug-lovett commented 3 months ago

@mstanton1 @seeker25 Attached below are the MH registrations from 2023-12-22 to now with the pay invoice id. Travis, from the pay invoice id can you find the request payment information (BCOL account, DAT)?

mhreg-pay-2023-12-22-to-2024-12-30.xlsx

lbergero819 commented 3 months ago

Do not process / re-process the payments for these production MHR (from the ticket description as specified below). These are the original payments reported as not working and staff has reprocessed the payments manually already. The registration numbers are MHR108186-7-8), MHR Invoice & timestamps: 2024-01-25 22:07:58 108186 4640717 2024-01-25 22:22:59 108187 4640885 2024-01-25 22:28:27 108188 4640959 2024-01-25 22:31:52 108189 4641008

lbergero819 commented 3 months ago

@doug-lovett @seeker25 Do not process / re-process the payments for these production MHR (from the ticket description as specified below). These are the original payments reported as not working and staff has reprocessed the payments manually already. The registration numbers are MHR108186-7-8), MHR Invoice & timestamps: 2024-01-25 22:07:58 108186 4640717 2024-01-25 22:22:59 108187 4640885 2024-01-25 22:28:27 108188 4640959 2024-01-25 22:31:52 108189 4641008

seeker25 commented 3 months ago

Ok, I'll refund the one I just did and email you and Sharon. 2024-01-25T22:07:57+00:00

seeker25 commented 3 months ago

@doug-lovett

pay-db=# select id, created_on, created_by, total, paid, dat_number, bcol_account, payment_method_code, payment_account_id, details from invoices where id in (4456934,4460853,4461010,4461030,4466571,4484279,4484313,4484353,4488604,4495513,4495532,4495540,4495561,4495574,4579705,4588954,4592212,4596351,4596380,4596410,4629043,4632304,4633005,4636557,4636618,4640717
,4640885,4640959,4641008,4642486,4644387,4644399,4644498,4644506,4644532,4644543,4644555,4646062,4648429,4648473,4648517,4648607,4648636,4648701);
   id    |         created_on         |              created_by               | total | paid  | dat_number | bcol_account | payment_method_code | payment_account_id |                                     details
---------+----------------------------+---------------------------------------+-------+-------+------------+--------------+---------------------+--------------------+---------------------------------------------------------------------------------
 4456934 | 2023-12-28 18:15:43.968683 | IDIR\SMERLINO                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4460853 | 2023-12-28 23:59:57.721651 | IDIR\SMERLINO                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4461010 | 2023-12-29 00:22:01.174597 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4461030 | 2023-12-29 00:24:58.787348 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4466571 | 2023-12-29 21:55:38.508899 | IDIR\SMERLINO                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4484279 | 2024-01-03 19:24:13.370219 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4484313 | 2024-01-03 19:26:33.678863 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4484353 | 2024-01-03 19:28:35.07818  | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4488604 | 2024-01-04 00:08:02.378183 | IDIR\SMERLINO                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4495513 | 2024-01-04 21:45:25.703583 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4495532 | 2024-01-04 21:46:56.433233 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4495540 | 2024-01-04 21:48:11.177667 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4495561 | 2024-01-04 21:49:58.745023 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4495574 | 2024-01-04 21:51:07.887249 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4579705 | 2024-01-17 18:10:07.075335 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4588954 | 2024-01-18 19:07:22.88558  | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4592212 | 2024-01-18 22:51:42.88038  | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4596351 | 2024-01-19 17:34:14.584846 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4596380 | 2024-01-19 17:36:01.711654 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4596410 | 2024-01-19 17:38:16.73307  | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4629043 | 2024-01-24 19:48:05.445446 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4632304 | 2024-01-24 23:28:37.358649 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4633005 | 2024-01-25 00:18:01.891759 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4636557 | 2024-01-25 17:47:45.210236 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4636618 | 2024-01-25 17:50:44.20828  | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4640717 | 2024-01-25 22:07:57.331728 | IDIR\SMERLINO                         |  0.00 |  0.00 | C1038657   | 274888       | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4640885 | 2024-01-25 22:22:58.532295 | IDIR\SMERLINO                         |  0.00 |  0.00 | C1038657   | 274888       | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4640959 | 2024-01-25 22:28:27.028523 | IDIR\SMERLINO                         |  0.00 |  0.00 | C1038657   | 274888       | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4641008 | 2024-01-25 22:31:51.186117 | IDIR\SMERLINO                         |  0.00 |  0.00 | C1038657   | 274888       | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4642486 | 2024-01-26 00:07:39.090061 | IDIR\KAWALLAC                         |  0.00 |  0.00 |            |              | INTERNAL            |              21509 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4644387 | 2024-01-26 14:21:42.365317 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4644399 | 2024-01-26 14:24:58.843575 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4644498 | 2024-01-26 14:43:30.115611 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4644506 | 2024-01-26 14:46:22.388374 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4644532 | 2024-01-26 14:50:00.972677 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4644543 | 2024-01-26 14:54:53.536859 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4644555 | 2024-01-26 14:59:35.164707 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4646062 | 2024-01-26 17:58:55.143837 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4648429 | 2024-01-26 20:39:59.127181 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4648473 | 2024-01-26 20:43:06.770417 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4648517 | 2024-01-26 20:45:12.563236 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4648607 | 2024-01-26 20:48:10.512374 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4648636 | 2024-01-26 20:50:27.721952 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
 4648701 | 2024-01-26 20:55:07.122381 | BCSC/RCW7LL6UZABUYEJNCGKS6MCZOF5F6DMG | 50.00 | 50.00 |            | 392606       | DRAWDOWN            |              57873 | [{"label": "MH Registration Type:", "value": "MANUFACTURED HOME REGISTRATION"}]
(44 rows)

doug-lovett commented 3 months ago

@seeker25 thanks Travis. @mstanton1 from Travis's query results above, and given that Sharon manually applied the charges for the 4 2024-01-25 registrations, it looks like there is no further action. The remaining staff registrations in this period are all no fee.

lbergero819 commented 3 months ago

@seeker25 @mstanton1 @arlentees @doug-lovett If you find other charges to be applied Sharon wants to see and approve the list before any action is taken to re-process the payments.

mstanton1 commented 3 months ago

@lbergero819 Doug provided a list above with MHR registration numbers since the date the issue arose. Travis provided a list showing which payments were taken. From that it appears about 24 payments failed, all of which were staff. Client payments show the 50.00 charge. I will compile a list of only those that did not include payment in an email which will need to be sent to the business but should include leads as well (Sharon, Jeff, Megan). I will have that sent off today and we can start to look at resolution.

mstanton1 commented 3 months ago

I have sent a list to Sharon, Jeff and Megan advising of the issue and requesting the businesses support in re-charging. I only included lines which showed a 0$ total and 0$ paid value in the screenshot above.

MHR Registration Number Invoice Number Date Dat Number BCOL Account 108126 4456934 2023-12-28 18:15:45.057196
108127 4460853 2023-12-28 23:59:58.686761
108128 4461010 2023-12-29 00:22:02.135383
108129 4461030 2023-12-29 00:24:59.664338
108130 4466571 2023-12-29 21:55:39.38915
108140 4484279 2024-01-03 19:24:14.186806
108141 4484313 2024-01-03 19:26:34.506814
108142 4484353 2024-01-03 19:28:35.928714
108148 4488604 2024-01-04 00:08:03.205867
108150 4495513 2024-01-04 21:45:27.166861
108151 4495532 2024-01-04 21:46:57.26416
108152 4495540 2024-01-04 21:48:12.07327
108153 4495561 2024-01-04 21:49:59.659968
108154 4495574 2024-01-04 21:51:08.739186
108158 4579705 2024-01-17 18:10:08.004656
108160 4588954 2024-01-18 19:07:23.807842
108161 4592212 2024-01-18 22:51:43.709519
108163 4596351 2024-01-19 17:34:15.480517
108164 4596380 2024-01-19 17:36:02.646084
108165 4596410 2024-01-19 17:38:17.975147
108174 4629043 2024-01-24 19:48:06.348241
108175 4632304 2024-01-24 23:28:38.685431
108176 4633005 2024-01-25 00:18:02.841862
108186 4640717 2024-01-25 22:07:58.213296 C1038657 274888 108187 4640885 2024-01-25 22:22:59.484691 C1038657 274888 108188 4640959 2024-01-25 22:28:27.904987 C1038657 274888 108189 4641008 2024-01-25 22:31:52.101629 C1038657 274888 108192 4642486 2024-01-26 00:07:39.939551

mstanton1 commented 3 months ago

@seeker25 we met with the business and in the further discussion they noted a number of initial registrations are sent in from Service BC offices. In those cases they would choose the 'No Fee' option in the staff payment modal since payment was collected at Service BC. @doug-lovett mentioned there is a waive_fee flag. Are you able to update the query you'd run above to also include the waive_fee flag?

cc @arlentees @lbergero819

lbergero819 commented 3 months ago

@arlentees @mstanton1 the payment reversal has been completed by Sharon successfully.

seeker25 commented 3 months ago

We'll have to reconcile this later with PAY.

Technically by manually adjusting in BCOL there are monetary transactions in BCOL that don't exist in PAY and no invoice reference that ties from PAY to BCOL I believe. (KEY column, doesn't have REG in it in COLIN)

Also there isn't any linkage in MHR/PPR -> Pay (besides the linkage to a $0 invoice, perhaps if we could update the invoice to the correct value it would match up, but we're still missing an invoice reference from PAY -> BCOL).

@pstemkens probably something we need to look at later -^

pstemkens commented 3 months ago

@seeker25 it looks like the fee code MHR400 is not configured correctly on the mainframe. Its showing as a negative. I checked a few other fee codes and none of them show as a negative. CC- @mstanton1 @arlentees @lbergero819

mstanton1 commented 3 months ago

@pstemkens what is the impact of the MHR400 being configured as a negative? A good comparison for how this should work would be MHR Search or one of the PPR codes. I could provide a fee code for comparison in the new system if needed?

lbergero819 commented 3 months ago

@pstemkens @mstanton1 @arlentees @lbergero819 This is not a negative, this is just the way it displays when setting a fix rate. All the legacy fee codes are like that. MHR400 is used when the transaction is done from the green screens and works fine. The new system uses variable rates and displays as 0.00 for rate. Just had a chat with Patty about this and it is fine.

mstanton1 commented 3 months ago

@lbergero819 @pstemkens it seems like there might be a need to meet and discuss. I'm not actually sure where MHR400 would play in. We are using the code INREG in the modernized system and I lack the knowledge of how that maps back to the legacy system. With this ticket becoming unwieldly I'd recommend we look at breaking it down into a few smaller tickets with clear objectives so we can reduce the number of comments in one spot.

For this ticket the first issue was the use of a code that wasn't collecting fees. INREG is activated and being used again. The two other pieces that could possibly be logged separately in my opinion are: 1) Verifying BCOL mapping for INREG 2) Ensuring past transactions using INREG have all incurred a fee / collecting fees on any that are outstanding.

seeker25 commented 3 months ago

I think we only use BMHRVC1,CMHRVC1,BMHRVC2,BMHRVC3 from pay-api

lbergero819 commented 3 months ago

@lbergero819 @pstemkens @seeker25 @arlentees MHR400 is only used when the filing is done from the mainframe and is working fine. As recommended by Melissa above, I will create 2 new tickets: 1) for Verifying BCOL mapping for INREG and 2) Ensuring past transactions using INREG have all incurred a fee / collecting fees on any that are outstanding.

lbergero819 commented 3 months ago

@mstanton1 @arlentees Created ticket 19652 - Verifying BCOL mapping for INREG. Created ticket 19653 - Verify past transactions using INREG that are outstanding and apply fee if required. I will enter more details/info on the tickets and schedule a meeting to review next steps.

bcgov / entity

Payment does not show in BCOL for Registration in Modernized System #19532