Included page: .FrontPage.QA.NRARollUp.2023.SetUp (edit)

import

TBOT.Generic.Database

!*****> Setup

ddt: SetUp
key	value
ConnectionString	Server=10.153.120.216,1600;database=AUTOTESTING;User Id=automation; password=automation*100
BytesColumn	DataObject
FileExtension	zip
ExpectedDir	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\IntegrationTests-OTT\BaselineFiles\Baseline\files\QA\YrSpecSuites\2023\BatchAutomation\NRA-new
ExpectedDirectory	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\IntegrationTests-OTT\BaselineFiles\Baseline\files\QA\YrSpecSuites\2023\BatchAutomation\NRA-new\DataFiles
ActualDir	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2023\BatchAutomation\NRA-new
TargetDirectory	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2023\BatchAutomation\NRA-new\DataFiles
ResultDir	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Results\QA\YrSpecSuites\2023\BatchAutomation\NRA-new
DownloadPath	\\EC2AMAZ-2GFJSTU.a648727226388.amazonaws.com\TZAPPS01SS-QA\AutomationQA\_QATeam\FitnesseTestArtifacts\files\Actuals\QA\YrSpecSuites\2023\BatchAutomation\NRA-new\DataFiles
CompareResultDir	\FitnesseTestArtifacts\files\Results\QA\YrSpecSuites\2021\BatchAutomation\NRA-new
ignorePathValidations	false
DeleteFile	true
NewYearFlag	false
skipIgnorePatterns	true

import

TBOT.Generic.Database

import

TBOT.Generic

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?
312G	$PAN1=	$Server1=	$DBName1=

ddt:DynamicQuery	Select A.ValueText As ProductCd from BANKCONST..Translate A Join BANKCONST..Translate B On A.TranslateCode=B.TranslateCode and A.Seq=B.Seq and A.ValueID = 1 and B.ValueID=2 where A.Translatecode={{TransCode}} and B.ValueText='{{Value}}'
TransCode	Value	ProductCd?
3002	1042-S Filing Request	$ProductCd1=

Delete Bankdata

ddt: DynamicQuery	DELETE A FROM {{Server}}.{{DBName}}.dbo.BankData A WHERE Year = '{{Year}}' and AcctNo = '{{PAN}}' and Product_Cd = '{{Product_Cd}}' DELETE A FROM {{Server}}.{{DBName}}.dbo.TrustYr A Join {{Server}}.{{DBName}}.dbo.TrustXref B On A.TrustKey=B.TrustKey WHERE YrSeq >= '{{Year}}0' and YrSeq <= '{{Year}}9'and AcctNo = '{{PAN}}' and NRAFiling_Dt is not Null
PAN	Server	DBName	Product_Cd	Year
$PAN1	$Server1	$DBName1	$ProductCd1	2023

Delete NRAExtract and Recip

ddt: DynamicQuery	DELETE A FROM {{Server}}.{{DBName}}.dbo.NRAExtract A JOIN {{Server}}.{{DBName}}.dbo.TrustXref B ON A.TrustKey = B.TrustKey WHERE YrSeq >= '{{Year}}0' and YrSeq <= '{{Year}}9' AND ACCTNO = '{{PAN}}' DELETE B FROM {{Server}}.{{DBName}}.dbo.NRARecip B ,{{Server}}.{{DBName}}.dbo.TrustXref A where A.AcctNo='{{PAN}}' and A.TrustKey=B.TrustKey and B.YrSeq >= '{{Year}}0'and B.YrSeq <= '{{Year}}9' and B.Corrected_Fl >0 DELETE B FROM {{Server}}.{{DBName}}.dbo.NRARecip B ,{{Server}}.{{DBName}}.dbo.TrustXref A where A.AcctNo='{{PAN}}' and A.TrustKey=B.TrustKey and B.YrSeq >= '{{Year}}0' and B.YrSeq <= '{{Year}}9' and A.TrustNo not like '%QI%' update {{DBName}}.dbo.NRARecip set Filing_Dt=NULL where TrustKey in (select Trustkey from {{DBName}}.dbo.TrustXref where AcctNo='{{PAN}}'and TrustNo like '%QI%') and YrSeq >= '{{Year}}0' and YrSeq <= '{{Year}}9' update {{DBName}}.dbo.NRAAgent set Filing_Dt=NULL where AcctNo='{{PAN}}'and Year={{Year}} and WAEIN_Cd=1
PAN	Server	DBName	Year
$PAN1	$Server1	$DBName1	2023

Delete Pending Records

ddt: DynamicQuery	Delete from OPERATIONSDB..Status where Key_Field Like '{{ACCTNO}}%' AND YrSeq='{{Year}}0' AND DB_Name='{{DBNAME}}' Delete from {{DBNAME}}..Trust_WIP where TrustKey in (select Trustkey from {{DBNAME}}..Trustxref where AcctNo = '{{ACCTNO}}')
ACCTNO	DBNAME	Year
312G	$DBName1	2023