Included page: .FrontPage.QA.BalanceDue.2020.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\2020\BatchAutomation\BalanceDue
DownloadPath	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2020\BatchAutomation\BalanceDue\Actuals
SourceDirectory	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2020\BatchAutomation\BalanceDue\Actuals
TargetDirectory	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\D_Drive\Trustez\ftp\LocalUser\EFTPS\qa\in
CompareResultDir	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Results\QA\YrSpecSuites\2020\BatchAutomation\BalanceDue
ExpectedDir	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\IntegrationTests-OTT\BaselineFiles\Baseline\files\QA\YrSpecSuites\2020\BatchAutomation\BalanceDue
ActualDir	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2020\BatchAutomation\BalanceDue\Actuals
ResultDir	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Results\QA\YrSpecSuites\2020\BatchAutomation\BalanceDue
Debug	TRUE
ignorePathValidations	false
DeleteFile	true

Import Necessary Libraries

import

TBOT.Business

TBOT.Generic.Database

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	2020	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	2020	$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=

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

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	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 ) insert into #tempPrepareBalDue EXEC {{DataBaseName}}.BANKDB_P.spGetBalDueFilingData '{{PAN}}', {{YEAR}},{{SeqNumber}} select @AssetID=LTRIM(RTRIM(AssetID)) from {{DataBaseName}}.dbo.bankdata where acctno = '{{PAN}} 'and year = '{{YEAR}} 'and prodopt = '27' and Product_Cd='F' 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, @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?
Test0001	$Seq_Number1	$DBName1	5010	2020	Federal EFTPS (1041 and 990)	True	$PreparedDateRegEx1=	$AssetID1=	$SettlementDateRegEx1=	$PreparedDateDateRegEx1=	$PreparedTimeRegEx1=

******!

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		$SettlementDateRegEx1;0749;236368653;210520;210524;$PreparedDateDateRegEx1;$PreparedTimeRegEx1;$AssetID1;ST~813~\d\\;SE~35~\d\\;SE~15~\d\\;SE~27~\d\\;SE~75~\d\\;SE~11~\d\\;GE~1~\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		5/20/2021;5/24/2021;$confirmationDate1;$csvCurrentDate1;$csvsettlementdate1;[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

Import Necessary Libraries

Get Data from Package Table

Assert Filing Status as Transmitting

Download EDI File

Generate EFTPSAck and Move file to execute EFTPS-IN Batch

Generate EFTPSAck

Move File to execute EFTPS-IN

Download Csv File

Validate Action_CD and Action_DT

Validate Filing Status

Settlement date in csv file

File Compare for EDI file

File Compare for CSV file

Keyboard Shortcuts ?

General

Go To

Edit Mode

Tools

Keyboard Shortcuts `?`