sql*plus program for providing Excel,Notepad,HTML output through sql file with correct format.
The below code is used to get the excel, notepad, html output in correct column wise.
Here used A8, A12 these are width of the column specified for characters.
The profile option enabled for excel, notepad, html then the below code will display output good :)
/* Formatted on 11/23/2015 5:45:53 PM (QP5 v5.227.12220.39724) */
/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Executable: REPORT.sql
Description:
Object Name: HISTORY_REPORT.sql
- - - - - - - -
W o r k L o g
- - - - - - - -
Work Work Date
Case dd-Mon-yyyy Resource Version Description of Work...
------ ----------- ---------- ------- ------------------------------------------
ADTi-1700 23-NOV-2015 X 1 Object genesis.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
set echo off
set VERIFY off
set markup html off spool off
set FEEDBACK off
set trimspool on
clear columns
clear breaks
clear buffer
clear COMPUTES
set HEADING on
set PAGESIZE 50000
set LINESIZE 500
set newpage 0
set TERMOUT off
-- Misc. Variables Declaration.
set feed off markup html on spool on ;
TTITLE -
Center 'HART AND COOLEY, INC.' -
Skip 1 -
Left 'Run Date: ' _DATE -
Center 'AD JQ/SA History Report' -
Skip 1 -
-- Column Headings
Column SA# FORMAT a8 heading 'SA#' justify center
Column SA_NAME FORMAT a12 heading 'SA_NAME' justify center
Column VERSION_REVISIONS FORMAT a3 heading 'VERSION_REVISIONS' justify center
Column CUSTOMER_NUMBER FORMAT a10 heading 'CUSTOMER_NUMBER' justify center
Column CUSTOMER FORMAT a30 heading 'CUSTOMER' justify left
Column SHIP_TO_ADDRESS1 FORMAT a30 heading 'SHIP_TO_ADDRESS1' justify left
Column SHIP_TO_NUMBER FORMAT a20 heading 'SHIP_TO_NUMBER' justify center
Column SALES_CHANNEL FORMAT a15 heading 'SALES_CHANNEL' justify center
Column ACTIVATION_DATE FORMAT a15 heading 'ACTIVATION_DATE' justify center
Column EXPIRATION_DATE FORMAT a15 heading 'EXPIRATION_DATE' justify center
Column FULFILLED_$ FORMAT a15 heading 'FULFILLED_$' justify center
Column SALESPERSON FORMAT a30 heading 'SALESPERSON' justify left
Column SALESREP_ID FORMAT a15 heading 'SALESREP_ID' justify center
Column STATUS FORMAT a15 heading 'STATUS' justify center
Column CSR FORMAT a20 heading 'CSR' justify center
Column JOB_NAME FORMAT a20 heading 'JOB_NAME' justify left
Column CONTRACTOR_NAME FORMAT a20 heading 'CONTRACTOR_NAME' justify left
-- Mainline query.
SELECT bha.order_number "SA #",
bha.sales_document_name "SA Name",
bha.version_number "Version#/Revisions",
hca.account_number "Customer Number",
hca.account_name "Customer",
loc.address1 "Ship To Address1",
loc.location_id "Ship to #",
hca.sales_channel_code "Sales Channel",
bhe.start_date_active "Activation Date",
bhe.end_date_active "Expiration Date",
bhe.fulfilled_amount "Fulfilled $",
jrd.resource_name "Salesperson",
to_char(bha.salesrep_id) "Salesrep id",
bha.flow_status_code "Status",
bha.attribute1 "CSR",
bha.attribute2 "Job Name",
bha.attribute3 "Contractor Name"
FROM apps.oe_blanket_headers_all bha
JOIN apps.oe_blanket_headers_ext bhe
ON bha.order_number = bhe.order_number
JOIN apps.hz_cust_accounts hca
ON bha.sold_to_org_id = hca.cust_account_id
LEFT OUTER JOIN apps.hz_cust_site_uses_all hcsu
ON bha.ship_to_org_id = hcsu.site_use_id
LEFT OUTER JOIN apps.hz_locations loc
ON hcsu.location = loc.location_id
LEFT OUTER JOIN apps.jtf_rs_salesreps jrs
ON bha.salesrep_id = jrs.salesrep_id
LEFT OUTER JOIN apps.jtf_rs_defresources_v jrd
ON jrs.resource_id = jrd.resource_id
WHERE hca.sales_channel_code <> 'INTERCOMPANY'
AND bha.flow_status_code <> 'TERMINATED'
ORDER BY bha.order_number;
-- Script wind-up.
spool off;
set markup html off;
/
2) Another code to bring proper excel file with white background
Excel o/p through sql query::
SET TRIMSPOOL ON
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET HEADING ON ;
SET PAGESIZE 50000 ;
SET LINESIZE 1500 ;
SET FEEDBACK OFF
Set VERIFY Off
set echo off
--SET TERMOUT OFF
set feed off markup html on spool on ;
--spool c:\test4.xls ;
TTITLE -
Center 'Company
Name' -
Skip 1 -
Left 'Run Date: ' _DATE -
Center 'Report
Name' -
Skip 1 -
-- Column Headings
COLUMN ITEM# FORMAT A45
WRAP -
HEADING 'Item Number'
COLUMN DESCRIPTION
FORMAT A45 WRAP -
HEADING 'Description' JUSTIFY CENTER
COLUMN ITEM_HEIGHT
FORMAT A15 WRAP -
HEADING 'Item Height'
COLUMN ITEM_LENGTH
FORMAT A15 -
HEADING 'Item Length' JUSTIFY CENTER
COLUMN ITEM_WEIGHT
FORMAT A15 -
HEADING 'Item Weight' JUSTIFY CENTER
COLUMN ITEM_WIDTH
FORMAT A15 WRAP -
HEADING 'ITEM_WIDTH' JUSTIFY CENTER
COLUMN PACK_QTY FORMAT
A8 WRAP -
HEADING 'Pack Qty' JUSTIFY LEFT
COLUMN CARTON_HEIGHT
FORMAT A15 WRAP -
HEADING 'Carton Height'
COLUMN CARTON_LENGTH
FORMAT A15 -
HEADING 'Carton Length' JUSTIFY CENTER
COLUMN CARTON_WEIGHT
FORMAT A15 -
HEADING 'Carton Weight' JUSTIFY CENTER
COLUMN HTS_CODE FORMAT
A20 -
HEADING 'HTS Code(Mst Level)' JUSTIFY CENTER
COLUMN GTIN FORMAT A25
WRAP -
HEADING 'GTIN' JUSTIFY CENTER
COLUMN i2of5 FORMAT A25
-
HEADING 'i2of5' JUSTIFY CENTER
COLUMN MUPC FORMAT A25
WRAP -
HEADING 'MUPC' JUSTIFY CENTER
COLUMN BRAND FORMAT A15
WRAP -
HEADING 'BRAND' JUSTIFY LEFT
COLUMN INSTANCE FORMAT A15 WRAP -
HEADING 'Instance'
COLUMN RUN_DATE FORMAT DATE -
HEADING 'Run Date'
-- *************************** Start
Selection *********************************
SELECT
substr('="' || to_char(mt.segment1) || '"', 1, 45) As "ITEM#",
substr(mt.Description, 1, 45) As "DESCRIPTION",
substr(mt.unit_height, 1, 15) As "ITEM_HEIGHT",
substr(mt.unit_length, 1, 15) As "ITEM_LENGTH",
substr(mt.unit_weight, 1, 15) As "ITEM_WEIGHT",
substr(mt.unit_width, 1, 15) AS "ITEM_WIDTH",
Substr(uo.conversion_rate, 1, 8) as "PACK_QTY",
substr(uo.height, 1, 15) As "CARTON_HEIGHT",
substr(uo.length, 1, 15) as "CARTON_LENGTH",
substr(uo.width, 1, 15) as "CARTON_WIDTH",
substr(mt.attribute8, 1, 20) as "HTS_CODE",
-- Cross Ref Values
substr(CASE (select Count(*) from apps.mtl_cross_references_v where mt.inventory_item_id =inventory_item_id and
cross_reference_type='GTIN')
WHEN 1 then (select cross_reference from apps.mtl_cross_references_v where mt.inventory_item_id =inventory_item_id and
cross_reference_type='GTIN')
ELSE 'Mult. entries or not defn'
END, 1, 25) As GTIN,
--
To_Char(substr(CASE (select Count(*) from apps.mtl_cross_references_v
where mt.inventory_item_id
=inventory_item_id and cross_reference_type='i2of5')
WHEN 1 then (select '''' || cross_reference from apps.mtl_cross_references_v where mt.inventory_item_id =inventory_item_id and
cross_reference_type='i2of5')
ELSE 'Mult. entries or not defn'
END, 1, 25)) As i2of5,
--
substr(CASE (select Count(*) from apps.mtl_cross_references_v where mt.inventory_item_id =inventory_item_id and
cross_reference_type='MUPC')
WHEN 1 then (select cross_reference from apps.mtl_cross_references_v where mt.inventory_item_id =inventory_item_id and
cross_reference_type='MUPC')
ELSE 'Mult. entries or not defn'
END, 1, 25) As MUPC,
--
substr(it.segment1, 1, 15) As "BRAND",
-- Finger Print
substr((select Name From apps.Fnd_Apps_System), 1, 15) As "INSTANCE",
to_char(Current_Timestamp ,'YYYY-MON-dd') AS "RUN_DATE"
--4 tables to pull from
from (SELECT X.*
FROM apps.MTL_SYSTEM_ITEMS_b X
, apps.MTL_ITEM_ORG_CATEG_V mioc
, apps.mtl_categories mc
WHERE 7=7
AND X.organization_id = '154'
and (mioc.Inventory_Item_Id = X.Inventory_Item_Id and
X.ORGANIZATION_ID = mioc.ORGANIZATION_ID AND
-- MT216621 Active items Only
X.Inventory_item_status_code = 'Active' AND
mioc.category_id = mc.CATEGORY_ID and
mioc.structure_id = mc.STRUCTURE_ID and
mioc.Category_set = 'Inventory' )
AND mc.Segment1 = 'FG') MT -- FG ONLY
,(SELECT * FROM apps.mtl_uom_conversions WHERE
(TRUNC(disable_date) >= TRUNC(SYSDATE) OR disable_date IS NULL)
AND uom_code = 'Ctn')uo
,apps.mtl_item_categories_v it
--
where mt.inventory_item_id = uo.inventory_item_id (+)
and mt.inventory_item_id =it.inventory_item_id (+)
and mt.organization_id = it.organization_id
---and mt.segment1 IN( '206100','74024','2000472','651276','651332','651333')
-- Carton dimensions Only
AND it.category_set_name='BRAND_CATEGORY'
;