System ‘A’ Integration

CBW 2010001: Account Customers with Negative MTD‘Awarded Credit’ Balances

Reporting Supplementary Specification

System ‘A’ Integration

Supplementary Specifications: Account Customers with Negative MTD ‘Awarded Credit’ Balances

Supplementary Specification:CBW 2010 001 - Account Customers with Negative MTD‘Awarded Credit’ Balances

Report Description – Purpose:

The report gives identifying information about those individuals and businesses that have had a MTD ‘Awarded Credit’ balance go below zero (infraction). By monitoring this report, management may potentially identify customers who are not demonstrating good customer behaviors.

Additional Report Assumptions:

  • The System ‘A’ will provide the necessary data for reporting requirements, including customer names.
  • All necessary security will be in place, since the report will display SSN/EINs. The Line of Business (LOB) will determine who will be allowed access to the report.
  • This report will allow DATE as an input parameter. When run, the default initial date will be the one most currently available.
  • A business EIN is the same length as a person’s SSN. Since they serve the same function, it will be acceptable to store in a single field.

General Notes:

  • Infraction count only includes those within the past 6-months of the report date. Older infractions will not be included.
  • Start Negative Balance Date is the most recent date the account went into a negative balance. It is not bound by a 6-month look-back period.
  • An infraction count of zero means the account has been in a negative balance status for more than 6 months, and there has been no additional activity to generate additional awarded credits.
  • An infraction count that is null means the account has never been in a negative balance status.
  • While a customer may have had a balance swing from non-negative to negative values multiple times, only the most current negative date will be displayed with the count from the rolling 6-month timeframe.
  • Include Report ID ‘CBW 2008 001’ at left-hand bottom of report page.

Special Considerations:

  • Only accounts that are in ‘Activated’ status will be included.
  • Allow mm/dd/yyyy input for date. Default is most currently available date in reporting environment.

Data Refresh:

Real time Daily Weekly Monthly Quarterly Other ____

Retention:

None days weeks months 2 years Other TBD.

Related to another Report/View:

No Yes

Report Exhibits:

‘Mock-up’


Report Layout:

ID / Element / Format Requirements / Display / Notes
H1 / Heading / Bold, Arial 12 pt / Accounts with Negative Awarded Credit Balances
H2 / Heading / Bold, Arial 10 pt / As of: MM/DD/YYYY / Parameter
C1 / Column Heading / Arial, 10 point / TaxID
C2 / Column Heading / Arial, 10 point / Name
C4 / Column Heading / Arial, 10 point, Accounting / Awarded Credits Balance
C5 / Column Heading / Arial, 10 point, m/dd/yyyy / Start Negative Bal Date
C6 / Column Heading / Arial, 10 point, integer / Infraction Count

Business Logic:

As of a chosen input date:

If the available points for an ‘Activated’ Point Account is negative, look for the most recent date that the account was zero or positive. The following day (which will then have a negative balance) will be the start date for the negative balance.

Subtract 6 months from the chosen input date. Count the number of times the available points has dipped from a non-negative balance to a negative balance. If the account has had a negative balance for more than six months, the infraction count will be zero.

Developer Logic:

Default ‘As of Date’ is the most currently available day’s data.
If PtAcctSummary.TransDate = AsOfDate
Then
If PtAcctSummary.PtsAvail < 0
Then
If CustType = ‘Consumer’ then
Display CustDetail.PtAcctOwnLastName, CustDetail.PtAcctLastName, PtAcctOwnSSN_EIN
Else
Display CustDetail.BusnName, PtAcctOwnSSN_EIN
Set LastNegPtDate = Most recent occurrence date where PtAcctSummary.PtsAvail < 0
If LastNegPtDate <= AsOfDate – 6 months
Then
Step through each date from LastNegPtDate through AsOfDate and count the number of times (incidentCount) that the balance goes from non-negative to negative.
Otherwise set IncidentCount = 0, because the negative balance is more than 6 months previous.
Otherwise, the account is in good standing
Otherwise, the PtAcctSummary.TransDate is not the date to check. /

Type of Reporting Solution:MSRS Smart Report SharePoint

Print Requirements: None specified

Export Requirements: / HTML / Excel / Web archive / PDF / TIFF / CSV / XML

Testing Procedures:

ID / Test Description: / Expected Results: / Dev Check / Considerations:
T0 / Activated account with no infractions during lifetime of account. / Account information will not appear on report.
T1 / Activated account with 1 infraction outside of the 6-month look-back period. / Account information will appear on report. Start Negative Date = most recent infraction date. Infraction Count = 0.
T2 / Activated account with multiple infractions during lifetime of account, with 1 during look-back period. / Account information will appear on report, with Start Negative Date = look-back period infraction date. Infraction Count = 1.
T3 / Activated account with multiple infractions during lifetime of account, with multiple during look-back period. / Account information will appear on report, with Start Negative Date = most recent look-back period infraction date. Infraction Count = number of infractions during look-back period.

Revision History:

Name / Date / Reason For Changes / Version
Korki Whitaker / Initial Version / 1.0

Approved By:

Name: / Title: / Method of Approval / Date of Approval

ConfidentialPage 19/16/2018