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_5498ESA_F_O_File_1.zip xml True True

ddt: KeywordFixture
sourcefilename EXTN FILECOPYNEW?
554M_2024_5498ESA_F_O_File_1 xml True


1 IRSubmission1Header With Payer Details


IRSubmission1Header Without Form5498ESATotalAmtGrp from XML


TBOT.Generic.xmlFixture
XMLFileName nodeXpath hasMultiSchema IgnoreNodes extractXmlDataToText?
554M_2024_5498ESA_F_O_File_1.xml IRTransmission/IRSubmission1Grp/IRSubmission1Header true IRSubmission1FormTotals $xmldbpath1=

Payer Details With Form5498ESATotalAmtGrp 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 Ten99548ESAData as ( select BR.RecipID from Ten99BERecip BR inner join Ten99BE5498EdIRA 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 Ten99BE5498EdIRA where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select 1 as SubmissionId, @TaxYear as TaxYr, CONCAT(case when cast(Tp.PayerForeignPostal as nvarchar) is not null then '1' else '0' 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(TP.PayerName1,0,76) end,-- as [BusinessNameLine1Txt], case when charindex('-',TP.PayerID)=3 Then SubString(TP.PayerName2,0,76) end,-- as [BusinessNameLine2Txt], case when charindex('-',TP.PayerID)=4 Then SubString(TP.PayerName1,0,21) end,-- as [PersonFirstNm], case when charindex('-',TP.PayerID)=4 Then SubString(TP.PayerName2,0,21) end,-- as [PersonLastNm], SubString(TP.PayerAddr,0,36),-- as [AddressLine1Txt], TP.PayerCity,-- as [CityNm], TP.PayerSt,-- as [StateAbbreviationCd], SubString(TP.PayerForeignProvince,0,18), TP.PayerCountry_Cd, TP.PayerForeignPostal, replace(TP.PayerZip,'-','')) as IssuerDetail,-- as [ZIPCd], '5498-ESA' as FormTypeCd,1096 as ParentFormTypeCd, 0 as CFSFElectionInd, count(T.RecipID) as TotalReportedRcpntFormCnt from Ten99yrPayer TP,Ten99548ESAData 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.PayerCountry_Cd,TP.PayerForeignPostal,TP.PayerForeignProvince
PAN Year DBName PayerNo TableName Export?
$PAN1 2024 $DBName1 28 5498ESAIRSubmission1Header $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 Form5498ESATotalAmtGrp from IRSubmission1Header


Form5498ESATotalAmtGrp from XML


TBOT.Generic.xmlFixture
XMLFileName nodeXpath hasMultiSchema IgnoreNodes extractXmlDataToText?
554M_2024_5498ESA_F_O_File_1.xml IRTransmission/IRSubmission1Grp/IRSubmission1Header/IRSubmission1FormTotals/Form5498ESATotalAmtGrp true $xmldbpath2=

Form5498ESATotalAmtGrp 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 Ten99548ESAData as ( select SUM(case when BM.EdIRAContrib>0 then Round(BM.EdIRAContrib,2) else null end) as CoverdellESAContributionAmt, SUM(case when BM.ROIRAContrib <=0 then null else Round(BM.ROIRAContrib,2) end) as RolloverContributionAmt from Ten99BERecip BR inner join Ten99BE5498EdIRA 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 Ten99BE5498EdIRA where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select * from Ten99548ESAData D
PAN Year DBName PayerNo TableName Export?
$PAN1 2024 $DBName1 28 Form5498ESATotals $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 IRSubmission1Detail With Form5498ESADetail


Form5498ESADetail from XML


TBOT.Generic.xmlFixture
XMLFileName nodeXpath hasMultiSchema IgnoreNodes OrderBy extractXmlDataToText?
554M_2024_5498ESA_F_O_File_1.xml IRTransmission/IRSubmission1Grp/IRSubmission1Detail/Form5498ESADetail true RecordId RecipientAccountNum $xmldbpath3=

Form5498ESADetail 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 Ten99548ESAData as (select 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, '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)=4 and BR.RecipNameSuf <> ' ' Then BR.RecipNameSuf end as SuffixNm, 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(replace(replace(LTRIM(RTRIM(BR.RecipAddr)),'\',''),' ',' '),0,36) end as AddressLine1Txt, case when BR.RecipAddr2 <> ' ' Then SubString(replace(replace(LTRIM(RTRIM(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, (case when BM.EdIRAContrib>0 then Round(BM.EdIRAContrib,2) else null end) as CoverdellESAContributionAmt, (case when BM.ROIRAContrib <=0 then null else Round(BM.ROIRAContrib,2) end) as RolloverContributionAmt from Ten99BERecip BR inner join Ten99BE5498EdIRA 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 Ten99BE5498EdIRA where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select D.TaxYr,/*D.RecordId,*/D.IssuerOfficeCd,D.VoidInd,D.CorrectedInd, CONCAT(D.TIN,D.TINSubmittedTypeCd,PersonFirstNm,D.PersonMiddleNm,D.PersonLastNm,D.SuffixNm,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.CoverdellESAContributionAmt,D.RolloverContributionAmt from Ten99548ESAData D Order by D.RecipientAccountNum
PAN Year DBName PayerNo TableName Export?
$PAN1 2024 $DBName1 28 5498ESAIRISSubmission1Detail $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



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
Close