Oracle Quoting form Bill to information Query

Oracle Quoting form Bill to information Query..


SELECT hcsua.location address1, hl.address1 address2, hl.address2 address3,
  hl.address3 address4, hl.address4 city, hl.city state, hl.state postal_code,
   hl.postal_code county, hl.county province, hcsua.site_use_id country_code,
  hpsu.primary_per_type primary_per_type, terr.territory_short_name country,
  hcsua.site_use_code site_use_type, hp.party_name party_name,
  hps.party_site_id
FROM
 HZ_PARTIES hp,
 HZ_PARTY_SITES hps,
 HZ_LOCATIONS hl,
 FND_TERRITORIES_VL terr,
 HZ_CUST_SITE_USES_ALL hcsua,
 HZ_CUST_ACCT_SITES_ALL hcasa,
 HZ_PARTY_SITE_USES hpsu
WHERE hp.party_id = ?
  AND hcasa.org_id = ?
  AND hcsua.site_use_code = 'BILL_TO'
  AND hps.party_id = hp.party_id
  AND hps.location_id = hl.location_id
  AND hcsua.CUST_ACCT_SITE_ID = hcasa.CUST_ACCT_SITE_ID
  AND hcasa.party_site_id = hps.party_site_id
  AND hl.country = terr.territory_code
  AND hpsu.party_site_id = hps.party_site_id
  AND hcsua.site_use_code = hpsu.site_use_type
  AND hp.status = 'A'
  AND hps.status = 'A'
  AND hpsu.status='A'
  ORDER BY hp.party_name, hl.address1,hl.address2, hl.address3, hl.address4

Regards,
Sal

Writing Text file into a table of BLOB column

BEGIN
Loading_BLOB_From_File('USER_DIR', '12345.txt',12345);END;
=================================================
Creating a Temp directory where the file can be placed..
=================================================

CREATE OR REPLACE DIRECTORY USER_DIR AS '/usr/tmp';

GRANT ALL on DIRECTORY USER_DIR TO PUBLIC; =================================================

CREATE OR REPLACE PROCEDURE Loading_BLOB_From_File ( P_DIR        IN VARCHAR2
                                    ,P_FILE_NAME IN VARCHAR2
                                    ,p_rowid  IN VARCHAR2)
AS
    dest_loc  BLOB;
    src_loc   BFILE := BFILENAME(P_DIR, P_FILE_NAME);
BEGIN
    -- +-------------------------------------------------------------+
    -- | INSERT INITIAL BLOB VALUE (an text file) INTO THE TABLE    |
    -- +-------------------------------------------------------------+
   DBMS_OUTPUT.put_line ('Before Updating');
  update XX_FILE_TABLE
  set TEXT = EMPTY_BLOB()
  where rowid = P_rowid
  RETURNING text INTO dest_loc;
DBMS_OUTPUT.put_line ('after Updating');
    -- +-------------------------------------------------------------+
    -- | OPENING THE SOURCE BFILE IS MANDATORY                       |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
DBMS_OUTPUT.put_line ('src_loc open');
    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_OUTPUT.put_line ('dest_loc open');
    -- +-------------------------------------------------------------+
    -- | SIMPLY CALL "loadfromfile" TO LOAD FILES INTO A LOB COLUMN  |
    -- +-------------------------------------------------------------+
    DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_loc
        , amount   => DBMS_LOB.getLength(src_loc));
DBMS_OUTPUT.put_line ('dest_loc load file');
    -- +-------------------------------------------------------------+
    -- | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(dest_loc);
    DBMS_LOB.CLOSE(src_loc);
DBMS_OUTPUT.put_line ('close all db');
    COMMIT;
Exception
when others then
DBMS_OUTPUT.put_line (' Exception in Loading_BLOB_From_File'||SQLERRM);
END Loading_BLOB_From_File;
/


Thank you..
Sal..

How to send the Short Text & Long Text Attachments as Attachment to the Oracle Workflow Notification.

Oracle Apps Technical.

How to send the Short Text & Long Text Attachments as Attachment to the Oracle Workflow Notification.

1) Create a File for Short Text and Long text.

DECLARE
CURSOR c_file_table (p_req_header_id NUMBER)
                      IS
select fad.pk1_value ,fdst.media_id, (fdst.short_text), fad.creation_date,fad.entity_name
from fnd_attached_documents fad, fnd_documents_tl fdt, FND_DOCUMENTS_SHORT_TEXT fdst
              where fad.document_id = fdt.document_id
              and fdt.language = 'US'
              and fdt.media_id = fdst.media_id
              and fad.pk1_value = p_req_header_id
              and fad.entity_name in ('REQ_HEADERS','REQ_LINES')
              order by length(fdst.short_text) desc;
              f utl_file.file_type;
    ln_req_header_id      NUMBER;
    l_file_content_type   fnd_lobs.file_content_type%TYPE   :=NULL;
BEGIN
    ln_req_header_id := 12345;
    l_file_content_type :=NULL;
    for s in c_file_table(ln_req_header_id)
    loop
        f := utl_file.fopen('USER_DIR',s.media_id||'.txt','W');
        utl_file.put_line(f,s.SHORT_TEXT);
         utl_file.fclose(f);
    COMMIT;
    INSERT INTO XX_SHORT_LONG_TABLE (MEDIA_ID,PK1_VALUE,ENTITY_NAME) VALUES (S.MEDIA_ID,S.PK1_VALUE,S.ENTITY_NAME);
    COMMIT;
     Loading_BLOB_From_File('USER_DIR', s.media_id||'.txt',s.media_id);
     COMMIT;
     utl_file.fremove('USER_DIR',s.media_id||'.txt');
    end loop;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.put_line ('In others ' || SQLERRM);
END;


==================

CREATE OR REPLACE PROCEDURE Loading_BLOB_From_File ( P_DIR        IN VARCHAR2
                                    ,P_FILE_NAME IN VARCHAR2
                                    ,p_rowid  IN VARCHAR2)
AS

    dest_loc  BLOB;
    src_loc   BFILE := BFILENAME(P_DIR, P_FILE_NAME);

BEGIN

    -- +-------------------------------------------------------------+
    -- | INSERT INITIAL BLOB VALUE (an text file) INTO THE TABLE    |
    -- +-------------------------------------------------------------+

   DBMS_OUTPUT.put_line ('Before Updating');
  update XX_SHORT_LONG_TABLE
  set TEXT = EMPTY_BLOB()
  where media_id = p_rowid
  RETURNING text INTO dest_loc;
DBMS_OUTPUT.put_line ('after Updating');
    -- +-------------------------------------------------------------+
    -- | OPENING THE SOURCE BFILE IS MANDATORY                       |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
DBMS_OUTPUT.put_line ('src_loc open');
    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_OUTPUT.put_line ('dest_loc open');
    -- +-------------------------------------------------------------+
    -- | SIMPLY CALL "loadfromfile" TO LOAD FILES INTO A LOB COLUMN  |
    -- +-------------------------------------------------------------+
    DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_loc
        , amount   => DBMS_LOB.getLength(src_loc));
DBMS_OUTPUT.put_line ('dest_loc load file');
    -- +-------------------------------------------------------------+
    -- | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(dest_loc);
    DBMS_LOB.CLOSE(src_loc);
DBMS_OUTPUT.put_line ('close all db');

    COMMIT;
Exception
when others then
DBMS_OUTPUT.put_line (' Exception in Load_BLOB_From_File'||SQLERRM);

END Loading_BLOB_From_File;
/

Thank you..
Sai..