Oracle Apps Knowledge Sharing

CREATE OR REPLACE DIRECTORY XX_CONCURRENT_PROGRAM_LOG 

 AS '<concurrent_request_log_dir>'

CREATE OR REPLACE DIRECTORY XX_CONCURRENT_PROGRAM_OP  

AS '<concurrent_request_output_dir>'

create or replace FUNCTION xx_get_conc_details(piv_file_type  IN VARCHAR2,

                                                 pin_request_id IN NUMBER

                                                )

RETURN CLOB

IS                                                                                         

lv_dir           VARCHAR2(100);

lv_file          VARCHAR2(100);

lc_return        CLOB;

lv_log_filename  VARCHAR2(1000);

lv_out_filename  VARCHAR2(1000);

ln_log_file_size NUMBER;

ln_out_file_size NUMBER;

BEGIN

  SELECT logfile_name,

         outfile_name,

                lfile_size,

                ofile_size

    INTO lv_log_filename,

         lv_out_filename,

         ln_log_file_size,

                ln_out_file_size

        FROM fnd_concurrent_requests

   WHERE request_id = pin_request_id;

 

  IF piv_file_type = 'LOG' AND ln_log_file_size = 0

  THEN

    RETURN 'Log File is 0KB in size';

  ELSIF piv_file_type = 'OUTPUT' AND ln_out_file_size = 0

  THEN

    RETURN 'Output File is 0KB in size';

  ELSIF piv_file_type = 'LOG'

  THEN

    lv_dir   := 'XX_CONCURRENT_PROGRAM_LOG';

        lv_file  := SUBSTR(lv_log_filename,INSTR(lv_log_filename,'/',-1)+1);

  ELSIF piv_file_type = 'OUTPUT'

  THEN

    lv_dir   := 'XX_CONCURRENT_PROGRAM_OP';

        lv_file  := SUBSTR(lv_out_filename,INSTR(lv_out_filename,'/',-1)+1);

  ELSE

    RETURN ('INVALID FILE TYPE. IT SHOULD BE LOG OR OUTPUT.');

  END IF;

 

  RETURN DBMS_XSLPROCESSOR.READ2CLOB(lv_dir,lv_file);

 

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    RETURN 'Given Request does not exists in FND_CONCURRENT_REQUESTS table';

END xxif_get_conc_details;

  select 

       XX_GET_CONC_DETAILS('LOG','98680')    LOG_FILE,

       XX_GET_CONC_DETAILS('OUTPUT','98680') OUTPUT_FILE

  from DUAL;