Oracle Query to get Oracle Menus & Functions along with Sub Menu's and Their functions

SELECT LPAD(' ', 6*(LEVEL-1))

  || menu_entry.entry_sequence sequence ,

  LPAD(' ', 6*(LEVEL-1))

  || menu.user_menu_name SubMenu_Description ,

  LPAD(' ', 6*(LEVEL-1))

  || func.user_function_name Function_Description ,

  menu.menu_id ,

  func.function_id ,

  menu_entry.grant_flag Grant_Flag ,

  DECODE( menu_entry.sub_menu_id , NULL, 'FUNCTION' , DECODE( menu_entry.function_id , NULL, 'SUBMENU' , 'BOTH') ) Type

FROM fnd_menu_entries menu_entry ,

  fnd_menus_tl menu ,

  fnd_form_functions_tl func

WHERE menu_entry.sub_menu_id    = menu.menu_id(+)

AND menu_entry.function_id      = func.function_id(+)

AND grant_flag                  = 'Y'

  START WITH menu_entry.menu_id =

  (SELECT menu_id

  FROM fnd_menus_tl menu2

  WHERE menu2.user_menu_name = 'XXX_OM_USER'

  )

  CONNECT BY MENU_ENTRY.MENU_ID = PRIOR MENU_ENTRY.SUB_MENU_ID

ORDER SIBLINGS BY menu_entry.entry_sequence;

Deleting a Sales order Line and Adding a attachment at header level stating that Line deleted from order and with other Reason of deleting and comments if any

DECLARE
    P_LINE_REC VARCHAR2(300) :='12345,56784,454562';
    P_REASON   VARCHAR2(300) := 'Customer No Longer Needed';
    p_comments VARCHAR2(300) := 'New Code developed to add the Attachement';
     TYPE SolCurTyp
IS
  ref
  CURSOR;
    ONT_CV SOLCURTYP;
     ONT_CV1 SOLCURTYP;
      ONT_STMT VARCHAR2(4000);
       ONT_STMT1 VARCHAR2(4000);
     L_LINE_ID OE_ORDER_LINES_ALL.LINE_ID%TYPE;
     L_ORG_ID OE_ORDER_LINES_ALL.ORG_ID%type;
    L_HEADER_ID OE_ORDER_LINES_ALL.HEADER_ID%type;
    L_RETURN_STATUS varchar2(1);
    L_RET_STATUS VARCHAR2(1);   
    l_msg_data      VARCHAR2(2000);
    l_Head_id oe_order_lines_all.Header_id%Type := NULL;
    P_ATTACH_LINE_REC VARCHAR2(2000) := NULL;   
    l_msg_count              NUMBER;
    L_MESSAGE_DATA           varchar (2000);   
    l_ret_message            VARCHAR (2000);  
    L_MSG_INDEX_OUT          NUMBER;
    V_ONT_STMT    VARCHAR2(4000);
    V_COUNT NUMBER;
      V_COUNT1 NUMBER;
      v_header_id number;
 
  BEGIN
 
   ONT_STMT1 := 'SELECT distinct Header_id
                  FROM oe_order_lines_all
                  WHERE line_id in (' ||p_line_rec|| ')
                  group by Header_id';
      
 OPEN ONT_CV1 FOR ONT_STMT1;
    LOOP
      FETCH ONT_CV1 INTO V_HEADER_ID;
      EXIT  WHEN ONT_CV1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE( 'V_COUNT1 ---'||V_HEADER_ID);
     
    ONT_STMT := 'SELECT Header_id,line_id,Org_id
                  FROM oe_order_lines_all
                  WHERE line_id in (' ||p_line_rec|| ')
                  AND HEADER_ID = ( '|| V_HEADER_ID ||')
                  ORDER by Header_id';
        
    OPEN ONT_CV FOR ONT_STMT;
    LOOP
      FETCH ONT_CV INTO l_Header_id,l_line_id,l_Org_id;
      EXIT WHEN ONT_CV%NOTFOUND;
    IF l_line_id IS NOT NULL THEN
      -- Calling the API to delete a line from existing order
       DBMS_OUTPUT.PUT_LINE( 'V_COUNT2 ---'||l_line_id);
         
      DBMS_OUTPUT.PUT_LINE( 'Before Calling API for line id'||L_LINE_ID ||'---'||l_Org_id);
      OE_ORDER_PUB.DELETE_LINE ( P_LINE_ID => L_LINE_ID ,
                                 P_ORG_ID => L_ORG_ID ,
                                 P_OPERATING_UNIT => NULL ,
                                 X_RETURN_STATUS => L_RETURN_STATUS ,
                                 X_MSG_COUNT => L_MSG_COUNT ,
                                 X_MSG_DATA => L_MSG_DATA);
                                  DBMS_OUTPUT.PUT_LINE( 'After Calling API -' ||l_return_status);
         IF l_return_status = fnd_api.g_ret_sts_success
            THEN
               COMMIT;
               l_ret_status := l_return_status;
               L_RET_MESSAGE := L_MSG_DATA;
                IF   P_ATTACH_LINE_REC IS NOT NULL THEN
                      P_ATTACH_LINE_REC := P_ATTACH_LINE_REC||CHR(10) ||L_LINE_ID;
                ELSE
                       P_ATTACH_LINE_REC := L_LINE_ID;
                END IF;
            ELSE              
               oe_msg_pub.get (p_msg_index          => 1,
                               p_encoded            => fnd_api.g_false,
                               p_data               => l_msg_data,
                               p_msg_index_out      => l_msg_index_out
                              );
               l_ret_status := l_return_status;
               L_RET_MESSAGE := L_MSG_DATA;             
  DBMS_OUTPUT.PUT_LINE( 'After Calling API -'|| 'Error at SO Line Deletion: '||'Return Status of Hearder ID and Line ID -  '||L_LINE_ID||' & '||L_HEADER_ID || ' is :'||
                          L_RET_STATUS || 'and Message is :' ||L_RET_MESSAGE );
 
FND_FILE.PUT_LINE (FND_FILE.log,
                         'Error at SO Line Deletion: '||'Return Status of Hearder ID and Line ID -  '||L_LINE_ID||' & '||L_HEADER_ID || ' is :'||
                          L_RET_STATUS || 'and Message is :' ||L_RET_MESSAGE);                       
          END IF;
   END IF;
   DBMS_OUTPUT.PUT_LINE('Coming here 1');

 
  END LOOP;
  CLOSE ONT_CV;
 
  IF P_ATTACH_LINE_REC IS NOT NULL THEN
 
 DBMS_OUTPUT.PUT_LINE( 'Adding attachment for header id :'||L_HEADER_ID);
       XX_ADD_HEADER_ATTACHEMENT(p_pk1_value  => l_Header_id,
                       p_org_id     => NULL,
                       P_SHORT_TEXT => P_REASON||CHR(10) ||P_COMMENTS||CHR(10)||P_ATTACH_LINE_REC);
                       P_ATTACH_LINE_REC := NULL;
                       DBMS_OUTPUT.PUT_LINE( 'completed adding attachment for header id :'||l_Header_id);
 END IF;
 
  END LOOP;
  CLOSE ONT_CV1;
     
  EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE( 'API Eror :'||SQLERRM);
END ;

Adding Attachment @ Header Level

DECLARE
p_pk1_value fnd_attached_documents.pk1_value%TYPE := '259330';
P_ORG_ID NUMBER := 204;
p_short_text varchar2(4000) := 'Successfully Deleted the Line' ;
     
    l_rowid ROWID;
    l_attached_document_id NUMBER;
    l_document_id NUMBER;
    l_media_id NUMBER;
    l_category_id NUMBER;
    l_pk1_value fnd_attached_documents.pk1_value%TYPE;
    l_seq_num NUMBER;
    l_fnd_user_id NUMBER := fnd_profile.VALUE('USER_ID');
    l_short_datatype_id NUMBER;
    l_entity_name VARCHAR2(100) := 'OE_ORDER_HEADERS';
    l_category_name VARCHAR2(100) := 'Short Text';--'Sales Order Lines Deletion';
   -- p_org_id number := fnd_profile.value(‘org_id’);
 BEGIN
    l_pk1_value := p_pk1_value;
 dbms_output.put_line( 'l_pk1_value :-'||l_pk1_value);
    SELECT fnd_documents_s.NEXTVAL
    INTO l_document_id
    FROM DUAL;
DBMS_OUTPUT.PUT_LINE( 'l_document_id :-'||l_document_id);
    SELECT fnd_attached_documents_s.NEXTVAL
    INTO l_attached_document_id
    FROM DUAL;
DBMS_OUTPUT.PUT_LINE( 'l_attached_document_id :-'||l_attached_document_id);
    SELECT NVL (MAX (seq_num), 0) + 10
    INTO l_seq_num
    FROM fnd_attached_documents
    WHERE pk1_value = l_pk1_value
    AND entity_name = l_entity_name;
DBMS_OUTPUT.PUT_LINE( 'l_seq_num :-'||l_seq_num);
    -- Get Data type id for Short Text types of attachments
    SELECT datatype_id
    INTO l_short_datatype_id
    FROM apps.fnd_document_datatypes
    WHERE NAME = 'SHORT_TEXT';
    -- Select Category id for Attachments
    DBMS_OUTPUT.PUT_LINE( 'l_short_datatype_id :-'||l_short_datatype_id);
   
    SELECT category_id
    INTO l_category_id
    FROM apps.fnd_document_categories_vl
    WHERE USER_NAME = l_category_name;
    -- This package allows user to share file across multiple orgs or restrict to single org
 DBMS_OUTPUT.PUT_LINE( 'l_category_id :-'||l_category_id);
    fnd_documents_pkg.insert_row
    (x_rowid => l_rowid,
     x_document_id => l_document_id,
     x_creation_date => SYSDATE,
     x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
     x_last_update_date => SYSDATE,
     x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
     x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
     x_datatype_id => l_short_datatype_id, -- SHORT TEXT
     x_security_id => Null,--p_org_id,
     x_publish_flag => 'Y', --This flag allow the file to share across multiple organization
     x_category_id => l_category_id,
     x_security_type => 4,--1 tBC
     x_usage_type => 'O',
     x_language => 'US',
     x_media_id => l_media_id
    );
DBMS_OUTPUT.PUT_LINE( 'l_media_id :-'||l_media_id);
    -- Insert a new record into the table containing the sort text
    INSERT INTO FND_DOCUMENTS_SHORT_TEXT
    (media_id, short_text
    )
    VALUES (l_media_id, p_short_text
            );
DBMS_OUTPUT.PUT_LINE( 'p_short_text :-'||p_short_text);
    fnd_documents_pkg.insert_tl_row
   (x_document_id => l_document_id,
    x_creation_date => SYSDATE,
    x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
    x_last_update_date => SYSDATE,
    x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
    x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
    x_language => 'US'
    );
DBMS_OUTPUT.PUT_LINE( 'Success :- 1');
   fnd_attached_documents_pkg.insert_row
   ( x_rowid => l_rowid,
     x_attached_document_id => l_attached_document_id,
     x_document_id => l_document_id,
     x_creation_date => SYSDATE,
     x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
     x_last_update_date => SYSDATE,
     x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
     x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
     x_seq_num => l_seq_num,
     x_entity_name => l_entity_name,
     x_column1 => NULL,
     x_pk1_value => l_pk1_value,
     x_pk2_value => NULL,
     x_pk3_value => NULL,
     x_pk4_value => NULL,
     x_pk5_value => NULL,
     x_automatically_added_flag => 'N',
     x_datatype_id => l_short_datatype_id,
     x_category_id => l_category_id,
     x_security_type => 4,
     x_security_id => p_org_id,
     x_publish_flag => 'Y',
     x_language => 'US',
     x_media_id => l_media_id
    );
DBMS_OUTPUT.PUT_LINE( 'Success :- 2');
 commit;
Exception
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE( 'Error from Above :'||SQLERRM);
END;

Sales order Line Delete API

SET SERVEROUTPUT ON;
DECLARE
v_api_version_number           NUMBER  := 1;
v_return_status                VARCHAR2 (2000);
v_msg_count                    NUMBER;
v_msg_data                     VARCHAR2 (2000);
-- IN Variables --
V_HEADER_REC                   OE_ORDER_PUB.HEADER_REC_TYPE;
 r_line_rec                    oe_order_pub.line_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;
v_action_request_tbl           oe_order_pub.request_tbl_type;
v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
-- OUT Variables --
v_header_rec_out               oe_order_pub.header_rec_type;
v_header_val_rec_out           oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out                 oe_order_pub.line_tbl_type;
v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out       oe_order_pub.request_tbl_type;
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting of script');
v_line_tbl           := oe_order_pub.g_miss_line_tbl;
r_line_rec           := oe_order_pub.g_miss_line_rec;
R_LINE_REC.LINE_ID   := 449280;
r_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
v_line_tbl (1)       := r_line_rec;
DBMS_OUTPUT.PUT_LINE('Starting of API');
-- CALLING THE API TO DELETE AN ORDER LINE--
OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
, p_action_request_tbl          => v_action_request_tbl
, p_line_adj_tbl                => v_line_adj_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);
DBMS_OUTPUT.PUT_LINE('Completion of API');
IF v_return_status = fnd_api.g_ret_sts_success THEN
  COMMIT;
  DBMS_OUTPUT.put_line ('Order Deletion Success : '||v_header_rec_out.header_id);
ELSE
  DBMS_OUTPUT.put_line ('Order Deletion failed:'||v_msg_data);
  ROLLBACK;
  FOR i IN 1 .. v_msg_count
  LOOP
    v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
    dbms_output.put_line( i|| ') '|| v_msg_data);
  END LOOP;
END IF;
END;
/

Oracle Workflow some imp scripts..

Oracle Workflow some imp scripts..

BEGIN --> purging the records from WF_ITEMS and WF_ITEM_ACTIVITY_STATUSES
WF_ENGINE.AbortProcess(ITEMTYPE,ITEMKEY,NULL,'FORCE');
WF_PURGE.Items(ITEMTYPE=>'XXXXX',ITEMKEY=>'XXXXX',ENDDATE=>SYSDATE,DOCOMMIT=>TRUE,FORCE=>TRUE);
END;

BEGIN              -- creates a record entry in WF_ITEMS TABLE
wf_engine.createprocess(itemtype => 'ITEMTYPE',itemkey => 'ITEMKEY',process => 'PROCESS_NAME');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(fnd_file.output,'In exception while call to wf_engine.createprocess : '||SQLERRM);
END;
       
BEGIN -- creates the action history in WF_ITEM_ACTIVITY_STATUSES table
wf_engine.startprocess(itemtype     => 'ITEMTYPE',itemkey      => 'ITEMKEY');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(fnd_file.output,'In exception while call to wf_engine.createprocess : '||SQLERRM);
END;

Regards,
Sal.

Oracle Quoting form Ship to information Query

Oracle Quoting form Ship 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 = 'SHIP_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.

Oracle Quoting Customer information Query

Oracle Quoting Customer information Query..

SELECT hp.party_name, hp.party_number, ca.account_number, hp.party_type,
  hp.party_id, ca.cust_account_id
FROM
 HZ_PARTIES HP,
 HZ_CUST_ACCOUNTS CA
WHERE ( UPPER(PARTY_NAME) LIKE :1
 AND (PARTY_NAME LIKE :2))
 AND ( hp.party_type in ('PERSON','ORGANIZATION')
 AND hp.status = 'A'
 AND trunc(nvl(ca.account_activation_date,sysdate)) <= trunc(sysdate)
 AND trunc(nvl(ca.account_termination_date,sysdate)) >= trunc(sysdate)
 AND HP.party_id = CA.party_id
 AND ca.status = 'A'
 and rownum <= 5000 ) ORDER BY party_name,account_number

Regards,
Sal.

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..