fusion phoenix year end table mapping
TRANSCRIPT
Fusion Phoenix
Year End Table Mapping
November 2020
Copyright © 2020 Finastra International Limited, or a member of the Finastra group of companies (“Finastra”). All Rights Reserved. Confidential: Limited Distribution to Authorized Persons Only, pursuant to the terms of the license agreement by which you were granted a license from Finastra for the applicable software or services and this documentation. Republication or redistribution, in whole or in part, of the content of this documentation or any other materials made available by Finastra is prohibited without the prior written consent of Finastra. The software and documentation are protected as unpublished work and constitute a trade secret of Finastra International Limited, or a member of the Finastra group of companies, Head Office: 4 Kingdom Street, Paddington, London W2 6BD, United Kingdom.
Trademarks Finastra, Fusion Phoenix, and their respective sub-brands, and the logos used with some of these marks, are trademarks or registered trademarks of Finastra International Limited, or a member of the Finastra group of companies (“Finastra”) in various countries around the world. All other brand and product names are trademarks, registered trademarks, or service marks of their respective owners, companies, or organizations, may be registered, and should be treated appropriately.
Disclaimer Finastra does not guarantee that any information contained herein is and will remain accurate or that use of the information will ensure correct and faultless operation of the relevant software, services or equipment. This document contains information proprietary to Finastra. Finastra does not undertake mathematical research but only applies mathematical models recognized within the financial industry. Finastra does not guarantee the intrinsic theoretical validity of the calculation models used.
Finastra, its agents, and employees shall not be held liable to or through any user for any loss or damage whatsoever resulting from reliance on the information contained herein or related thereto. The information contained in this document and the general guidance of Finastra staff does not take the place of qualified compliance personnel or legal counsel within your institution. FINASTRA CANNOT RENDER LEGAL, ACCOUNTING OR OTHER PROFESSIONAL SERVICES TO YOUR INSTITUTION. THE INFORMATION CONTAINED HEREIN IS GENERAL IN NATURE AND DOES NOT CONSTITUTE LEGAL ADVICE OR A LEGAL OPINION. CONSULT YOUR LEGAL COUNSEL FOR LEGAL ADVICE SPECIFIC TO YOUR SITUATION OR CIRCUMSTANCES OR TO ANSWER ANY LEGAL QUESTIONS.
This document is not intended as a substitute for formal education in the regulatory requirements of banking, banking operations, lending, lending operations, or other topics generally applicable to financial institutions. Your financial institution is solely responsible for configuring and using the software or services in a way that meets policies, practices, and laws applicable to your institution, including, without limitation: (1) options and selections made on prompts; (2) entries in the software program; (3) program setup; and (4) documents produced by the software or services. It is the obligation of the customer to ensure that responsible decisions are taken when using Finastra products. Information in this document is subject to change without notice and does not represent a commitment on the part of Finastra.
Feedback Do you have comments about our guides and online help? Please address any comments and questions to your local Finastra representative.
Need more information? Read more about our products at http://www.finastra.com or contact your local Finastra office at http://www.finastra.com/contact.
Printed to PDF on 11/24/2020.
© Finastra | Fusion Phoenix | Year End Table Mapping i
CONTENTS OVERVIEW ________________________________________________________________ 1
DEPOSIT ACCOUNT YEAR-END TABLES _______________________________________ 2
1099-INT - Interest Paid/Bond Interest Paid 2
Selection Criteria 3
Task Information 4
Table Mapping [Payer] 4
Table Mapping [Recipient] 5
1099-OID - Interest Earned (Deposit) 11
Selection Criteria 11
Task Information 12
Table Mapping [Payer] 13
Table Mapping [Recipient] 14
1042-S - Interest Paid/Bond Interest Paid 19
Selection Criteria 19
Task Information 22
Table Mapping [Withholding Agent Information] 22
Table Mapping [Recipient] 23
RETIREMENT PLAN YEAR-END TABLES _______________________________________ 33
1099-R - Retirement Plan Distributions 33
Selection Criteria 33
Task Information 34
Table Mapping [Payer] 34
Table Mapping [Recipient] 35
1099-SA - Retirement Plan Distributions 43
Selection Criteria 43
Task Information 44
Table Mapping [Trustee] 44
Table Mapping [Recipient] 45
1099-Q - ESA Retirement Plan Distributions 51
Selection Criteria 51
Task Information 51
© Finastra | Fusion Phoenix | Year End Table Mapping ii
Table Mapping [Payer] 51
Table Mapping [Recipient] 52
5498 - Retirement Plan Summary 56
Selection Criteria for Address 56
Selection Criteria for Accounts 59
Task Information 62
Table Mapping [Trustee] 62
Table Mapping [Participant] 64
5498-SA - Retirement Plan Summary 74
Selection Criteria 74
Task Information 74
Table Mapping [Trustee] 74
Table Mapping [Participant] 76
5498-ESA - Retirement Plan Summary 80
Selection Criteria 80
Task Information 81
Table Mapping [Trustee] 81
Table Mapping [Participant] 82
LOAN ACCOUNT YEAR-END TABLES _________________________________________ 86
1098 - All Accounts 86
Selection Criteria 86
Task Information 88
Table Mapping [Borrower] 88
Table Mapping [Recipient] 89
1098-E - All Accounts 97
Selection Criteria 97
Task Information 98
Table Mapping [Trustee] 98
Table Mapping [Participent] 100
1099-A - Secured Property 104
Selection Criteria 104
Task Information 105
Table Mapping [Lender] 105
© Finastra | Fusion Phoenix | Year End Table Mapping iii
Table Mapping [Borrower] 106
1099-C - Cancellation of Debt 110
Selection Criteria 110
Task Information 111
Table Mapping [Creditor] 111
Table Mapping [Debtor] 112
1099-MISC - Miscellaneous Income 116
Selection Criteria 116
Task Information 116
Table Mapping [Payer] 117
Table Mapping [Recipient] 118
FINASTRA SUPPORT______________________________________________________ 122
© Finastra | Fusion Phoenix | IRS Tables 1
Overview Fusion Phoenix provides all standard year-end processing functions, including general ledger closeout and tax reporting, for the accounts processed in Fusion Phoenix.
The year-end process occurs in the following stages:
1. First nightly process to the close of the current business day. The fiscal year-end closeout executes during this nightly process with fiscal years ending 12/31 and populate the IRS Tables.
2. The year-end process runs.
3. Second nightly process runs to prepare for the next processing date.
4. In-house printing of year-end customer/member tax statements.
5. Outsourced printing and mailing of year-end customer/member tax statements through Finastra.
Note: Clients using Finastra to print and mail tax statements must now generate a standard FIRE file format. This change by our print partner to use the FIRE file format may result in additional mailing envelopes and postage expense.
© Finastra | Fusion Phoenix | IRS Tables 2
Deposit Account Year-End Tables • 1099-INT - Interest Paid/Bond Interest Paid • 1099-OID - Interest Earned (Deposit) • 1042-S - Interest Paid/Bond Interest Paid
1099-INT - Interest Paid/Bond Interest Paid The 1099int task generates all the information required to produce IRS form 1099-INT. The file contains the following information for each interest bearing, non-retirement deposit account and escrow account that received interest during the current tax year, and for each customer/member that redeemed savings bonds or I bonds.
Report when:
• Pay at least $10.00 in interest, or bond interest (Below Minimum defined in System Administration). • Any withholding regardless on interest paid. • Any foreign tax paid or withheld. • Exclude OID interest and report on 1099-OID.
Exceptions:
• Corporations (not supported by Fusion Phoenix) • Tax-exempt Organizations (not supported by Fusion Phoenix) • Retirement Plans • US Agency (not supported by Fusion Phoenix) • US state (not supported by Fusion Phoenix) • District of Columbia • US possession (not supported by Fusion Phoenix) • Registered securities or commodities dealer (not supported by Fusion Phoenix)
© Finastra | Fusion Phoenix | IRS Tables 3
Selection Criteria For Deposit Accounts
RM_ACCT.tin_format = E (EIN), A (ATIN), C (Custom), or X (Tax Exempt), or (S with W8 Not Selected (null/N) ) and RM_ACCT.tin_cert = ‘Y’ or ‘N’ and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘N’ and DP_ACCT.third_party_irs_int = ‘N’ and DP_ACCT_INT_OPT.int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 RM_ACCT.tin_format = X and RM_ACCT.tin_cert = ‘N’ and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘N’ and DP_ACCT.third_party_irs_int = ‘N’ and DP_ACCT_INT_OPT.int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 RM_ACCT.tin_format = X and RM_ACCT.tin_cert = ‘Y’ and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘N’ and DP_ACCT.third_party_irs_int = ‘N’ and DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0
For Loan Accounts
RM_ACCT.tin_format = E (EIN), A (ATIN), C (Custom) or X (Tax Exempt) or (S with W8 Not Selected (null/N) ) and RM_ACCT.tin_cert = ‘Y’ or ‘N’ and LN_ESCROW.cr_int_pd_ytd > 0 or LN_ESCROW.int_wh_ytd > 0 RM_ACCT.tin_format = X and RM_ACCT.tin_cert = ‘N’ and LN_ESCROW.cr_int_pd_ytd > 0 or LN_ESCROW.int_wh_ytd > 0
© Finastra | Fusion Phoenix | IRS Tables 4
RM_ACCT.tin_format = X and RM_ACCT.tin_cert = ‘Y’ and LN_ESCROW.int_wh_ytd > 0
For Bond Interest
RM_ACCT.tin_format = E (EIN), A (ATIN), C (Custom) or X (Tax Exempt) or (S with W8 Not Selected (null/N) ) and RM_ACCT.tin_cert = ‘Y’ or ‘N’ and RM_DISPLAY.bond_int_pd_ytd > 0 or RM_DISPLAY.ibond_int_pd_ytd > 0 RM_ACCT.tin_format = X and RM_ACCT.tin_cert = ‘N’ and RM_DISPLAY.bond_int_pd_ytd > 0 or RM_DISPLAY.ibond_int_pd_ytd > 0
Task Information
Form Description Task Task_id Stored Proc
1099-INT Interest Income 1099-INT System Generation 591 psp_irs_1099int
Table Mapping [Payer]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 5
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099int irs_recipient
Tax Year 20XX Int (4) irs_1099int.tax_year
TIN Type Char (1) irs_1099int.tin_type
Federal Tax ID Recipient ID
Varchar (15) RM _ACCT.tin irs_1099int.tin
If irs_1099int.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type=NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_2
Street 1 Address Char (40) If RM_ADDRESS.international=N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international =Y Select RM_ADDRESS.address_line_1
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international=N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_2
© Finastra | Fusion Phoenix | IRS Tables 6
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099int irs_recipient If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international =Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
Street 3 Address Char (40) If irs_1099int.rim_no IS NULL Irs_recipient.Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international=N RM_ADDRESS.city (25) If RM_ADDRESS.international =Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international=N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char(9) If RM_ADDRESS.international=N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099int.rim_no IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international= Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international= Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international= Y AD_GB_COUNTRY.description (25)
If irs_recipient.international= Y irs_recipient.country_code (2)
© Finastra | Fusion Phoenix | IRS Tables 7
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099int irs_recipient Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
Else blank
Account # Account Nbr
Char (20) DP_DISPLAY.acct_type DP_DISPLAY.acct_no or RM_ACCT.rim_no or LN_ESCROW.acct_type LN_ESCROW.acct_no
irs_1099int.acct_type + irs_1099int.acct_no
Interest Income
1 Decimal (9) DP_ACCT_INT_OPT.int_ytd + DP_DISPLAY2.bonus_pd_ytd or LN_ESCROW.cr_int_pd_ytd
irs_1099int.int_income
Early Withdrawal Pnlty
2 Decimal (9) DP_DISPLAY.pen_amt_ytd irs_1099int.early_withdrawal_penalty
Interest on US Savings Bonds & Treas. Obligations
3 Decimal (9) RM_DISPLAY.bond_int_pd_ytd + RM_DISPLAY.ibond_int_pd_ytd
irs_1099int.bond_int
Fed Income Tax Withhld
4 Decimal (9) DP_ACCT_INT_OPT.fed_int_wh_ytd or LN_ESCROW.int_wh_ytd
irs_1099int.fed_income_tax_whld
Investment Expense
5 Decimal (9) N/A irs_1099int.investment_expense
Foreign Tax Paid
6 Decimal (9) N/A irs_1099int.foreign_tax_paid
© Finastra | Fusion Phoenix | IRS Tables 8
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099int irs_recipient
Foreign Country or U.S. Possession
7 Char (40) N/A irs_1099int.foreign_country_name
Tax-Exempt Interest
8 Decimal (9) N/A irs_1099int.tax_exmpt_int
Specific Private Activity Bond Interest
9 Decimal (9) N/A irs_1099int.bond_int_priv_actvty
Market Discount 10 Decimal (9) N/A irs_1099int.market_discount
Bond Premium 11 Decimal (9) N/A irs_1099int.bond_premium
Bond Premium on Treasury Obligations
12 Decimal (9) N/A irs_1099int.bond_prem_tres_oblg
Bond Premium on Tax-Exempt Bond
13 Decimal (9) N/A irs_1099int.bond_prm_tax_exmpt
Tax-Exempt and Tax Credit Bond CUSIP No.
14 Varchar (13) N/A irs_1099int.cusip_no
© Finastra | Fusion Phoenix | IRS Tables 9
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099int irs_recipient
State 15 Varchar (30) If DP_ACCT_INT_OPT.state_int_wh_ytd > 0.00 populate RM_ADDRESS.state If more than 1 address exists where RM_ADDRESS.addr_of_residence=Y Order by RM_ADDRESS.addr_id and select RM_ADDRESS.state from the lowest RM_ADDRESS.addr_id Or if no address exists where RM_ADDRESS.addr_of_residence = Y select RM_ADDRESS.state where GB_ACCT_NOT_ADDR.rpt_id=-101 And GB_ACCT_NOT_ADDR.addr_id = RM_ADDRESS.addr_id And RM_ADDRESS.status not= Closed If GB_ACCT_NOT_ADDR.rpt_id = -101 does not exist Then select RM_ADDRESS.state where GB_ACCT_NOT_ADDR.rpt_id = -100 And GB_ACCT_NOT_ADDR.addr_id = RM_ADDRESS.addr_id And RM_ADDRESS.status not= Closed If RM_ADDRESS.status = Closed Where GB_ACCT_NOT_ADDR.addr_id = RM_ADDRESS.addr_id Then select RM_ADDRESS.state Where RM_ADDRESS.addr_id= 1 Modified 10/09/2012 CR 19572 Add criteria for updating psp_ye_rim and address for state
irs_1099int.state
State Identification No.
16 Char (2) N/A irs_1099int.state_code
© Finastra | Fusion Phoenix | IRS Tables 10
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099int irs_recipient
State Income Tax Withheld
17 Decimal (9) DP_ACCT_INT_OPT.state_int_wh_ytd irs_1099int.state_income_tax_whld
FATCA Filing Requirement
Char (1) irs_1099int.fatca_req
2nd TIN Notice Char (1) irs_1099int.second_tin_notice
Filing Status Varchar (25) irs_1099int.filing_status
© Finastra | Fusion Phoenix | IRS Tables 11
1099-OID - Interest Earned (Deposit) The 1099oid task generates all the information required to produce Form 1099-OID. The file contains the following information for each interest-bearing time-deposit account that has the OID check box selected, and that accrued interest in the current tax year.
Report when:
• Pay at least $10.00 in interest, or bond interest (Below Minimum can be configured by the FI in Phoenix System Admin). • Any withholding regardless on interest accrued. • Any foreign tax paid or withheld.
Exceptions:
• Corporations (not supported by Fusion Phoenix) • Tax-exempt organizations (not supported by Fusion Phoenix) • Retirement Plans • US Agency (not supported by Fusion Phoenix) • US state (not supported by Fusion Phoenix) • District of Columbia (not supported by Fusion Phoenix) • US possession (not supported by Fusion Phoenix) • Registered securities or commodities dealer (not supported by Fusion Phoenix)
Selection Criteria RM_ACCT.tin_format = E (EIN), A (ATIN), C (Custom), or X (Tax Exempt), or (S with W8 Not Selected (null/N) )
and RM_ACCT.tin_cert = (‘Y’ or ‘N’ ) and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘Y’ and DP_ACCT.third_party_irs_int = ‘N’ and DP_DISPLAY.accr_cr_int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 or DP_ACCT_INT_OPT.int_ytd > 0 RM_ACCT.tin_format = X and RM_ACCT.tin_cert = ‘N’ and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘Y’
© Finastra | Fusion Phoenix | IRS Tables 12
and DP_ACCT.third_party_irs_int = ‘N’ and DP_DISPLAY.accr_cr_int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 or DP_ACCT_INT_OPT.int_ytd > 0 RM_ACCT.tin_format = X and RM_ACCT.tin_cert = ‘Y’ and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘Y’ and DP_ACCT.third_party_irs_int = ‘N’ and DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_ACCT_INT_OPT.int_ytd > 0 AD_RM_CONTROL.irs_1042s_reporting = ‘Canadians Only’ and RM_ACCT.w8_cert = (‘Y’ or ‘N’) and RM_ACCT.w8_country_code != CA and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘Y’ and DP_ACCT.third_party_irs_int = ‘N’ and DP_DISPLAY.accr_cr_int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 or DP_ACCT_INT_OPT.int_ytd > 0
Task Information
Form Description Task Task_id Stored Proc
1099-OID Original Issue Discount
1099-OID System Generation
592 psp_irs_1099oid
© Finastra | Fusion Phoenix | IRS Tables 13
Table Mapping [Payer]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 14
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099oid irs_recipient
Tax Year 20XX
Int (4) Irs_1099oid.tax_year
TIN Type Char (1) irs_1099oid.tin_type
Federal Tax ID
Recipient ID
Varchar (15) RM_ACCT.tin Irs_1099oid.tin
If irs_1099oid.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099oid.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099oid.rim_no IS NULL irs_recipient.Name_2
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1099oid.rim_no IS NULL irs_recipient.Address_line_1
© Finastra | Fusion Phoenix | IRS Tables 15
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099oid irs_recipient
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099oid.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099oid.rim_no IS NULL Irs_recipient.Address_line_3
US/Foreign City
Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099oid.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099oid.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099oid.rim_no IS NULL irs_recipient.zip
Foreign State/ Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10)
If irs_recipient.international= Y irs_recipient.zip
© Finastra | Fusion Phoenix | IRS Tables 16
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099oid irs_recipient
Else blank Else blank
Foreign Country
Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr
Char (20) DP_DISPLAY.acct_type - DP_DISPLAY.acct_no irs_1099oid.acct_type + irs_1099oid.acct_no
Original Issue Discount
1 Decimal (9) DP_DISPLAY.accr_cr_int_ytd - DP_ACCT_INT_OPT.int_ytd
irs_1099oid.orginal_issue_disc
Other Periodic Interest
2 Decimal (9) DP_ACCT_INT_OPT.int_ytd + DP_DISPLAY2.bonus_pd_ytd
irs_1099oid.other_periodic_int
Early Withdrawal Penalty
3 Decimal (9) DP_DISPLAY.pen_amt_ytd irs_1099oid.early_wd_penalty
Federal Income Tax Withheld
4 Decimal (9) DP_ACCT_INT_OPT. backup_wh_ytd + DP_ACCT_INT_OPT.fed_int_wh_ytd
irs_1099oid.fed_income_tax_whld
Market Discount
5 Decimal (9) N/A irs_1099oid.market_disc
Acquisition Premium
6 Decimal (9) N/A irs_1099oid.acquisition_prem
Description 7 Char (39) CD irs_1099oid.description
OID on U.S. Treasury Obligations
8 Decimal (9) N/A irs_1099oid.org_iss_disc_us_treas
© Finastra | Fusion Phoenix | IRS Tables 17
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099oid irs_recipient
Investment Expenses
9 Decimal (9) N/A irs_1099oid.invest_expenses
Bond Premium
10 Decimal (9) N/A irs_1099oid.bond_premium
Tax-Exempt OID
11 Decimal (9) N/A irs_1099oid. tax_exempt_oid
State 12 Char (2) If DP_ACCT_INT_OPT.state_int_wh_ytd > 0.00 populate RM_ADDRESS.state If more than 1 address exists where RM_ADDRESS.addr_of_residence = Y Order by RM_ADDRESS.addr_id and select RM_ADDRESS.state from the lowest RM_ADDRESS.addr_id Or if no address exists where RM_ADDRESS.addr_of_residence = Y select RM_ADDRESS.state where GB_ACCT_NOT_ADDR.rpt_id = -101 And GB_ACCT_NOT_ADDR.addr_id = RM_ADDRESS.addr_id And RM_ADDRESS.status not= Closed If GB_ACCT_NOT_ADDR.rpt_id = -101 does not exist Then select RM_ADDRESS.state where GB_ACCT_NOT_ADDR.rpt_id = -100 And GB_ACCT_NOT_ADDR.addr_id = RM_ADDRESS.addr_id And RM_ADDRESS.status not= Closed If RM_ADDRESS.status = Closed Where GB_ACCT_NOT_ADDR.addr_id = RM_ADDRESS.addr_id Then select RM_ADDRESS.state
irs_1099oid.state
© Finastra | Fusion Phoenix | IRS Tables 18
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099oid irs_recipient
Where RM_ADDRESS.addr_id = 1
State Identification No.
13 Char (2) N/A irs_1099oid.state_ident_no
State Income Tax Withheld
14 Decimal (9) DP_ACCT_INT_OPT.state_int_wh_ytd irs_1099oid.state_tax_whld
FATCA Filing Requirement
Char (1) irs_1099int.fatca_req
2nd TIN Notice
Char (1) irs_1099int.second_tin_notice
Filing Status Varchar (25) irs_1099int.filing_status
© Finastra | Fusion Phoenix | IRS Tables 19
1042-S - Interest Paid/Bond Interest Paid The 1042s task generates all the information required to produce form 1042-S. The file contains the following information for each interest-bearing, non-retirement deposit account and escrow account, held by a foreign customer/member who received interest during the current tax year.
Report when
• Pay any interest, or bond interest for Canadians or where AD_RM_CONTROL. irs_1042s_reporting = All Foreign. • Any tax withheld for a W-8 customer/member regardless of the value of AD_RM_CONTROL. irs_1042s_reporting • Includes OID interest paid and not reported on Form 1099-OID.
Selection Criteria For Deposit Accounts
If AD_RM_CONTROL.irs_1042S_reporting = ‘Canadians Only’, SELECT all RM_ACCT.tin_format = ‘N’ or ‘I’ or (S with W8 Flag selected (Y)) and RM_ACCT.W8_country_code = PC_COUNTRY.ptid and PC_COUNTRY.w8_country_code = ‘CA’ and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘N’ and DP_ACCT.third_party_irs_int = ‘N’ DP_ACCT_INT_OPT.int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 or
If AD_RM_CONTROL.irs_1042S_reporting = ‘Canadians Only’, SELECT all RM_ACCT.tin_format = ‘N’ or ‘I’ or (S with W8 Flag selected (Y)) and RM_ACCT.W8_country_code = PC_COUNTRY.ptid and PC_COUNTRY.w8_country_code = ‘CA’’ and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘Y’ and DP_ACCT.third_party_irs_int = ‘N’ DP_DISPLAY.accr_cr_int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_pytd > 0 or
© Finastra | Fusion Phoenix | IRS Tables 20
DP_ACCT_INT_OPT.int_ytd > 0 If AD_RM_CONTROL.irs_1042S_reporting = ‘All Foreign’, SEELCT all RM_ACCT.tin_format = ‘N’ or ‘I’ or (S with W8 Flag selected (Y)) and RM_ACCT.W8_country_code = PC_COUNTRY.ptid and RM_ACCT.w8_country_code is not NULL and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘N’ and DP_ACCT.third_party_irs_int = ‘N’ DP_ACCT_INT_OPT.int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 If AD_RM_CONTROL.irs_1042S_reporting = ‘All Foreign’,
SELECT all RM_ACCT.tin_format = ‘N’ or ‘I’ or (S with W8 Flag selected (Y)) and RM_ACCT.W8_country_code = PC_COUNTRY.ptid and RM_ACCT.w8_country_code is not NULL and DP_ACCT.ira = ‘N’ and DP_ACCT.oid = ‘Y’ and DP_ACCT.third_party_irs_int = ‘N’ DP_DISPLAY.accr_cr_int_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 or DP_ACCT_INT_OPT.int_ytd > 0
If the following applies, a single 1042s row should be written to the irs_1042s table:
If DP_ACCT.oid = Y and DP_DISPLAY.accr_cr_int_ytd > 0 and DP_ACCT_INT_OPT.int_ytd = 0 and DP_DISPLAY2.bonus_pd_ytd = 0 and DP_ACCT_INT_OPT.backup_wh_ytd = 0 and DP_ACCT_INT_OPT.fed_int_wh_ytd = 0
Row writes with:
© Finastra | Fusion Phoenix | IRS Tables 21
• Income Code = 30 • Gross Income = DP_DISPLAY.accr_cr_int_ytd
If the following applies, two 1042s row should be written to the irs_1042s table:
If DP_ACCT.oid = Y and DP_DISPLAY.accr_cr_int_ytd > 0 and (DP_ACCT_INT_OPT.int_ytd > 0 or DP_DISPLAY2.bonus_pd_ytd > 0 or DP_ACCT_INT_OPT.backup_wh_ytd > 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd > 0)
One row writes with:
• Income Code = 30 • Gross Income = DP_DISPLAY.accr_cr_int_ytd - DP_ACCT_INT_OPT.int_ytd
Another row writes with:
• Income Code = 29 • Gross Income = DP_ACCT_INT_OPT.int_ytd + DP_DISPLAY2.bonus_pd_pytd • Federal Withholding = DP_ACCT_INT_OPT.backup_wh_ytd + DP_ACCT_INT_OPT.fed_int_wh_ytd
For Loan Accounts
If AD_RM_CONTROL.irs_1042S_reporting = ‘Canadians Only’, SELECT all RM_ACCT.tin_format = ‘N’ or ‘I’ or (S with W8 Flag selected (Y)) and RM_ACCT.W8_country_code = PC_COUNTRY.ptid and PC_COUNTRY.w8_country_code = ‘CA’ and LN_ESCROW.cr_int_pd_ytd > 0 or LN_ESCROW.int_wh_ytd > 0 If AD_RM_CONTROL.irs_1042S_reporting = ‘All Foreign’, SELECT all RM_ACCT.tin_format = ‘N’ or ‘I’ or (S with W8 Flag selected (Y)) and RM_ACCT.W8_country_code = PC_COUNTRY.ptid and PC_COUNTRY.w8_country_code is not NULL and LN_ESCROW.cr_int_pd_ytd > 0 or LN_ESCROW.int_wh_ytd > 0
© Finastra | Fusion Phoenix | IRS Tables 22
All NRAs reported for bond interest:
SELECT all RM_ACCT.tin_format = ‘N’ or ‘I’ and RM_ACCT.W8_country_code = PC_COUNTRY.ptid and RM_ACCT.w8_country_code is not NULL RM_DISPLAY.bond_int_pd_ytd > 0 or RM_DISPLAY.ibond_int_pd_ytd > 0
Task Information
Form Description Task Task_id Stored Proc
1042-S Foreign Person's U.S. Source Income Subject to Withholding
1042-S System Generation
599 psp_irs_1042s
Table Mapping [Withholding Agent Information]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 23
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Tax Year 20XX Int (4) irs_1042s.tax_year
TIN Type N/A Char (1) If tin_format = ‘I’, populate as 1; Else (i.e. tin_format = ‘N’, populate as 0 Note: TIN type check box (SSN or ITIN).
irs_1042s.tin_type
TIN Varchar (15) RM_ACCT.tin irs_1042s.tin
Name 1 Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1042s.rim_no IS NULL irs_recipient.Name_1
Street 1 c Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1042s.rim_no IS NULL irs_recipient.address_line_1
© Finastra | Fusion Phoenix | IRS Tables 24
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Street 2 Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank Note: Additional address line no longer provided for recipient effective 2014.
If irs_1042s.rim_no IS NULL irs_recipient.address_line_2
Street 3 Char (40) If irs_1042s.rim_no IS NULL irs_recipient.address_line_3
US/Foreign City d Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1042s.rim_no IS NULL irs_recipient.city
US State d Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state Else blank
If irs_1042s.rim_no IS NULL irs_recipient.state
US ZIP d Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1042s.rim_no IS NULL irs_recipient.zip
© Finastra | Fusion Phoenix | IRS Tables 25
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Foreign Country Name
d Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
Foreign Country Code
d Char (2) If RM_ADDRESS.international = Y And AD_GB_COUNTRY.w8_country_code is Not NULL Select PC_COUNTRY.w8_country_code Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code And AD_GB_COUNTRY.w8_country_code = PC_COUNTRY.ptid If If RM_ADDRESS.international = Y And AD_GB_COUNTRY.w8_country_code is NULL, select AD_GB_COUNTRY.iso_code Else blank
Foreign Province
d Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Elese blank
Foreign Postal Code
Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
Recipient Account #
13k Char (15) DP_ACCT_INT_OPT.acct_type DP_ACCT_INT_OPT.acct_no
irs_1042s.acct_type + irs_1042s.acct_no
© Finastra | Fusion Phoenix | IRS Tables 26
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Income Code 1 Char (2) Deposit Interest or Escrow Interest ‘29’ if DP_ACCT_INT_OPT.int_ytd or LN_ESCROW.cr_int_pd_ytd or DP_DISPLAY2.bonus_pd_ytd Bond Interest ‘01’ if RM_DISPLAY.bond_int_pd_ytd or RM_DISPLAY.ibond_int_pd_ytd OID Interest ‘30’ if DP_DISPLAY.accr_cr_int_ytd
irs_1042s.income_code
Gross Income 2 Decimal (9) Deposit Interest or Escrow Interest If Income Code = 29 DP_ACCT_INT_OPT.int_ytd + DP_DISPLAY2.bonus_pd_ytd or LN_ESCROW.cr_int_pd_ytd Bond Interest If Income Code = 01 RM_DISPLAY.bond_int_pd_ytd + RM_DISPLAY.ibond_int_pd_ytd OID Interest If Income Code = 30 (OID Interest) DP_DISPLAY.accr_cr_int_ytd - DP_ACCT_INT_OPT.int_ytd
irs_1042s.gross_income
© Finastra | Fusion Phoenix | IRS Tables 27
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Chapter 3 3 Char (1) Deposit Interest or Escrow Interest If ( DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd ) or LN_ESCROW.int_wh_ytd, move 1 Else, move 0 Bond Interest 0 OID Interest 0 Note: When populated as 1, the Chap. 3 (box 3) must be marked. Otherwise, it will be un-marked.
irs_1042s.chapter_indicator
Chapter 3 Exemption Code
3a Char (2) Deposit Interest or Escrow Interest If DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd = 0 or LN_ESCROW.int_wh_ytd = 0, move 02; Else, move 00. Bond Interest 00 OID Interest 00
irs_1042s.chapter3_exemp_code
© Finastra | Fusion Phoenix | IRS Tables 28
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Chapter 3 Tax Rate
3b Decimal (5) ‘blank’ if RM_DISPLAY.bond_int_pd_ytd or RM_DISPLAY.ibond_int_pd_ytd ‘0000’ if DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd = 0.00 or LN_ESCROW.int_wh_ytd = 0.00 Else AD_GB_BANK.backup_wh_rate Note: Enter the Tax Rate as a 2-digit whole number and 2-digit decimal - do not round and no decimal point (e.g., enter 39.612% as 3961, 15% as 1500 or 6% as 0600) Note: If this is for OID accrued interest (Income Code = 30), Fed Income Tax Withheld is always 0000
irs_1042s.chapter3_tax_rate
Chapter 4 3 Char (1) Populate as 0 Note: The assumption is that if any taxes are being withheld,they are being withheld pursuant Chapter 3 of the Internal Revenue Code (IRC).
irs_1042s.chapter_indicator
Chapter 4 Exemption Code
4a Char (2) Blank irs_1042s.chapter4_exemp_code
Chapter 4 Tax Rate
4b Decimal (5) Blank irs_1042s.chapter4_tax_rate
Withholding Allowance
5 Decimal (9) N/A irs_1042s.whld_allowance
Net Income 6 Decimal (9) N/A irs_1042s.net_income
© Finastra | Fusion Phoenix | IRS Tables 29
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Fed Income Tax Withheld
7aa Decimal (9) Deposit Interest or Escrow Interest If Income Code = 29 DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd or LN_ESCROW.int_wh_ytd Bond Interest 0 OID Interest 0
irs_1042s.federal_tax_whld
Tax not Deposited
7b Char (1) N/A irs_1042s.tax_not_dep
Tax Withheld by Other
8 Decimal (9) N/A irs_1042s.tax_whld_other
Overwithheld Tax Repaid to Recipeint
9 Decimal (9) N/A irs_1042s.tax_pd_whld_agent
Total Withholdong Credit
10 Decimal (9) N/A irs_1042s.tot_whld_credit
Tax Paid by Withholding Agent
11 Decimal (9) N/A irs_1042s.tax_pd_whld_agent
Recipeint’s Country Code
13b Char (2) N/A irs_1042s.recip_country_code
Recipeint’s Ch 3 Code
13f Char (2) RM_ACCT.W8_recipient_code
irs_1042s.chapter3_status_code
Recipeint’s Ch 4 Code
13g Char (2) N/A irs_1042s.chapter4_status_code
Recipeint’s GIIN 13h Char (19) N/A irs_1042s.recip_giin
© Finastra | Fusion Phoenix | IRS Tables 30
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient
Recipeint’s Foreign TIN
13i Char (22) RM_ACCT.w8_foreign_tin irs_1042s.recip_foreign_tin
LOB Code 13j Char (2) irs_1042s.lob_code lob_code
Recipient’s Date of Birth
13l Smalldatetime (4)
If RM_ACCT.rim_type = ‘Personal’ and RM_ACCT.birth_dt is not NULL, move RM_ACCT.birth_dt. Note: Format as MMDDYYYY
irs_1042s.recip_dob
Recipient Country Code
15f Char (2)
Select PC_COUNTRY.w8_country_code where RM_ACCT.w8_country_code = PC_COUNTRY.ptid
Irs_1042.recip_country_code
State Income Tax Withheld
17a Decimal (9) N/A irs_1042s.state_income_tax_whld
Payer’s State Tax No
17b Char (10) N/A irs_1042s.payer_state_tax_no
Name of State 17c Char (2) N/A irs_1042s.name_of_state
© Finastra | Fusion Phoenix | IRS Tables 31
Tax Over/Under WH Status
Char (1) If Income Code = 29 and Tax Rate = 0000 Print 0 If Income Code = 29 and Tax Rate > 0000 then And DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd > 0 And AD_GB_BANK. backup_wh_rate X (DP_ACCT_INT_OPT.int_ytd + DP_DISPLAY2.bonus_pd_ytd) / 100 = DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd Print 0 or DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd > 0 And AD_GB_BANK. backup_wh_rate X (DP_ACCT_INT_OPT.int_ytd + DP_DISPLAY2.bonus_pd_ytd) / 100 > DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd Print 2 or DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd > 0 And AD_GB_BANK. backup_wh_rate X (DP_ACCT_INT_OPT.int_ytd + DP_DISPLAY2.bonus_pd_ytd) / 100 < DP_ACCT_INT_OPT.fed_int_wh_ytd + DP_ACCT_INT_OPT.backup_wh_ytd Print 1 or LN_ESCROW.int_wh_ytd > 0 And AD_GB_BANK.backup_wh_rate X LN_ESCROW.cr_int_pd_ytd / 100 = LN_ESCROW.int_wh_ytd Print 0 or LN_ESCROW.int_wh_ytd > 0
irs_1042s.tax_over_under_whld_status
© Finastra | Fusion Phoenix | IRS Tables 32
Field Name Box Nbr Data Type Source Data during Task Generation irs_1042s irs_recipient And AD_GB_BANK.backup_wh_rate X LN_ESCROW.cr_int_pd_ytd / 100 > LN_ESCROW.int_wh_ytd Print 2 or LN_ESCROW.int_wh_ytd > 0 And AD_GB_BANK.backup_wh_rate X LN_ESCROW.cr_int_pd_ytd / 100 < LN_ESCROW.int_wh_ytd Print 1 If Income Code = 29 and Tax Rate = <blank> Print <blank> Note: Make sure to keep the fixed position
Filing Status Varchar (25) irs_1042s.filing_status
© Finastra | Fusion Phoenix | IRS Tables 33
Retirement Plan Year-End Tables • 1099-R - Retirement Plan Distributions • 1099-SA - Retirement Plan Distributions • 1099-Q - ESA Retirement Plan Distributions • 5498 - Retirement Plan Summary • 5498-SA - Retirement Plan Summary • 5498-ESA - Retirement Plan Summary
1099-R - Retirement Plan Distributions The 1099r task generates all the information required to produce Form 1099-R. The file contains the following information for each retirement plan umbrella that had distributions for the current tax year.
Report when:
• DP_UMB.type = IRA, Roth, Roth Conv, SEP, SIMPLE, Keogh, Qualified
Exceptions:
• If DP_UMB.third_party_irs_rpt = ‘Y’, do not generate
Selection Criteria Generate one 1099-R record for each distribution code combination.
(DP_UMB_DISPLAY_DIST.umb_code/umb_code_2) SELECT DP_UMB.TYPE = IRA, Roth, Roth Conv, SEP, SIMPLE, Keogh, Qualified and DP_UMB.third_party_irs_rpt = ‘N’ and DP_UMB_DISPLAY_DIST.fmv_at_death = ‘N’
© Finastra | Fusion Phoenix | IRS Tables 34
Task Information
Form Description Task Task_id Stored Proc
1099-R Distributions From Pensions, Annuities, Retirement or Profit-Sharing Plans, IRAs, Insurance Contracts, etc
1099-R System Generation
593 psp_irs_1099r
Table Mapping [Payer]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 35
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
Tax Year 20XX Int (4) tax_year
TIN Type Char (1) irs_1099r.tin_type
Federal Tax ID Recipient ID
Varchar (15) RM_ACCT.tin If irs_1099int.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_2
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_2
© Finastra | Fusion Phoenix | IRS Tables 36
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
Street 3 If irs_1099int.rim_no IS NULL Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099int.rim_no IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25)
If irs_recipient.international= Y irs_recipient.country_code (2)
© Finastra | Fusion Phoenix | IRS Tables 37
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
Else blank
Account # Account Nbr
Char (20) DP_UMB_DISPLAY.plan_no <space> DP_UMB_DISPLAY_DIST.umb_code <space> DP_UMB_DISPLAY_DIST.umb_code_2 Note: There could be 2 UMB codes in a record. UMB Code 2 is 0 do not print 0.
Irs_1099r.acct_type + Irs_1099r.acct_no OR Irs_1099r.plan_no
Gross Distribution
1 Decimal (9) Sum (DP_UMB_DIST_DISPLAY.distribution_ytd + federal_tax_wh_ytd + state_tax_wh_ytd + other_tax_wh_ytd) Where X = distinct DP_UMB_DISPLAY_DIST.umb_code and DP_UMB_DISPLAY_DIST.umb_code_2
Irs_1099r.gross_dist
Taxable Amount
2a Decimal (9) If DP_UMB_DISPLAY_DIST.umb_code = 8, 10 or 14 write DP_UMB_DISPLAY_DIST.income_attrib_ytd or If DP_UMB_DISPLAY_DIST.umb_code = 9, write Gross Distribution amount or If DP_UMB_DISPLAY_DIST.umb_code = 23 or 24, write 0.00 or If DP_UMB.type = IRA and DP_UMB_DISPLAY_DIST.umb_code = 18, write 0.00
Irs_1099r.taxable_amt
© Finastra | Fusion Phoenix | IRS Tables 38
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
or If DP_UMB.type = IRA, SEP, SIMPLE and DP_UMB_DISPLAY_DIST.umb_code = 17, write 0.00 or If DP_UMB.type = Keogh or Qualified and DP_UMB_DISPLAY_DIST.umb_code = 17 or 18, write 0.00 or If DP_UMB.type = IRA and DP_UMB_DISPLAY_DIST.umb_code != 18, 8, 10, 14, 23, 24, 17 write Gross Distribution amount or If DP_UMB.TYPE = SEP, SIMPLE, and DP_UMB_DISPLAY_DIST.umb_code != 8, 10, 14, 23, 24, 17 write Gross Distribution amount Else write Blank
Taxable Amount
2b √ box 1
Char (1) If DP_UMB_DISPLAY_DIST.umb_code = 8, 9, 10, 14, 23, 24 write N or If DP_UMB.type = IRA and DP_UMB_DISPLAY_DIST.umb_code = 18, write N or If DP_UMB.type = IRA, SEP, SIMPLE and DP_UMB_DISPLAY_DIST.umb_code = 17, write N or
Irs_1099r.taxable_amt_not_det
© Finastra | Fusion Phoenix | IRS Tables 39
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
If DP_UMB.type = Keogh or Qualified and DP_UMB_DISPLAY_DIST.umb_code = 17 or 18, write N Else write Y
Capital Gain (Included in Box 2a)
3 Decimal (9) Irs_1099r.capital_gain
Federal Income Tax Withheld
4 Decimal (9) DP_UMB_DIST_DISPLAY.federal_tax_wh_ytd Where X = distinct DP_UMB_DISPLAY_DIST.umb_code and DP_UMB_DISPLAY_DIST.umb_code_2
Irs_1099r.federal_income_tax_whld
Emp. Contrib / Insurance
5 Decimal (9) Write this column to record only when DP_DISPLAY_UMB_DIST.umb_code = 9 Sum (DP_UMB_DIST_DISPLAY.distribution_ytd + federal_tax_wh_ytd + state_tax_wh_ytd + other_tax_wh_ytd) Else write Blank
Irs_1099r.empl_contrib
Net Unrealized Appreciation
6 Decimal (9) Irs_1099r.net_unrealized_appr
IRA / SEP / SIMPLE Distribution
Char (1) If DP_UMB.TYPE = IRA, SEP, SIMPLE And DP_UMB_DISPLAY_DIST.umb_code = 8, 10 or 14 write DP_UMB_DISPLAY_DIST. income_attrib_ytd or If DP_UMB.TYPE = IRA, SEP, SIMPLE And DP_UMB_DISPLAY_DIST.umb_code = 23, 24, 17 write 0.00 or
Irs_1099r.ira_sep_simple
© Finastra | Fusion Phoenix | IRS Tables 40
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
If DP_UMB.type = IRA and DP_UMB_DISPLAY_DIST.umb_code = 18, write 0.00 or If DP_UMB.type = IRA, SEP, SIMPLE and DP_UMB_DISPLAY_DIST.umb_code not= 18, 8, 10, 14, 23, 24, 17 write Gross Distribution amount Else write Blank
IRA, SEP or SIMPLE?
Char (1) If DP_UMB.type = IRA, SEP or SIMPLE and DP_UMB_DISPLAY_DIST.umb_code not= 23, 24 write X Else write Blank Note: The exception is where DP_UMB_DISPLAY_DIST.umb_code = 23 or 24 for recharacterizations.
Irs_1099r.ira_sep_simple
Distribution Code - 1
7 Char (2) PC_DP_UMB. irs_value where DP_UMB_DISPLAY_DIST.umb_code = PC_DP_UMB.umb_code PC_DP_UMB. irs_value where DP_UMB_DISPLAY_DIST.umb_code_2 = PC_DP_UMB.umb_code
Irs_1099r.dist_code
Distribution Code - 2
7 Char (2) Irs_1099r.dist_code2
Other Amount 8 Decimal (9) Irs_1099r.other_amt
Other Percent 8 Decimal (5) Irs_1099r.other_percent
Your Percentage of Total Distribution
9a Decimal (5) Irs_1099r.percent_total_dist
© Finastra | Fusion Phoenix | IRS Tables 41
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
Total Employee Contributions
9b Decimal (9) Irs_1099r.total_empl_contrib
Amount Allocable to IRR Within 5 Years
10 Decimal (9) Irs_1099r.amt_allocable
1st Year of Desig. Roth Contrib
11 Int (4) Irs_1099r.first_year_roth_contrb
FATCA Filing Requirement
12 Char (1) N/A irs_1099r.fatca_req
Date of Payment
13 Smalldatetime (4)
NA irs_1099r.payment_dt
State Tax Withheld
14 Decimal (9) DP_UMB_DIST_DISPLAY.state_tax_wh_ytd Where X = distinct DP_UMB_DISPLAY_DIST.umb_code and DP_UMB_DISPLAY_DIST.umb_code_2
Irs_1099r.state_tax_whld
State/Payer’s State No.
15 Varchar (15) When OV_CONTROL. OV_CONTROL. use_residence_state = Y Locate the record where RM_ADDRESS.addr_of_residence = ‘Y’ Write RM_ADDRESS.state Else write AD_GB_BRANCH.state where AD_GB_BRANCH.branch_no = DP_UMB.branch_no Write State only where State Inc. Tax Withheld > 0
Irs_1099r.state_payer_state_no
State Distribution
16 Decimal (9) Irs_1099r.state_dist
Local Tax Withheld
17 Decimal (9) Irs_1099r.local_tax_whld
Name of Locality
18 Varchar (40) Irs_1099r.name_of_locality
© Finastra | Fusion Phoenix | IRS Tables 42
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099r Irs_recipient
Local Distribution
19 Decimal (9) Irs_1099r.local_dist
Filing Status Varchar (25) irs_1099r.filing_status
© Finastra | Fusion Phoenix | IRS Tables 43
1099-SA - Retirement Plan Distributions The 1099sa.nnn task generates all the information required to produce form 1099-SA. The file contains the following information for each Medical Savings Account (MSA) and Health Savings Account (HSA) retirement plan umbrella that had distributions for the current tax year.
Report when:
• DP_UMB.type = Health, Medical
Exceptions:
• If DP_UMB.third_party_irs_rpt = ‘Y’, do not include in the irs_1099sa table
Selection Criteria Generate one 1099-SA record for each distribution code.
SELECT accounts in plan where DP_UMB.TYPE = Medical, Health and DP_UMB.third_party_irs_rpt = ‘N’ and DP_UMB_DISPLAY_DIST.fmv_at_death = ‘N’ IF DP_UMB.dist_type = Participant and DP_UMB.status = Deceased and DP_UMB_BENE has no active rows WHERE DP_UMB_BENE.type = Primary and DP_UMB_DISPLAY_DIST.death_rpt_yr equals the system date year WHERE DP_UMB_DISPLAY.fmv_at_death = Y
Then, include the FMV at Death for the decedent plan.
IF DP_UMB.dist_type = Beneficiary and DP_UMB_DISPLAY_DIST.death_rpt_yr equals the system date year WHERE DP_UMB.part_plan_no = DP_UMB.plan_no and DP_UMB_DISPLAY.fmv_at_death = Y and DP_ACCT.status = Closed for all the beneficiary plan accounts
Then, include the FMV at Death for the decedent plan in the beneficiary’s 1099-MSA.
© Finastra | Fusion Phoenix | IRS Tables 44
Task Information
Form Description Task Task_id Stored Proc
1099-SA Distributions From an HSA, Archer MSA, or Medicare Advantage MSA
1099-SA System Generation
595 psp_irs_1099sa
Table Mapping [Trustee]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 45
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099sa irs_recipient
Tax Year 20XX Int (4) tax_year
TIN Type Char (1) irs_1099sa.tin_type
Federal Tax ID Recipient ID
Varchar (15) RM_ACCT.tin irs_1099isa.tin
If irs_1099sa.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099sa.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099sa.rim_no IS NULL irs_recipient.Name_2
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y
If irs_1099as.rim_no IS NULL irs_recipient.Address_line_1
© Finastra | Fusion Phoenix | IRS Tables 46
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099sa irs_recipient Select RM_ADDRESS.address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099sa.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099sa.rim_no IS NULL Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099sa.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099sa.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099sa.rim_no IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5)
If irs_recipient.international= Y irs_recipient.state
© Finastra | Fusion Phoenix | IRS Tables 47
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099sa irs_recipient Else blank Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr
Char (20) DP_UMB_DISPLAY.plan_no <space> DP_UMB_DISPLAY_DIST.umb_code
irs_1099sa.acct_type + irs_1099sa.acct_no OR Irs_1099r.plan_no
Gross Distribution
1 Decimal (9) Sum (DP_UMB_DIST_DISPLAY.distribution_ytd + federal_tax_wh_ytd + state_tax_wh_ytd + other_tax_wh_ytd) Where X = distinct DP_UMB_DISPLAY_DIST.umb_code and DP_UMB_DISPLAY_DIST.umb_code_2
irs_1099sa. gross_dist
Earnings on Excess Contributions
2 Decimal (9) If DP_UMB_DISPLAY_DIST.umb_code = 71 write DP_UMB_DISPLAY_DIST. income_attrib_ytd Else write NULL
irs_1099sa.earnings_on_contb
© Finastra | Fusion Phoenix | IRS Tables 48
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099sa irs_recipient
Distribution Code 3 Char (2) PC_DP_UMB. irs_value where DP_UMB_DISPLAY_DIST..umb_code = PC_DP_UMB.umb_code If PC_DP_UMB.irs_value = M1 = display 1 M2 = display 2 M3 = display 3 M4 = display 4 M5 = DISPLAY 5 M6 = display 6 Can remove this. We removed the ‘M’ from the table value.
irs_1099sa.dist_code
FMV on Date of Death
4 Decimal (9) If DP_UMB.dist_type = Participant and DP_UMB.status = Deceased and DP_UMB_BENE has no active rows where DP_UMB_BENE.type = Primary and DP_UMB_DISPLAY_DIST.death_rpt_yr equals the system date year where DP_UMB_DISPLAY.fmv_at_death = Y then include the FMV at Death for the decedent plan If DP_UMB.dist_type = Beneficiary and DP_UMB_DISPLAY_DIST.death_rpt_yr equals the system date year where DP_UMB.part_plan_no = DP_UMB.plan_no and DP_UMB_DISPLAY.fmv_at_death = Y and DP_ACCT.status = Closed for all the beneficiary plan accounts then include the FMV at Death for the decedent plan in the beneficiary’s 1099-MSA If DP_UMB_DISPLAY.death_rpt_yr = the reporting year
irs_1099sa.fmv_on_dod
© Finastra | Fusion Phoenix | IRS Tables 49
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099sa irs_recipient Calculate FMV where If DP_UMB_DISPLAY.death_rpt_yr = the reporting year Calculate FMV where DP_UMB_DISPLAY.fmv_at_death = Y Sum DP_UMB_DISPLAY.begin_bal + DP_UMB_DISPLAY.int_pd_ytd + DP_UMB_DISPLAY.norm_cont_ytd + DP_UMB_DISPLAY.tfr_cont_ytd + DP_UMB_DISPLAY.roll_cont_ytd + DP_UMB_DISPLAY.prev_yr_cont_ytd + DP_UMB_DISPLAY.empl_cont_ytd + DP_UMB_DISPLAY.roth_conv_ytd + DP_UMB_DISPLAY.rechar_cont_ytd + DP_UMB_DISPLAY.prev_yr_empl_cont_ytd + DP_UMB_DISPLAY.other_credits_ytd + DP_UMB_DISPLAY.death_accr_int - DP_UMB_DISPLAY.other_debits_ytd - DP_UMB_DISPLAY.penalty_ytd - DP_UMB_DISPLAY.tfr_wd_ytd - DP_UMB_DISPLAY.cc_amt_ytd where DP_UMB_DISPLAY.fmv_at_death = Y Minus sum DP_UMB_DISPLAY_DIST.distribution_ytd + DP_UMB_ DISPLAY_DIST.federal_tax_wh_ytd + DP_UMB_ DISPLAY_DIST.state_tax_wh_ytd + DP_UMB_ DISPLAY_DIST.other_tax_wh_ytd
© Finastra | Fusion Phoenix | IRS Tables 50
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099sa irs_recipient where DP_UMB_DISPLAY_DIST.fmv_at_death = Y
Plan Type
5 Char (2) If DP_UMB.type = Medical pass A If DP_UMB.type = Health pass H
irs_1099sa.plan_type
Filing Status Varchar (25) irs_1099sa.filing_status
© Finastra | Fusion Phoenix | IRS Tables 51
1099-Q - ESA Retirement Plan Distributions The 1099q task generates all the information required to produce form 1099-Q. The file contains the following for each Coverdell ESA retirement plan umbrella that had distributions for the current tax year.
Report when:
• DP_UMB.type = Education
Exceptions:
• If DP_UMB.third_party_irs_rpt = ‘Y’, do not include in the irs_1099q table
Selection Criteria SELECT accounts in plan where DP_UMB.type = Education and DP_UMB.third_party_irs_rpt = ‘N’ and DP_UMB_DISPLAY_DIST.fmv_at_death = ‘N’
Task Information
Form Description Task Task_id Stored Proc
1099-Q Payments from Qualified Education Programs (Under Sections 529 and 530)
1099-Q System Generation
594 psp_irs_1099q
Table Mapping [Payer]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
© Finastra | Fusion Phoenix | IRS Tables 52
Field Name Data Type ad_irs_filer
Zip Char (10) ad_irs_filer.zip
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099q irs_recipient
Tax Year 20XX Int (4) tax_year
TIN Type Char (1) irs_1099q.tin_type
Federal Tax ID Recipient ID
Varchar (15) RM_ACCT.tin Irs_1099q.tin
If irs_1099q.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal
RM_ACCT.last_name RM_ACCT.first_name
If irs_1099q.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of
RM_ACCT.last_name RM_ACCT.first_name
If irs_1099q.rim_no IS NULL irs_recipient.Name_2
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2
If irs_1099q.rim_no IS NULL irs_recipient.Address_line_1
© Finastra | Fusion Phoenix | IRS Tables 53
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099q irs_recipient
If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2
Else blank
If irs_1099q.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099q.rim_no I S NULL irs_recipient.Address_line_3
US/Foreign City
Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’
Note: No spaces between 1 and city
If irs_1099q.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5)
Else blank
If irs_1099qrim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘
Else blank
If irs_1099q.rim_no IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5)
Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
© Finastra | Fusion Phoenix | IRS Tables 54
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099q irs_recipient
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10)
Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country
Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code
Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr
Char (20) DP_UMB_DISPLAY.plan_no <space> DP_UMB_DISPLAY_DIST.umb_code
irs_1099q.acct_type + irs_1099q.acct_no OR irs_1099q.plan_no
Gross Distribution
1 Decimal (9) Sum (DP_UMB_DIST_DISPLAY. distribution_ytd + federal_tax_wh_ytd + state_tax_wh_ytd + other_tax_wh_ytd) Where X = distinct DP_UMB_DISPLAY_DIST.umb_code and DP_UMB_DISPLAY_DIST.umb_code_2 and DP_UMB_DISPLAY_DIST.period_id = 0 and DP_UMB_DISPLAY_DIST.fmv_at_death = N or DP_UMB_DISPLAY.tfr_wd_ytd where period_id = 0 and DP_UMB_DISPLAY_DIST.fmv_at_death = N
This gets 1 form per distinct X and tfr_wd_ytd
irs_1099q.plan_no.gross_dist
Earnings 2 Decimal (9) If DP_UMB_DISPLAY_DIST.umb_code = 8, 10 or 14
write DP_UMB_DISPLAY_DIST. income_attrib_ytd
irs_1099q.earnings
© Finastra | Fusion Phoenix | IRS Tables 55
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099q irs_recipient
Basis 3 Decimal (9) irs_1099q.basis
Trustee to Trustee Transfer
4 Char (1) If from reporting for DP_UMB_DISPLAY.tfr_wd_ytd DP_UMB_DISPLAY.tfr_wd_ytd where period_id = 0 and DP_UMB_DISPLAY_DIST.fmv_at_death = N print Y
Else leave blank
irs_1099q.trustee_tfr
Tuition Program Type
5 Char (10) print Y irs_1099q. tuition_prgrm_type
Recipeint is not the Designated Beneficiary
6 Char (1) irs_1099q.recip_not_des_bene
Fair Market Value
Decimal (9) Sum of DP_DISPLAY.cur_bal plus DP_DISPLAY.accr_cr
where DP_ACCT.plan_no = DP_UMB.plan_no and DP_ACCT.acct_type/acct_no = DP_DISPLAY.acct_type/acct_no
irs_1099q.fmv
Distribution Code
Char (2) irs_1099q.dist_code
Filing Status Varchar (25) irs_1099q.filing_status
© Finastra | Fusion Phoenix | IRS Tables 56
5498 - Retirement Plan Summary The 5498 task generates all the information required to produce form 5498. The file contains the following information for each active IRA, Roth, Roth Conversion, SEP, or SIMPLE retirement plan umbrella in the system.
IMPORTANT! Schedule the 5498 task for the national retirement contribution cut-off date. Go to the Year End Processing Guide for more information.
Report when:
• DP_UMB.type = IRA, Roth, Roth Conv, SEP or SIMPLE
Exceptions:
• DP_UMB.third_party_irs_rpt = ‘Y’
Selection Criteria for Address Living Plans
Need to report for Plan Owners
IF DP_UMB.dist_type = Participant and DP_UMB.status = Contribution, Distribution, Both, Closed Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Use Participant’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no) IF DP_UMB.dist_type = Beneficiary and DP_UMB.status = Distribution, Both, Closed Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.part_rim_no = RM_ACCT.rim_no Use Beneficiary’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no)
For Decedent Plans
WHERE the Plan Owner Died in the Current Year (DP_UMB.plan_status = Deceased, Split and
© Finastra | Fusion Phoenix | IRS Tables 57
RM_ACCT.deceased_dt = system year)
Need to report for plan owners:
IF DP_UMB.dist_type = Participant and DP_UMB.status = Deceased, Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Use Participant’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no)
Do not report FMV for Plan Beneficiaries since Participant was alive on Decemebr 31st of the report year.
IF DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 0 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.part_rim_no = RM_ACCT.rim_no Use Beneficiary’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no)
Beneficiary plans do not allow beneficiaries:
For Date of Death Year on Participant Plans (Report Year = Deceased Year) Need to report for Participant and Primary Beneficiaries
Participant Plan
IF DP_UMB.dist_type = Participant DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial
© Finastra | Fusion Phoenix | IRS Tables 58
Use Participant’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no)
Beneficiary(s) on Participant Plan
IF DP_UMB.dist_type = Participant and DP_UMB.status = Deceased and RM_ACCT.deceased_dt BETWEEN DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB_BENE.rim_no = RM_ACCT.rim_no and DP_UMB-_BENE.type = Primary and DP_UMB_BENE.status = Active. Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.rim_no = RM_ACCT.rim_no Use Beneficiary’s primary address (DP_UMB_BENE.rim_no = RM_ADDRESS.rim_no
Note: If not the date of death year, do not generate a 5498 for either Participant or Primary Beneficiary(s). The decedent Plan must be split by December 31st of the year following the date of death.
For Date of Death Year on Beneficiary Plans (Report Year = Deceased Year) - Need to report for Beneficiary
Beneficiary Plan 5498
IF DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.part_rim_no = RM_ACCT.rim_no Use Beneficiary’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no)
© Finastra | Fusion Phoenix | IRS Tables 59
Note: Beneficiary plans do not allow Beneficiaries.
If not the date of death year, do not generate a 5498 for Beneficiary. The decedent Plan must be split by December 31 of the year following the date of death.
Selection Criteria for Accounts Do not need to generate a statement if:
• Plan = Education, Medical, KEOGH, or Qualified
(DP_UMB.type = Medical, KEOGH, or Qualified)
• Third Party IRS Reporting = Y
(DP_UMB.third_party_irs_rpt = Y)
If Plan beginning balance was $0.00 and no contributions, rollovers, transfers, conversion or recharacterizations were made.
DP_UMB_DISPLAY.begin_bal = 0 where period_id = 0 and DP_UMB_DISPLAY.normal_cont.ytd = 0 where period_id = 0 and DP_UMB_DISPLAY.roll_cont_ytd = 0 where period_id = 0 and DP_UMB_DISPLAY.tfr_cont_ytd = 0 where period_id = 0 and DP_UMB_DISPLAY.prev_yr_cont_ytd = 0 where period_id = 0 and DP_UMB_DISPLAY.empl_cont_ytd = 0 where period_id = 0 and DP_UMB_DISPLAY.prev_yr_empl_cont_ytd = 0 where period_id = 0 and DP_UMB_DISPLAY.roth_conv_ytd = 0 where period_id = 0 or and DP_UMB_DISPLAY.rechar_cont_ytd = 0 where period_id = 0 Special Instructions
Living Plans - Need to report for Plan Owners
IF DP_UMB.dist_type = Participant and DP_UMB.status = Contribution, Distribution, Both, Closed Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Use Participant’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no), TIN, and calculated FMV. IF DP_UMB.dist_type = Beneficiary and DP_UMB.status = Distribution, Both, Closed Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial
© Finastra | Fusion Phoenix | IRS Tables 60
WHERE DP_UMB.rim_no = RM_ACCT.rim_no Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.part_rim_no = RM_ACCT.rim_no Use Beneficiary’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no), TIN, and calculated FMV.
For Decedent Plans - Need to report for Plan Owners
WHERE the Plan Owner Died in the Current Year (DP_UMB.plan_status = Deceased, Split and RM_ACCT.deceased_dt = system year) IF DP_UMB.dist_type = Participant and DP_UMB.status = Deceased, Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 0 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no
Use Participant’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no), TIN, and calculated FMV.
Do not report FMV for Plan Beneficiaries since Participant was alive on Decemebr 31st of the report year.
IF DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 0 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.part_rim_no = RM_ACCT.rim_no
Use Beneficiary’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no), TIN, and calculated FMV.
Note: Beneficiary plans do not allow Beneficiaries
© Finastra | Fusion Phoenix | IRS Tables 61
For Date of Death Year on Participant Plans (Report Year = Deceased Year)
Need to report for Participant and Primary Beneficiaries
Participant Plan
IF DP_UMB.dist_type = Participant DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial
Use Participant’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no), TIN, and FMV = $0.00.
Beneficiary(s) on Participant Plan
IF DP_UMB.dist_type = Participant and DP_UMB.status = Deceased and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB_BENE.rim_no = RM_ACCT.rim_no and DP_UMB-_BENE.type = Primary and DP_UMB_BENE.status = Active. Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.rim_no = RM_ACCT.rim_no
Use Beneficiary’s primary address (DP_UMB_BENE.rim_no = RM_ADDRESS.rim_no), TIN, and the beneficiary’s portion of calculated FMV.
If not the date of death year, do not generate a 5498 for either Participant or Primary Beneficiary(s). The decedent Plan must be split by December 31 of the year following the date of death.
For Date of Death Year on Beneficiary Plans (Report Year = Deceased Year)
Need to report for Beneficiary
© Finastra | Fusion Phoenix | IRS Tables 62
Beneficiary Plan 5498
IF DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt WHERE DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial WHERE DP_UMB.rim_no = RM_ACCT.rim_no Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> WHERE DP_UMB.part_rim_no = RM_ACCT.rim_no
Use Beneficiary’s primary address (DP_UMB.rim_no = RM_ADDRESS.rim_no), TIN, and FMV = $0.00.
Note: Beneficiary plans do not allow Beneficiaries
If not the date of death year, do not generate a 5498 for Beneficiary. The decedent Plan must be split by December 31 of the year following the date of death.
Task Information
Form Description Task Task_id Stored Proc
5498 IRA Contribution Information
5498 System Generation
604 psp_irs_5498
Table Mapping [Trustee]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
© Finastra | Fusion Phoenix | IRS Tables 63
Field Name Data Type ad_irs_filer
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 64
Table Mapping [Participant]
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient
Tax Year 20XX irs_5498.tax_year
TIN Type Char (1) irs_5498.tin_type
Federal Tax ID Recipient ID
Varchar (15) If DP_UMB.status = Contribution, Distribution, Both, Closed RM_ACCT.tin Participant Plan if DP_UMB.dist_type = Participant DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Use Participant’s RM_ACCT.tin if DP_UMB.dist_type = Participant DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Use Participant’s RM_ACCT.tin Beneficiary(s) on Participant Plan if DP_UMB.dist_type = Participant DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Use Participant Plan Beneficiary’s RM_ACCT.tin Beneficiary Plan if DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0
irs_5498.tin If irs_5498.rim_no IS NULL irs_recipient.tin
© Finastra | Fusion Phoenix | IRS Tables 65
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient Use Beneficiary’s RM_ACCT.tin if DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Use Beneficiary’s RM_ACCT.tin
Name 1 Recipient Name 1
Char (40) If DP_UMB.status = Contribution, Distribution, Both, Closed Name 1 = RM_ACCT.last_name RM_ACCT.suffix‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial where DP_UMB.rim_no = RM_ACCT.rim_no Participant Plan if DP_UMB.dist_type = Participant and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial if DP_UMB.dist_type = Participant and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial Beneficiary(s) on Participant Plan if DP_UMB.dist_type = Participant and DP_UMB.status = Deceased and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
© Finastra | Fusion Phoenix | IRS Tables 66
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial where DP_UMB_BENE.rim_no = RM_ACCT.rim_no and DP_UMB-_BENE.type = Primary and DP_UMB_BENE.status = Active Beneficiary Plan If DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial where DP_UMB.rim_no = RM_ACCT.rim_no If DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Name 1 = RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial where DP_UMB.rim_no = RM_ACCT.rim_no
Name 2 Recipient Name 2
Char (40) if DP_UMB.dist_type = Participant and DP_UMB.status = Contribution, Distribution, Both, Closed Name 2 is NULL If DP_UMB.dist_type = Beneficiary and DP_UMB.status = Distribution, Both, Closed Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> where DP_UMB.part_rim_no = RM_ACCT.rim_no Participant Plan
If irs_1099int.rim_no IS NULL irs_recipient.Name_2
© Finastra | Fusion Phoenix | IRS Tables 67
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient if DP_UMB.dist_type = Participant DP_UMB.status = Deceased and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Name 2 is NULL if DP_UMB.dist_type = Participant DP_UMB.status = Deceased and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> where DP_UMB.rim_no = RM_ACCT.rim_no Beneficiary Plan If DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> where DP_UMB.part_rim_no = RM_ACCT.rim_no If DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 Name 2 = as Bene of <RM_ACCT.last_name‘,’ RM_ACCT.first_name, RM_ACCT.middle_initial> where DP_UMB.part_rim_no = RM_ACCT.rim_no
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else
If irs_5498.rim_no IS NULL irs_recipient.Address_line_1
© Finastra | Fusion Phoenix | IRS Tables 68
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_5498.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_5498.rim_no IS NULL irs_recipient.Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099int.rim_no IS NULL irs_recipient.zip
© Finastra | Fusion Phoenix | IRS Tables 69
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr
Char (20) DP_UMB_DISPLAY.plan_no irs_5498.plan_no
Regular IRA Contribution
1 Decimal (9) If DP_UMB.type = IRA or SEP DP_UMB_DISPLAY.normal_cont.ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_cont.ytd where period_id = 0
irs_5498.reg_IRA_contrib
Rollover Contribution
2 Decimal (9) If DP_UMB.type = IRA or SEP, Roth, Roth Conv, SIMPLE (excludes DP_UMB.type = Education) DP_UMB_DISPLAY.roll_cont.ytd where period_id = 1
irs_5498.rollover_contrib
Roth Conversion Amount
3 Decimal (9) If DP_UMB.type = Roth, Roth Conv DP_UMB_DISPLAY.roth_conv_ytd where period_id = 1
irs_5498.roth_conv_amt
Recharacterization Contributions
4 Decimal (9) If DP_UMB_DISPLAY.rechar_cont_ytd where period_id = 1
irs_5498.rechar_contrib
FMV of Account 5 Decimal (9) If DP_UMB.status = Contribution, Distribution, Both, Closed Sum of DP_UMB_DISPLAY.begin_bal where period_id = 0 +
irs_5498.fmv_acct
© Finastra | Fusion Phoenix | IRS Tables 70
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient DP_UMB_DISPLAY.dec_31_accr_int where period_id = 1 If period_id = 1 does not exist then calculate as DP_UMB_DISPLAY.begin_bal where period_id = 0 Participant Plan if DP_UMB.dist_type = Participant and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Sum of DP_UMB_DISPLAY.begin_bal where period_id = 0 + DP_UMB_DISPLAY.dec_31_accr_int where period_id = 1 If period_id = 1 does not exist then calculate as DP_UMB_DISPLAY.begin_bal where period_id = 0 if DP_UMB.dist_type = Participant and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 FMV = $0.00 if DP_UMB.dist_type = Participant and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 FMV = (DP_UMB_DISPLAY.begin_bal where DP_UMB_DISPLAY.period_id = 0 + DP_UMB_DISPLAY.dec_31_accr_int where period_id = 1 times DP_UMB_BENE.percentage) where DP_UMB_BENE.type = Primary and DP_UMB_BENE. status = Active
© Finastra | Fusion Phoenix | IRS Tables 71
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient Beneficiary Plan If DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 0 Sum of DP_UMB_DISPLAY.begin_bal where period_id = 0 + DP_UMB_DISPLAY.dec_31_accr_int where period_id = 1 If period_id = 1 does not exist then calculate as DP_UMB_DISPLAY.begin_bal where period_id = 0 If DP_UMB.dist_type = Beneficiary and DP_UMB.status = Deceased or Split and RM_ACCT.deceased_dt between DP_UMB_DISPLAY.begin_dt and DP_UMB_DISPLAY.end_dt where DP_UMB_DISPLAY.period_id = 1 FMV = $0.00 Note: If calculated FMV is negative, populate using 0.00
Life Insurance Cost in Box 1
6 Decimal (9) irs_5498.life_insurance_cost
Contribution Type
7 Char (1) If DP_UMB.type = IRA = I Roth, Roth Conv = R SEP = S SIMPLE = L
irs_5498.contrib_type
SEP Contribtions
8 Decimal (9) If DP_UMB.type = SEP DP_UMB_DISPLAY.empl_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_empl_cont_ytd where period_id = 1
irs_5498.SEP_contrib
© Finastra | Fusion Phoenix | IRS Tables 72
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient
SIMPLE Contributions
9 Decimal (9) If DP_UMB.type = SIMPLE DP_UMB_DISPLAY.empl_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.normal_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_empl_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_cont_ytd where period_id = 1
irs_5498.SIMPLE_contrib
ROTH IRA Contributions
10 Decimal (9) IF DP_UMB.type = Roth, Roth Conv DP_UMB_DISPLAY.normal_cont.ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_cont.ytd where period_id = 0
irs_5498. ROTH_IRA_contrib
Check if RMD Indicator for YYYY
11 Char (1) If DP_UMB.required_begin_dt <= ‘12/31/system date year + 1> print Y Else blank
irs_5498.RMD
RMD Date 12a Smalldatetime (4)
N/A irs_5498.RMD_dt
RMD Amount 12b Decimal (9) N/A irs_5498.RMD_amt
Postponed/Late Contrib. Amount
13a Decimal (9) N/A irs_5498.postponed_contrib_amt
Postponed/Late Contrib. Year
13b Char (4) N/A irs_5498.postponed_contrib_yr
Postponed/Late Contrib. Code
13c Char (2) N/A irs_5498.RMD.postponed_contrib_code
Repayment Amount
14a Decimal (9) CARES??? irs_5498.repay_amt
Repayment Code
14b Char (2) CARES??? irs_5498.repay_code
FMV of Certain Specified Assets
15a Decimal (9) N/A irs_5498.FMV_assets
Code 15b Char (2) N/A irs_5498.code
© Finastra | Fusion Phoenix | IRS Tables 73
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498 irs_recipient
Filing Status Varchar (25) N/A irs_5498.filing_status
© Finastra | Fusion Phoenix | IRS Tables 74
5498-SA - Retirement Plan Summary The 5498sa task generates all the information required to produce form 5498-MSA. The file contains the following information for all active Archer Medical Savings Account retirement plan umbrellas in the system.
IMPORTANT! Schedule the 5498 task for the national retirement contribution cut-off date. Go to Year End Processing Guide for more information.
Report when:
• DP_UMB.type = Medical, Health
Exceptions:
• DP_UMB.third_party_irs_rpt = ‘Y’
Selection Criteria SELECT accounts within plan where DP_UMB.TYPE = Medical, Health and DP_UMB.third_party_irs_rpt = ‘N’ and DP_UMB_DISPLAY_DIST.fmv_at_death = ‘N’
Task Information
Form Description Task Task_id Stored Proc
5498-SA HSA, Archer MSA, or Medicare Advantage MSA Information
5498-SA System Generation
603 psp_irs_5498sa
Table Mapping [Trustee]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
© Finastra | Fusion Phoenix | IRS Tables 75
Field Name Data Type ad_irs_filer
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 76
Table Mapping [Participant]
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498sa irs_recipient
Tax Year 20XX Int (4) irs_5498sa.tax_year If irs_1099int.rim_no IS NULL irs_recipient.tin
TIN Type Char (1) irs_5498sa.tin_type
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_2
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_1
© Finastra | Fusion Phoenix | IRS Tables 77
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498sa irs_recipient
If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099int.rim_no IS NULL Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N If irs_1099int.rim_no
© Finastra | Fusion Phoenix | IRS Tables 78
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498sa irs_recipient
RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr
Char (20) DP_UMB_DISPLAY.plan_no irs_5498sa.plan_no
Reglar MSA Contribution
1 Decimal (9) If DP_UMB.type = Medical DP_UMB_DISPLAY.normal_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_cont_ytd where period_id = 0 Else leave blank
irs_5498sa.reg_MSA_contrib
Total Contributions Made in XXXX XXXX = Current Tax Year
2 Decimal (9) Sum of DP_UMB_DISPLAY.normal_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.empl_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_cont_ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_empl_cont_ytd where period_id = 1
irs_5498sa.total_contrib
© Finastra | Fusion Phoenix | IRS Tables 79
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498sa irs_recipient
Total MSA Contribution in YYYY for XXXX Where XXXX = Current Tax Year YYYY = Next Tax Year
3 Decimal (9) DP_UMB_DISPLAY.prev_yr_cont_ytd where period_id = 0 + DP_UMB_DISPLAY.prev_yr_empl_cont_ytd where period_id = 0
irs_5498sa.total_contrib_sa
Rollover Contribution
4 Decimal (9) DP_UMB_DISPLAY.roll_cont_ytd where period_id = 1
irs_5498sa. rollover_contrib
Fair Market Value of MSA
5 Decimal (9) Sum of DP_UMB_DISPLAY.begin_bal where period_id = 0 + DP_UMB_DISPLAY.dec_31_accr_int where period_id = 1 If period_id = 1 does not exist then calculate as DP_UMB_DISPLAY.begin_bal where period_id = 0 Note: If calculated FMV is negative, populate using 0.00
irs_5498sa.FMV_MSA
Plan Type
6 Char (1) If DP_UMB.type = Medical pass A If DP_UMB.type = Health pass H
irs_5498sa.plan_type
Filing Status Varchar (25) N/A irs_5498sa.filing_status
© Finastra | Fusion Phoenix | IRS Tables 80
5498-ESA - Retirement Plan Summary The 5498esa task generates all the information required to produce form 5498-ESA. The 5498esa contains the following information for each active Coverdell ESA retirement plan umbrella in the system.
IMPORTANT! Remember to schedule the 5498 task for the national retirement contribution cut-off date. Go to the Year End Processing Guide for more information.
Include in the irs_5498esa table when:
• DP_UMB.type = Education • DP_UMB_DISPLAY.normal_cont.ytd > 0 where period_id = 1 or DP_UMB_DISPLAY.prev_yr_cont_ytd > 0 where period_id = 0 • DP_UMB_DISPLAY.roll_cont_ytd > 0 where period_id = 1
Selection Criteria Do not need to generate to the table if:
• Third Party IRS Reporting = Y - DP_UMB.third_party_irs_rpt = Y • Plan beginning balance was $0.00 and no contributions or rollovers were made.
DP_UMB_DISPLAY.normal_cont_ytd = 0 where period_id = 1 and
DP_UMB_DISPLAY.roll_cont_ytd = 0 where period_id = 1 and
DP_UMB_DISPLAY.prev_yr_cont_ytd = 0 where period_id = 0
Select accounts in plan where:
DP_UMB.type = Education and DP_UMB,third_party_irs_rpt = ‘N’ and DP_UMB_DISPLAY_DIST.fmv_at_death = ‘N’ and (DP_UMB_DISPLAY.normal_cont.ytd > 0 where period_id = 1 or DP_UMB_DISPLAY.roll_cont_ytd > 0 where period_id = 1 or DP_UMB_DISPLAY.prev_yr_cont_ytd > 0 where period_id = 0)
© Finastra | Fusion Phoenix | IRS Tables 81
Task Information
Form Description Task Task_id Stored Proc
5498-ESA Coverdell ESA Contribution Information
5498-ESA System Generation
605 psp_irs_5498esa
Table Mapping [Trustee]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 82
Table Mapping [Participant]
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498esa irs_recipient
Tax Year 20XX Int (4) irs_5498esa.tax_year
TIN Type Char (1) irs_5498esa.tin_type
Federal Tax ID Recipient ID Varchar (15) RM_ACCT.tin If irs_1099int.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_2
© Finastra | Fusion Phoenix | IRS Tables 83
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498esa irs_recipient
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099int.rim_no IS NULL Address_line_3
© Finastra | Fusion Phoenix | IRS Tables 84
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498esa irs_recipient
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099int.rim_no IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr Char (20) DP_UMB_DISPLAY.plan_no irs_5498esa.plan_no
Coverdell ESA Contribution
1 Decimal (9) DP_UMB_DISPLAY.normal_cont.ytd where period_id = 1 + DP_UMB_DISPLAY.prev_yr_cont.ytd where period_id = 0
irs_5498esa.coverdell_esa_contrib
Rollover Contribution
2 Decimal (9) DP_UMB_DISPLAY.roll_cont_ytd where period_id = 1
irs_5498esa.rollover_contrib
© Finastra | Fusion Phoenix | IRS Tables 85
Field Name Box Nbr Data Type Source Data during Task Generation irs_5498esa irs_recipient
Filing Status Varchar (25) N/A irs_5498esa.filing_status
© Finastra | Fusion Phoenix | IRS Tables 86
Loan Account Year-End Tables Task:
• 1098 - All Accounts • 1098-E - All Accounts • 1099-A - Secured Property • 1099-C - Cancellation of Debt • 1099-MISC - Miscellaneous Income
1098 - All Accounts The 1098 task generates all the information required to produce Form 1098. The file contains all loan accounts with the 1098 option selected in the IRS group box on the Reporting information tab of the Add New/Edit Existing Loan Account window, regardless of interest paid.
Report when:
• Loan is 1098 reportable on Form 1098. • Any amount of 1098 reportable interest and/or fees are to be reported. • Taxes paid do not need to be reported.
Note: This file includes the PMI amount.
Selection Criteria SELECT RM_ACCT.tin, RTRIM(RM_ACCT.last_name + ' ' + RM_ACCT.suffix), RM_ACCT.first_name, RM_ACCT.middle_initial, RM_ADDRESS.address_line_1, RM_ADDRESS.address_line_2, RM_ADDRESS.address_line_3, RM_ADDRESS.city, RM_ADDRESS.state, RM_ADDRESS.zip, LN_DISPLAY.acct_type, LN_DISPLAY.acct_no, LN_ACCT_INT_OPT.int_type, LN_ACCT_INT_REB.earn_ytd, LN_ACCT_INT_OPT.int_pd_ytd, LN_DISPLAY.int_ppd_ytd, LN_DISPLAY.accr_dr, LN_DISPLAY.int_refund_ytd, LN_DISPLAY.late_fees_pd_ytd, LN_DISPLAY.class_code,
© Finastra | Fusion Phoenix | IRS Tables 87
LN_DISPLAY.pd_ytd_1098, LN_DISPLAY.status, RM_ADDRESS.international, RM_ACCT.rim_type, AD_GB_COUNTRY.description, RM_ACCT.rim_no, LN_DISPLAY.re_taxes_pd_pytd, LN_ACCT.branch_no FROM RM_ACCT, RM_ADDRESS, LN_DISPLAY, RM_DISPLAY, LN_ACCT, LN_ACCT_INT_OPT, LN_ACCT_INT_REB, AD_GB_COUNTRY WHERE RM_ACCT.rim_no = LN_DISPLAY.rim_no and RM_ACCT.rim_no = RM_ADDRESS.rim_no and RM_ACCT.rim_no = RM_DISPLAY.rim_no and LN_ACCT.irs = 'y' and LN_DISPLAY.part_count>= 0 and LN_DISPLAY.acct_no = LN_ACCT_INT_OPT.acct_no and LN_DISPLAY.acct_type = LN_ACCT_INT_OPT.acct_type and LN_DISPLAY.acct_no = LN_ACCT.acct_no and LN_DISPLAY.acct_type = LN_ACCT.acct_type and LN_DISPLAY.acct_no *= LN_ACCT_INT_REB.acct_no and LN_DISPLAY.acct_type *= LN_ACCT_INT_REB.acct_type and ( LN_DISPLAY.status != 'closed' or ( LN_DISPLAY.status = 'closed' and LN_DISPLAY.closed_dt between @pdtfrom and @pdtto ) ) and RM_ADDRESS.country_code = ad_gb_country.country_code and RM_ADDRESS.addr_id = ( select max(x.addr_id) FROM RM_ADDRESS x WHERE x.rim_no = RM_ACCT.rim_no and ( ( x.addr_type_id = 10 and @pdtto between x.start_dt and x.end_dt and x.status = 'active' ) or x.addr_type_id = 1) ) ORDER BY RM_ACCT.last_name, RM_ACCT.first_name
© Finastra | Fusion Phoenix | IRS Tables 88
Note: Participated loans are excluded from reporting. The exclusion is defined as LN_DISPLAY.part_count >= 0.
Task Information
Form Description Task Task_id Stored Proc
1098 Mortgage Interest Statement
1099 System Generation
601 psp_irs_1098
Table Mapping [Borrower]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 89
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098 irs_recipient
Tax Year 20XX Int (4) irs_1098.tax_year
TIN Type Char (1) irs_1098.tin_type
Federal Tax ID Recipient ID
Varchar (15) RM_ACCT.tin If irs_1099int.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_2
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_1
© Finastra | Fusion Phoenix | IRS Tables 90
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098 irs_recipient
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099int.rim_no IS NULL Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099int.rim_no IS NULL irs_recipient.zip
Foreign State/ Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
© Finastra | Fusion Phoenix | IRS Tables 91
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098 irs_recipient
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr
Char (20) LN_ACCT.acct_type - LN_ACCT.acct_no irs_1098.acct_type + irs_1098.acct_no
Mortgage Interest Received from Payer(s)
1 Decimal (9) If LN_ACCT_INT_OPT.int_type = ‘Add-On’, this will be equal to LN_ACCT_INT_REB.earn_ytd. To this, the system will add LN_DISPLAY.late_fees_pd_ytd if the late fees are 1098 reportable and any prepayment penalties that are also 1098 reportable. For all other interest types, this will be equal to LN_ACCT_INT_OPT.int_pd_ytd. To this, the system will add LN_DISPLAY.late_fees_pd_ytd if the late fees are 1098 reportable and any prepayment penalties that are also 1098 reportable. And, if the accrued interest is currently less than zero, the accrued interest is added to this total.
irs_1098.int_paid
Original Mortgage Principal
2 Decimal (9) LN_DISPLAY.cur_yr_begin_bal irs_1098.cur_bal
Mortgage Origination Date
3 Smalldatetime (4)
LN_DISPLAY.orig_contract_dt irs_1098.orig_dt
Refund of Overpaid Interest
4 Decimal (9) LN_DISPLAY.int_refund_ytd irs_1098.
© Finastra | Fusion Phoenix | IRS Tables 92
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098 irs_recipient
Mortgage Insurance Premium
5 Decimal (9) SUM of LN_ESCROW_AGENT.disb_ytd for escrow agent records configured for PMI. Note: For current tax year, PMI is reported.
Irs_1098.ins_premium
Points Paid on Purchase of Principal Residence
6 Decimal (9) LN_DISPLAY.pd_ytd_1098 minus any prepayment penalties that were paid that are 1098 reportable.
Irs_1098.points_paid
Address or Property Securing Mortgage
7 Char (1) LN_ACCT.property_securing_same irs_1098.address_secure
© Finastra | Fusion Phoenix | IRS Tables 93
Address or Property Securing Mortgage
8 Varchar (120) If LN_COLLATERAL.primary = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ or RM_FIN_STMT.entry_type = ‘Vehicle’ and RM_FIN_STMT.1098_rep_propr = ‘Y’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_stmt_item_id, INSERT RM_FIN_STMT.address_line_1 CONCATENATE If LN_COLLATERAL.primary = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ or RM_FIN_STMT.entry_type = ‘Vehicle’ and RM_FIN_STMT.1098_rep_propr = ‘Y’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_stmt_item_id, INSERT RM_FIN_STMT.address_line_2. CONCATENATE If LN_COLLATERAL.primary = ‘Y’ or RM_FIN_STMT.entry_type = ‘Vehicle’ and RM_FIN_STMT.1098_rep_propr = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_stmt_item_id, INSERT RM_FIN_STMT.city. CONCATENATE If LN_COLLATERAL.primary = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ or RM_FIN_STMT.entry_type = ‘Vehicle’ and
irs_1098.property_securing
© Finastra | Fusion Phoenix | IRS Tables 94
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098 irs_recipient
RM_FIN_STMT.1098_rep_propr = ‘Y’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_stmt_item_id, INSERT RM_FIN_STMT.state. CONCATENATE If LN_COLLATERAL.primary = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ or RM_FIN_STMT.entry_type = ‘Vehicle’ and RM_FIN_STMT.1098_rep_propr = ‘Y’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_sm,t_item_id, INSERT RM_FIN_STMT.zip. CONCATENATE Only populate this box if LN_ACCT.property_securing_same = ‘N’ If LN_COLLATERAL.primary = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_stmt_item_id, INSERT RM_FIN_STMT.country_code. ***Note: The country code will appear after the property zip.
© Finastra | Fusion Phoenix | IRS Tables 95
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098 irs_recipient
Address or Property Securing Mortgage Cont.
8 Varchar (120) If LN_COLLATERAL.primary = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ or RM_FIN_STMT.entry_type = ‘Vehicle’ and RM_FIN_STMT.1098_rep_propr = ‘Y’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_sm,t_item_id, INSERT RM_FIN_STMT.jurisdiction. If LN_COLLATERAL.primary = ‘Y’ where LN_COLLATERAL.acct_type = LN_ACCT.acct_type and LN_COLLATERAL.acct_no = LN_ACCT.acct_no And RM_FIN_STMT.entry_type = ‘Property’ or RM_FIN_STMT.entry_type = ‘Vehicle’ and RM_FIN_STMT.1098_rep_propr = ‘Y’ where RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_stmt_item_id, INSERT RM_FIN_STMT.parcel_id.
irs_1098.property_desc
© Finastra | Fusion Phoenix | IRS Tables 96
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098 irs_recipient
Number of Properties Securing the Mortgage
9 Char (4) If LN_ACCT.irs = 'Y' LN_COLLATERAL.acct_type = LN_ACCT.acct_type LN_COLLATERAL.acct_no = LN_ACCT.acct_no RM_FIN_STMT.fin_stmt_item_id = LN_COLLATERAL.fin_stmt_item_id RM_FIN_STMT.entry_type = 'Property' or RM_FIN_STMT.entry_type = 'Vehicle' and RM_FIN_STMT.1098_rep_prop = 'Y' LN_COLLATERAL.status != 'Closed', 'Inactive' LN_DISPLAY.status != 'Closed' or LN_DISPLAY.status = 'Closed' and LN_DISPLAY.closed_dt between 01/01/2017 and 12/31/2017 RM_FIN_STMT.status != ‘Closed’ Sum up number of rows found attached to account.
irs_1098.no_properties
Taxes Paid 10 Char (39) irs_1098.other
Mortgage Acquition Date
11 Smalldatetime (4)
LN_ACCT.acquisition_dt irs_1098.acquition_dt
Filing Status Varchar (25) N/A irs_1098.filing_status
© Finastra | Fusion Phoenix | IRS Tables 97
1098-E - All Accounts IMPORTANT! When importing in IRS Tax Reporting for Fusion Phoenix, choose ONE 1098 and ONE 1098-E, if applicable. Refer to the IRS Tax Reporting for Fusion Phoenix Release Installation and Processing Instructions for more information.
Report when:
• a loan is 1098-E reportable on Form 1098-E.
Selection Criteria SELECT RM_ACCT.tin, rtrim(RM_ACCT.last_name + ' ' + RM_ACCT.suffix), RM_ACCT.first_name, RM_ACCT.middle_initial, RM_ADDRESS.address_line_1, RM_ADDRESS.address_line_2, RM_ADDRESS.address_line_3, RM_ADDRESS.city, RM_ADDRESS.state, RM_ADDRESS.zip, LN_DISPLAY.acct_type, LN_DISPLAY.acct_no, LN_ACCT_INT_OPT.int_type, LN_ACCT_INT_REB.earn_ytd, LN_ACCT_INT_OPT.int_pd_ytd, LN_DISPLAY.int_ppd_ytd, LN_DISPLAY.accr_dr, LN_DISPLAY.int_refund_ytd, LN_DISPLAY.late_fees_pd_ytd, LN_DISPLAY.class_code, LN_DISPLAY.pd_ytd_1098, LN_DISPLAY.status, RM_ADDRESS.INTERNATIONAL, RM_ACCT.RIM_TYPE, AD_GB_COUNTRY.DESCRIPTION, RM_ACCT.rim_no, LN_DISPLAY.re_taxes_pd_pytd, LN_ACCT.branch_no FROM RM_ACCT, RM_ADDRESS, LN_DISPLAY, RM_DISPLAY, LN_ACCT, LN_ACCT_INT_OPT, LN_ACCT_INT_REB, AD_GB_COUNTRY WHERE RM_ACCT.rim_no = LN_DISPLAY.rim_no and RM_ACCT.rim_no = RM_ADDRESS.rim_no and RM_ACCT.rim_no = RM_DISPLAY.rim_no and
© Finastra | Fusion Phoenix | IRS Tables 98
LN_ACCT.irs = 'Y' and LN_DISPLAY.part_count>= 0 and LN_DISPLAY.acct_no = LN_ACCT_INT_OPT.acct_no and LN_DISPLAY.acct_type = LN_ACCT_INT_OPT.acct_type and LN_DISPLAY.acct_no = LN_ACCT.acct_no and LN_DISPLAY.acct_type = LN_ACCT.acct_type and LN_DISPLAY.acct_no *= LN_ACCT_INT_REB.acct_no and LN_DISPLAY.acct_type *= LN_ACCT_INT_REB.acct_type and ( LN_DISPLAY.status != 'Closed' OR ( LN_DISPLAY.status = 'Closed' and LN_DISPLAY.closed_dt between @pdtFrom and @pdtTo ) ) and RM_ADDRESS.COUNTRY_CODE = AD_GB_COUNTRY.COUNTRY_CODE and RM_ADDRESS.ADDR_ID = ( SELECT MAX(X.ADDR_ID) FROM RM_ADDRESS X WHERE X.RIM_NO = RM_ACCT.RIM_NO and ( ( X.ADDR_TYPE_ID = 10 and @pdtTo BETWEEN X.START_DT and X.END_DT and X.STATUS = 'Active' ) OR X.ADDR_TYPE_ID = 1) ) ORDER BY RM_ACCT.last_name, RM_ACCT.first_name
Note: Participated loans are excluded from reporting. The exclusion is defined as LN_DISPLAY.part_count >= 0.
Task Information
Form Description Task Task_id Stored Proc
1098_ Student Interest 1098-E System Generation
602 psp_irs_1098e
Table Mapping [Trustee]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
© Finastra | Fusion Phoenix | IRS Tables 99
Field Name Data Type ad_irs_filer
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 100
Table Mapping [Participent]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098e irs_recipient
Tax Year 20XX Int (4) irs_1098e.tax_year
TIN Type Char (1) irs_1098e.tin_type
Federal Tax ID Recipient ID Varchar (15) RM_ACCT.tin
Name Recipient’s/ Lender’s Name
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.tin
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_140
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
© Finastra | Fusion Phoenix | IRS Tables 101
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098e irs_recipient
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099int.rim_no IS NULL Irs_recipient.Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099int.rim_no IS NULL irs_recipient.zip
Foreign State/ Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
© Finastra | Fusion Phoenix | IRS Tables 102
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098e irs_recipient
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr Char (20) LN_ACCT.acct_type - LN_ACCT.acct_no irs_1098e.acct_type + irs_1098e.acct_no
Student Loan Interest Received by Lender
1 Decimal (9) If LN_ACCT_INT_OPT.int_type = ‘Add-On’, this will be equal to LN_ACCT_INT_REB.earn_ytd. To this, the system will add LN_DISPLAY.late_fees_pd_ytd if the late fees are 1098 reportable and any prepayment penalties that are also 1098 reportable. For all other interest types, this will be equal to LN_ACCT_INT_OPT.int_pd_ytd. To this, the system will add LN_DISPLAY.late_fees_pd_ytd if the late fees are 1098 reportable and any prepayment penalties that are also 1098 reportable. And, if the accrued interest is currently less than zero, the accrued interest is added to this total.
irs_1098e.student_int_rec
Box 1 does not include loan origination fees and/or capitailized interest and loan was made before September 1, 2004
2 Char (1) If LN_ACCT_INT_OPT.cap_int = Y And LN_ACCT_INT_OPT.int_pd_ytd > 0 or LN_ACCT_INT_REB.earn_ytd > 0 or LN_DISPLAY.pd_ytd_1098 > 0 or LN_ACCT.contract_dt >= '2004-09-04' Print <blank space> else Print Y
irs_1098e.no_orig_fee
© Finastra | Fusion Phoenix | IRS Tables 103
Field Name Box Nbr Data Type Source Data during Task Generation irs_1098e irs_recipient
Filing Status Varchar (25) N/A irs_1098e.filing_status
© Finastra | Fusion Phoenix | IRS Tables 104
1099-A - Secured Property The 1099a.nnn file contains all the information required to produce form 1099-A. The 1099a.nnn file contains the following information for each loan with an acquisition or abandonment.
Report when:
• the financial institution acquires an interest in property that is security for the debt, or you have reason to know that the property has been abandoned.
Selection Criteria SELECT RM_ACCT.tin, RM_ACCT.last_name, RM_ACCT.first_name, RM_ACCT.middle_initial, RM_ACCT.suffix, RM_ACCT.rim_type, RM_ADDRESS.address_line_1, RM_ADDRESS.address_line_2, RM_ADDRESS.address_line_3, RM_ADDRESS.city, RM_ADDRESS.state, RM_ADDRESS.zip, LN_1099A.acquisition_dt, LN_1099A.debt_outstanding, LN_1099A.bid_or_value, LN_1099A.description, LN_1099A.borrower_liable, LN_1099A.rim_no, LN_1099A.fin_stmt_item_id FROM RM_ACCT, RM_ADDRESS, LN_1099A WHERE RM_ACCT.rim_no = RM_ADDRESS.rim_no and RM_ACCT.rim_no = ln_1099a.rim_no and RM_ADDRESS.addr_type_id = 1 and ln_1099a.status = 'active' and ln_1099a.report_owner = 'Y' and ln_1099a.acquisition_dt between $FromYearEnddt and $ToYearEnddt
© Finastra | Fusion Phoenix | IRS Tables 105
Task Information
Form Description Task Task_id Stored Proc
1099-A Acquisition or Abandonment of Secured Property
1099-A System Generation
596 psp_irs_1099a
Table Mapping [Lender]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 106
Table Mapping [Borrower]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099a irs_recipient
Tax Year 20XX Int (4) irs_1099a.tax_year
TIN Type Char (1) irs_1099a.tin_type
Federal Tax ID Recipient ID Varchar (15) RM_ACCT.tin If irs_1099a.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099a.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099a.rim_no IS NULL irs_recipient.Name_2
© Finastra | Fusion Phoenix | IRS Tables 107
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099a irs_recipient
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1099a.rim_no IS NULL irs_recipient.Address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099a.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099a.rim_no IS NULL Address_line_3
US/Foreign City
Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099a.rim_no IS NULL irs_recipient.city
© Finastra | Fusion Phoenix | IRS Tables 108
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099a irs_recipient
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099a.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099a.rim_no IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country
Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr Char (20) LN_1099a.rim_no - LN_1099a.fin_stmt_item_id
irs_1099a.acct_type + irs_1099a.acct_no
Acquisition or Abandonment Date
1 Smalldaterime (4)
LN_1099A.acquisition_dt Month portion + LN_1099A.acquisition_dt Day Porrtion + LN_1099A.acquisition_dt Year Portion
irs_1099a. acquisition_abandon_dt
Balance of Principal Outstanding
2 Decimal (9) LN_1099A.debt_outstanding irs_1099a. bal_prin_outstanding
© Finastra | Fusion Phoenix | IRS Tables 109
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099a irs_recipient
Fair Market Value of Property
4 Decimal (9) LN_1099A.bid_or_value irs_1099a. FMV_property
Borrower Liable for Repayment of the Debt
5 Char (1) LN_1099A.borrower_liable irs_1099a. borrower_liable_repay_debt
Property Description
6 Varchar (39) LN_1099A.description irs_1099a. property_desc
Filing Status Varchar (25) N/A irs_1099a.filing_status
© Finastra | Fusion Phoenix | IRS Tables 110
1099-C - Cancellation of Debt The 1099c task generates all the information required to produce Form 1099-C. The file contains all information for each loan that had a debt cancellation.
Report when:
• the financial institution canceled a debt owned.
Selection Criteria The selection criteria must be modified. Currently, it’s pulling records from LN_1099C and retrieving the customer/member information based on the rim_no for the corresponding account record in LN_DISPLAY (shown below).
SELECT RM_ACCT.TIN, rtrim(RM_ACCT.LAST_NAME + ' ' + RM_ACCT.SUFFIX), RM_ACCT.FIRST_NAME, RM_ACCT.MIDDLE_INITIAL, RM_ADDRESS.ADDRESS_LINE_1, RM_ADDRESS.ADDRESS_LINE_2, RM_ADDRESS.ADDRESS_LINE_3, RM_ADDRESS.CITY, RM_ADDRESS.STATE, RM_ADDRESS.ZIP, LN_1099C.ACCT_NO, LN_1099C.ACCT_TYPE, LN_1099C.CANCELED_DT, LN_1099C.CANCELED_DEBT, LN_1099C.CANCELED_INT, LN_1099C.COMBINED_1099A, LN_1099C.DESCRIPTION, LN_1099C.IDENTIFIABLE_EVENT, RM_ADDRESS.INTERNATIONAL, AD_GB_COUNTRY.DESCRIPTION, RM_ACCT.RIM_TYPE, RM_ACCT.RIM_NO, LN_1099C.BORROWER_LIABLE FROM RM_ACCT, RM_ADDRESS, LN_DISPLAY, LN_1099C, AD_GB_COUNTRY WHERE LN_DISPLAY.ACCT_NO = LN_1099C.ACCT_NO and LN_DISPLAY.ACCT_TYPE = LN_1099C.ACCT_TYPE and RM_ACCT.RIM_NO = LN_1099C.RIM_NO and RM_ACCT.RIM_NO = RM_ADDRESS.RIM_NO and
© Finastra | Fusion Phoenix | IRS Tables 111
LN_1099C.status = 'Active' and LN_1099C.year= YYYY and RM_ADDRESS.ADDR_ID = ( SELECT MAX(X.ADDR_ID) FROM RM_ADDRESS X WHERE X.RIM_NO = RM_ACCT.RIM_NO and ( ( X.ADDR_TYPE_ID = 10 and ‘YYYY-12-31’ BETWEEN X.START_DT and X.END_DT and X.STATUS = 'Active' ) OR X.ADDR_TYPE_ID = 1)) and RM_ADDRESS.COUNTRY_CODE = AD_GB_COUNTRY.COUNTRY_CODE
Task Information
Form Description Task Task_id Stored Proc
1099-C Cancellation of Debt 1099-C System Generation
597 psp_irs_1099c
Table Mapping [Creditor]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 112
Table Mapping [Debtor]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099c irs_recipient
Tax Year 20XX Int (4) irs_1099c.tax_year
TIN Type Char (1) irs_1099c.tin_type
TIN Recipient ID
Varchar (15) RM_ACCT.tin If irs_1099c.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099c.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099c.rim_no IS NULL irs_recipient.Name_2
© Finastra | Fusion Phoenix | IRS Tables 113
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099c irs_recipient
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1099c.rim_no IS NULL irs_recipient.Address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099c.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099c.rim_no IS NULL Address_line_3
US/Foreign City
Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099c.rim_no IS NULL irs_recipient.city
© Finastra | Fusion Phoenix | IRS Tables 114
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099c irs_recipient
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099c.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099c.rim_no IS NULL irs_recipient.zip
Foreign State/ Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country
Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr
Char (20) LN_1099C.acct_type - LN_1099C.acct_no irs_1099c.acct_type + irs_1099c.acct_no
Date of Identifiable Event
1 Smalldatetime (4)
LN_1099C.canceled_dt Month Portion + LN_1099C.canceled_dt Day Portion + LN_1099C.canceled_dt Year Portion
irs_1099c.identifiable_event_dt
Amount of Debt Discharged
2 Decimal (9) LN_1099C.canceled_debt irs_1099c.amt_debt_discharged
© Finastra | Fusion Phoenix | IRS Tables 115
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099c irs_recipient
Interest Included in Box 2
3 Char (12) LN_1099C.canceled_int irs_1099c.interest_included
Debt Description
4 Char (39) LN_1099C.description irs_1099c.debt_desc
Personal Liable for Repmt
5 Char (1) LN_1099C.borrower_liable irs_1099c.personal_liable_repay
Identifiable Event
6 Char (1) LN_1099C.identifiable_event irs_1099c.identifiable_event_code
FMV of Property
7 Char (12) LN_1099C.combined_1099a irs_1099c.FMV_property
Filing Status Varchar (25) N/A irs_1099c.filing_status
© Finastra | Fusion Phoenix | IRS Tables 116
1099-MISC - Miscellaneous Income The 1099misc task generates all the information required to produce Form 1099-MISC. The file contains all the following information for each contractor that had reportable miscellaneous income for the calendar year.
Report when:
• gross proceeds to an attorney
Selection Criteria SELECT RM_ACCT.tin, RM_ACCT.last_name, RM_ACCT.first_name, RM_ACCT.middle_initial, RM_ADDRESS.address_line_1, RM_ADDRESS.address_line_2, RM_ADDRESS.address_line_3, RM_ADDRESS.city, RM_ADDRESS.state, RM_ADDRESS.zip, RM_3RD_CONTRACT.status, RM_3RD_CONTRACT.misc_1099_pmts_ytd LTRIM(RTRIM(RM_3RD_CONTRACT.sub_type)), RM_ACCT.rim_no, RM_ADDRESS.name_1 RTRIM(RM_ACCT.suffix) FROM RM_ACCT , RM_ADDRESS , RM_3RD_CONTRACT WHERE RM_ACCT.rim_no = RM_ADDRESS.rim_no and RM_ACCT.rim_no = rm_3rd_contract.rim_no and rm_3rd_contract.misc_1099_pmts_ytd > 0 and rm_3rd_contract.misc_1099 = 'Y'
Task Information
Form Description Task Task_id Stored Proc
1099-MISC
Miscellaneous Income 1099-MISC System Generation
598 psp_irs_1099misc
© Finastra | Fusion Phoenix | IRS Tables 117
Table Mapping [Payer]
Field Name Data Type ad_irs_filer
Federal Tax ID Char (11) ad_irs_filer.federal_tax_id
Name 1 Char (40) ad_irs_filer.payer_name_1
Name 2 Char (40) ad_irs_filer.payer_name_2
Address Char (40) ad_irs_filer.payer_address
City Char (40) ad_irs_filer.city
State Char (2) ad_irs_filer.state
Zip Char (10) ad_irs_filer.zip
© Finastra | Fusion Phoenix | IRS Tables 118
Table Mapping [Recipient]
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099misc irs_recipient
Tax Year 20XX Int (4) irs_1099misc.tax_year
TIN Type Char (1) irs_1099misc.tin_type
Federal Tax ID Recipient ID Varchar (15) RM_ACCT.tin If irs_1099int.rim_no IS NULL irs_recipient.tin
Name 1 Recipient Name 1
Char (40) If RM_ACCT.rim_type = Personal RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_1
Name 2 Recipient Name 2
Char (40) Note: Only 5498 type exports Name 2. If RM_ACCT.rim_type = Personal As Bene of RM_ACCT.last_name RM_ACCT.suffix ‘,’ RM_ACCT.first_name RM_ACCT.middle_initial If RM_ACCT.rim_type = NonPersonal As Bene of RM_ACCT.last_name RM_ACCT.first_name
If irs_1099int.rim_no IS NULL irs_recipient.Name_2
© Finastra | Fusion Phoenix | IRS Tables 119
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099misc irs_recipient
Street 1 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_1 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 If RM_ADDRESS.international = Y Select RM_ADDRESS.address_line_1
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_1
Street 2 Address Char (40) If RM_ADDRESS.international = N If RM_ADDRESS.address_line_3 is NULL Select RM_ADDRESS.address_line_2 Else If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_3 If RM_ADDRESS.international = Y If RM_ADDRESS.address_line_3 is Not NULL Select RM_ADDRESS.address_line_2 Else blank
If irs_1099int.rim_no IS NULL irs_recipient.Address_line_2
Street 3 If irs_1099int.rim_no IS NULL Address_line_3
US/Foreign City Address Char (25) If RM_ADDRESS.international = N RM_ADDRESS.city (25) If RM_ADDRESS.international = Y ‘1’ then ‘RM_ADDRESS.city’ Note: No spaces between 1 and city
If irs_1099int.rim_no IS NULL irs_recipient.city
© Finastra | Fusion Phoenix | IRS Tables 120
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099misc irs_recipient
US State Address Char (2) If RM_ADDRESS.international = N RM_ADDRESS.state (5) Else blank
If irs_1099int.rim_no IS NULL irs_recipient.state
US ZIP Address Char (9) If RM_ADDRESS.international = N RM_ADDRESS.zip (10) strip out ‘-‘ Else blank
If irs_1099int.rim_no IS NULL irs_recipient.zip
Foreign State/Province
Address Char (2) If RM_ADDRESS.international = Y RM_ADDRESS.state (5) Else blank
If irs_recipient.international= Y irs_recipient.state Else blank
Foreign Postal Code
Address Char (9) If RM_ADDRESS.international = Y RM_ADDRESS.zip (10) Else blank
If irs_recipient.international= Y irs_recipient.zip Else blank
Foreign Country Address Char (25) If RM_ADDRESS.international = Y AD_GB_COUNTRY.description (25) Where RM_ADDRESS.country_code = AD_GB_COUNTRY.country_code Else blank
If irs_recipient.international= Y irs_recipient.country_code (2) Else blank
Account # Account Nbr Char (20) RM_3RD_CONTRACT.rim_no - RM_3RD_CONTRACT.rm_3RD_contract_id
irs_1099misc.acct_type + irs_1099misc.acct_no
Rent 1 Decimal (9) irs_1099misc.rents
Royalties 2 Decimal (9) irs_1099misc.royalties
Other Income 3 Decimal (9) RM_3RD_CONTRACT.misc_1099_pmts_ytd where sub_type != ‘Attorney’
irs_1099misc.other_income
Federal Income Tax Withheld
4 Decimal (9) N/A irs_1099misc.fed_income_tax_whld
Boat Proceeds 5 Decimal (9) N/A irs_1099misc.fishing_boat_proceeds
© Finastra | Fusion Phoenix | IRS Tables 121
Field Name Box Nbr Data Type Source Data during Task Generation irs_1099misc irs_recipient
Health Care Pmt
6 Decimal (9) N/A irs_1099misc.medical_health_care
Direct Sales $5000+
7 Char (1) N/A irs_1099misc.direct_sale
Sub Pmt in Lieu 8 Decimal (9) N/A irs_1099misc.substitute_payment
Crop Insurance Procceds
9 Decimal (9) N/A irs_1099misc.crop_insurance
Gross Proceeds Paid to Attorney
10 Decimal (9) RM_3RD_CONTRACT.misc_1099_pmts_ytd where sub_type = ‘Attorney’
irs_1099misc.gross_proceeds
Section 409A Deferral
12 Decimal (9) N/A irs_1099misc.deferrals
Excess Golden Parachute Pmt
13 Decimal (9) N/A irs_1099misc.excess_payments
Section 409A Income
14 Decimal (9) N/A irs_1099misc.income
State Tax Withheld
15 Decimal (9) N/A irs_1099misc.state_tax_whld
State Payer’s State No
16 Char (2) N/A irs_1099misc.state_no
State Income 17 Decimal (9) N/A irs_1099misc.state_income
Filing Status Varchar (25) N/A irs_1099misc.filing_status
© Finastra | Fusion Phoenix | IRS Tables 122
Finastra Support Finastra support offers several options to help you get the most out of your software, including a self-service Case Management tool, and phone support.
Please visit the Finastra Customer Center at https://customercenter.dh.com/ to log in to our online self-service Case Management system. If you forgot your password, simply click the Forgot Password link. Once logged in to Customer Center, you have the ability to use the Knowledge Center to troubleshoot issues and answer questions.
If your financial institution is not currently using these tools and would like to, please contact Finastra support for assistance.
Note: The Financial Modernization Act of 1999, also known as the Gramm-Leach-Bliley Act or GLB Act, includes provisions to protect consumers' personal financial information held by financial institutions. Therefore, Finastra support cannot accept data or screen captures that contain personal financial information via email or fax. For information about secure file transfer methods, contact Finastra support.
About Finastra Finastra is building an open platform that accelerates collaboration and innovation in financial services, creating better experiences for people, businesses and communities. Supported by the broadest and deepest portfolio of financial services software, Finastra delivers this vitally important technology to financial institutions of all sizes across the globe, including 90 of the world’s top100 banks. Our open architecture approach brings together a number of partners and innovators. Together we are leading the way in which applications are written, deployed and consumed in financial services to evolve with the changing needs of customers. Learn more at finastra.com
North American Headquarters 744 Primera Boulevard
Suite 2000 Lake Mary, FL 32746
United States
T: +1 888 989 9009
The future of digital finance can start here. Consider our planet before printing.