Included page: .FrontPage.QA.IRISTen99Filing.SetUp (edit)
| import |
| TBOT.Generic.Database |
| ddt: SetUp | |
| key | value |
| ConnectionString | Server=10.153.120.216,1600;database=AUTOTESTING;User Id=automation; password=automation*100;Timeout=45 |
| SourceDirectory | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\OTTDATAQA\IRISEfiling\TESTCLIENT1\2024\554M |
| TargetDirectory | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations |
| ExpectedDir | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations |
| ActualDir | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations |
| ResultDir | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations |
| DownloadPath | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\OTTDATAQA\IRISEfiling\TESTCLIENT1\2024\554M |
| CompareResultDir | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations |
| ExpectedDirectory | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations |
| BytesColumn | DataObject |
| FileExtension | ZIP |
| import |
| TBOT.Generic.Database |
| import |
| TBOT.Generic |
Get XML File
Extract and copy the xml file for assertion
| ddt: DynamicQuery | Select AcctNo As PAN,LTRIM(RTRIM(Server)) As Server,LTRIM(RTRIM(DB_NAME)) As DBName from BTMDB..Package where AcctNo='{{PAN}}' | ||
| PAN | PAN? | Server? | DBName? |
| 554M | $PAN1= | $Server1= | $DBName1= |
| ddt: KeywordFixture | |||
| FileName | EfileFileExtn | UNZIP? | GETEFILEXML? |
| 554M_2024_1099INT_CFS_O_File_2.zip | xml | True | True |
| ddt: KeywordFixture | ||
| sourcefilename | EXTN | FILECOPYNEW? |
| 554M_2024_1099INT_CFS_O_File_2 | xml | True |
1 IRSubmission1Header With Payer Details
IRSubmission1Header With Form1099INTTotalAmtGrp from XML
| TBOT.Generic.xmlFixture | ||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | extractXmlDataToText? |
| 554M_2024_1099INT_CFS_O_File_2.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Header | true | IRSubmission1FormTotals | $xmldbpath1= |
Payer Details With Form1099INTTotalAmtGrp from Database
| ddt: KeywordFixture | USE {{DBName}} DECLARE @AcctNo VARCHAR(4) = '{{PAN}}', @TaxYear SMALLINT = {{Year}}, @TaxYearseq1 SMALLINT = '{{Year}}0', @TaxYearseq2 SMALLINT = '{{Year}}9', @PayerID smallInt= {{PayerNo}} -- Payer ;with IntData as ( select BR.RecipID, case when BM.State_Cd in ('AL','AZ','AR','CA','CO','CT','DE','GA','HI','ID','IN','KS','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NJ','NM','NC','ND','OH','OK','SC','WI') then --- DW and CFS Check case when BM.State_Cd in ('AZ','CO','DE','GA','KS','LA','MI','MN','MS','NC','ND','SC','WI') then case when BM.StateTaxWithHeld>0 then null else BM.State_Cd end --- DW check when BM.State_Cd in ('AL','AR','NJ','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('AR') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>100 then null --- C$ Check When BM.State_Cd in ('AL') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))<1500 then null When BM.State_Cd in ('MO') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('CA','CT','HI','ID','NE','NM','OK','OH','MT','MA','AR','AL') then BM.State_Cd end else null end as CFSFElectionStateCd from Ten99BERecip BR inner join Ten99BEINT BM on BM.RecipNo=BR.RecipNo and BM.YrSeq=BR.YrSeq and BM.TrustKey=BR.TrustKey and BM.IRSAcctNo is not null --and BM.Filing_Dt=BR.Filing_Dt inner join TEN99YRPAYER TP ON TP.PAYERNO = BM.PAYERNO and TP.Acctno=@AcctNo and TP.Year=@TaxYear inner join TRUSTXREF TX ON BR.TRUSTKEY = TX.TRUSTKEY and TP.AcctNO=TX.AcctNo where BM.Yrseq>=@TaxYearseq1 and BM.Yrseq<=@TaxYearseq2 and TX.AcctNo=@AcctNo and BM.PayerNo=@PayerID and BR.CorSeqNo=(select Max(CorSeqNo) from Ten99BERecip where Trustkey = BR.Trustkey and RecipNo=BR.RecipNo and YrSeq=BR.YrSeq) and BM.CorSeqNO=(select Max(CorSeqNo) from Ten99BEINT where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select 1 as SubmissionId, @TaxYear as TaxYr, CONCAT(case when Tp.PayerForeignPostal is null or TP.PayerForeignPostal='' then '0' else '1' end,--as [ForeignEntityInd], LTRIM(RTRIM(Replace(TP.PayerID,'-',''))) ,--as [TIN], case when charindex('-',TP.PayerID)=3 Then 'BUSINESS_TIN' when charindex('-',TP.PayerID)=4 Then 'INDIVIDUAL_TIN' end ,--as [TINSubmittedTypeCd], case when charindex('-',TP.PayerID)=3 Then SubString(LTRIM(RTRIM(TP.PayerName1)),0,76) end,-- as [BusinessNameLine1Txt], case when charindex('-',TP.PayerID)=3 Then SubString(LTRIM(RTRIM(TP.PayerName2)),0,76) end,-- as [BusinessNameLine2Txt], case when charindex('-',TP.PayerID)=4 Then SubString(LTRIM(RTRIM(TP.PayerName1)),0,21) end,-- as [PersonFirstNm], case when charindex('-',TP.PayerID)=4 Then SubString(LTRIM(RTRIM(TP.PayerName2)),0,21) end, SubString(lTRIM(RTRIM(TP.PayerAddr)),0,36),-- as [AddressLine1Txt], LTRIM(RTRIM(TP.PayerCity)),-- as [CityNm], LTRIM(RTRIM(TP.PayerSt)),-- as [StateAbbreviationCd], SubString(LTRIM(RTRIM(TP.PayerForeignProvince)),0,18), case when cast(Tp.PayerForeignPostal as nvarchar) is null or TP.PayerForeignPostal ='' then null else LTRIM(RTRIM(TP.PayerCountry_Cd)) end, replace(TP.PayerZip,'-','')) as IssuerDetail,-- as [ZIPCd], '1099INT' as FormTypeCd,1096 as ParentFormTypeCd, case when count(T.CFSFElectionStateCd)>0 then 1 else 0 end as CFSFElectionInd, count(T.RecipID) as TotalReportedRcpntFormCnt from Ten99yrPayer TP,IntData T where AcctNO=@AcctNo and Year=@TaxYear and PayerNo =@PayerID Group By TP.PayerID,TP.PayerAddr,TP.PayerCity,TP.PayerSt,TP.PayerZip,TP.PayerName1,TP.PayerName2,TP.PayerName2,TP.PayerCountry_Cd,TP.PayerForeignPostal,TP.PayerForeignProvince | ||||
| PAN | Year | DBName | PayerNo | TableName | Export? |
| $PAN1 | 2024 | $DBName1 | 1 | INTIRSubmission1Header | $txtdtpath1= |
File Comparison
File Compare Block
| TBOT.Generic.SFTP | ||||||
| Comments | sourceFile | destinationFile | destinationFullFilePath | resultFileName | IgnorePattern | compareFiles? |
| Test0001 | $txtdtpath1 | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations\$xmldbpath1 | Yes | Both Files are Equal | ||
2 Form1099INTTotalAmtGrp from IRSubmission1Header
Form1099INTTotalAmtGrp from XML
| TBOT.Generic.xmlFixture | ||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | extractXmlDataToText? |
| 554M_2024_1099INT_CFS_O_File_2.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Header/IRSubmission1FormTotals/Form1099INTTotalAmtGrp | true | $xmldbpath2= | |
Form1099INTTotalAmtGrp from Database
| ddt: KeywordFixture | USE {{DBName}} DECLARE @AcctNo VARCHAR(4) = '{{PAN}}', @TaxYear SMALLINT = {{Year}}, @TaxYearseq1 SMALLINT = '{{Year}}0', @TaxYearseq2 SMALLINT = '{{Year}}9', @PayerID smallInt= {{PayerNo}} ;with IntData as (select SUM(case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntInc<=0 then 0 else Round(BM.IntInc,2) end) +SUM( case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntSavBnds<=0 then 0 else Round(BM.IntSavBnds,2) end) +SUM(case when BM.TEIntInc<10 or BM.TEINTInc<=0 then 0 else Round(BM.TEIntInc,2) end) +SUM(case when BM.MarketDiscount >0 then Round(BM.MarketDiscount,2) else 0 end) +SUM(case when BM.BondPremium >0 then Round(BM.BondPremium,2) else 0 end) +SUM(case when BM.BondPremtreasury>0 then Round(BM.BondPremtreasury,2) else 0 end) as TotalReportedAmt, SUM(case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntInc<=0 then null else Round(BM.IntInc,2) end) as InterestIncomeAmt, SUM(case when BM.EarlyWdPen>0 then Round(EarlyWdPen,2) else null end) as EarlyWithdrawalPenaltyAmt, SUM(case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntSavBnds<=0 then null else Round(BM.IntSavBnds,2) end) as USSavingsBondsTreasObligIntAmt, SUM(case when BM.TaxWithHeld<>0 then Round(ABS(BM.TaxWithHeld),2)end) as FederalIncomeTaxWithheldAmt, SUM(case when BM.InvExp>0 then Round(BM.InvExp,2) else null end) as InvestmentExpenseAmt, SUM(case when BM.ForTaxPaid>0 then Round(BM.ForTaxPaid,2) else null end) as ForeignTaxesPaidAmt, SUM(case when (BM.TEIntInc)<10 then null else Round(BM.TEIntInc,2) end) as TaxExemptInterestAmt, SUM(case when (BM.TEIntInc)<10 or BM.PrivActBondInt<=0 then null else Round(BM.PrivActBondInt,2)end) as SpcfdPrvtActyBondInterestAmt, SUM(case when BM.MarketDiscount>0 then Round(BM.MarketDiscount,2) else null end) as MarketDiscountAmt, SUM(case when (BM.BondPremium)>0 then Round(BM.BondPremium,2) else null end) as BondPremiumAmt, SUM(case when BM.BondPremtreasury>0 then Round(BM.BondPremtreasury,2)else null end) as TreasuryObligBondPremiumAmt, SUM(case when BM.BondPremiumTE>0 then Round(BM.BondPremiumTE,2)else null end) as TaxExemptBondPremiumAmt from Ten99BERecip BR inner join Ten99BEINT BM on BM.RecipNo=BR.RecipNo and BM.YrSeq=BR.YrSeq and BM.TrustKey=BR.TrustKey and BM.IRSAcctNo is not null --and BM.Filing_Dt=BR.Filing_Dt where BM.Yrseq>=@TaxYearseq1 and BM.Yrseq<=@TaxYearseq2 and BM.TrustKey in (select TrustKey from TRUSTXREF TX where TX.AcctNo=@AcctNo) and BM.PayerNo=@PayerID and BR.CorSeqNo=(select Max(CorSeqNo) from Ten99BERecip where Trustkey = BR.Trustkey and RecipNo=BR.RecipNo and YrSeq=BR.YrSeq) and BM.CorSeqNO=(select Max(CorSeqNo) from Ten99BEINT where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select * from IntData D | ||||
| PAN | Year | DBName | PayerNo | TableName | Export? |
| $PAN1 | 2024 | $DBName1 | 1 | Form1099INTTotals | $txtdtpath2= |
File Comparison
File Compare Block
| TBOT.Generic.SFTP | ||||||
| Comments | sourceFile | destinationFile | destinationFullFilePath | resultFileName | IgnorePattern | compareFiles? |
| Test0002 | $txtdtpath2 | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations\$xmldbpath2 | Yes | Both Files are Equal | ||
3 Form1099INTTotalByStateGrp from IRSubmission1Header
Form1099INTTotalByStateGrp from XML
| TBOT.Generic.xmlFixture | |||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | OrderBy | extractXmlDataToText? |
| 554M_2024_1099INT_CFS_O_File_2.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Header/IRSubmission1FormTotals/Form1099INTTotalByStateGrp | true | StateAbbreviationCd | $xmldbpath3= | |
Form1099INTTotalByStateGrp from Database
| ddt: KeywordFixture | USE {{DBName}} DECLARE @AcctNo VARCHAR(4) = '{{PAN}}', @TaxYear SMALLINT = {{Year}}, @TaxYearseq1 SMALLINT = '{{Year}}0', @TaxYearseq2 SMALLINT = '{{Year}}9', @PayerID smallInt= {{PayerNo}} ;with IntData as (select case When BM.State_Cd in ('AK','AS','FL','FM','GU','IL','MH','MP','NV','NH','NY','PW','PA','PR','RI','SD','TN','TX','VI','WA','WY') then BM.State_Cd when BM.State_Cd in ('AL','AZ','AR','CA','CO','CT','DE','GA','HI','ID','IN','KS','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NJ','NM','NC','ND','OH','OK','SC','WI') then --- DW and CFS Check case when BM.State_Cd in ('AZ','CO','DE','GA','KS','LA','MI','MN','MS','NC','ND','SC','WI') then case when BM.StateTaxWithHeld>0 then null else BM.State_Cd end --- DW check when BM.State_Cd in ('AL','AR','NJ','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('AR') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>100 then null --- C$ Check When BM.State_Cd in ('AL') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))<1500 then null When BM.State_Cd in ('MO') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('CA','CT','HI','ID','NE','NM','OK','OH','MT','MA','AR','AL') then BM.State_Cd end else null end as StateCode, case when BM.StateTaxWithHeld<>0 then Round(ABS(BM.StateTaxWithHeld),2) end as StateTaxWithheldAmt, (case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntInc<=0 then null else Round(BM.IntInc,2) end) as InterestIncomeAmt, (case when BM.EarlyWdPen>0 then Round(EarlyWdPen,2) end) as EarlyWithdrawalPenaltyAmt, (case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntSavBnds<=0 then null else Round(BM.IntSavBnds,2) end) as USSavingsBondsTreasObligIntAmt, (case when BM.TaxWithHeld<>0 then Round(ABS(BM.TaxWithHeld),2)end) as FederalIncomeTaxWithheldAmt, (case when BM.InvExp>0 then Round(BM.InvExp,2)end) as InvestmentExpenseAmt, (case when BM.ForTaxPaid>0 then Round(BM.ForTaxPaid,2)end) as ForeignTaxesPaidAmt, (case when (BM.TEIntInc)<10 then null else Round(BM.TEIntInc,2) end) as TaxExemptInterestAmt, (case when (BM.TEIntInc)<10 or BM.PrivActBondInt<=0 then null else Round(BM.PrivActBondInt,2)end) as SpcfdPrvtActyBondInterestAmt, (case when BM.MarketDiscount>0 then Round(BM.MarketDiscount,2)end) as MarketDiscountAmt, (case when (BM.BondPremium)>0 then Round(BM.BondPremium,2) end) as BondPremiumAmt, (case when BM.BondPremtreasury>0 then Round(BM.BondPremtreasury,2)end) as TreasuryObligBondPremiumAmt, (case when BM.BondPremiumTE>0 then Round(BM.BondPremiumTE,2)end) as TaxExemptBondPremiumAmt From Ten99BERecip BR inner join Ten99BEInt BM on BM.RecipNo=BR.RecipNo and BM.YrSeq=BR.YrSeq and BM.TrustKey=BR.TrustKey and BM.IRSAcctNo is not null --and BM.Filing_Dt=BR.Filing_Dt inner join TEN99YRPAYER TP ON TP.PAYERNO = BM.PAYERNO and TP.Acctno=@AcctNo and TP.Year=@TaxYear inner join TRUSTXREF TX ON BR.TRUSTKEY = TX.TRUSTKEY and TP.AcctNO=TX.AcctNo where BM.Yrseq>=@TaxYearseq1 and BM.Yrseq<=@TaxYearseq2 and TX.AcctNo=@AcctNo and BM.PayerNo=@PayerID and BR.CorSeqNo=(select Max(CorSeqNo) from Ten99BERecip where Trustkey = BR.Trustkey and RecipNo=BR.RecipNo and YrSeq=BR.YrSeq) and BM.CorSeqNO=(select Max(CorSeqNo) from Ten99BEINT where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select D.StateCode as StateAbbreviationCd,COUNT(D.StateCode) as TotalReportedRcpntFormCnt, SUM(D.StateTaxWithheldAmt) as StateTaxWithheldAmt, SUM(D.InterestIncomeAmt) as InterestIncomeAmt, SUM(D.EarlyWithdrawalPenaltyAmt) as EarlyWithdrawalPenaltyAmt, SUM(D.USSavingsBondsTreasObligIntAmt) as USSavingsBondsTreasObligIntAmt, SUM(D.FederalIncomeTaxWithheldAmt) as FederalIncomeTaxWithheldAmt, SUM(D.InvestmentExpenseAmt) as InvestmentExpenseAmt, SUM(D.ForeignTaxesPaidAmt) as ForeignTaxesPaidAmt, SUM(D.TaxExemptInterestAmt) as TaxExemptInterestAmt, SUM(D.SpcfdPrvtActyBondInterestAmt) as SpcfdPrvtActyBondInterestAmt, SUM(D.MarketDiscountAmt) as MarketDiscountAmt, SUM(D.BondPremiumAmt) as BondPremiumAmt, SUM(D.TreasuryObligBondPremiumAmt) as TreasuryObligBondPremiumAmt, SUM(D.TaxExemptBondPremiumAmt) as TaxExemptBondPremiumAmt from IntData D where D.StateCode is not null Group by D.StateCode Order By D.StateCode | ||||
| PAN | Year | DBName | PayerNo | TableName | Export? |
| $PAN1 | 2024 | $DBName1 | 1 | Form1099INTTotalByStateGrp | $txtdtpath3= |
File Comparison
File Compare Block
| TBOT.Generic.SFTP | ||||||
| Comments | sourceFile | destinationFile | destinationFullFilePath | resultFileName | IgnorePattern | compareFiles? |
| Test0003 | $txtdtpath3 | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations\$xmldbpath3 | Yes | Both Files are Equal | ||
4 IRSubmission1Detail With Form1099INTDetail
Form 1099INTDetail from XML
| TBOT.Generic.xmlFixture | |||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | OrderBy | extractXmlDataToText? |
| 554M_2024_1099INT_CFS_O_File_2.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Detail/Form1099INTDetail | true | RecordId | RecipientAccountNum | $xmldbpath4= |
Form1099INTDetail from Database
| ddt: KeywordFixture | USE {{DBName}} DECLARE @AcctNo VARCHAR(4) = '{{PAN}}', @TaxYear SMALLINT = {{Year}}, @TaxYearseq1 SMALLINT = '{{Year}}0', @TaxYearseq2 SMALLINT = '{{Year}}9', @PayerID smallInt= {{PayerNo}} ;with IntData as (select case When BM.State_Cd in ('AK','AS','FL','FM','GU','IL','MH','MP','NV','NH','NY','PW','PA','PR','RI','SD','TN','TX','VI','WA','WY') then BM.State_Cd when BM.State_Cd in ('AL','AZ','AR','CA','CO','CT','DE','GA','HI','ID','IN','KS','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NJ','NM','NC','ND','OH','OK','SC','WI') then --- DW and CFS Check case when BM.State_Cd in ('AZ','CO','DE','GA','KS','LA','MI','MN','MS','NC','ND','SC','WI') then case when BM.StateTaxWithHeld>0 then null else BM.State_Cd end --- DW check when BM.State_Cd in ('AL','AR','NJ','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('AR') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>100 then null --- C$ Check When BM.State_Cd in ('AL') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))<1500 then null When BM.State_Cd in ('MO') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('CA','CT','HI','ID','NE','NM','OK','OH','MT','MA','AR','AL') then BM.State_Cd end else null end as StateCode, substring(convert(varchar(5),BM.yrseq),1,4) as TaxYr, (ROW_NUMBER() over (order by BR.recipID ,BM.Trustkey )) as RecordId, LTRIM(RTRIM(TP.PayerOffice_Cd)) as IssuerOfficeCd, case when BM.State_Cd in ('AL','AZ','AR','CA','CO','CT','DE','GA','HI','ID','IN','KS','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NJ','NM','NC','ND','OH','OK','SC','WI') then --- DW and CFS Check case when BM.State_Cd in ('AZ','CO','DE','GA','KS','LA','MI','MN','MS','NC','ND','SC','WI') then case when BM.StateTaxWithHeld>0 then null else BM.State_Cd end --- DW check when BM.State_Cd in ('AL','AR','NJ','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('AR') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>100 then null --- C$ Check When BM.State_Cd in ('AL') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))<1500 then null When BM.State_Cd in ('MO') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('CA','CT','HI','ID','NE','NM','OK','OH','MT','MA','AR','AL') then BM.State_Cd end else null end as CFSFElectionStateCd, '0' as VoidInd, case when BM.Corrected_Fl is null then 0 end as CorrectedInd, LTRIM(RTRIM(Replace(BR.RecipID,'-',''))) as TIN, case when charindex('-',BR.RecipID)=3 Then 'BUSINESS_TIN' when charindex('-',BR.RecipID)=4 Then 'INDIVIDUAL_TIN' end as TINSubmittedTypeCd, case when charindex('-',BR.RecipID)=4 and BR.RecipNameFirst <>' ' Then BR.RecipNameFirst end as PersonFirstNm, case when charindex('-',BR.RecipID)=4 and BR.RecipNameMid <>' ' Then BR.RecipNameMid end as PersonMiddleNm, case when charindex('-',BR.RecipID)=4 and BR.RecipNameLast <>' ' Then BR.RecipNameLast end as PersonLastNm, case when charindex('-',BR.RecipID)=3 and BR.RecipName1 <>' ' Then BR.RecipName1 end as BusinessNameLine1Txt, case when charindex('-',BR.RecipID)=3 and BR.RecipName2 <>' ' Then BR.RecipName2 end as BusinessNameLine2Txt, case when BR.RecipAddr <>' ' Then SubString(BR.RecipAddr,0,36) end as AddressLine1Txt, case when BR.RecipAddr2 <> ' ' Then SubString(BR.RecipAddr2,0,36) end as AddressLine2Txt, case when BR. RecipForeignProvince <> ' ' Then SubString(BR.RecipForeignProvince,0,18) end as ProvinceOrStateNm, case when BR.RecipCountry_Cd <> ' ' Then BR.RecipCountry_Cd end as CountryCd, case when BR.RecipForeignPostal <> ' ' Then BR.RecipForeignPostal end as ForeignPostalCd, case when BR.RecipCity<> ' ' Then BR.RecipCity end as CityNm, BR.RecipSt as StateAbbreviationCd, replace(BR.RecipZip,'-','') as ZIPCd , BM.IRSAcctNo as RecipientAccountNum, BR.TINNotice2 as SecondTINNoticeInd, BM.FATCA_Fl as FATCAFilingRequirementInd, (case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntInc<=0 then null else Round(BM.IntInc,2) end) as InterestIncomeAmt, (case when BM.EarlyWdPen>0 then Round(EarlyWdPen,2) end) as EarlyWithdrawalPenaltyAmt, (case when (BM.IntInc+BM.IntSavBnds)< 10 or BM.IntSavBnds<=0 then null else Round(BM.IntSavBnds,2) end) as USSavingsBondsTreasObligIntAmt, (case when BM.TaxWithHeld<>0 then Round(ABS(BM.TaxWithHeld),2)end) as FederalIncomeTaxWithheldAmt, (case when BM.InvExp>0 then Round(BM.InvExp,2)end) as InvestmentExpenseAmt, (case when BM.ForTaxPaid>0 then Round(BM.ForTaxPaid,2)end) as ForeignTaxesPaidAmt, (case when BM.ForCountry is not null or BM.ForCountry<>'' then BM.ForCountry end )as ForeignCountryOrUSPossessionCd, (case when (BM.TEIntInc)<10 then null else Round(BM.TEIntInc,2) end) as TaxExemptInterestAmt, (case when (BM.TEIntInc)<10 or BM.PrivActBondInt<=0 then null else Round(BM.PrivActBondInt,2)end) as SpcfdPrvtActyBondInterestAmt, (case when BM.MarketDiscount>0 then Round(BM.MarketDiscount,2)end) as MarketDiscountAmt, (case when (BM.BondPremium)>0 then Round(BM.BondPremium,2) end) as BondPremiumAmt, (case when BM.BondPremtreasury>0 then Round(BM.BondPremtreasury,2)end) as TreasuryObligBondPremiumAmt, (case when BM.BondPremiumTE>0 then Round(BM.BondPremiumTE,2)end) as TaxExemptBondPremiumAmt, case when BM.AssetID is null or BM.AssetID=' ' then null else LTRIM(RTRIM(BM.AssetID)) end as TaxExemptTaxCreditBondCUSIPNum, case when BM.State_Cd in ('AL','AZ','AR','CA','CO','CT','DE','GA','HI','ID','IN','KS','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NJ','NM','NC','ND','OH','OK','SC','WI') then --- DW and CFS Check case when BM.State_Cd in ('AZ','CO','DE','GA','KS','LA','MI','MN','MS','NC','ND','SC','WI') then case when BM.StateTaxWithHeld>0 then null else BM.State_Cd end --- DW check when BM.State_Cd in ('AL','AR','NJ','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('AR') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>100 then null --- C$ Check When BM.State_Cd in ('AL') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))<1500 then null When BM.State_Cd in ('MO') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and ((case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else Round(BM.IntInc,2) end) + (case when (BM.IntInc+BM.IntSavBnds)< 10 then 0 else ROUND(BM.IntSavBnds,2) end) + (case when BM.TEIntInc <10 then 0 else round(BM.PrivActBondInt,2)end))>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('CA','CT','HI','ID','NE','NM','OK','OH','MT','MA','AR','AL') then BM.State_Cd end else null end as StateCd, case when BM.StatePayerID is not null or BM.StatePayerID= '' then case when BM.State_Cd in ('WI','CT','NC','IA') then replace(BM.StatePayerID,'-','') else BM.StatePayerId end end as StateIdNum, case when BM.StateTaxWithHeld<>0 then Round(Abs(BM.StateTaxWithHeld),2) end as StateTaxWithheldAmt from Ten99BERecip BR inner join Ten99BEInt BM on BM.RecipNo=BR.RecipNo and BM.YrSeq=BR.YrSeq and BM.TrustKey=BR.TrustKey and BM.IRSAcctNo is not null --and BM.Filing_Dt=BR.Filing_Dt inner join TEN99YRPAYER TP ON TP.PAYERNO = BM.PAYERNO and TP.Acctno=@AcctNo and TP.Year=@TaxYear inner join TRUSTXREF TX ON BR.TRUSTKEY = TX.TRUSTKEY and TP.AcctNO=TX.AcctNo where BM.Yrseq>=@TaxYearseq1 and BM.Yrseq<=@TaxYearseq2 and TX.AcctNo=@AcctNo and BM.PayerNo=@PayerID) select D.TaxYr,/*D.RecordId,*/D.CFSFElectionStateCd,D.IssuerOfficeCd,D.VoidInd,D.CorrectedInd, CONCAT(D.TIN,D.TINSubmittedTypeCd,PersonFirstNm,D.PersonMiddleNm,D.PersonLastNm,D.BusinessNameLine1Txt,D.BusinessNameLine2Txt, LTRIM(RTRIM(D.AddressLine1Txt)),LTRIM(RTRIM(D.AddressLine2Txt)),LTRIM(RTRIM(D.CityNm)),LTRIM(RTRIM(D.ProvinceOrStateNm)),LTRIM(RTRIM(D.CountryCd)),LTRIM(RTRIM(D.ForeignPostalCd)),LTRIM(RTRIM(D.StateAbbreviationCd)),LTRIM(RTRIM(D.ZIPCd))) as RecipientDetail, D.RecipientAccountNum,D.SecondTINNoticeInd,D.FATCAFilingRequirementInd,D.InterestIncomeAmt,D.EarlyWithdrawalPenaltyAmt,D.USSavingsBondsTreasObligIntAmt,D.FederalIncomeTaxWithheldAmt,D.InvestmentExpenseAmt, D.ForeignTaxesPaidAmt,D.ForeignCountryOrUSPossessionCd, D.TaxExemptInterestAmt,D.SpcfdPrvtActyBondInterestAmt,D.MarketDiscountAmt,D.BondPremiumAmt, D.TreasuryObligBondPremiumAmt,D.TaxExemptBondPremiumAmt,D.TaxExemptTaxCreditBondCUSIPNum, case when D.StateCode is not null then CONCAT(D.StateCode,D.StateIdNum,cast(D.StateTaxWithheldAmt as Decimal)) end as StateLocalTaxGrp from IntData D order by D.RecipientAccountNum | ||||
| PAN | Year | DBName | PayerNo | TableName | Export? |
| $PAN1 | 2024 | $DBName1 | 1 | INTIRISSubmission1Detail | $txtdtpath4= |
File Comparison
File Compare Block
| TBOT.Generic.SFTP | ||||||
| Comments | sourceFile | destinationFile | destinationFullFilePath | resultFileName | IgnorePattern | compareFiles? |
| Test0004 | $txtdtpath4 | \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2024\BatchAutomation\IRISValidations\$xmldbpath4 | Yes | Both Files are Equal | ||
Keyboard Shortcuts ?
General
- t Test page or suite
- e Edit page
- v View page (after test)
- a Add new page
Go To
- g u User Guide
- g q Quick Reference Guide
- g f Full Reference Guide
- g c Recent Changes
Edit Mode
- CTRL+s Save page
- ESC Cancel edit
Tools
- p Properties
- w Where used?
- AK+v Versions
- AK+h Page History
- / Focus on search bar
- s Open search page