0 Replies Latest reply on Feb 13, 2016 10:37 PM by 1567114

    SQL Plus Execution Method issue

    1567114

      Hello,

       

      In Concurrent program window when I do the view output its opening in a HTML format not in Text file.

       

      Is there anything I need to introduce in the above sql to generate the output in csv.I cannot do the spol in Concurrent program while registering the SQL plus as Executable Method.

       

      Also how to include the Column in the Report.If i do the option SET HEADING ON it prints all the column in the SQL used .

       

      Please assist.

       

      Please assist me if I missed anything in the below sql.Also If want to generate the output in .csv how to do do in EBS Concurrent Program?

       

      SET ECHO OFF

      SET PAGESIZE 32767

      SET PAUSE OFF

      SET SPACE 0

      SET LINESIZE 2000

      SET FEEDBACK OFF

      SET HEADING OFF

      SET SHOW OFF

      SET FEED OFF

      SET VERIFY OFF

      SET TERM OFF

      SET COLSEP ' '

      COLUMN PARTY_NAME HEADING 'Party Name' FORMAT A50

      COLUMN PARTY_SITE_NAME HEADING 'Party|Site|Name' FORMAT A30

      COLUMN PARTY_SITE_UPDATED_BY HEADING 'Party Site Updated By' FORMAT A22

      COLUMN PARTY_SITE_UPDT_DATE HEADING 'Party Site Updt Date' FORMAT A20

      COLUMN ADDRESS1 HEADING 'address1' FORMAT A40

      COLUMN ADDRESS2 HEADING 'address2' FORMAT A40

      COLUMN ADDRESS3 HEADING 'address3' FORMAT A40

      COLUMN ADDRESS4 HEADING 'address4' FORMAT A40

      COLUMN CITY     HEADING 'City' FORMAT A40

      COLUMN STATE HEADING 'State' FORMAT A40

      COLUMN POSTAL_CODE HEADING 'Postal Code' FORMAT A15

      COLUMN COUNTRY HEADING 'Country' FORMAT A15

      COLUMN ADDRESS_UPDATED_BY HEADING 'Address Updated By' FORMAT A15

      COLUMN ADDRESS_UPDATE_DATE HEADING 'Address Updt Date' FORMAT A15

       

       

      BREAK ON REPORT ON PARTY_NAME DUPLICATES SKIP 1

      --

      REPHEADER -

      'Party Nm Party Site Nm Party Site Updt by Party Site Updt Dt  Address1 Address2  Address3 Address4 City State Postal Code Country Address Updt By Address Updt Dt  '

      select --Location Previous

      DISTINCT

        hz_pty.party_name,

        NULL party_site_name,--hz_pty_site_a.party_site_name,

        NULL "Party Site Updated by",--hz_pty_site_a.audit_user_name "Party Site Updated by",

        NULL "Party Site Upt Date",

        decode(hz_loc_a.address1,NULL,NULL,hz_loc_a.address1) address1,

        decode(hz_loc_a.address2,NULL,NULL,hz_loc_a.address2) address2,

        decode(hz_loc_a.address3,NULL,NULL,hz_loc_a.address3) address3,

        decode(hz_loc_a.address4,NULL,NULL,hz_loc_a.address4) address4,

        decode(hz_loc_a.city,NULL,NULL,hz_loc_a.city) city,

        decode(hz_loc_a.state,NULL,NULL,hz_loc_a.state) state,

        decode(hz_loc_a.postal_code,NULL,NULL,hz_loc_a.postal_code) postal_code,

        decode(hz_loc_a.country,NULL,NULL,hz_loc_a.country) country,

        (SELECT email_address from fnd_user where user_name=hz_loc_a.audit_user_name) "Address Updated by",

        hz_loc_a.audit_timestamp "Address Update Date"

        from

        hz_party_sites   hz_pty_site,

        hz_locations     hz_loc,

        hz_locations_a   hz_loc_a,

        hz_cust_accounts hz_cust_acct,

        hz_parties  hz_pty

      WHERE

         hz_pty_site.location_id=hz_loc.location_id

      AND hz_loc.location_id=hz_loc_a.location_id

      AND hz_cust_acct.party_id=hz_pty_site.party_id

      AND hz_pty.party_id=hz_pty_site.party_id

      AND (hz_loc_a.address1 is NOT NULL OR hz_loc_a.address2 is NOT NULL OR hz_loc_a.address3 is NOT NULL

      OR hz_loc_a.address4 is NOT NULL OR hz_loc_a.state is NOT NULL OR hz_loc_a.postal_code is NOT NULL

      OR hz_loc_a.country is NOT NULL)

      AND hz_pty.party_name IN ('ABC','BCD','DEF')

      AND hz_loc_a.LAST_UPDATE_DATE is NOT NULL

      UNION

      select --Location New

      DISTINCT

        hz_pty.party_name,

        NULL party_site_name,--hz_pty_site_a.party_site_name,

        NULL "Party Site Updated by",--hz_pty_site_a.audit_user_name "Party Site Updated by",

        NULL "Party Site Upt Date",

        decode(hz_loc_a.address1,NULL,NULL,hz_loc.address1),

        decode(hz_loc_a.address2,NULL,NULL,hz_loc.address2),

        decode(hz_loc_a.address3,NULL,NULL,hz_loc.address3),

        decode(hz_loc_a.address4,NULL,NULL,hz_loc.address4),

        decode(hz_loc_a.city,NULL,NULL,hz_loc.city),

        decode(hz_loc_a.state,NULL,NULL,hz_loc.state),

        decode(hz_loc_a.postal_code,NULL,NULL,hz_loc.postal_code),

        decode(hz_loc_a.country,NULL,NULL,hz_loc.country),

        (SELECT email_address from fnd_user where user_id=hz_loc.last_updated_by) "Address Updated by",

        hz_loc.last_update_date "Address Update Date"

      from

        hz_party_sites   hz_pty_site,

        hz_locations     hz_loc,

        hz_locations_a     hz_loc_a,

        hz_cust_accounts hz_cust_acct,

        hz_parties  hz_pty

      WHERE

          hz_pty_site.location_id=hz_loc.location_id

      AND hz_cust_acct.party_id=hz_pty_site.party_id

      AND hz_pty.party_id=hz_pty_site.party_id

      AND hz_loc.location_id=hz_loc_a.location_id

      AND hz_loc_a.LAST_UPDATE_DATE is NOT NULL

      AND (hz_loc_a.address1 is NOT NULL OR hz_loc_a.address2 is NOT NULL OR hz_loc_a.address3 is NOT NULL

      OR hz_loc_a.address4 is NOT NULL OR hz_loc_a.state is NOT NULL OR hz_loc_a.postal_code is NOT NULL

      OR hz_loc_a.country is NOT NULL)

      AND hz_pty.party_name  IN ('ABC','DEF','BCD')

      /