Oracle EBS - How to Upload RTF Using XDOLoader Utility

 Subject: How to Upload RTF Using XDOLoader Utility

--RTF Upload Using XDOLoader Utility--

 

Step1: Uploading the RTF file on TEST file server home directory. 

Step2: Change the TEST application  DB_USERNAME, DB_PASSWORD,JDBC_CONNECTION, FILE_NAME

Step3: Execute the script in TEST using putty.

 

--RTF Upload Command--
java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD p7v8S36 \
-JDBC_CONNECTION odev3.telamon.telamon-corp.com:1522/TEST \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME XXTLM \
-LOB_CODE XXTLM_PRJ_SO_MATRIAL_STATUS \
-LANGUAGE en \
-TERRITORY US \
-NLS_LANG American_America.WE8ISO8859P1 \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE 'text/html' \
-FILE_NAME XXTLM_PRJ_SO_MATRIAL_STATUS.rtf

 

SQL Query - Get the Journal Batch Workflow Item Key

SELECT item_type, item_key

FROM wf_items

WHERE user_key = '<journal batch name>';

Oracle PLSQL Script - How to Generate File and Place it on Oracle File Server Using Oracle Utility

 DECLARE

  -- Declare file handler
  file_handle UTL_FILE.FILE_TYPE;

  -- Declare variables for data to be written
  v_line VARCHAR2(100);
  v_filename VARCHAR2(50) := 'handlingunits'||TO_CHAR(SYSDATE,'MMDDRRHH24MISS')||'.csv';  -- Name of the file
  v_dir VARCHAR2(50) := 'XX_CAO_OUTBOUND';  -- Directory path (ensure it's accessible by Oracle)

 

BEGIN
  -- Open the file for writing. If the file does not exist, it will be created.
  file_handle := UTL_FILE.FOPEN(v_dir, v_filename, 'w');  -- 'w' mode means write

  -- Example data to write to the file
  FOR xxtlm_rec IN (SELECT DISTINCT ';;;'||item||';'||vendor_lot||';1;1;1;' line
                    FROM xxtlm.xxtlm_rcv_transactions xrt
                    WHERE xrt.organization_id = (SELECT organization_id FROM mtl_parameters WHERE organization_code = 'TIF')
                        AND xrt.status_id = 2
                        AND transaction_type = 'RECEIVE'
                    UNION
                    SELECT '99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999'
                    FROM dual
                    ORDER BY 1 DESC) 
    LOOP    
    -- Write the data to the file
    UTL_FILE.PUT_LINE(file_handle, xxtlm_rec.line);
  END LOOP;

  -- Close the file after writing
  UTL_FILE.FCLOSE(file_handle);

 

  -- Success message
  DBMS_OUTPUT.PUT_LINE('Data written successfully to ' || v_dir || '/' || v_filename);

 

EXCEPTION
  WHEN OTHERS THEN
    -- Handle any exceptions
    DBMS_OUTPUT.PUT_LINE('Exception: '||SQLERRM);
    IF UTL_FILE.IS_OPEN(file_handle) THEN
      UTL_FILE.FCLOSE(file_handle);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/