01-29-2011, 12:13 AM
1. I am connecting to an Oracle DB with an ADOB connection and running a SQL query to return the results back to the runtime datatable. This was the first issue I had trouble with that is now working.
2. As long as I only need one query ran and a single output sheet added to the datatable everything is fine. The problem arises when running 2 or more queries that add more sheets to the datatable. When I try to return the value of a cell in any of those sheets with this code:
I get the following error:
The Sheet.GetParameter operation failed. The <Segment1> column does not exist.
I have ran some scripts and figured out that the sheets have been created and they have the correct number of parameters. I added my variables to the debug "add to watch" and I get the same error.
If I run the following code:
I get a return of the active sheet is "Action1"
My question is:
1. Is there a way to change the active runtime datatable sheet to the sheet I need to return the values from?
2. If not does anyone have any advice as to how to proceed.
I will include the code for the script below:
2. As long as I only need one query ran and a single output sheet added to the datatable everything is fine. The problem arises when running 2 or more queries that add more sheets to the datatable. When I try to return the value of a cell in any of those sheets with this code:
Code:
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").EnterField 1, "Product Value", DataTable.GetSheet("output1").GetParameter("Segment1").ValueI get the following error:
The Sheet.GetParameter operation failed. The <Segment1> column does not exist.
I have ran some scripts and figured out that the sheets have been created and they have the correct number of parameters. I added my variables to the debug "add to watch" and I get the same error.
If I run the following code:
Code:
Sheetname = DataTable.LocalSheet.Name
'
'Reporter.ReportEvent 1, "The Active Sheet is", SheetnameI get a return of the active sheet is "Action1"
My question is:
1. Is there a way to change the active runtime datatable sheet to the sheet I need to return the values from?
2. If not does anyone have any advice as to how to proceed.
I will include the code for the script below:
Code:
'Connect to Oracle DB and execute a SQL Query, then write the data to
'the QTP runtime datatable.
'Variables for the database connection
Dim adocon,adorecordset
'Create the Connection object
Set adocon=CreateObject("ADODB.Connection")
constr="Provider=OraOLEDB.Oracle.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Data Source=Dev"
adocon.Open constr
'Execute the SQL query
Set adorecordset=CreateObject("ADODB.Recordset")
Set adorecordset.ActiveConnection=adocon
Strsql="Select 'TEST PRICE LIST:' || to_char(sysdate,' MM-DD-YY HH:MM:SS') as PRICE_LST_NAME from Dual"
adorecordset.Source=Strsql
adorecordset.Open
'Create the datatable to export the data
'Create an output sheet
Set outsheet=Datatable.Addsheet("output")
'Loop through each field to get the name to
'create the datable parameters
For each fld in adoRecordset.Fields
outsheet.AddParameter fld.name,""
Next
i=1
Do while Not adorecordset.EOF
DataTable.GetSheet(outsheet.name).SetCurrentRow(i)
For each fld in adorecordset.Fields
outsheet.GetParameter(fld.name).value=fld.value
Next
adorecordset.MoveNext
i=i+1
Loop
'close the recordset object
adorecordset.close
'Destroy the recordset object
Set adorecordset = nothing
'close the database connection
adoCon.close
'Destroy the connection object
Set adoCon = nothing
'Connect to Oracle DB and execute a SQL Query, then write the data to
'the QTP runtime datatable.
'Create the Connection object
Set adocon=CreateObject("ADODB.Connection")
constr="Provider=OraOLEDB.Oracle.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Data Source=Dev"
adocon.Open constr
'Execute the SQL query
Set adorecordset=CreateObject("ADODB.Recordset")
Set adorecordset.ActiveConnection=adocon
Strsql="SELECT Segment1, Quantity FROM (Select Description, Enabled_Flag, A.Inventory_Item_Id, B.Inventory_Item_Id, Inventory_Item_Status_Code, Organization_Id, Segment1, Primary_Uom_Code, Primary_Unit_Of_Measure,(Case When B.Conversion_Rate Is Null Then 1 Else B.Conversion_Rate End) Quantity, B.Uom_Code, B.Unit_Of_Measure From Mtl_System_Items_B A Left Outer Join Mtl_Uom_Conversions B On (A.Inventory_Item_Id = B.Inventory_Item_Id) Where -1= -1 And (Organization_Id = 356 Or Organization_Id = 359 Or Organization_Id = 591) AND (ITEM_TYPE =('LNV_FGM') OR ITEM_TYPE =('LNV_FGB'))And Enabled_Flag = 'Y' And Inventory_Item_Status_Code = 'Active' ORDER BY dbms_random.value ) WHERE rownum = 1"
adorecordset.Source=Strsql
adorecordset.Open
'Create the datatable to export the data
'Create an output sheet
Set outsheet=Datatable.Addsheet("output1")
'Loop through each field to get the name to
'create the datable parameters
For each fld in adoRecordset.Fields
outsheet.AddParameter fld.name,""
Next
i=1
Do while Not adorecordset.EOF
DataTable.GetSheet(outsheet.name).SetCurrentRow(i)
For each fld in adorecordset.Fields
outsheet.GetParameter(fld.name).value=fld.value
Next
adorecordset.MoveNext
i=i+1
Loop
'close the recordset object
adorecordset.close
'Destroy the recordset object
Set adorecordset = nothing
'close the database connection
adoCon.close
'Destroy the connection object
Set adoCon = nothing
Dim objIE
Set objIE = CreateObject("InternetExplorer.Application")
objIE.visible = True
objIE.navigate "http://devap1.linvatec.com:8030/OA_HTML/AppsLocalLogin.jsp?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F%2Fdevap1.linvatec.com%3A8030%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&s2=CCC48C9F7A462B6500680579CCCFEB46A1937E84A75CCEAC13471DA4D4E81AB9"
objIE.statusbar = 1
Wait 3
Browser("Oracle Applications Home").Page("Login").WebEdit("username").Set "xxxx"
Browser("Oracle Applications Home").Page("Login").WebEdit("password").SetSecure "xxxxx"
Browser("Oracle Applications Home").Page("Login").Image("Login").Click
Browser("Oracle Applications Home").Page("Oracle Applications Home_4").Link("LNV Pricing Super User").Click
Browser("Oracle Applications Home").Page("Oracle Applications Home_5").Link("Price List Setup").Click
Browser("Oracle Applications 11i").Page("Oracle Applications 11i_2").Sync
Browser("Oracle Applications Home").Page("Oracle Applications Home_5").Sync
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("Main").OracleTextField("Name").Enter "ADV ALL BLADES & BURS"
OracleNotification("Note").Approve
OracleFormWindow("Advanced Pricing").PressToolbarButton "Clear Record"
DataTable.GetSheet("output").SetCurrentRow(1)
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("Main").OracleTextField("Name").Enter DataTable.GetSheet("output").GetParameter("PRICE_LST_NAME").Value
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("Main").OracleTextField("Description").Enter "QTP Test"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("Main").OracleTextField("Effective Dates").OpenDialog
OracleCalendar("Calendar").Enter "15-NOV-2010"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("Main").OracleTextField("-").OpenDialog
OracleCalendar("Calendar").Enter "15-APR-2011"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("Main").OracleTextField("Multi-Currency Conversion").OpenDialog
OracleListOfValues("Multi Currency Conversion").Select "Generated Currency Conversion For USD 2002"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").OpenDialog 1,"Product Context"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").OpenDialog 1,"Product Attribute"
OracleListOfValues("Product Attributes").Select "Item Number"
DataTable.GetSheet("output1").SetCurrentRow(1)
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").EnterField 1, "Product Value", DataTable.GetSheet("output1").GetParameter("Segment1").Value
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").EnterField 1, "Value", "35.00"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").OpenDialog 1,"Start Date"
OracleCalendar("Calendar").Enter "15-NOV-2010"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").OpenDialog 1,"End Date"
OracleCalendar("Calendar").Enter "15-APR-2011"
OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List Lines").OracleTable("LIST_LINES").SetFocus 1,"[ ]"
OracleFlexWindow("Additional Info for List").OracleTextField("DM FLOOR").Enter "1"
OracleFlexWindow("Additional Info for List").Approve
OracleFormWindow("Advanced Pricing").SelectMenu "File->Save"
OracleFormWindow("Advanced Pricing").SelectMenu "File->Exit Oracle Applications"
OracleNotification("Caution").Approve
Browser("Oracle Applications 11i").Page("Oracle Applications 11i_2").Sync
Browser("Oracle Applications 11i").CloseAllTabs
Browser("Oracle Applications Home").Page("Oracle Applications Home_5").Link("Logout").Click
Browser("Oracle Applications Home").Page("Login_2").Sync
Browser("Oracle Applications Home").CloseAllTabs