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_1099NEC_CFS_O_File_12.zip | xml | True | True |
| ddt: KeywordFixture | ||
| sourcefilename | EXTN | FILECOPYNEW? |
| 554M_2024_1099NEC_CFS_O_File_12 | xml | True |
1 IRSubmission1Header With Payer Details
IRSubmission1Header With Form1099NECTotalAmtGrp from XML
| TBOT.Generic.xmlFixture | ||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | extractXmlDataToText? |
| 554M_2024_1099NEC_CFS_O_File_12.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Header | true | IRSubmission1FormTotals | $xmldbpath1= |
Payer Details With Form1099NECTotalAmtGrp 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 NecData 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','CA','CO','CT','GA','IN','LA','MN','MS','NE','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','KS','MD','NJ','NC','MD','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('OK') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>750 then null when BM.State_Cd in ('AR') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>2500 then null --- C$ Check When BM.State_Cd in ('AL') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)<1500 then null When BM.State_Cd in ('MO') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('HI','ID','NM','OH','MT','MA','AR','AL') then BM.State_Cd end else null end as CFSFElectionStateCd from Ten99BERecip BR inner join Ten99BENEC 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 Ten99BENEC 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, TP.PayerForeignPostal, replace(TP.PayerZip,'-','')) as IssuerDetail,-- as [ZIPCd], '1099NEC' 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,NecData 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 | 64 | NECIRSubmission1Header | $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 Form1099NECTotalAmtGrp from IRSubmission1Header
Form1099NECTotalAmtGrp from XML
| TBOT.Generic.xmlFixture | ||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | extractXmlDataToText? |
| 554M_2024_1099NEC_CFS_O_File_12.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Header/IRSubmission1FormTotals/Form1099NECTotalAmtGrp | true | $xmldbpath2= | |
Form1099NECTotalAmtGrp 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}} --- FormTotals ;with NecData as (select SUM(case when BM.TaxWithHeld>0 then Round(ABS(BM.TaxWithHeld),2) else null end) as [FederalIncomeTaxWithheldAmt], SUM(case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end) as [TotalReportedAmt], SUM(case when (case when BM.NonEmpComp<600 then 0 else BM.NonEmpComp end)<>0 then Round(BM.NonEmpComp,2)end) as NonemployeeCompensationAmt from Ten99BERecip BR inner join Ten99BENec 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 Ten99BENEC where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select * from NecData D | ||||
| PAN | Year | DBName | PayerNo | TableName | Export? |
| $PAN1 | 2024 | $DBName1 | 64 | Form1099NECTotalByStateGrp | $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 Form1099NECTotalByStateGrp from IRSubmission1Header
Form1099NECTotalByStateGrp from XML
| TBOT.Generic.xmlFixture | |||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | OrderBy | extractXmlDataToText? |
| 554M_2024_1099NEC_CFS_O_File_12.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Header/IRSubmission1FormTotals/Form1099NECTotalByStateGrp | true | StateAbbreviationCd | $xmldbpath3= | |
Form1099NECTotalByStateGrp 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}} -- state Totals ;with NecData 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','CA','CO','CT','GA','IN','LA','MN','MS','NE','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','KS','MD','NJ','NC','MD','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('OK') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>750 then null when BM.State_Cd in ('AR') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>2500 then null --- C$ Check When BM.State_Cd in ('AL') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)<1500 then null When BM.State_Cd in ('MO') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('HI','ID','NM','OH','MT','MA','AR','AL') then BM.State_Cd end else null end as StateCode, case when (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)<>0 then Round(BM.NonEmpComp,2) end as NonemployeeCompensationAmt, case when BM.TaxWithHeld>0 then Round(ABS(BM.TaxWithHeld),2) else null end as FederalIncomeTaxWithheldAmt, case when BM.StateTaxWithHeld>0 then Round(ABS(BM.StateTaxWithHeld),2) else null end as StateTaxWithheldAmt from Ten99BERecip BR inner join Ten99BENEC 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 Ten99BENEC where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select D.StateCode as StateAbbreviationCd,COUNT(D.StateCode) as TotalReportedRcpntFormCnt, SUM(D.FederalIncomeTaxWithheldAmt) as FederalIncomeTaxWithheldAmt, SUM(D.StateTaxWithheldAmt) as StateTaxWithheldAmt, SUM(D.NonemployeeCompensationAmt)as NonemployeeCompensationAmt from NecData D where D.StateCode is not null Group by D.StateCode order by StateCode | ||||
| PAN | Year | DBName | PayerNo | TableName | Export? |
| $PAN1 | 2024 | $DBName1 | 64 | Form1099NECTotalByStateGrp | $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 Form1099NECDetail
Form 1099MISCDetail from XML
| TBOT.Generic.xmlFixture | |||||
| XMLFileName | nodeXpath | hasMultiSchema | IgnoreNodes | OrderBy | extractXmlDataToText? |
| 554M_2024_1099NEC_CFS_O_File_12.xml | IRTransmission/IRSubmission1Grp/IRSubmission1Detail/Form1099NECDetail | true | RecordId | RecipientAccountNum | $xmldbpath4= |
Form1099MISCDetail 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 NecData 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','CA','CO','CT','GA','IN','LA','MN','MS','NE','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','KS','MD','NJ','NC','MD','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('OK') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>750 then null when BM.State_Cd in ('AR') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>2500 then null --- C$ Check When BM.State_Cd in ('AL') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)<1500 then null When BM.State_Cd in ('MO') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('HI','ID','NM','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','CA','CO','CT','GA','IN','LA','MN','MS','NE','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','KS','MD','NJ','NC','MD','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('OK') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>750 then null when BM.State_Cd in ('AR') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>2500 then null --- C$ Check When BM.State_Cd in ('AL') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)<1500 then null When BM.State_Cd in ('MO') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('HI','ID','NM','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)=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, BR.TINNotice2 as SecondTINNoticeInd, BM.FATCA_Fl as FATCAFilingRequirementInd, case when (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)<>0 then Round(BM.NonEmpComp,2) end as NonemployeeCompensationAmt, BM.DirectSale_Fl as DirectSaleAboveThresholdInd, case when BM.TaxWithHeld>0 then Round(ABS(BM.TaxWithHeld),2) else null end as FederalIncomeTaxWithheldAmt, 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','CA','CO','CT','GA','IN','LA','MN','MS','NE','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','KS','MD','NJ','NC','MD','MO') and BM.StateTaxWithHeld > 0 then null --- CW check --- D$ Check when BM.State_Cd in ('OK') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>750 then null when BM.State_Cd in ('AR') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>2500 then null --- C$ Check When BM.State_Cd in ('AL') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)<1500 then null When BM.State_Cd in ('MO') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1200 then BM.State_Cd When BM.State_Cd in ('NJ') and (case when BM.NonEmpComp<600 then 0 else Round(BM.NonEmpComp,2) end)>1000 then BM.State_Cd --cfs states check when BM.State_Cd in ('HI','ID','NM','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) else null end as StateTaxWithheldAmt, case when BM.StateInc<>0 then Round(BM.StateInc,2) end as StateIncomeAmt from Ten99BERecip BR inner join Ten99BENEC 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 Ten99BENEC where Trustkey = BM.Trustkey and RecipNo=BM.RecipNo and YrSeq=BM.YrSeq) ) select D.TaxYr,/*D.RecordId,*/D.CFSFElectionStateCd,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.SecondTINNoticeInd,/*D.FATCAFilingRequirementInd,*/D.NonemployeeCompensationAmt,D.DirectSaleAboveThresholdInd,D.FederalIncomeTaxWithheldAmt, case when D.StateCode is not null then CONCAT(D.StateCode,D.StateIdNum,cast(D.StateTaxWithheldAmt as Decimal),cast(D.StateIncomeAmt as decimal)) end as StateLocalTaxGrp from NecData D order by D.RecipientAccountNum | ||||
| PAN | Year | DBName | PayerNo | TableName | Export? |
| $PAN1 | 2024 | $DBName1 | 64 | NECIRISSubmission1Detail | $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