EBS - Outbound Interface

Outbound Interface:
Exports data from Oracle application's base table into flat files.

The below steps are followed to perform outbound interface

• A package or a procedure is created with UTL_FILE wrapper operation , to move the data from the base table into the flat file in the server.
• This procedure can be registered as a concurrent program with parameter with gets the destination SFTP directory as input.

UTL_FILE :

UTL_FILE is a PL/SQL package that can be used to write to operating system files on a local file system where the database resides

• FND_FILE is built-in package provided in Oracle Apps/E-Business Suite to write log and output file in PL/SQL concurrent program. 
• FND_FILE.LOG – This is constant variable to denote log file
• FND_FILE.OUTPUT  – This is constant variable to denote log file
• UTL_FILE.FCLOSE_ALL -This procedure closes all open file identified by a file handle.
• UTL_FILE.FOPEN - This procedure opens identified files.

The below are the few UTL_FILE package exception we will be used in our code.

Exception Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The requested file rename operation failed.

: 

Sample Code:

The below is sample package for outbound interface to send the item description to the SFTP which uses the UTL_FILE. 

Code:

CREATE OR REPLACE PROCEDURE xx_inv_outbound_pkg(
    errbuf out VARCHAR2,
    retcode out VARCHAR2)
AS
  CURSOR cur
  IS
    SELECT msi.segment1 item,
      msi.inventory_item_id itemid,
      msi.description item_description,
      msi.primary_uom_code uom_code,
      ood.organization_name,
      ood.organization_id,
      mc.segment1
      ||','
      ||mc.segment2 CATEGORY
    FROM mtl_system_items msi,
      org_organization_definitions ood,
      mtl_item_categories mic,
      mtl_categories mc
    WHERE msi.organization_id    = ood.organization_id
    AND msi.inventory_item_id    = mic.inventory_item_id
    AND mc.category_id           = mic.category_id
    AND msi.organization_id      = mic.organization_id
    AND msi.purchasing_item_flag = 'Y'
    AND msi.organization_id      = 204;
  x_id utl_file.file_type;
  l_count NUMBER DEFAULT 0 ;
BEGIN
  x_id := utl_file.fopen('/u01/EBS1213/db/tech_st/12.1.0/appsutil/outbound/EBS1213_fserver','xx_item.xls','W');
  FOR x1 IN cur
  loop
    l_count :=l_count+1;
    utl_file.put_line(x_id,x1.itemid||'-'||x1.item||'-'||x1.item_description||'-'||x1.uom_code||'-'||x1.CATEGORY);
  END loop;
  utl_file.fclose(x_id);
  fnd_file.put_line(fnd_file.output,'Number of Records transferred to data file:'||l_count||' Submitted User name '||fnd_profile.VALUE('USERNAME')||' Submitted Responsibility Name : '||fnd_profile.VALUE('RESP_NAME')||' Submission Name : '||SYSDATE);
exception
WHEN utl_file.invalid_operation THEN
  fnd_file.put_line(fnd_file.LOG,'Invalid Operation');
  utl_file.fclose_all;
WHEN utl_file.read_error THEN
  fnd_file.put_line(fnd_file.LOG,'read error');
  utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
  fnd_file.put_line(fnd_file.LOG,'invalid_path');
  utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
  fnd_file.put_line(fnd_file.LOG,'invalid_mode');
  utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
  fnd_file.put_line(fnd_file.LOG,'invalid_filehandle');
  utl_file.fclose_all;
WHEN utl_file.internal_error THEN
  fnd_file.put_line(fnd_file.LOG,'internal_error');
  utl_file.fclose_all;
WHEN others THEN
  fnd_file.put_line(fnd_file.LOG,'other error');
  utl_file.fclose_all;

END;