plsql- plsql stored procedure submitting rdf and xml bursting program with timing intervals

plsql stored procedure submitting rdf and xml bursting program with timing intervals

A main program say Email ASN is an stored procedure upon submitting this program it will submit an rdf program with bursting xml program to receive email

Package Specification:

CREATE OR REPLACE PACKAGE <pkg name> AUTHID CURRENT_USER AS
  -- -----------------------------------------------------------------------------
  --                       
  -- -----------------------------------------------------------------------------
  --
  --  Program Name     :  XX01.sql
  --  Description      :  This package is used to send ASN in Email
  --
  --  Package Name     : 
  --  Change Record:
  -- -----------------------------------------------------------------------------
  --  Version   Date         Author           Remarks
  --  
  -- -----------------------------------------------------------------------------
  PROCEDURE delivery_asn_email_main(x_errbuf           OUT NOCOPY VARCHAR2
                                   ,x_retcode          OUT NOCOPY NUMBER
                                   ,x_burst_request_id OUT NOCOPY NUMBER);
  PROCEDURE asn_email_main(x_errbuf         OUT NOCOPY VARCHAR2
                          ,x_retcode        OUT NOCOPY NUMBER
                          ,p_ou             IN NUMBER
                          ,p_order_num_from IN NUMBER
                          ,p_order_num_to   IN NUMBER
                          ,p_ship_date_from IN VARCHAR2
                          ,p_ship_date_to   IN VARCHAR2
                          ,p_delivery_from  IN NUMBER
                          ,p_delivery_to    IN NUMBER);
END xx01_asn_email_pkg;
/


Package Body:

CREATE OR REPLACE PACKAGE BODY <pkg name> AS
  -- -----------------------------------------------------------------------------
  --                         
  -- -----------------------------------------------------------------------------
  --
  --  Program Name     :  XX01.sql
  --  Description      :  This package is used to send custom faxes with rightfax
  --
  --  Package Name     :  XX01
  --
  --  Change Record:
  -- ---------------------------------------------------------------------------------------------------
  --  Version   Date         Author           Remarks

  -- ---------------------------------------------------------------------------------------------------
  g_conc_req_id VARCHAR2(150) := to_char(fnd_global.conc_request_id);

  PROCEDURE delivery_asn_email_main(x_errbuf           OUT NOCOPY VARCHAR2
                                   ,x_retcode          OUT NOCOPY NUMBER
                                   ,x_burst_request_id OUT NOCOPY NUMBER) IS
    l_req_wait1         BOOLEAN;
    l_req_wait2         BOOLEAN;
    l_req_wait3         BOOLEAN;
    l_phase_code        VARCHAR2(100);
    l_phase             VARCHAR2(100);
    l_dev_status        VARCHAR2(100);
    l_status_code       VARCHAR2(100);
    l_msg               VARCHAR2(100);
    l_burst_req_id      fnd_concurrent_requests.request_id%TYPE; --Variable to hold "XML Publisher Report Bursting Program" Request Id
    l_req_id            fnd_concurrent_requests.request_id%TYPE; --Variable to hold "TBP Pack List Report - Email" Request Id
    l_burst_status_code VARCHAR2(100);
    l_burst_phase_code  VARCHAR2(100);
    l_burst_phase       VARCHAR2(100);
    l_burst_dev_status  VARCHAR2(100);
    l_burst_msg         VARCHAR2(100);
    l_burst_src_file    VARCHAR2(500);
    l_retcode           NUMBER := 0;
    l_errbuf            VARCHAR2(100);
    -- -------------------------------------
    xml_layout       BOOLEAN;
    l_org_id         NUMBER := 0;
    l_set_of_book_id NUMBER;
    -- -------------------------------------
  BEGIN
    fnd_file.put_line(fnd_file.log
                     ,'---------------------------------------------------------------');
    -- call the ADD_LAYOUT API prior to submitting the Printed Purchase Order Report
    xml_layout := fnd_request.add_layout(template_appl_name => 'XX01'
                                        ,template_code      => 'XX01_WSHRDPACK_EMAIL'
                                        ,template_language  => 'en'
                                        ,template_territory => 'US'
                                        ,output_format      => 'PDF');
    --l_org_id := fnd_global.org_id;
    --FND_FILE.PUT_LINE(FND_FILE.LOG,'  org_id: '||l_org_id);
    --fnd_request.set_org_id(l_org_id);
    --Submit the TBP Pack List Report
    -- Case # 130929 - removed parameter to accomodate change in Pack List Report Program change
    fnd_file.put_line(fnd_file.log, 'Calling Pack List Report - Email');
    l_req_id := fnd_request.submit_request('XX01'
                                          ,'XX01_WSHRDPACK_EMAIL'
                                          ,NULL
                                          ,NULL
                                          ,FALSE
                                          ,NULL
                                          ,NULL
                                          , -- Passing NULL to delivery_id
                                           NULL
                                          ,NULL
                                          ,'MSTK'
                                          ,'MTLL'
                                          ,g_conc_req_id);
    COMMIT;
    --Wait for the Program Completion
    IF l_req_id > 0
    THEN
      l_phase := 'XX';
      WHILE (upper(l_phase) <> 'COMPLETE')
      LOOP
        l_req_wait1 := fnd_concurrent.wait_for_request(request_id => l_req_id
                                                      ,INTERVAL   => 2
                                                      ,max_wait   => 0
                                                      ,phase      => l_phase_code
                                                      ,status     => l_status_code
                                                      ,dev_phase  => l_phase
                                                      ,dev_status => l_dev_status
                                                      ,message    => l_msg);
        EXIT WHEN upper(l_phase) = 'COMPLETE';
      END LOOP;
    END IF; -- l_req_id > 0
    IF (upper(l_status_code) = 'NORMAL')
    THEN
      --Case# 151266: Added Profile option check before emailing
      IF nvl(fnd_profile.value_specific(NAME              => 'XX01_ENABLE_BURSTING_EMAIL'
                                       ,user_id           => fnd_global.user_id
                                       ,responsibility_id => fnd_global.resp_id
                                       ,application_id    => fnd_global.resp_appl_id
                                       ,org_id            => fnd_global.org_id
                                       ,server_id         => fnd_global.server_id)
            ,'N') = 'Y'
      THEN
        --Submit the XX01 SALES ORDER ACK CONTROL FILE to generate the control file for rightfax
        fnd_file.put_line(fnd_file.log, 'Calling XML Publisher Report Bursting Program');
        fnd_file.put_line(fnd_file.log, '   Reqest ID: ' || l_req_id);
        l_burst_req_id := fnd_request.submit_request(application => 'XDO'
                                                    ,program     => 'XDOBURSTREP'
                                                    ,description => NULL
                                                    ,start_time  => NULL
                                                    ,sub_request => FALSE
                                                    ,argument1   => 'Y'
                                                    ,argument2   => l_req_id
                                                    ,argument3   => 'Y'
                                                    ,argument4   => chr(0)
                                                    ,argument5   => chr(0)
                                                    ,argument6   => chr(0)
                                                    ,argument7   => chr(0)
                                                    ,argument8   => chr(0)
                                                    ,argument9   => chr(0)
                                                    ,argument10  => chr(0)
                                                    ,argument11  => chr(0)
                                                    ,argument12  => chr(0)
                                                    ,argument13  => chr(0)
                                                    ,argument14  => chr(0)
                                                    ,argument15  => chr(0)
                                                    ,argument16  => chr(0)
                                                    ,argument17  => chr(0)
                                                    ,argument18  => chr(0)
                                                    ,argument19  => chr(0)
                                                    ,argument20  => chr(0)
                                                    ,argument21  => chr(0)
                                                    ,argument22  => chr(0)
                                                    ,argument23  => chr(0)
                                                    ,argument24  => chr(0)
                                                    ,argument25  => chr(0)
                                                    ,argument26  => chr(0)
                                                    ,argument27  => chr(0)
                                                    ,argument28  => chr(0)
                                                    ,argument29  => chr(0)
                                                    ,argument30  => chr(0)
                                                    ,argument31  => chr(0)
                                                    ,argument32  => chr(0)
                                                    ,argument33  => chr(0)
                                                    ,argument34  => chr(0)
                                                    ,argument35  => chr(0)
                                                    ,argument36  => chr(0)
                                                    ,argument37  => chr(0)
                                                    ,argument38  => chr(0)
                                                    ,argument39  => chr(0)
                                                    ,argument40  => chr(0)
                                                    ,argument41  => chr(0)
                                                    ,argument42  => chr(0)
                                                    ,argument43  => chr(0)
                                                    ,argument44  => chr(0)
                                                    ,argument45  => chr(0)
                                                    ,argument46  => chr(0)
                                                    ,argument47  => chr(0)
                                                    ,argument48  => chr(0)
                                                    ,argument49  => chr(0)
                                                    ,argument50  => chr(0)
                                                    ,argument51  => chr(0)
                                                    ,argument52  => chr(0)
                                                    ,argument53  => chr(0)
                                                    ,argument54  => chr(0)
                                                    ,argument55  => chr(0)
                                                    ,argument56  => chr(0)
                                                    ,argument57  => chr(0)
                                                    ,argument58  => chr(0)
                                                    ,argument59  => chr(0)
                                                    ,argument60  => chr(0)
                                                    ,argument61  => chr(0)
                                                    ,argument62  => chr(0)
                                                    ,argument63  => chr(0)
                                                    ,argument64  => chr(0)
                                                    ,argument65  => chr(0)
                                                    ,argument66  => chr(0)
                                                    ,argument67  => chr(0)
                                                    ,argument68  => chr(0)
                                                    ,argument69  => chr(0)
                                                    ,argument70  => chr(0)
                                                    ,argument71  => chr(0)
                                                    ,argument72  => chr(0)
                                                    ,argument73  => chr(0)
                                                    ,argument74  => chr(0)
                                                    ,argument75  => chr(0)
                                                    ,argument76  => chr(0)
                                                    ,argument77  => chr(0)
                                                    ,argument78  => chr(0)
                                                    ,argument79  => chr(0)
                                                    ,argument80  => chr(0)
                                                    ,argument81  => chr(0)
                                                    ,argument82  => chr(0)
                                                    ,argument83  => chr(0)
                                                    ,argument84  => chr(0)
                                                    ,argument85  => chr(0)
                                                    ,argument86  => chr(0)
                                                    ,argument87  => chr(0)
                                                    ,argument88  => chr(0)
                                                    ,argument89  => chr(0)
                                                    ,argument90  => chr(0)
                                                    ,argument91  => chr(0)
                                                    ,argument92  => chr(0)
                                                    ,argument93  => chr(0)
                                                    ,argument94  => chr(0)
                                                    ,argument95  => chr(0)
                                                    ,argument96  => chr(0)
                                                    ,argument97  => chr(0)
                                                    ,argument98  => chr(0)
                                                    ,argument99  => chr(0)
                                                    ,argument100 => chr(0));
        COMMIT;
        --Wait for the Program Completion
        IF l_burst_req_id > 0
        THEN
          l_burst_phase := 'XX';
          WHILE (upper(l_burst_phase) <> 'COMPLETE')
          LOOP
            l_req_wait2 := fnd_concurrent.wait_for_request(request_id => l_burst_req_id
                                                          ,INTERVAL   => 2
                                                          ,max_wait   => 0
                                                          ,phase      => l_burst_phase_code
                                                          ,status     => l_burst_status_code
                                                          ,dev_phase  => l_burst_phase
                                                          ,dev_status => l_burst_dev_status
                                                          ,message    => l_burst_msg);
            EXIT WHEN upper(l_burst_phase) = 'COMPLETE';
          END LOOP;
          l_burst_src_file   := 'o' || l_burst_req_id || '.out';
          x_burst_request_id := l_burst_req_id;
        END IF; -- l_burst_req_id > 0
        fnd_file.put_line(fnd_file.log, '   Burst Program Req ID: ' || l_burst_req_id);
        fnd_file.put_line(fnd_file.log, '   Burst Program request Status: ' || l_burst_status_code);
        --
      ELSE
        fnd_file.put_line(fnd_file.log
                         ,'***Profile Option XX01: Enable Report Bursting Email set to "No", Bursting program NOT submitted');
      END IF; --if FND_PROFILE.VALUE_SPECIFIC() = 'Y'
    END IF; --IF (UPPER (l_status_code) = 'NORMAL') THEN
    IF (upper(l_burst_status_code) = 'ERROR')
       OR (upper(l_status_code) = 'ERROR')
    THEN
      x_retcode := 2; --Setting the Concurrent program status to Error
      x_errbuf  := 'Burst Program/Pack List - Email programs completed in error';
    ELSIF (upper(l_burst_status_code) = 'WARNING')
          OR (upper(l_status_code) = 'WARNING')
    THEN
      x_retcode := 1; --Setting the Concurrent program status to Warning
      x_errbuf  := 'Burst Program/Pack List - Email programs completed in warning';
    ELSIF (upper(l_burst_status_code) = 'NORMAL')
          OR (upper(l_status_code) = 'NORMAL')
    THEN
      x_retcode := 0; --Setting the Concurrent program status to Normal
      x_errbuf  := 'Program completed normally';
    END IF;
    COMMIT;
    fnd_file.put_line(fnd_file.log, 'End of delivery_ASN_Email_Main');
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.log, 'Error in delivery_ASN_Email_Main: ' || SQLERRM);
      x_retcode := 2;
  END delivery_asn_email_main;
  -- --------------------------------------------------------------------------
  PROCEDURE asn_email_main(x_errbuf         OUT NOCOPY VARCHAR2
                          ,x_retcode        OUT NOCOPY NUMBER
                          ,p_ou             IN NUMBER
                          ,p_order_num_from IN NUMBER
                          ,p_order_num_to   IN NUMBER
                          ,p_ship_date_from IN VARCHAR2
                          ,p_ship_date_to   IN VARCHAR2
                          ,p_delivery_from  IN NUMBER
                          ,p_delivery_to    IN NUMBER) IS
    CURSOR get_confirmed_delivery(p_ou             NUMBER
                                 ,p_order_num_from NUMBER
                                 ,p_order_num_to   NUMBER
                                 ,p_ship_date_from DATE
                                 ,p_ship_date_to   DATE
                                 ,p_delivery_from  NUMBER
                                 ,p_delivery_to    NUMBER) IS
      SELECT DISTINCT c.delivery_id
        FROM wsh_delivery_assignments a
            ,wsh_delivery_details     b
            ,wsh_new_deliveries       c
            ,oe_order_headers_all     d
            ,oe_order_lines_all       f
      --,hz_cust_site_uses_all    g --*<3.0> Commented
      --,hz_cust_acct_sites_all   h --*<3.0> Commented
       WHERE a.delivery_id = c.delivery_id
         AND a.delivery_detail_id = b.delivery_detail_id
         AND b.source_header_id = d.header_id
         AND b.source_code = 'OE'
         AND d.header_id = f.header_id
         AND b.source_line_id = f.line_id
            --AND g.cust_acct_site_id = h.cust_acct_site_id --*<3.0> Commented
            --AND d.ship_to_org_id = g.site_use_id --*<3.0> Commented
            --*<3.0> Added an EXISTS condition
         AND EXISTS (SELECT 1
                FROM hz_cust_site_uses_all  ship_su
                    ,hz_cust_acct_sites_all ship_cas
                    ,hz_cust_site_uses_all  bill_su
                    ,hz_cust_acct_sites_all bill_cas
               WHERE ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
                 AND ship_su.site_use_id = d.ship_to_org_id
                    --
                 AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id
                 AND bill_su.site_use_id = d.invoice_to_org_id
                    --
                 AND decode(nvl(ship_cas.attribute3, 'NULL')
                           ,'NULL'
                           ,nvl(bill_cas.attribute3, 'NULL')
                           ,ship_cas.attribute3) = 'EMAIL')
            --and    d.invoice_to_org_id = g.site_use_id
         AND c.status_code = 'CL'
            --AND h.attribute3 = 'EMAIL' --'FAX' --*<3.0> Commented
         AND d.org_id = nvl(p_ou, d.org_id)
         AND trunc(c.confirm_date) BETWEEN p_ship_date_from AND p_ship_date_to
         AND d.order_number BETWEEN p_order_num_from AND p_order_num_to
         AND c.delivery_id BETWEEN p_delivery_from AND p_delivery_to;
    l_order_num_from NUMBER;
    l_order_num_to   NUMBER;
    l_delivery_from  NUMBER;
    l_delivery_to    NUMBER;
    l_ship_date_from DATE;
    l_ship_date_to   DATE;
    x_burst_req_id   NUMBER := 0;
    l_burst_flag     NUMBER := 0;
  BEGIN
    x_retcode := 0;
    IF p_ship_date_from IS NULL
       AND p_ship_date_to IS NULL
       AND p_order_num_from IS NULL
       AND p_order_num_to IS NULL
       AND p_delivery_from IS NULL
       AND p_delivery_to IS NULL
    THEN
      x_retcode := 2;
      x_errbuf  := 'Order number, Ship date, Delivery all null,' ||
                   'please enter order number/ship date/deliver.';
      fnd_file.put_line(fnd_file.log, x_errbuf);
      RETURN;
    END IF;
    IF p_ship_date_from IS NOT NULL
    THEN
      l_ship_date_from := trunc(fnd_date.canonical_to_date(p_ship_date_from));
    ELSE
      l_ship_date_from := trunc(SYSDATE) - 360;
    END IF;
    fnd_file.put_line(fnd_file.log
                     ,'Date from: ' || to_char(l_ship_date_from, 'DD-MON-YYYY HH:MI:SS'));
    IF p_ship_date_to IS NOT NULL
    THEN
      l_ship_date_to := trunc(fnd_date.canonical_to_date(p_ship_date_to));
    ELSE
      l_ship_date_to := trunc(SYSDATE);
    END IF;
    fnd_file.put_line(fnd_file.log, 'Date To: ' || to_char(l_ship_date_to, 'DD-MON-YYYY HH:MI:SS'));
    IF p_order_num_from IS NULL
    THEN
      l_order_num_from := -9999;
    ELSE
      l_order_num_from := p_order_num_from;
    END IF;
    fnd_file.put_line(fnd_file.log, 'Order From: ' || l_order_num_from);
    IF p_order_num_to IS NULL
    THEN
      l_order_num_to := 99999999999;
    ELSE
      l_order_num_to := p_order_num_to;
    END IF;
    fnd_file.put_line(fnd_file.log, 'Order To: ' || l_order_num_to);
    IF p_delivery_from IS NULL
    THEN
      l_delivery_from := -9999;
    ELSE
      l_delivery_from := p_delivery_from;
    END IF;
    fnd_file.put_line(fnd_file.log, 'Delivery From: ' || l_delivery_from);
    IF p_delivery_to IS NULL
    THEN
      l_delivery_to := 99999999999;
    ELSE
      l_delivery_to := p_delivery_to;
    END IF;
    fnd_file.put_line(fnd_file.log, 'Delivery To: ' || l_delivery_to);
    FOR x IN get_confirmed_delivery(p_ou
                                   ,l_order_num_from
                                   ,l_order_num_to
                                   ,l_ship_date_from
                                   ,l_ship_date_to
                                   ,l_delivery_from
                                   ,l_delivery_to)
    LOOP
      l_burst_flag := 1;
      BEGIN
        UPDATE wsh_new_deliveries
           SET --*<ADTI-269>
               /*attribute1 = 'N'*/ attribute3 = g_conc_req_id --setting a flag - so that the ASN Email program would pick these records
         WHERE delivery_id = x.delivery_id;
        fnd_file.put_line(fnd_file.log
                         ,'Updated wsh_new_deliveries for Delivery ID: ' || x.delivery_id);
      EXCEPTION
        WHEN OTHERS THEN
          fnd_file.put_line(fnd_file.log
                           ,'Error updating wsh_new_deliveries in ASN_Email_Main: ' || SQLERRM);
          x_retcode := 2;
      END;
    END LOOP;
    IF l_burst_flag = 1 --Call subsequent program only when there are eligible records from the cursor
    THEN
      delivery_asn_email_main(x_errbuf, x_retcode, x_burst_req_id);
    ELSE
      fnd_file.put_line(fnd_file.log
                       ,'****** No eligible records found for the given input parameters. ******');
    END IF;
    IF x_retcode = 0
    THEN
      FOR x IN get_confirmed_delivery(p_ou
                                     ,l_order_num_from
                                     ,l_order_num_to
                                     ,l_ship_date_from
                                     ,l_ship_date_to
                                     ,l_delivery_from
                                     ,l_delivery_to)
      LOOP
        BEGIN
          UPDATE wsh_new_deliveries
             SET --*<ADTI-269>
                 /*attribute1*/ attribute3 = g_conc_req_id || ': Y' || '; Req ID; ' ||
                                             x_burst_req_id || '; ' ||
                                             to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') --Processed the record
           WHERE delivery_id = x.delivery_id;
          fnd_file.put_line(fnd_file.log
                           ,'Updated wsh_new_deliveries after burst program for Delivery ID: ' ||
                            x.delivery_id);
        EXCEPTION
          WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.log
                             ,'Error updating wsh_new_deliveries in ASN_Email_Main: ' || SQLERRM);
            x_retcode := 2;
        END;
      END LOOP;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.log, 'Error in ASN_Email_Main: ' || SQLERRM);
      x_retcode := 2;
  END asn_email_main;
END xx01_asn_email_pkg;
/
 

In production how to drop the table defined in specific schema?

 

In production how to drop the table defined in specific schema?

Drop table command does not work in .sql file or .adh (adhoc) file?

So we need to write the drop command in .grt file (Grant File)

Drop table schema.tablename;

Save the file as .grt

By executing this file will drop the table as well removes in dba_objects table.

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'

;