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
Return to : Oracle
Database, SQL, Application, Programming Tips