0

I have a macro in excel 2013 that is trying to connect to an Oracle database through "Microsoft ODBC for Oracle", the 32bit version, as the 64bit has issues for some reason. And though the query works from Toad, it returns no results in my macro.

I have tried shorter queries and they do return results making me think that this monster query cannot be appended correctly, but I have no way to test this.

My current situation is a giant and very ugly append to get this query into one long string to be executed by my ADOConnection.

SQLString = "SELECT LEVEL, "
SQLString = SQLString & "MSIL.SEGMENT1 ITEM, "
SQLString = SQLString & "MSIL.DESCRIPTION DESC_COMPONENT, "
SQLString = SQLString & "BIC.ITEM_TYPE, "
SQLString = SQLString & "BIC.SUPPLY_TYPE, "
SQLString = SQLString & "MSIL.PRIMARY_UOM_CODE UOM, "
SQLString = SQLString & "BIC.COMPONENT_QUANTITY, "
SQLString = SQLString & "item_attribute.ITEM_LENGTH, "
SQLString = SQLString & "item_attribute.ANGLE_LEFT, "
SQLString = SQLString & "item_attribute.ANGLE_RIGHT, "
SQLString = SQLString & "item_attribute.WT_HT_FLAG, "
SQLString = SQLString & "item_attribute.FABRICATION_NUMBER, "
SQLString = SQLString & "item_attribute.PANEL_WIDTH, "
SQLString = SQLString & "item_attribute.PANEL_HEIGHT, item_attribute.GLASS_DIM1, item_attribute.GLASS_DIM2, item_attribute.DIMENSION1, item_attribute.DIMENSION2, "
SQLString = SQLString & "item_attribute.DIMENSION3, item_attribute.DIMENSION4, item_Attribute.DIMENSION5 "
SQLString = SQLString & "FROM APPS.MTL_SYSTEM_ITEMS MSI, APPS.BOM_BILL_OF_MATERIALS BOM, APPS.BOM_INVENTORY_COMPONENTS_V BIC, APPS.MTL_SYSTEM_ITEMS MSIL, APPS.mtl_parameters mp, "
SQLString = SQLString & "APPS.MTL_ITEM_CATALOG_GROUPS_VL micg, (SELECT * FROM (SELECT msi.inventory_item_id, mde.element_name, "
SQLString = SQLString & "mdev.element_value "
SQLString = SQLString & "FROM APPS.mtl_parameters mp, APPS.mtl_system_items_b msi, APPS.MTL_ITEM_CATALOG_GROUPS_VL micg, APPS.MTL_DESCRIPTIVE_ELEMENTS mde, APPS.MTL_DESCR_ELEMENT_VALUES mdev "
SQLString = SQLString & "WHERE mp.organization_id = MSI.organization_id AND mp.organization_code = 'POR' "
SQLString = SQLString & "AND micg.ITEM_CATALOG_GROUP_ID = MSI.ITEM_CATALOG_GROUP_ID AND mde.ITEM_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID "
SQLString = SQLString & "AND mdev.element_name = mde.element_name AND mdev.inventory_item_id = msi.inventory_item_id AND mde.element_name IN ('Length', "
SQLString = SQLString & "'Angle Left', 'Angle Right', 'Wt Ht Flag', 'Fabrication Number', 'Panel Width', 'Panel Height', 'Glass Dim 1', 'Glass Dim 2', "
SQLString = SQLString & "'Dimension 1', 'Dimension 2', 'Dimension 3', 'Dimension 4', 'Dimension 5')) PIVOT (MAX (Element_value) FOR (element_name) "
SQLString = SQLString & "IN ('Length' AS ITEM_LENGTH, 'Angle Left' AS ANGLE_LEFT, 'Angle Right' AS ANGLE_RIGHT, 'Wt Ht Flag' AS WT_HT_FLAG, "
SQLString = SQLString & "'Fabrication Number' AS FABRICATION_NUMBER, 'Panel Width' AS PANEL_WIDTH, 'Panel Height' AS PANEL_HEIGHT, 'Glass Dim 1' AS GLASS_DIM1, "
SQLString = SQLString & "'Glass Dim 2' AS GLASS_DIM2, 'Dimension 1' AS DIMENSION1, 'Dimension 2' AS DIMENSION2, 'Dimension 3' AS DIMENSION3, "
SQLString = SQLString & "'Dimension 4' AS DIMENSION4, 'Dimension 5' AS DIMENSION5)) ORDER BY inventory_item_id) Item_attribute WHERE 1 = 1 "
SQLString = SQLString & "AND item_Attribute.inventory_item_id(+) = msil.INVENTORY_ITEM_ID AND BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID "
SQLString = SQLString & "AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_id AND bom.bill_sequence_id = bic.bill_sequence_id AND NVL (bic.disable_date, SYSDATE) >= SYSDATE "
SQLString = SQLString & "AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID AND mp.organization_id = msi.organization_id "
SQLString = SQLString & "AND mp.organization_code = 'POR' "
SQLString = SQLString & "AND micg.ITEM_CATALOG_GROUP_ID = msi.ITEM_CATALOG_GROUP_ID AND MSI.ORGANIZATION_ID = (SELECT DISTINCT OOL.SHIP_FROM_ORG_ID "
'These two lines are the ones that have our variables
SQLString = SQLString & "FROM APPS.OE_ORDER_HEADERS_ALL OOH, APPS.OE_ORDER_LINES_ALL OOL, APPS.MTL_SYSTEM_ITEMS MSI1 WHERE OOH.ORDER_NUMBER = '" & OracleOrderNumber & "' "
SQLString = SQLString & "AND OOL.LINE_NUMBER = '" & OracleLineNumber & "' "
'----------------------------------------------------
SQLString = SQLString & "AND OOL.HEADER_ID = OOH.HEADER_ID AND MSI1.SEGMENT1 = OOL.ORDERED_ITEM AND MSI1.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID) "
SQLString = SQLString & "CONNECT BY PRIOR BIC.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID START WITH MSI.INVENTORY_ITEM_ID IN (SELECT MSI1.INVENTORY_ITEM_ID "
SQLString = SQLString & "FROM APPS.OE_ORDER_HEADERS_ALL OOH, APPS.OE_ORDER_LINES_ALL OOL, APPS.MTL_SYSTEM_ITEMS MSI1 "
'These two lines are the ones that have our variables...again
SQLString = SQLString & "WHERE OOH.ORDER_NUMBER = '" & OracleOrderNumber & "' "
SQLString = SQLString & "AND OOL.LINE_NUMBER = '" & OracleLineNumber & "' "
'----------------------------------------------------
SQLString = SQLString & "AND OOL.HEADER_ID = OOH.HEADER_ID AND MSI1.SEGMENT1 = OOL.ORDERED_ITEM AND MSI1.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID)"

There original query in it's cleaned up form is here.

SELECT LEVEL,
       MSIL.SEGMENT1 ITEM,
       MSIL.DESCRIPTION DESC_COMPONENT,
       BIC.ITEM_TYPE,
       BIC.SUPPLY_TYPE,
       MSIL.PRIMARY_UOM_CODE UOM,
       BIC.COMPONENT_QUANTITY,
       item_attribute.ITEM_LENGTH,
       item_attribute.ANGLE_LEFT,
       item_attribute.ANGLE_RIGHT,
       item_attribute.WT_HT_FLAG,
       item_attribute.FABRICATION_NUMBER,
       item_attribute.PANEL_WIDTH,
       item_attribute.PANEL_HEIGHT,
       item_attribute.GLASS_DIM1,
       item_attribute.GLASS_DIM2,
       item_attribute.DIMENSION1,
       item_attribute.DIMENSION2,
       item_attribute.DIMENSION3,
       item_attribute.DIMENSION4,
       item_attribute.DIMENSION5
  FROM APPS.MTL_SYSTEM_ITEMS MSI,
       APPS.BOM_BILL_OF_MATERIALS BOM,
       APPS.BOM_INVENTORY_COMPONENTS_V BIC,
       APPS.MTL_SYSTEM_ITEMS MSIL,
       APPS.mtl_parameters mp,
       APPS.MTL_ITEM_CATALOG_GROUPS_VL micg,
       (  SELECT *
            FROM (SELECT msi.inventory_item_id,
                         mde.element_name,
                         mdev.element_value
                    FROM APPS.mtl_parameters mp,
                         APPS.mtl_system_items_b msi,
                         APPS.MTL_ITEM_CATALOG_GROUPS_VL micg,
                         APPS.MTL_DESCRIPTIVE_ELEMENTS mde,
                         APPS.MTL_DESCR_ELEMENT_VALUES mdev
                   WHERE     mp.organization_id = msi.organization_id
                         AND mp.organization_code = 'POR' -- Warehouse -- POR(Portland), ITM (Item Master), TAC (Tacoma), TEM (Temecula), CMP (Composites)
                         AND micg.ITEM_CATALOG_GROUP_ID =
                                msi.ITEM_CATALOG_GROUP_ID
                         AND mde.ITEM_CATALOG_GROUP_ID =
                                micg.ITEM_CATALOG_GROUP_ID
                         AND mdev.element_name = mde.element_name
                         AND mdev.inventory_item_id = msi.inventory_item_id
                         AND mde.element_name IN ('Length',
                                                  'Angle Left',
                                                  'Angle Right',
                                                  'Wt Ht Flag',
                                                  'Fabrication Number',
                                                  'Panel Width',
                                                  'Panel Height',
                                                  'Glass Dim 1',
                                                  'Glass Dim 2',
                                                  'Dimension 1',
                                                  'Dimension 2',
                                                  'Dimension 3',
                                                  'Dimension 4',
                                                  'Dimension 5'))
                 PIVOT
                    (MAX (Element_value)
                    FOR (element_name)
                    IN ('Length' AS ITEM_LENGTH,
                       'Angle Left' AS ANGLE_LEFT,
                       'Angle Right' AS ANGLE_RIGHT,
                       'Wt Ht Flag' AS WT_HT_FLAG,
                       'Fabrication Number' AS FABRICATION_NUMBER,
                       'Panel Width' AS PANEL_WIDTH,
                       'Panel Height' AS PANEL_HEIGHT,
                       'Glass Dim 1' AS GLASS_DIM1,
                       'Glass Dim 2' AS GLASS_DIM2,
                       'Dimension 1' AS DIMENSION1,
                       'Dimension 2' AS DIMENSION2,
                       'Dimension 3' AS DIMENSION3,
                       'Dimension 4' AS DIMENSION4,
                       'Dimension 5' AS DIMENSION5))
        ORDER BY inventory_item_id) Item_attribute
 WHERE     1 = 1
       AND item_Attribute.inventory_item_id(+) = msil.INVENTORY_ITEM_ID
       AND BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
       AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_id
       AND bom.bill_sequence_id = bic.bill_sequence_id
       AND NVL (bic.disable_date, SYSDATE) >= SYSDATE
       AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
       AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
       AND mp.organization_id = msi.organization_id
       AND mp.organization_code = 'POR' -- Warehouse -- POR(Portland), ITM (Item Master), TAC (Tacoma), TEM (Temecula), CMP (Composites)
       AND micg.ITEM_CATALOG_GROUP_ID = msi.ITEM_CATALOG_GROUP_ID
       AND MSI.ORGANIZATION_ID =
              (SELECT DISTINCT OOL.SHIP_FROM_ORG_ID
                 FROM APPS.OE_ORDER_HEADERS_ALL OOH,
                      APPS.OE_ORDER_LINES_ALL OOL,
                      APPS.MTL_SYSTEM_ITEMS MSI1
                WHERE     OOH.ORDER_NUMBER = '1483' --Oracle Sales Order Number
                      AND OOL.LINE_NUMBER = '1' -- Oracle Sales Order Line Number 
                      AND OOL.HEADER_ID = OOH.HEADER_ID
                      AND MSI1.SEGMENT1 = OOL.ORDERED_ITEM
                      --AND OOL.ORDERED_ITEM LIKE '%*%'
                      AND MSI1.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID)
CONNECT BY PRIOR BIC.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
START WITH MSI.INVENTORY_ITEM_ID IN
              (SELECT MSI1.INVENTORY_ITEM_ID
                 FROM APPS.OE_ORDER_HEADERS_ALL OOH,
                      APPS.OE_ORDER_LINES_ALL OOL,
                      APPS.MTL_SYSTEM_ITEMS MSI1
                WHERE     OOH.ORDER_NUMBER = '1483' --Oracle Sales Order Number
                      AND OOL.LINE_NUMBER = '1' -- Oracle Sales Order Line Number 
                      AND OOL.HEADER_ID = OOH.HEADER_ID
                      AND MSI1.SEGMENT1 = OOL.ORDERED_ITEM
                      --AND OOL.ORDERED_ITEM like '%*%'
                      AND MSI1.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID)

And thoughts would be fantastic, I have tired multiple drivers and installs of Oracle, connection does not seem to be my issue, it is how the query is executed.

As asked for here is my connection code...

Private Const ConnectionString = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=string; uid=APPSRO; pwd=APPSRO;"

' ADO Connection Global Variable - Declared here so that, once the connection is opened, it can be used by any part of the code'
Public ADOConn As Object
Private OracleOrder As ADODB.Recordset
Private Const AppName = "Oracle BOM Data"
Private Const Version = "v1.0.0"
Public Const BoxTitle = AppName & " " & Version
Public Function ADOOpen(ByRef ADOConn As Object) As Boolean
' Open the ADO connection to the database and link it to the Object we've been passed'
' Handle errors here'
On Error Resume Next
' Clear the Error table'
Err.Clear
' Create a new connection and link it to the Object passed in'
Set ADOConn = CreateObject("ADODB.Connection")
' Now open the ADO connection to the database'
ADOConn.Open ConnectionString
'ADOConn.Properties("PLSQLRSet") = True'

' Now check for errors. Err.Number will be zero if there's been no error' since we cleared the Error table
If Err.Number = 0 Then
    ' So if there's been no error, return true'
    ADOOpen = True
Else
    ' Otherwise, return false'
    ADOOpen = False
End If
End Function

Ok, here is the actual function using the connections we know work...

Public Function LoadOracleOrder(OracleOrderNumber As Double, OracleLineNumber As Double)

Dim SQLString As String
Dim TargetSheet As Worksheet
Dim TargetCell As Range
Dim SQLQuery As String

On Error Resume Next

SQLString = "MONSTROUS QUERY HERE"
Debug.Print SQLString

Set OracleOrder = ADOFetch(SQLQuery, ADOConn)
If Err.Number <> 0 Then
    WarnMsgBox "Warning: Query failed." & Chr(10) & "Error No: " & Err.Number & Chr(10) & "Error Description: " & Err.Description, BoxTitle
    Err.Clear
ElseIf OracleOrder.RecordCount = 0 Then
    InfoMsgBox "Query Returned No Results.", BoxTitle
Else
    Set TargetSheet = Nothing
    Set TargetSheet = ThisWorkbook.Sheets("Oracle Order Data")
    If TargetSheet Is Nothing Then
        Set TargetSheet = ThisWorkbook.Sheets.Add
        TargetSheet.Name = "Oracle Order Data"
    End If
    TargetSheet.Cells.ClearContents
    TargetSheet.Range("A1") = "LEVEL"
    TargetSheet.Range("B1") = "ITEM"
    TargetSheet.Range("C1") = "DESC_COMPONENT"
    TargetSheet.Range("D1") = "ITEM_TYPE"
    TargetSheet.Range("E1") = "SUPPLY_TYPE"
    TargetSheet.Range("F1") = "UOM"
    TargetSheet.Range("G1") = "COMPONENT_QUANTITY"
    TargetSheet.Range("H1") = "ITEM_LENGTH"
    TargetSheet.Range("I1") = "ANGLE_LEFT"
    TargetSheet.Range("J1") = "ANGLE_RIGHT"
    TargetSheet.Range("K1") = "WT_HT_FLAG"
    TargetSheet.Range("L1") = "FABRICATION_NUMBER"
    TargetSheet.Range("M1") = "GLASS_DIM1"
    TargetSheet.Range("N1") = "GLASS_DIM2"
    TargetSheet.Range("O1") = "DIMENSION1"
    TargetSheet.Range("P1") = "DIMENSION2"
    TargetSheet.Range("Q1") = "DIMENSION3"
    TargetSheet.Range("R1") = "DIMENSION4"
    TargetSheet.Range("S1") = "DIMENSION5"


    Set TargetCell = TargetSheet.Range("A2")
    OracleOrder.MoveFirst
    While Not OracleOrder.EOF
        TargetCell.Value = OracleOrder.Fields(0).Value
        TargetCell.Offset(0, 1).Value = OracleOrder.Fields(1).Value
        TargetCell.Offset(0, 2).Value = OracleOrder.Fields(2).Value
        TargetCell.Offset(0, 3).Value = OracleOrder.Fields(3).Value
        TargetCell.Offset(0, 4).Value = OracleOrder.Fields(4).Value
        TargetCell.Offset(0, 5).Value = OracleOrder.Fields(5).Value
        TargetCell.Offset(0, 6).Value = OracleOrder.Fields(6).Value
        TargetCell.Offset(0, 7).Value = OracleOrder.Fields(7).Value
        TargetCell.Offset(0, 8).Value = OracleOrder.Fields(8).Value
        TargetCell.Offset(0, 9).Value = OracleOrder.Fields(9).Value
        TargetCell.Offset(0, 10).Value = OracleOrder.Fields(10).Value
        TargetCell.Offset(0, 11).Value = OracleOrder.Fields(11).Value
        TargetCell.Offset(0, 12).Value = OracleOrder.Fields(12).Value
        TargetCell.Offset(0, 13).Value = OracleOrder.Fields(13).Value
        TargetCell.Offset(0, 14).Value = OracleOrder.Fields(14).Value
        TargetCell.Offset(0, 15).Value = OracleOrder.Fields(15).Value
        TargetCell.Offset(0, 16).Value = OracleOrder.Fields(16).Value
        TargetCell.Offset(0, 17).Value = OracleOrder.Fields(17).Value
        TargetCell.Offset(0, 18).Value = OracleOrder.Fields(18).Value
        TargetCell.Offset(0, 19).Value = OracleOrder.Fields(19).Value
        Set TargetCell = TargetCell.Offset(1, 0)
        OracleOrder.MoveNext
    Wend

End If
On Error GoTo 0
End Function

ADO Fetch, as asked...

Public Function ADOFetch(SQLQuery As String, ByRef ADOConn As Object) As Object
' Takes an existing, open ADO connection and uses it to return a Recordset, Nothing if no data is returned, or False if there's an error
' Local Object for the Recordset
Dim ADORecSet As Object

' Handle errors internally
On Error Resume Next
' Clear the Error table
Err.Clear

' Set up the Recordset by executing the SQL query
Set ADORecSet = ADOConn.ExecuteSQL(SQLQuery)

' Now check for errors. Err.Number will be zero if there's been no error since we cleared the Error table
If Err.Number = 0 Then
    ' So if there's been no error, return the record set
    Set ADOFetch = ADORecSet
End If
' And either way, clear our temporary Recordset
ADORecSet = Nothing
On Error GoTo 0
End Function
Amarus Cameron
  • 3
  • 4
  • Can you verify the query works via ODBC? Toad does not use ODBC, so there is no guarantee your ODBC connection is good. I'd recommend MS Query, directly through Excel (on the data tab). Just paste in "select * from dual" and see if it returns results or has some other issue. Also, can you show your connection code in VBA? – Hambone Oct 09 '15 at 15:34
  • The only way I have verified that a query will be run through ODBC is that I have run shorter simpler ones. – Amarus Cameron Oct 09 '15 at 15:36
  • OKay, so your ODBC works -- that's good. Can you show the rest of your VBA code? You can omit the actual SQL and just use a variable named SQL, for brevity. By the way, you had 64-bit issues because your Excel version is probably 32-bit. – Hambone Oct 09 '15 at 15:38
  • Perhaps save the query with the join critiera as a view and pass the limits into the view thus simplifying the monster query from the ODBC perspective. – xQbert Oct 09 '15 at 15:38
  • I will ask if that is an option. I am not the Oracle dev, though I could write this myself I do not know the environment enough to call what would be needed. **Update- Views are not going to be an option for me – Amarus Cameron Oct 09 '15 at 15:40
  • Annnnnd the code is up – Amarus Cameron Oct 09 '15 at 15:48
  • The code actually looks like it renders the SQL properly. I know some ODBC drivers don't accept all keywords (`with` as an example), but your code doesn't appear to do anything that would normally cause ODBC problems. When you debug this, what line of code errors, and what is the error message? – Hambone Oct 09 '15 at 15:49
  • That is the thing, I don't get an error I get "Query Returned No Results" But I know that the parameters I am passing in have results to return and when run in the Oracle environment this returns 69 rows of data. – Amarus Cameron Oct 09 '15 at 15:51
  • What about `ADOFetch()` - that is likely where the problem is occuring... What happens if you remove the On Error Resume Next in `LoadOracleOrder` ? Also worth checking the Connection object's `Errors` collection: https://msdn.microsoft.com/en-us/library/ms675299(v=vs.85).aspx – Tim Williams Oct 09 '15 at 16:54
  • Added ADO Fetch, I am going to try removing the On Error Resume Next and I will see what that does. – Amarus Cameron Oct 09 '15 at 17:33
  • Just curious, have you simply tried copy the `Debug.Print SQLString`ed SQLString out of the immediate window into toad and run it there? – Axel Richter Oct 09 '15 at 17:57
  • Yes I did, had no issues. Turns out the issue was a call to create a worksheet and place the data in the project in which the macro was in (which is my personal file, not helpful) also taking out the On Error Resume Next allowed me to debug much more effectively I should've thought of that sooner. Thanks Tim! – Amarus Cameron Oct 09 '15 at 18:50

2 Answers2

0

Ok after running through issues that were overlooked due to my On Error Resume Next(thanks Tim), I found that I had a type mismatch, and I had a comma that should not have been in the sql (it had originally commented out but I must have changed it when appending the giant string).

Lastly, turns out there was a call to create a worksheet and then place the data in the project in which the macro was in (which is my personal file, not helpful.)

Thank you for your comments, and suggestions all of them helped me find the solution.

Amarus Cameron
  • 3
  • 4
0

While I understand you resolved your syntax issue, I thought I might suggest two approaches for best practices moving forward:

EXPLICIT JOIN

Use the current ANSI-syntax in joining tables in your query. Right now, your SQL is using the older, deprecated implicit join (keys being used in WHERE clause) versus the current ANSI-standard with the explicit join (keys used in INNER JOIN...ON). While this makes no difference in the query optimizer in terms of performance (see SO post), using INNER JOIN tends to be more readable and maintainable especially if you have various WHERE conditions to manage. Below is an attempt to translate your query with the INNER JOIN clause. Please test before use (notice WHERE is more readable):

SELECT LEVEL,
       MSIL.SEGMENT1 ITEM,
       MSIL.DESCRIPTION DESC_COMPONENT,
       BIC.ITEM_TYPE,
       BIC.SUPPLY_TYPE,
       MSIL.PRIMARY_UOM_CODE UOM,
       BIC.COMPONENT_QUANTITY,
       item_attribute.ITEM_LENGTH,
       item_attribute.ANGLE_LEFT,
       item_attribute.ANGLE_RIGHT,
       item_attribute.WT_HT_FLAG,
       item_attribute.FABRICATION_NUMBER,
       item_attribute.PANEL_WIDTH,
       item_attribute.PANEL_HEIGHT,
       item_attribute.GLASS_DIM1,
       item_attribute.GLASS_DIM2,
       item_attribute.DIMENSION1,
       item_attribute.DIMENSION2,
       item_attribute.DIMENSION3,
       item_attribute.DIMENSION4,
       item_attribute.DIMENSION5
  FROM APPS.MTL_SYSTEM_ITEMS MSI
  INNER JOIN APPS.BOM_BILL_OF_MATERIALS BOM
  INNER JOIN APPS.BOM_INVENTORY_COMPONENTS_V BIC
  INNER JOIN APPS.MTL_SYSTEM_ITEMS MSIL
  INNER JOIN APPS.mtl_parameters mp
  INNER JOIN APPS.MTL_ITEM_CATALOG_GROUPS_VL micg
  INNER JOIN
       (  SELECT *
            FROM (SELECT msi.inventory_item_id,
                         mde.element_name,
                         mdev.element_value
                    FROM APPS.mtl_parameters mp,
                    INNER JOIN APPS.mtl_system_items_b msi
                    INNER JOIN APPS.MTL_ITEM_CATALOG_GROUPS_VL micg
                    INNER JOIN APPS.MTL_DESCRIPTIVE_ELEMENTS mde
                    INNER JOIN APPS.MTL_DESCR_ELEMENT_VALUES mdev

                    ON mp.organization_id = msi.organization_id AND
                    ON micg.ITEM_CATALOG_GROUP_ID = msi.ITEM_CATALOG_GROUP_ID AND
                    ON mde.ITEM_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID AND
                    ON mdev.element_name = mde.element_name AND
                    ON mdev.inventory_item_id = msi.inventory_item_id

                   WHERE mp.organization_code = 'POR' -- Warehouse -- POR(Portland), ITM (Item Master), TAC (Tacoma), TEM (Temecula), CMP (Composites)
                         AND mde.element_name IN ('Length',
                                                  'Angle Left',
                                                  'Angle Right',
                                                  'Wt Ht Flag',
                                                  'Fabrication Number',
                                                  'Panel Width',
                                                  'Panel Height',
                                                  'Glass Dim 1',
                                                  'Glass Dim 2',
                                                  'Dimension 1',
                                                  'Dimension 2',
                                                  'Dimension 3',
                                                  'Dimension 4',
                                                  'Dimension 5'))
                 PIVOT
                    (MAX (Element_value)
                    FOR (element_name)
                    IN ('Length' AS ITEM_LENGTH,
                       'Angle Left' AS ANGLE_LEFT,
                       'Angle Right' AS ANGLE_RIGHT,
                       'Wt Ht Flag' AS WT_HT_FLAG,
                       'Fabrication Number' AS FABRICATION_NUMBER,
                       'Panel Width' AS PANEL_WIDTH,
                       'Panel Height' AS PANEL_HEIGHT,
                       'Glass Dim 1' AS GLASS_DIM1,
                       'Glass Dim 2' AS GLASS_DIM2,
                       'Dimension 1' AS DIMENSION1,
                       'Dimension 2' AS DIMENSION2,
                       'Dimension 3' AS DIMENSION3,
                       'Dimension 4' AS DIMENSION4,
                       'Dimension 5' AS DIMENSION5))
        ORDER BY inventory_item_id) Item_attribute

 ON item_Attribute.inventory_item_id(+) = msil.INVENTORY_ITEM_ID AND
 ON BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND
 ON BOM.ORGANIZATION_ID = MSI.ORGANIZATION_id AND
 ON bom.bill_sequence_id = bic.bill_sequence_id AND
 ON BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID AND
 ON Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID AND
 ON mp.organization_id = msi.organization_id AND
 ON micg.ITEM_CATALOG_GROUP_ID = msi.ITEM_CATALOG_GROUP_ID

 WHERE 1 = 1
       AND NVL (bic.disable_date, SYSDATE) >= SYSDATE
       AND mp.organization_code = 'POR' -- Warehouse -- POR(Portland), ITM (Item Master), TAC (Tacoma), TEM (Temecula), CMP (Composites)       
       AND MSI.ORGANIZATION_ID =
              (SELECT DISTINCT OOL.SHIP_FROM_ORG_ID
                       FROM APPS.OE_ORDER_HEADERS_ALL OOH
                 INNER JOIN APPS.OE_ORDER_LINES_ALL OOL
                 INNER JOIN APPS.MTL_SYSTEM_ITEMS MSI1
                         ON OOL.HEADER_ID = OOH.HEADER_ID AND
                         ON MSI1.SEGMENT1 = OOL.ORDERED_ITEM AND
                         ON MSI1.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID

                 WHERE  OOH.ORDER_NUMBER = '1483' --Oracle Sales Order Number
                        AND OOL.LINE_NUMBER = '1' -- Oracle Sales Order Line Number 
                      --AND OOL.ORDERED_ITEM LIKE '%*%'
                      )
CONNECT BY PRIOR BIC.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
START WITH MSI.INVENTORY_ITEM_ID IN
              (SELECT MSI1.INVENTORY_ITEM_ID
                       FROM APPS.OE_ORDER_HEADERS_ALL OOH
                 INNER JOIN APPS.OE_ORDER_LINES_ALL OOL
                 INNER JOIN APPS.MTL_SYSTEM_ITEMS MSI1                 
                         ON OOL.HEADER_ID = OOH.HEADER_ID AND
                         ON MSI1.SEGMENT1 = OOL.ORDERED_ITEM AND 
                         ON MSI1.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID

                 WHERE OOH.ORDER_NUMBER = '1483' --Oracle Sales Order Number
                      AND OOL.LINE_NUMBER = '1' -- Oracle Sales Order Line Number                       
                      --AND OOL.ORDERED_ITEM like '%*%'
                      )

EXTERNAL .SQL FILE

Because VBA does not allow strings across multiple lines without underscores and ampersands, consider avoiding the need for a long concatenation of the "giant" VBA string. Instead, read in an external .sql file into the VBA string.

This will allow effortless change of the SQL query as you only need to copy/paste from your database console into the .sql text file and your Excel macro will read in the latest version without worrying about adjusting commas, underscores, and ampersands. Save below script in a separate function and in your query function, LoadOracleOrder(), simply call it as: SQLString = ReadSQL():

Function ReadSQL() As String
On Error GoTo ErrHandle
    Dim objFSO As Object, ofile As Object
    Dim sqlFile As String, strSQL As String, dataLine As String
    Dim readFile As Integer, i As Integer
    Const ForReading = 1

    ' INITIALIZE OBJECTS
    sqlFile = ActiveWorkbook.Path & "\OracleQuery.sql"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ofile = objFSO.OpenTextFile(sqlFile, ForReading)

    ' OPEN FILE, READ LINES, CLOSE FILE
    readFile = FreeFile()
    Open sqlFile For Input As #readFile

    strSQL = ""
    Do While ofile.AtEndofStream <> True
        strSQL = strSQL & ofile.ReadLine & vbNewLine
    Loop

    ReadSQL = strSQL
    Close #readFile
    ofile.Close   

    Set ofile = Nothing
    Set objFSO = Nothing
    Exit Function

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Set ofile = Nothing
    Set objFSO = Nothing
    Exit Function

End Function
Community
  • 1
  • 1
Parfait
  • 104,375
  • 17
  • 94
  • 125