CC: Rakesh Roy Prasad Pise Priya Dhamande

Error Log :

ErrorID : 0
ErrorSource : ControllerAppTier.SaveAndGetData->WORKTERRAControllerAppTier.SaveAndGetData->ExecuteImportDTS.ImportData_Isolated->WTIsolatedRoutines.Main->ExecuteImportDTS.ImportData->ImportValidation.ImportData->ImportValidation.ListOutDuplicates
ErrorMessage: No column name was specified for column 4 of 'Temp'.
StackTrace: at WORKTERRA.Shared.WORKTERRAControllerAppTier.SaveAndGetData(WORKTERRAControllerWebTierEntity objWORKTERRAControllerWebTierEntity)
at WORKTERRA.ControllerAppTier.SaveAndGetData(Int32 intProjectsId, String strInput)
TargetSite: System.String SaveAndGetData(WORKTERRA.Shared.WORKTERRAControllerWebTierEntity)

Uniqueness is an option to identify the given data from file already exists in file or not. So uniqueness should be applied to Data only.

The data might be : First Name, last name, date of birth etc. These attributes, if set as unique, help us identify if the record is new or existing. The template creator decides his uniqueness. But it should be on any data.

Effective Date on other hand is not data in case of demographics. Effective date just tells us from when the current data is effective. (In case of election or waived benefit effective date is part of data, so can be set as unique.) So setting effective date as unique for demographics is not as idle scenario.

This is existing issue and not caused due to enhancement. However, such validation can be applied to avoid the exception is needed. But I think this should not block the Enhancement WT-7755.

Jaideep Vinchurkar please suggest.

CC- Prasad Pise, Jayshree Nagpure, Rakesh Roy

Hi Jayshree Nagpure,

This is not a business scenario and not even related to the enhancement you are testing. I am making this as lowest priority as this is breaking application but very less likely scenario.
We will schedule this as and when bandwidth allows us.

Hi Suraj Sokasane,

When State is marked as unique for emergency contact -> Import Fail
Company - Asml
Below is the error log :
ErrorID : 0
ErrorSource : ControllerAppTier.SaveAndGetData->WORKTERRAControllerAppTier.SaveAndGetData->ExecuteImportDTS.ImportData_Isolated->WTIsolatedRoutines.Main->ExecuteImportDTS.ImportData->ImportValidation.ImportData->ImportValidation.ProcessImportDataFile->ImportValidation.ValidateAndInsertData->ImportValidation.ExecuteAllQueries
ErrorMessage: Violation of UNIQUE KEY constraint 'IX_Employee_Emergency_EmployeeID'. Cannot insert duplicate key in object 'dbo.Employee_Emergency'. The duplicate key value is (4485).
The statement has been terminated. INSERT INTO Employee_Emergency(EmployeeID,StateID,EffectiveDate,UserID) Values( 4485, 1 ,'5/17/2017',1); IF NOT EXISTS( SELECT * FROM ImportEmployeeInfoForBackendRoutine WHERE ImportExportBadFileID = 2127 AND EmployeeID = 4485 AND DATEDIFF(DAY, EffectiveDate, '5/17/2017') = 0 AND InsertORUpdate = 1 AND MemberTypeID = 1 ) BEGIN INSERT INTO ImportEmployeeInfoForBackendRoutine (ImportExportBadFileID, EmployeeID, EffectiveDate, InsertORUpdate, MemberTypeID, MemberID, CorePlanStatus, EligibilityStatus) VALUES( 2127, 4485, '5/17/2017' , 1, 1, 4485 , 0, 0 ) END

Concern : Import Fail when Dependent effective date is marked as "Unique".

Cause : Query was having syntax error when effective date was marked as unique.

Correction : As this is not a business scenario, have made corrections on UI. Now, the effective dates of Employee, Spouse, child and Medicaid information cannot be marked as unique if already mapped or if already the user defined field is marked as unique it cannot be mapped to any of the fields mentioned above as it is unique. Proper messages are shown.

Impacted areas - All types of import.

Test scenarios
1) Added a new user defined field, effective date for spouse and marked it as unique. On the mapping screen tried to map the field to Spouse Information Effective date. The field cannot be mapped as it is marked as unique error message is show.

2) Added a new user defiled field, effective date for spouse and did not mark it as unique. Now, mapped this field to Spouse Information Effective date. Again coming back to SubTemplate screen tried to mark the user defined field as unique. The field cannot be marked as unique error message is shown .

Regards,
Ankur

Hi Ankur Deshpande,

Verified this issue on LB.
Company - City of Durham

Verified for scenario

1) Added a new user defined field, effective date for spouse and marked it as unique. On the mapping screen tried to map the field to Spouse Information Effective date. the field is mapped and save
Verified spouse import - import Fail

Error log :
ErrorID : 0
ErrorSource : ControllerAppTier.SaveAndGetData->WORKTERRAControllerAppTier.SaveAndGetData->ExecuteImportDTS.ImportData_Isolated->WTIsolatedRoutines.Main->ExecuteImportDTS.ImportData->ImportValidation.ImportData->ImportValidation.ListOutDuplicates
ErrorMessage: No column name was specified for column 2 of 'Temp'.

For another scenario :
2) Added a new user defiled field, effective date for spouse and did not mark it as unique. Now, mapped this field to Spouse Information Effective date. Again coming back to Sub Template screen tried to mark the spouse effective date field as unique.system shows proper validation message "Spouse effective date cannot be unique"

As first scenario is not working hence re-opening this jira

Thanks,
Jayshree

CC : Rakesh Roy Prasad Pise Suraj Sokasane

Hi Ankur Deshpande,

Verified on Lb
company - City of Durham

Verified Effective date validation message scenario for following import :
1)Employee import - Pass (Shows validation for unique effective date)
2)Spouse import - Pass (Shows validation for unique effective date)
3)Child import - Pass (Shows validation for unique effective date)
4)Salary - Pass (Shows validation for unique effective date)
5)Election import - Pass(Effective date is by default unique)
6)Paycheck import - (Effective date is by default unique)
7)Emergency contact import - Fail (Allowed to mark effective date unique, ideally as per implementation of effective date it should not allow to mark effective date unique)
8)Beneficiary import - Fail (Allowed to mark effective date unique, ideally as per implementation of effective date it should not allow to mark effective date unique)

As 7th and 8th scenario is not working fine, Re-opening this jira

Thanks,
Jayshree

CC : Rakesh Roy Prasad Pise Jaideep Vinchurkar

Verified on LB
company - City of Durham

Verified below effective date uniqueness validation scenario :

1) Added a new user defined field, effective date for spouse and marked it as unique. On the mapping screen tried to map the field to Spouse Information Effective date. The field cannot be mapped as it is marked as unique error message is show.
2) Added a new user defiled field, effective date for spouse and did not mark it as unique. Now, mapped this field to Spouse Information Effective date. Again coming back to Sub Template screen tried to mark the user defined field as unique. The field cannot be marked as unique validation message is shown .

Verified Effective date validation message scenario for following import - Working Fine
1)Employee import - Pass (Shows validation for unique effective date)
2)Spouse import - Pass (Shows validation for unique effective date)
3)Child import - Pass (Shows validation for unique effective date)
4)Salary - Pass (Shows validation for unique effective date)
5)Election import - Pass(Effective date is by default unique)
6)Paycheck import - (Effective date is by default unique)
7)Emergency contact import - Pass (Shows validation for unique effective date)
8)Beneficiary import - Pass (Shows validation for unique effective date)

Marking item state as Ready for stage.

Thanks,
Jayshree

CC : Rakesh Roy Prasad Pise

Verified on stage all above scenario
Company - Caiso for hspl

Working fine

Marking item state as Ready for production

Thanks,
Jayshree

verified on production
working fine