From time to time we want to look up cross references in Oracle Manufacturing, but we have so many types of references
to so many Items, and the application can be so slow. This query lets users specify New (Oracle)  Items, Old Items, creation or update date--or it defaults to 'All Cross References'. It spools output to a CSV file.

The user then opens the Excel spreadsheet manually. It has an Auto_Open macro that is just looking for the spool file (name
is hard-coded, so the spool file name in the query is vital).  Excel opens the text spool file, formats it for viewing, and displays messages to the user. Then the auto-open file closes automatically, too, leaving only 'the data' on screen in Excel. The information in Excel is much easier to read more quickly, sort as required, and print. (By itself, the query simply generates the CSV file, and the user can do what he wants with that afterward, of course. You don't HAVE to use the Excel sheet.)

References to the J:\ drive in the script and Excel file are to access a common drive that we share between European and
American users in this company. You'd probably want to change that, but you would have to do the same in the SQL query AND    in the spreadsheet file. To open the spreadsheet for editing, hold down the shift key while the file is selected to be
opened. Sheet protection is 'on' with no password, and the macro sheet is 'hidden', also easy to find and open with
simple Excel commands.

  SET heading OFF serveroutput ON pagesize 15000 buffer 150
  SET linesize 150 echo OFF verify OFF serveroutput ON

  SELECT DISTINCT cross_reference_type
  FROM inv.mtl_cross_references
/

  ACCEPT v_type PROMPT 'Search a C. R. Type from the list (or default "ALL"): '
  ACCEPT v_seg1 PROMPT 'Search for Particular NEW Item (default "ALL"): '
  ACCEPT v_item PROMPT 'Search for Particular OLD Item (default "ALL"): '
  ACCEPT v_crea PROMPT 'Created since (default "ALL"; Or enter dd-MMM-yyyy): '
  ACCEPT v_upda PROMPT 'Updated since (default "ALL"; Or enter dd-MMM-yyyy): '

  SPOOL J:\Queries\Cross_Ref.LST;
  --Capture output to specific file that Excel will look for later.
  --You may want to spool to C:\Path\Path\filename.

  SELECT DISTINCT a.cross_reference_type ||','||
  b.segment1 ||','||
  a.cross_reference ||','||
  a.description ||','||
  TO_CHAR( a.organization_id) ||','||
  TO_CHAR( a.creation_date) ||','||
  DECODE( TRUNC( a.last_update_date - a.creation_date),
  0, ' -',
  TO_CHAR( a.last_update_date)) ||','||
  c.name || '--' ||
  RTRIM( TO_CHAR( sysdate, 'fmDD-Mon-YYYY "at" fmHH:MIam "GMT"')) ||','
  --Concatenated comma-separated values for export to Excel CSV file.
  --Be sure to keep the trailing comma for the final column.
  FROM inv.mtl_cross_references a,
  inv.mtl_system_items b,
  v$database c
  WHERE a.cross_reference_type LIKE UPPER( NVL( '%&v_type%', '%'))
  AND b.segment1 LIKE UPPER( NVL( '%&v_seg1%', '%'))
  AND a.cross_reference LIKE UPPER( NVL( '%&v_item%', '%'))
  AND a.creation_date >= UPPER( TO_DATE( NVL( '&v_crea', '01-JAN-1950'),
  'dd-Mon-yyyy'))
  AND a.last_update_date >= UPPER( TO_DATE( NVL( '&v_upda', '01-JAN-1950'),
  'dd-Mon-yyyy'))
  AND a.inventory_item_id = b.inventory_item_id
  --'Limited Inclusion' variables added 26-Feb-99.
  ORDER BY a.cross_reference_type ||','||
  b.segment1 ||','||
  a.cross_reference ||','||
  a.description ||','||
  TO_CHAR( a.organization_id) ||','||
  TO_CHAR( a.creation_date) ||','||
  DECODE( TRUNC( a.last_update_date - a.creation_date),
  0, ' -',
  TO_CHAR( a.last_update_date)) ||','||
  c.name || '--' ||
  RTRIM( TO_CHAR( sysdate, 'fmDD-Mon-YYYY "at" fmHH:MIam "GMT"')) ||','

/
  SPOOL OFF

  SET linesize 60
  SELECT 'Now use Excel to open the file
  c:\windows\temp\crossref.xls',
  '(Really. Do it this very minute.)'
  FROM dual
/

  SET heading ON pagesize 23 linesize 150
 
 
 
 

'
' Open_New_Cross_Reference_List Macro
' Open whatever is the newest Cross Reference export from ORACLE (on J:\Queries).
'   by Chris Nelson, Jan/Feb 1999.  Eaton MAD / Oracle Implementation Team.
'
'
Sub Auto_Open()
'  This macro runs upon the opening of the spreadsheet, and opens the text file generated by
'  the SQL*Plus Query "Export_Cross_Ref.SQL".  (J:\Queries\Cross_Ref.LST)

    Workbooks.OpenText Filename:="J:\Queries\Cross_Ref.LST", _
        Origin:=xlWindows, StartRow:=2, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab _
        :=True, Semicolon:=False, Comma:=True, Space:=False, Other _
        :=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array( _
        4, 2), Array(5, 2), Array(6, 3), Array(7, 3), Array(8, 2))
    Range("A1").EntireRow.Insert            ' Set column headings
    Range("A1").Formula = "C.R. Type"
    Range("B1").Formula = "ORACLE Item"
    Range("C1").Formula = "Value"
    Range("D1").Formula = "Description"
    Range("E1").Formula = "Org"
    Range("F1").Formula = "Created"
    Range("G1").Formula = "Updated"
    Range("H1").Formula = "Query Date"
    Range("I:I").EntireColumn.Delete        ' This column has extra 'spaces' from the import.

    Range("A1:H1").Select                   ' Format the column headers
    Selection.Font.Bold = True
    Range("A1: H" & Range("A1").Offset.End(xlDown).Row).Select

    Selection.Columns.AutoFit               ' Adjust column widths

    ActiveCell.SpecialCells(xlLastCell).Select
    Selection.EntireRow.Delete              ' Remove "rows selected"
    Range("A2").Select
    ActiveWindow.FreezePanes = True         ' Set Windows 'panes'.

' Messages to user(s).
    MsgBox "This is the most recent Cross Reference query from ORACLE." & Chr(10) & _
        "If you want to save this file, please 'SAVE AS' another name." & Chr(10) & Chr(10) & _
        "PLEASE NOTE that this MAY NOT BE an all-inclusive Cross-Reference Listing." & Chr(10) & _
        "More comments in J:\SQL\Export_Cross_Ref.SQL."

    MsgBox "Query Date and Time(when this list was exported) is shown" & Chr(10) & _
        "in the rightmost column of the spreadsheet." & Chr(10) & Chr(10) & _
        "Be aware that this is NOT a 'dynamic' query, and that data may have" & Chr(10) & _
        "changed since then.  The 'latest and greatest' data is in ORACLE."
    Windows("Open_Cross_Refs.XLS").Close
End Sub

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips