Included page: .FrontPage.QA.BalanceDue.2022.QA.SetUp (edit)

import
TBOT.Generic.Database

variable defined: ENV=QA
variable defined: QA=http://10.184.7.166:9510/
variable defined: SAT=http://10.184.73.99:9510/
variable defined: URL=http://10.184.7.166:9510/

ddt: SetUp
key value
ConnectionString Server=10.153.120.216,1600;database=AUTOTESTING;User Id=automation; password=automation*100; Timeout=30
BytesColumn DataObject
FileExtension ZIP
ExpectedDirectory \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\IntegrationTests-OTT\BaselineFiles\Baseline\files\QA\YrSpecSuites\2022\BatchAutomation\BalanceDue
DownloadPath \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2022\BatchAutomation\BalanceDue\Actuals
SourceDirectory \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2022\BatchAutomation\BalanceDue\Actuals
TargetDirectory \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\OTTDataQA\TrustElf\acks\EFTPS\incoming
CompareResultDir \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Results\QA\YrSpecSuites\2022\BatchAutomation\BalanceDue
ExpectedDir \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\IntegrationTests-OTT\BaselineFiles\Baseline\files\QA\YrSpecSuites\2022\BatchAutomation\BalanceDue
ActualDir \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2022\BatchAutomation\BalanceDue\Actuals
ResultDir \\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Results\QA\YrSpecSuites\2022\BatchAutomation\BalanceDue
Debug TRUE
ignorePathValidations false
DeleteFile true

Import Necessary Libraries

import
TBOT.Business
TBOT.Generic.Database


Get Data from Package Table

Get Data from Package Table

ddt: DynamicQuery SELECT LTRIM(RTRIM(Server)) AS Server, LTRIM(RTRIM(DB_NAME)) AS DB_NAME ,AcctNo,'{{TaxYear}}' AS Tax_Year, '{{SeqNumber}}' as Seq_Number,CONVERT(varchar(10),getdate(),111) as ActionDate, RTRIM(LTRIM(CAST(DATEPART(MM,convert(date,GETDATE())) as CHAR(2))))+'/' + RTRIM(LTRIM(CAST(DATEPART(DD,convert(date,GETDATE())) as CHAR(2)))) +'/' + CAST(DATEPART(YEAR,convert(date,GETDATE())) as CHAR(4)) as confirmationDate from BTMDB..Package where AcctNo='{{PAN}}'
Comments# PAN TaxYear SeqNumber DB_NAME? AcctNo? Tax_Year? Seq_Number? ActionDate? Server? confirmationDate?
Test0001 5010 2022 1 $DBName1= $AcctNo1= $Tax_Year1= $Seq_Number1= $Action_DT1= $Server1= $confirmationDate1=



Assert Filing Status as Transmitting


ddt:DynamicQuery EXEC {{DataBaseName}}.BANKDB_P.spGetBalDueFilingData '{{PAN}}', {{YEAR}},{{SeqNumber}} Filing = 'Federal EFTPS (1041 and 990)'
Comments# PAN YEAR SeqNumber DataBaseName FilingType FiledStatus? IsFiled?
Test0001 5010 2022 $Seq_Number1 $DBName1 Federal EFTPS (1041 and 990) Transmitting False

Download EDI File

Download Block

DDT: KeywordFixture use {{DataBaseName}} select Action_Cd,DataObject,CONVERT(varchar(10),Action_DT,111) as Action_DT from bankdata where acctno='{{PAN}}' and year='{{TaxYear}}' and state='' and prodopt=27 and SeqNo={{SeqNumber}} and Product_CD='F'
Comment PAN TaxYear SeqNumber DataBaseName FileName DOWNLOAD? UNZIP? GETFILES?
Test0001 $AcctNo1 $Tax_Year1 $Seq_Number1 $DBName1 FederalEFTPS1041And990Base.zip True True $EDIFile1=

ddt: DynamicQuery -- Waits for 60 Second from Dynamic Query.| select LTRIM(RTRIM(Server)) AS Server from btmdb.dbo.package where AcctNo='{{AccountNumber}}'
Comments# AccountNumber WaitSec WAIT?
Test001 $AcctNo1 60 True


Generate EFTPSAck and Move file to execute EFTPS-IN Batch

Generate EFTPSAck and Move file to execute EFTPS-IN Batch

Generate EFTPSAck


ddt: BusinessKeyword
EDIPath AckDirPath EFTPSACK?
$EDIFile1 True

Move File to execute EFTPS-IN


ddt: KeywordFixture
sourcefilename EXTN FILECOPYNEW?
FederalEFTPS1041And990Base EDI True

Run EFTPS-IN JOb


ddt: Dynamic Query exec OPERATIONSDB.OPERATIONSDB_P.QueueProcess @que_name = 'TEZIN-FST', @param_str = ' A=PRD', @ProdOpt = 0,@Key_Field = '{{JobName}}', @Trust_Key = 0, @Product_Code = '{{ProductCode}}', @Seq_Yr = 0, @process_code = 531, @locator='', @PermSeq = 0, @Mode = 'PRD'
JobName ProductCode
EFTPSIN F

Download Csv File

Download Block

DDT: KeywordFixture WAITFOR DELAY '00:00:15';use {{DataBaseName}} select Action_Cd,DataObject,CONVERT(varchar(10),Action_DT,111) as Action_DT from bankdata where acctno='{{PAN}}' and year='{{TaxYear}}' and state='' and prodopt=28 and SeqNo={{SeqNumber}} and Product_CD='F'
Comment PAN TaxYear SeqNumber DataBaseName FileName DOWNLOAD? UNZIP? GETFILES?
Test0001 $AcctNo1 $Tax_Year1 $Seq_Number1 $DBName1 FederalEFTPS1041And990CSV.zip True True $CSVFile1=


Validate Action_CD and Action_DT


ddt: DynamicQuery use {{DataBaseName}} select Action_Cd,DataObject,CONVERT(varchar(10),Action_DT,111) as Action_DT from bankdata where acctno='{{PAN}}' and year={{TaxYear}} and state='' and prodopt=27 and SeqNo={{SeqNumber}} and Product_CD='F'
Comments# DataBaseName PAN TaxYear SeqNumber Action_Cd? Action_DT? DataObject?
Test0001 $DBName1 $AcctNo1 $Tax_Year1 $Seq_Number1 1 $Action_DT1 System.Byte[]


******!


Validate Filing Status


ddt:DynamicQuery EXEC {{DataBaseName}}.BANKDB_P.spGetBalDueFilingData '{{PAN}}', {{YEAR}},{{SeqNumber}} Filing = 'Federal EFTPS (1041 and 990)'
Comments# PAN YEAR SeqNumber DataBaseName FilingType IsFiled?
Test0001 5010 2022 $Seq_Number1 $DBName1 Federal EFTPS (1041 and 990) True


ddt: DynamicQuery WAITFOR DELAY '00:00:10'; DECLARE @AssetID NVARCHAR(20) IF OBJECT_ID('tempdb..#tempPrepareBalDue') IS NOT NULL DROP TABLE #tempPrepareBalDue CREATE TABLE #tempPrepareBalDue ( Filing VARCHAR(200), StateCode VARCHAR(3), ProdOpt SMALLINT, Product_Cd CHAR(1), IsRowEnabled BIT, IsEstimatedEnabled BIT, SettlementDate DateTime, IsSettlementDateEnabled BIT, PrepareStatus VARCHAR(100), IsPrepareStatusEnabled BIT, PreparedDate DateTime, IsPreparedDateEnabled BIT, PreparedBy Varchar(3), IsPreparedByEnabled BIT, IsDoFileEnabled BIT, IsFiled BIT, IsFiledEnabled BIT, IsFiledVisible BIT, FiledDate DateTime, IsFiledDateEnabled BIT, FiledBy VARCHAR(3), IsFiledByEnabled BIT, FiledStatus VARCHAR(100), IsFiledStausEnabled BIT, ID INT, SeqNo SMALLINT, PreparedSettlementDate datetime, BatchOpt SMALLINT, Comments VARCHAR(255), ActionCode TINYINT ) select @AssetID=LTRIM(RTRIM(AssetID)) from {{DataBaseName}}.dbo.bankdata where acctno = '{{PAN}} 'and year = '{{YEAR}} 'and prodopt = '27' and Product_Cd='F' insert into #tempPrepareBalDue EXEC {{DataBaseName}}.BANKDB_P.spGetBalDueFilingData '{{PAN}}', {{YEAR}},{{SeqNumber}} select *, CONVERT(nvarchar(6),PreparedDate,12)+'~'+REPLACE(CONVERT(VARCHAR(5),PreparedDate,108),':','') AS PreparedDateRegEx, REPLACE(CONVERT(VARCHAR(5),FiledDate,108),':','') as PreparedTimeRegEx, CONVERT(nvarchar(6),PreparedDate,12) AS PreparedDateDateRegEx, CONVERT(nvarchar(6),SettlementDate,12) AS SettlementDateRegEx, Convert(nvarchar(10), PreparedDate, 101) AS PreparedDate1, Convert(nvarchar(10), SettlementDate, 101) AS SettlementDate1, @AssetID as AssetID from #tempPrepareBalDue where Filing='{{FilingType}}' and PreparedDate between DATEADD(DD,-1,GETDATE()) AND GETDATE() IF OBJECT_ID('tempdb..#tempPrepareBalDue') IS NOT NULL DROP TABLE #tempPrepareBalDue
Comments# SeqNumber DataBaseName PAN YEAR FilingType IsFiled? PreparedDateRegEx? AssetID? SettlementDateRegEx? PreparedDateDateRegEx? PreparedTimeRegEx? SettlementDate1? PreparedDate1?
Test0001 $Seq_Number1 $DBName1 5010 2022 Federal EFTPS (1041 and 990) True $PreparedDateRegEx1= $AssetID1= $SettlementDateRegEx1= $PreparedDateDateRegEx1= $PreparedTimeRegEx1= $SettlementDate1= $PreparedDate1=

******!

Settlement date in csv file

Get Data from Package Table

ddt: DynamicQuery declare @dt DATETIME set @dt='{{settilementDate}}' select RTRIM(LTRIM(CAST(DATEPART(MM,convert(date,GETDATE())) as CHAR(2))))+'/' + RTRIM(LTRIM(CAST(DATEPART(DD,convert(date,GETDATE())) as CHAR(2)))) +'/' + CAST(DATEPART(YEAR,convert(date,GETDATE())) as CHAR(4)) as csvCurrentDate, RTRIM(LTRIM(CAST(DATEPART(MM,convert(date,@dt)) as CHAR(2))))+'/' + RTRIM(LTRIM(CAST(DATEPART(DD,convert(date,@dt)) as CHAR(2)))) +'/' + CAST(DATEPART(YEAR,convert(date,@dt)) as CHAR(4)) as csvSettlementDate
Comments# settilementDate csvCurrentDate? csvSettlementDate?
Test0001 $SettlementDateRegEx1 $csvCurrentDate1= $csvsettlementdate1=


File Compare for EDI file

File Compare Block

TBOT.Generic.SFTP
Comments sourceFile destinationFile destinationFullFilePath resultFileName IgnorePattern compareFiles?
Test0001 FederalEFTPS1041And990Base.edi $EDIFile1 Yes 230704;1034;551054285;230706;$SettlementDateRegEx1;$PreparedDateDateRegEx1;$PreparedTimeRegEx1;$AssetID1;ST~813~\d*\\;SE~35~\d*\\;SE~11~\d*\\;SE~27~\d*\\;GE~1~\d*\\;GE~4~\d*\\;IEA~1~\d*\\ Both Files are Equal



File Compare for CSV file

File Compare Block

TBOT.Generic.SFTP
Comments sourceFile destinationFile destinationFullFilePath resultFileName IgnorePattern compareFiles?
Test0001 FederalEFTPS1041And990CSV.txt $CSVFile1 Yes 06/29/2023;07/03/2023;$PreparedDate1;$SettlementDate1;[0-9]{1,}:[0-5]{1}[0-9]{1}:[0-5]{1}[0-9]{1} [AMPM]{2};[0-9]{9}-[0-9]{1} 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