Flex:- Function for selecting the item from the spark dropdown list.

'*******************************************************************************************************************************
' Name: Fun_SelectDropdownListValue
' Purpose: This function is used for selecting the item from the spark dropdown list
' Argument(s):
'     ip_ObjDropDown: Object under the test EX: SparkDropDownList
'     ip_strDropdownName: Name of the dropdown ex:Merchandise
'     ip_strSelectableItemValue: Value to be selected from the dropdown. Ex: All Mdse
' Returns:
'     1: If expected value is found under the dropdown
'     0: If expected value is NOT found under the dropdown
' Example: Call Fun_SelectDropdownListValue(objDropdown, "Merchandise", "All Mdse")
'------------------------------------------------------------------------------------------------------------*******************************************************************************************************************************
Public Function Fun_SelectDropdownListValue(ByRef ip_ObjDropDown, ip_strDropdownName, ip_strSelectableItemValue)
 Dim objDicListIndex, intTotalItemCnt, blnScrollBarExists, intMaxScrollValue, intCurrentScrollPoint
 Dim objSparkListLabelDescr, objSparkListLabelDescrChild
 Dim intChildIndex, strListLabel
 Set objDicListIndex = CreateObject("Scripting.Dictionary")
 '<<< Validate the current selection before making a new selection >>>
 strListLabel = ip_ObjDropDown.GetROProperty("selecteditem")
 If Trim(strListLabel) = Trim(ip_strSelectableItemValue) Then
  Fun_SelectDropdownListValue = 1
  Exit Function
 End If 

 'Get the total items under the list
 intTotalItemCnt = ip_ObjDropDown.GetItemsCount
 '<<< Get the maximum possible scroll point based on the existence of scroll bar object >>>
 ip_ObjDropDown.Open
 Wait 2
 If ip_ObjDropdown.SparkScrollBar("verticalScrollBar").Exist(2) Then
  blnScrollBarExists = True
        intMaxScrollValue = ip_ObjDropDown.SparkScrollBar("verticalScrollBar").GetROProperty("maximum")
 Else
  blnScrollBarExists = False
  intMaxScrollValue = 0
 End If 

 '<<< Get all child objects of type SparkListLabel >>>
 Set objSparkListLabelDescr = Description.Create()
 objSparkListLabelDescr("automationclassname").Value = "SparkListLabel"
 '<<< Extract all child objects until the maximum scroll point is reached >>>
 Do
  '<<< Move the scroll bar based on the child objects extracted within the loop >>>
  If blnScrollBarExists = True Then
   intCurrentScrollPoint = 20 * objDicListIndex.Count
   ip_ObjDropDown.SparkScrollBar("verticalScrollBar").Change intCurrentScrollPoint
   Wait 2
  Else
   intCurrentScrollPoint = 1
  End If 
  '<<< Get child objects >>>
  Set objSparkListLabelDescrChild = ip_ObjDropDown.ChildObjects(objSparkListLabelDescr)
  '<<< Fill the dictionary object based on the child objects extracted >>>>
  For intChildIndex = 0 To objSparkListLabelDescrChild.Count -1
   strListLabel = objSparkListLabelDescrChild(intChildIndex).GetROProperty("label")
   
   If Trim(strListLabel) = Trim(ip_strSelectableItemValue) Then
    objSparkListLabelDescrChild(intChildIndex).Click
    Fun_SelectDropdownListValue = 1 'Indicates success
    Exit Function
   ElseIf Not objDicListIndex.Exists(Trim(strListLabel)) And Trim(strListLabel) <> "" Then
    objDicListIndex.Add Trim(strListLabel), objDicListIndex.Count
   End If
  Next
  '<<< Exit the Loop on scroll bar reaching the maximum end point >>>
  If intCurrentScrollPoint > intMaxScrollValue Then
   Exit Do
  End If     
 Loop 
 subJCP_EventReport "Fail", "Unable to find item as: " & ip_strSelectableItemValue & " under the given dropdown: " & ip_strDropdownName, Browser("FlexApp")    
 Fun_SelectDropdownListValue = 0 'Indicates failure
End Function
'<<< ---------- Register this function to the SparkDropdownList object ----------- >>>
RegisterUserFunc "SparkDropDownList", "Fun_SelectDropdownListValue", "Fun_SelectDropdownListValue"
Example:-

returnValue = .SparkDropDownList("timeDropDown").Fun_SelectDropdownListValue("Time", "Custom...")
  If Cstr(returnValue) <> "1" Then
       ExitComponentIteration
  End If

QTP -> Function for providing the synch to the application object

'*******************************************************************************************************************************
' Name: Fun_SynchObjectToAppear
' Purpose: This function is used for providing the synch to the application object
' Argument(s):
'     ip_obj: Object under the test EX: SparkDropDownList
'     ip_intStartTime: Start time from which the max wait time  is evaluated. Ex: Timer value at the time of calling this function
'     ip_intWaitTimeInSec: Maximum wait time for object to appaer in the application and get visible
' Returns:
'      NON Zero Value: Indicates that the object is appeared on the screen and is visible.
'        0 Indicates that the object is not available under the application after waiting for the given max wait time.  
' Example: returnValue = Fun_SynchObjectToAppear(objSparkApplication, Timer, "360")
'------------------------------------------------------------------------------------------------------------
*******************************************************************************************************************************
Public Function Fun_SynchObjectToAppear(ByRef ip_Obj, ip_intStartTime, ip_intWaitTimeInSec)
 Dim blnObjectLoaded, intStartTime, intEndTime
 blnObjectLoaded = False 'Set the flag to false to indicate that the object is not loaded
 intStartTime = ip_intStartTime 'Start tiem is set as the passed argument value
 Do
  If ip_Obj.Exist(1) Then
   If CStr(ip_Obj.GetRoProperty("visible")) = "True" Then
     blnObjectLoaded = True 'Reset the flag to indicate that the object is appeared on the application
     Exit Do 'Exit the loop if the expected object is exists and is visible in the application
   End If
  End If   
  intEndTime = Timer 'Set the current time as end time
 Loop Until intEndTime > intStartTime + ip_intWaitTimeInSec 'Break the loop if the end time reached as more than the max wait time

 '<<< Report the report based upon the load event on the tab selection >>>
 If blnObjectLoaded = True Then
   Fun_SynchObjectToAppear = (intEndTime - ip_intStartTime)/60 'Return the total time taken in minutes as a return value
 Else
   Fun_SynchObjectToAppear = "0" 'Return ZERO to indicate that the object is not appeared on the screen
 End If
End Function
'<<< ---------- Function registration----------- >>>
RegisterUserFunc "SparkDropDownList", "Fun_SynchObjectToAppear", "Fun_SynchObjectToAppear"
RegisterUserFunc "SparkTextInput", "Fun_SynchObjectToAppear", "Fun_SynchObjectToAppear"
RegisterUserFunc "SparkCheckBox", "Fun_SynchObjectToAppear", "Fun_SynchObjectToAppear"
RegisterUserFunc "SparkButton", "Fun_SynchObjectToAppear", "Fun_SynchObjectToAppear"


Example : -

 '<<< Synch provided until the filter object gets displayed in the application >>>
 Set objSparkApplication = Browser("FlexApp").FlexApplication("app").SparkApplication("erfr-flex-gui.swf").SparkSkinnableContainer("showHidePane").SparkButton("Filters")
 intWaitTimeInSec = intConstWaitShort * 60
 '<<< Calling user defined function for providing application object synchronization >>>
 returnValue = Fun_SynchObjectToAppear(objSparkApplication, Timer, intWaitTimeInSec)

QC-> Code to get the user id and Password from QC resources tab

Preconditions: -
You need to upload a excel sheet in the QC resources tab, as below:

Key    Value
UID   naresh
PWD XXXX

 **********************************************************************************************************************
'Name:   Fun_GetCredentials
'Purpose:  This function will get the credential details from the QC resource
'Argument(s): ip_intResourceId: Resource ID of the item
'Returns:  0: Indicates failure
'     Credential details as: UserID|Password
'Example: returnValue = Fun_GetCredentials("1055")
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Revised:
'Revised by:
'Changes:
' **********************************************************************************************************************
Function Fun_GetCredentials(ip_intResourceId)
 Dim objQCResource, objDataSheet, intDSRowCnt, intDSRowIndex, strUserID, strPwd
 On Error Resume Next
  'Get the resource object from QC with resource id as ip_intResourceId
  Set objQCResource = QCUtil.QCConnection.QCResourceFactory.Item(ip_intResourceId)

  'Download the resource file into the result directory
  objQCResource.DownloadResource Environment("ResultDir"), True

  'Get the sheet 1 of resource file from result directory into the design time datatable at sheet 1
  DataTable.ImportSheet Environment("ResultDir") & "\" & objQCResource.Filename, 1, 1

  'Get the object Datasheet from the design time datatable
  Set objDataSheet = DataTable.GetSheet(1)  
  intDSRowCnt = objDataSheet.GetRowCount

  'Loop for each row and validate the Key column, based on key value get value column value
  For intDSRowIndex = 1 To intDSRowCnt
   objDataSheet.SetCurrentRow(intDSRowIndex)
   Select Case UCase(Trim(DataTable.Value(intConstKeyColIndex, 1)))
    Case "UID"
     strUserID = Trim(DataTable.Value(intConstValueColIndex, 1))
    Case "PWD"
     strPwd = Trim(DataTable.Value(intConstValueColIndex, 1))
   End Select
  Next
  'Set the return value
  If Trim(strUserID) <> "" And Trim(strPwd) <> "" Then
   Fun_GetCredentials = strUserID & "|" & strPwd
  Else
   Fun_GetCredentials = 0
   subJCP_EventReport "Fail", "Required credential details are not found. " & VBNewLine & "User ID = " & strUserID & VBNewLine & "Password = " & strPwd, Nothing
   Exit Function
  End If 
  'If unexpected error is reported here
  If Trim(CStr(Err.Description)) <> "" Then
    Fun_GetCredentials = 0
    subJCP_EventReport "Fail", "Unable to get login credentials. Error: " & Err.Description & VBNewLine &  Err.source, Nothing
    Err.Clear
  End If
 On Error GoTo 0

 'Delocate the reference
 Set objQCResource = Nothing
 Set objDataSheet = Nothing
End Function
**********************************************************************

Then use the below function like :

Example: -
 '<<< ---- If Credential pop up is loaded, get the JCP credentials from the Datatable () :Begins ---->>>
 If Browser("FlexApp").Dialog("Credential Popup").Exist(intConstWaitShort) Then
  returnValue = Fun_GetCredentials("1035")
  If Trim(CStr(returnValue)) = "0" Then
   ExitComponentIteration
  Else
   arrCredentials = Split(returnValue, "|")
   Browser("FlexApp").Dialog("Credential Popup").WinEdit("User name:").Set arrCredentials(0)
   Browser("FlexApp").Dialog("Credential Popup").WinEdit("Password:").Set arrCredentials(1)
   Browser("FlexApp").Dialog("Credential Popup").WinButton("OK").Click
  End If
 End If
 '<<< --- If Credential pop up is loaded, get the JCP credentials from the Datatable () :Ends ------->>>

QTP & QC->Code for function validate the displayed Error pop up

'****************************************************************************************************************************
'Function Name -------- Fun_ValidateExpectedPopUp(ip_strExpectPopUpDetails)
'Description ------------ Use this function validate the displayed Error pop up
'Parameters ----------- ip_strExpectPopUpDetails : EX: Y|Invalid Date|OK or N|NO Message
'Return Value ---------
'         1: Indicates expected pop up is displayed (Need to exit the component on this event)
'         1: Indicates expected pop up is not displayed (Need to exit the component on this event)
'         1: Unexpectedly pop up is displayed (Need to exit the component on this event)
'         0: Unexpected pop up is not displayed (Need not to exit the component on this event)
'Example -------------- Call Fun_ValidateExpectedPopUp(ip_strExpectPopUpDetails)
'****************************************************************************************************************************
Function Fun_ValidateExpectedPopUp(ip_strExpectPopUpDetails)
 '<<< Local variable declaration >>>
 Dim arrErrorMessageDetails, strErrorMsg, blnFlag
 Fun_ValidateExpectedPopUp = "1" '
 arrErrorMessageDetails = Split(ip_strExpectPopUpDetails, "|")
 If UCase(arrErrorMessageDetails(0)) = "Y" Then
   If Browser("Browser Main").Window("-- Web Page Dialog").Exist(intConstMidWaitTime) Or _
    Browser("Browser Main").Dialog("Message from webpage").Exist(intConstMidWaitTime) Then
     '<<< Expected Pop Up expectedly displayed => Success >>>
     '<<< Validating the error message >>>
     strErrorMsg = ""
     If Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").Exist(intConstShortWaitTime) Then
       strErrorMsg = Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").GetROProperty("innertext")
     ElseIf Browser("Browser Main").Dialog("Message from webpage").Static("Please").Exist(intConstShortWaitTime) Then
       strErrorMsg =Browser("Browser Main").Dialog("Message from webpage").Static("Please").GetROProperty("text")
     End If
     If Instr(strErrorMsg, arrErrorMessageDetails(1)) Then
      '<<< Expected error message is displayed >>>
      Fun_ReportSuccess "Validate the pop up Message", "Expected Pop up message is displayed on the pop up Message as:" & VBNewline & strErrorMsg
     Else
      '<<< Expected error message is not displayed >>>
      Fun_ReportFailure "Validate the pop up Message", "Expected Pop up message is not displayed on the pop up message as:" & VBNewline & arrErrorMessageDetails(1) & VBNewline & "Instead it is displayed as:" & VBNewline & strErrorMsg, Browser("Browser Main")
     End If
   Else
     '<<< Expected Pop Up unexpectedly not displayed=> Failure >>>
     Fun_ReportFailure "Expected Pop up Validation", "Expected pop up not displayed.", Browser("Browser Main")
   End If  
 Else
   If Browser("Browser Main").Window("-- Web Page Dialog").Exist(intConstShortWaitTime) Or _
    Browser("Browser Main").Dialog("Message from webpage").Exist(intConstShortWaitTime) Then
     strErrorMsg = ""
     If Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").Exist(intConstShortWaitTime) Then
       strErrorMsg = Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").GetROProperty("innertext")
     ElseIf Browser("Browser Main").Dialog("Message from webpage").Static("Please").Exist(intConstShortWaitTime) Then
       strErrorMsg =Browser("Browser Main").Dialog("Message from webpage").Static("Please").GetROProperty("text")
     End If
     '<<< Unexpected Pop Up unexpectedly displayed => Failure >>>
     Fun_ReportFailure "Unexpected Pop up Validation", "Unexpected pop up is displayed with text as:" & VBNewLine & strErrorMsg, Browser("Browser Main")
   Else
     '<<< Unexpected Pop Up expectedly not displayed=> Success >>>
     Fun_ReportSuccess "Unexpected Pop up Validation", "No pop ups displayed as expected."  
     Fun_ValidateExpectedPopUp = "0" 
   End If
 End If
 
 If Browser("Browser Main").Window("-- Web Page Dialog").Exist(intConstShortWaitTime) Then
   Browser("Browser Main").Window("-- Web Page Dialog").Close
 ElseIf Browser("Browser Main").Dialog("Message from webpage").Exist(intConstShortWaitTime) Then
   Browser("Browser Main").Dialog("Message from webpage").Close
 End If
End Function


'****************************************************************************************************************************
'SubRoutine Name -------- sub_popUpValidate(ip_ExpectPopUp)
'Description ------------ Use this sub routine to call the eexpected pop up funtion and handling the pop up message
'Author ----------------- Naresh gupta
'Parameters ----------- ip_ExpectPopUp : EX: Parameter("p_SC_CSS_ExpectPopUp")
'Return Value --------- NA
'Registerd To ---------- NA
'Base Project ---------- EFAS
'Example -------------- Call sub_popUpValidate(Parameter("p_SC_CSS_ExpectPopUp"))     
'****************************************************************************************************************************
Sub sub_popUpValidate(ip_strExpectPopUp)
 Dim returnVal
 returnVal = Fun_ValidateExpectedPopUp(ip_strExpectPopUp)
 If Cstr(returnVal) = "1"  Then
  ExitComponentIteration
 End If
End Sub

JAVA Addin->Code for function used for java table

'**********************************************************************************************************
'Function Name:   funGetColumnNumberFromJavaTable
'Purpose :           This function will return the column number from the JAVA Table based on the column name passed
'Parameters:      ip_objTable: Object Table
'       ip_strExpectedColName: Expected column Name
'      ip_intStartColNum: Start index from this point search for the expected column will start
'      ip_blnIsRelativeComparison: Pass this parameter as False if column name is to be matched with exact column header
'                else pass as True for relative compartison
'Return  value       "": If Expected column is not found
'Example:           returnValue = funGetColumnNumberFromJavaTable(objLeftGrid, "Item", 0, False)
'**********************************************************************************************************
Public Function funGetColumnNumberFromJavaTable(ByRef ip_objTable, ip_strExpectedColName, ip_intStartColNum, ip_blnIsRelativeComparison)
  '<<< Default Return value is set as empty >>>
 funGetColumnNumberFromJavaTable = ""
 
 Dim intColCnt, intColIndex, strColName
 intColCnt = ip_objTable.GetROProperty("cols")
 '<<< Extract the cell value from given row number starting from the mentioned start index for column >>>
 For intColIndex = ip_intStartColNum To intColCnt - 1
  strColName = ip_objTable.GetColumnName(intColIndex)

  '<<< Using Instr function for relative comparison based upon the flag value passed >>>
  If ip_blnIsRelativeComparison = True Then  
   If Instr(UCase(Trim(strColName)),UCase(Trim(ip_strExpectedColName))) Then
    funGetColumnNumberFromJavaTable = intColIndex
    Exit Function
   End If
  Else
   If UCase(Trim(strColName)) = UCase(Trim(ip_strExpectedColName)) Then
    funGetColumnNumberFromJavaTable = intColIndex
    Exit Function
   End If
  End If
 Next
End Function

'**********************************************************************************************************
'Function Name:   Fun_ExpandJavaTable
'Purpose :           This function will exapnd the grid based on the value cell details passed
'Parameters:      ip_objTable: Object Table
'Return  value       0: If succeeded
'       1: If Fails
'Example:           returnValue = Fun_ExpandJavaTable(objLeftGrid)
'**********************************************************************************************************
Public Function Fun_ExpandJavaTable(ByRef ip_objTable)
 Dim intExpandableColNum, strExpandableColName
 Fun_ExpandJavaTable = 0 'indicates success
 intExpandableColNum = funGetColumnNumberFromJavaTable(ip_objTable, "(+)", 0, False)
 If Trim(CStr(intExpandableColNum)) = "" Then
  Fun_ReportFailure "Expandworklist grid", "Unable to find the expandable column with name as (+)", Browser("Browser Main")
  Fun_ExpandJavaTable = 1 'indicates failure
 End If

 strExpandableColName = ip_objTable.GetColumnName(intExpandableColNum)
 ip_objTable.SelectColumnHeader strExpandableColName
 Wait intConstShortWaitTime

 intExpandableColNum = funGetColumnNumberFromJavaTable(ip_objTable, "(-)", 0, False)
 If Trim(CStr(intExpandableColNum)) = "" Then
  Fun_ReportFailure "Expandworklist grid", "Unable to expand the java table", Browser("Browser Main")
  Fun_ExpandJavaTable = 1 'indicates failure
    End If
End Function

'**********************************************************************************************************
'Function Name:   fun_GetLotRowNumberWithDetails
'Purpose :           This function will return the expected lot under the javatable
'Parameters:      ip_objGridLeftGrid: Object Left Table which contains Item Lot information
'      ip_objGridRightGrid: Object Right Table which contains Item details information
'      ip_strItemNum: Expected Item Number EX: 5889550
'      ip_strLineNum: Expected Line Number under the given lot EX: 0108
'      ip_strColNameCollection: Column name details EX: "SSC#|Whse PO #| Engine Status"
'      ip_strColValueCollection: Expected value collection: "258|9564|Running"
'Return  value       "": If Fails
'       ROw number: If succeeds in finding a row
'Example:           returnValue = fun_GetLotRowNumberWithDetails(objLeftGrid, objRightGrid, "5889550", "0108", "SSC#|Whse PO #| Engine Status", "258|9564|Running")
'**********************************************************************************************************
Public Function fun_GetLotRowNumberWithDetails(ip_objGridLeftGrid,ip_objGridRightGrid, ip_strItemNum, ip_strLineNum, ip_strColNameCollection, ip_strColValueCollection)
 Dim arrColNameCollection, arrValueCollection, intExpectedColIndex, intRowCount, intRowIndex
 Dim blnDetailsMatched, intDetailsIndex, strActualValue, strLineValue, intItemColNum
 Dim arrColNumCollection
 Dim intFindItemNumIndex, strItemNum
 
 fun_GetLotRowNumberWithDetails = "" 'indicates failure
 intItemColNum = funGetColumnNumberFromJavaTable(ip_objGridLeftGrid, "Item", 0, False)
 If Trim(intItemColNum) = "" Then
  Fun_ReportFailure "Find Columns", "Unable to find the column with name as Item", Browser("Browser Main")
  fun_GetLotRowNumberWithDetails = "" 'indicates failure
  Exit Function
 End If
 arrColNameCollection = Split(ip_strColNameCollection, "|")
 arrValueCollection = Split(ip_strColValueCollection, "|")
 ReDim arrColNumCollection(UBound(arrColNameCollection))

 For intExpectedColIndex = 0 To UBound(arrColNameCollection)
  arrColNumCollection(intExpectedColIndex) = funGetColumnNumberFromJavaTable(ip_objGridRightGrid, Trim(arrColNameCollection(intExpectedColIndex)), 0, False)
  If Trim(arrColNumCollection(intExpectedColIndex)) = "" Then
   Fun_ReportFailure "Find Columns", "Unable to find the column with name as:" & arrColNameCollection(intExpectedColIndex), Browser("Browser Main")
   fun_GetLotRowNumberWithDetails = "" 'indicates failure
   Exit Function
  End If
 Next

 intRowCount = ip_objGridLeftGrid.GetROProperty("rows")
 For intRowIndex = 0 To intRowCount - 1
   blnDetailsMatched = True
   For intDetailsIndex = 0 To UBound(arrValueCollection)
    strActualValue = ip_objGridRightGrid.GetCellData(intRowIndex, arrColNumCollection(intDetailsIndex))
    Select Case UCase(Trim(arrValueCollection(intDetailsIndex)))
      Case "BLANK"
           If Trim(strActualValue) <> "" Then
            blnDetailsMatched = False
            Exit For
           End If
      Case "NON BLANK"   
           If Trim(strActualValue) = "" Then
            blnDetailsMatched = False
            Exit For
           End If
      Case Else
           If Trim(strActualValue) <> Trim(arrValueCollection(intDetailsIndex)) Then
            blnDetailsMatched = False
            Exit For
           End If
    End Select
   Next
   If blnDetailsMatched = True Then
     strLineValue = ip_objGridLeftGrid.GetCellData(intRowIndex, intItemColNum)
     If Trim(strLineValue) = Trim(ip_strLineNum) Then
       For intFindItemNumIndex = intRowIndex - 1 To 0 Step -1
         strItemNum = Trim(ip_objGridLeftGrid.GetCellData(intFindItemNumIndex, intItemColNum))
         If Len(strItemNum) = 7 Then
          If Trim(strItemNum) = Trim(ip_strItemNum) Then
           fun_GetLotRowNumberWithDetails = intFindItemNumIndex
           Exit Function
          Else
           Exit For
          End If
         End If               
       Next    
     End If 
   End If
 Next
End Function


'**********************************************************************************************************
'Function Name:   fun_GetLotRowNumberWithoutDetails
'Purpose :           This function will return the expected lot under the javatable
'Parameters:      ip_objLeftGrid: Object Left Table which contains Item Lot information
'      ip_strItemNum: Expected Item Number EX: 5889550
'      ip_strLineNum: Expected Line Number under the given lot EX: 0108
'Return  value       "": If Fails
'       ROw number: If succeeds in finding a row
'Example:           returnValue = fun_GetLotRowNumberWithoutDetails(objLeftGrid, "5889550", "0108")
'**********************************************************************************************************
Public Function fun_GetLotRowNumberWithoutDetails(ip_objLeftGrid, ip_strItemNum, ip_strLineNum)
 Dim intRowCount, intItemColNum, intRowIndex, strLineValue, intFindItemNumIndex, strItemNum
 fun_GetLotRowNumberWithoutDetails = ""
 intRowCount = ip_objLeftGrid.GetROProperty("rows")
 intItemColNum = funGetColumnNumberFromJavaTable(ip_objLeftGrid, "Item", 0, False)
 If Trim(Cstr(intItemColNum)) = "" Then
  Fun_ReportFailure "Validate Item column", "Unable to find column with name Item", Browser("Browser Main")
  fun_GetLotRowNumberWithoutDetails = ""
  Exit Function
 End If
 For intRowIndex = 0 To intRowCount - 1
  strLineValue = ip_objLeftGrid.GetCellData(intRowIndex, intItemColNum)
  If Trim(strLineValue) = Trim(ip_strLineNum) Then
    For intFindItemNumIndex = intRowIndex - 1 To 0 Step -1
      strItemNum = Trim(ip_objLeftGrid.GetCellData(intFindItemNumIndex, intItemColNum))
      If Len(strItemNum) = 7 Then
       If Trim(strItemNum) = Trim(ip_strItemNum) Then
        fun_GetLotRowNumberWithoutDetails = intFindItemNumIndex
       Else
        Exit For
       End If
      End If               
    Next    
  End If 
 Next
End Function

'**********************************************************************************************************
'Function Name:   Fun_SelectingRows
'Purpose :           This Function will Select Rows depending on Line Item,Lot Number,Warehouse Number, Source DI
'Parameters:      ip_objLeftGrid: Object Left Table which contains Item Lot information
'      ip_objRightGrid: Object Right Table which contains Source DI and WarehouseDetails
'      ip_strItemsToBeSelected: Expected Item Numbers to be selected EX: "5230304|L|2965"
'      ip_strSourceDI: Expected Source DI numbers to be Selected EX: "77793247-00"
'Return  value       "": If Fails
'       ROw number: If succeeds in finding a row
'Example:           Fun_SelectingRows = fun_GetLotRowNumberWithoutDetails(objLeftGrid,ip_objRightGrid, "5230304##9764|L|2965", "77793247-00")
'**********************************************************************************************************
Function Fun_SelectingRows(ByRef ip_objLeftGrid, ByRef ip_objRightGrid, ip_strItemsToBeSelected, ip_strSourceDI)
 Dim strMatchedRowCollection,arrSourceDINumCollection,intItemIndex,arrItemCollection,arrItemDetails
 Dim arrItemWarehouseDetails,intTryIndex,strUpdatedSourceDIValue,intMatchedRow,arrSourceDI
 strMatchedRowCollection = ""
 '<<< Select items based on the Source DI also along with item no and line no>>>
 arrItemCollection = Split(ip_strItemsToBeSelected, ",")
 If Trim(ip_strSourceDI) <> "" Then
   arrSourceDINumCollection = Split(ip_strSourceDI, ",")
 End If 
 For intItemIndex = 0 To UBound(arrItemCollection)   
   arrItemDetails = Split(arrItemCollection(intItemIndex), "|")
   If Instr(arrItemDetails(0), "##") Then
     arrItemWarehouseDetails = Split(arrItemDetails(0), "##")
     If Trim(ip_strSourceDI) <> "" Then
       '<<< Getting Row Number with Warehouse Details and  Source DI>>>
       '<<< Ignoring the -00, -01, -02 -03 >>>
       arrSourceDI = Split(arrSourceDINumCollection(intItemIndex), "-")
       For intTryIndex = 0 To 3
        strUpdatedSourceDIValue = arrSourceDI(0) & "-0" & CStr(intTryIndex)       
        intMatchedRow = fun_GetLotRowNumberWithDetails(ip_objLeftGrid, ip_objRightGrid, arrItemWarehouseDetails(0), arrItemDetails(2), "PO Whse #|Source DI #", arrItemWarehouseDetails(1) & "|" & strUpdatedSourceDIValue)
        If CStr(intMatchedRow) <> "" Then
         '<<< Indicates expected row with source DI is found >>>
         Exit For
        End If
       Next
     Else
       '<<< Getting Row Number with Warehouse Details and  without Source DI>>>
       intMatchedRow = fun_GetLotRowNumberWithDetails(ip_objLeftGrid, ip_objRightGrid, arrItemWarehouseDetails(0), arrItemDetails(2), "PO Whse #", arrItemWarehouseDetails(1))
     End If
   Else
     '<<< Getting Row Number without Warehouse Details with Source DI>>>
     If Trim(ip_strSourceDI) <> "" Then
       '<<< Ignoring the -00, -01, -02 -03 >>>
       arrSourceDI = Split(arrSourceDINumCollection(intItemIndex), "-")
       For intTryIndex = 0 To 3
        strUpdatedSourceDIValue = arrSourceDI(0) & "-0" & CStr(intTryIndex)       
        intMatchedRow = fun_GetLotRowNumberWithDetails(ip_objLeftGrid, ip_objRightGrid, arrItemDetails(0), arrItemDetails(2), "Source DI #", strUpdatedSourceDIValue)
        If CStr(intMatchedRow) <> "" Then
         '<<< Indicates expected row with source DI is found >>>
         Exit For
        End If
       Next
     Else
       '<<< Getting Row Number without Warehouse Details and Source DI>>>      
       intMatchedRow = fun_GetLotRowNumberWithoutDetails(ip_objLeftGrid, arrItemDetails(0), arrItemDetails(2))
     End If
   End If
   If CStr(intMatchedRow) <> "" Then
    ip_objLeftGrid.SelectCell intMatchedRow, 0
    strMatchedRowCollection = strMatchedRowCollection & "|" & Cstr(intMatchedRow)
   Else
    '<<< Expected Row with Lot and Source DI details not found >>>
    Fun_ReportFailure "Validate if Row with Lot and Source DI details found", "Validate if Row with Lot and Source DI details found", Browser("Browser Main")
    ExitComponentIteration
   End If
 Next
 Fun_SelectingRows = Right(strMatchedRowCollection, Len(strMatchedRowCollection) - 1)
End Function

QTP & QC->DB->Code for funcitons to connect to Database and exexute query.

'**********************************************************************************************************
'Function Name:   fun_OpenDBConnection(ip_objDBConnection, ip_strConnectionString)
'Description :        Used to connect to the database based upon the connection string passed
'Parameters:        ip_objDBConnection: Connection object which will be used for db connection
'        ip_strConnectionString : Actual connection string
'Return  value       0 : Indicates the successful deleteion of the file
'        Non Zero Vale: Indicates the failure and contains the error description for the failure  
'Example:            fun_OpenDBConnection(objDBConnection, "Driver={Microsoft ODBC for Oracle};Server=NALXTST2;Uid=ab4p_app01;Pwd=ab4p_app01_bibtb;")
'**********************************************************************************************************
Public Function fun_OpenDBConnection(ip_objDBConnection, ip_strConnectionString)
   On Error Resume Next
   ip_objDBConnection.ConnectionString= ip_strConnectionString
   ip_objDBConnection.Open
    If Err.Description <> "" Then
     '<<< Return the error code along with the description if function is unable to make connection >>>
     fun_OpenDBConnection = Err.Description & VBNewLine &  Err.source
     Err.Clear
     Exit Function
   Else
    '<<< Return 1 if function is able to make connection successfully >>>
    fun_OpenDBConnection = ip_objDBConnection.State
   End If  
End Function


'**********************************************************************************************************
'Function Name:   fun_ExecuteQuery(ip_objDBConnection,ip_strQuery)
'Description :        Used to execute the query to retrieve the record set
'Parameters:        ip_objDBConnection: Connection object which will be used for db connection
'        ip_strQuery : Actual query to be executed
'Return  value       Error text with error description
'          Recordset as a result of query execution
'Example:            fun_ExecuteQuery(objDBConnection, "Select * from MyTable")
'**********************************************************************************************************
Public Function fun_ExecuteQuery(ip_objDBConnection,ip_strQuery)
   On Error resume next
   Dim objRes
   '<<< Get the result set by executing the passed query >>>
    Set objRes = ip_objDBConnection.Execute(ip_strQuery)
    If Err.Description <> "" Then
     fun_ExecuteQuery = "ERROR: " & VBNewLine & Err.Description & VBNewLine &  Err.source
     Err.Clear
   Else
    '<<< ---------- Converting the Recorset to the array ---------- >>>
    Dim intTotalRows , intTotalColumns
    intTotalRows = 0
    intTotalColumns = objRes.Fields.Count - 1
    '<<< Calculating the total rows returned by the recordset >>>
    While objRes.EOF = False
      objRes.moveNext
      intTotalRows = intTotalRows + 1
    WEnd
    '<<< Forming the Array based upon t he total Rows and Total Coulns returned by the query >>>
    Dim arrCollection()
    ReDim arrCollection(CInt(intTotalRows) - 1, intTotalColumns)
   
    '<<< Get the recordset cursor to the first point >>>
    objRes.MoveFirst
    Dim intRowIndex, intColumnIndex
    intRowIndex = 0
    While objRes.EOF = False
      For intColumnIndex = 0 To objRes.Fields.Count -1  
       If  CStr(objRes.fields(intColumnIndex).Value) <> "" Then
          arrCollection(intRowIndex, intColumnIndex) = objRes.fields(intColumnIndex).Value   
       End If
       Next
      objRes.moveNext
      intRowIndex = CInt(intRowIndex) + 1
    WEnd
    '<<< Return the newly formed array >>>
    fun_ExecuteQuery = arrCollection
    objRes.Close
   End If
End Function


Example Code:-
 Set objDatabaseCon = CreateObject("ADODB.Connection")
 Set objRecordset = CreateObject("ADODB.RecordSet")

strConString="Driver={Microsoft ODBC for Oracle};Server=NALXTST2;Uid=ab4p_app01;Pwd=ab4p_app01_bibtb;")

strConnectionState =  fun_OpenDBConnection(objDatabaseCon, strConString)
    '<<< Exit the component if the connection status is not returned as 1 => connection was not successfll >>>
    If CStr(strConnectionState) <> "1" Then
     Fun_ReportFailure "Connecting to database " & vbNewLine & strConString, "Unable to make connection :" & vbNewLine & strConnectionState, Nothing
     ExitComponentIteration
    End If

    strQuery = "select * from bridge.pl2pom_logical_msg where system_id in (select system_id from bridge.pl2pom_xml_msg where correlation_id ='" & Trim(arrAllocCollection(3)) & "')"

    '<<<Collecting the result of the query in to an array>>>
    arrRecordSet =  fun_ExecuteQuery(objDatabaseCon, strQuery)
    '<<<----- Validate the return type from the function call ------ >>>
    If Not IsArray(arrRecordSet) Then
     If Instr(arrRecordSet, "ERROR") Then
      Fun_ReportFailure "Execute Query", "Unable to execute the following query due to the error :" & arrRecordSet & vbNewLine & " Query: " & strQuery, Nothing
     Else
      Fun_ReportFailure "Execute Query", "Unable to execute the following query : " & strQuery, Nothing
     End If
     ExitComponentIteration
    Else
     If UBound(arrRecordSet) < 0 Then
      Fun_ReportFailure "Execute Query", "Unable to retrieve any rows by executing the following query : " & VBNewline & strQuery, Nothing 
      ExitComponentIteration
     End If
    End If

QTP & QC ->Code for to delete the existing file at specified path

'**********************************************************************************************************
'Function Name:   fun_DeleteExistingFile(ip_strFileName)
'Description :        Used to delete the existing file at path ip_strFileName
'Parameters:        ip_strFileName: Path of the file need to be deleted
'Return  value       0 : Indicates the successful deletion of the file
'        Non Zero Vale: Indicates the failure and contains the error description for the failure  
'Example:            fun_DeleteExistingFile("C:\MyFile.xls")
'**********************************************************************************************************
Public Function fun_DeleteExistingFile(ip_strFileName)
 On Error Resume Next
 Dim objFileSystem, objFile
 Set objFileSystem = CreateObject("Scripting.FileSystemObject")
 '<<< Attempt to Delete the file only if the file exists >>>
 If (objFileSystem.FileExists(ip_strFileName)) Then
  Set objFile = objFileSystem.GetFile(ip_strFileName)
  objFile.Delete
 End If
 '<<< Any error during the Delete operation, will be captured in Err object and will be sent back to the calling the function >>>
 If Err.Description <> "" Then
   '<<< Return the error code along with the description if function is unable to delete a file >>>
   fun_DeleteExistingFile = Err.Description & VBNewLine &  Err.source
   Err.Clear
   Exit Function
 Else
  '<<< Return Zero if function is able to delete a file successfully >>>
  fun_DeleteExistingFile = 0
 End If
End Function


Example Code:-
   Parameter("p_POFilesLocation")  = "C:\EFAS_Tools\Automation\PO_Files"
   strPOFilePath = Parameter("p_POFilesLocation") & "\POMsg.txt"      
   Set objFileSys = CreateObject("Scripting.FileSystemObject")
   '<<<Delete the file if it exists in the specified path>>>
   strFileStatus= fun_DeleteExistingFile(strPOFilePath)
   If  CStr(strFileStatus) <> "0" Then
    Fun_ReportFailure "Verify the file deleted", "Text file not deleted in the specified path : ERROR : " & strFileStatus , Nothing
    ExitComponentIteration
   End If