Monday, 23 November 2015

sql*plus program for providing Excel output through sql file


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'

;