Oracle Apps Outbound Interface Script for AP Supplier using UTL file.

By using below given script we can send data in out directory using UTL_File.
Kindly register below given procedure in concurrent executable as Executable file Name and register concurrent program with parameter Directory Name which would be your server outgoing directory name.

Script:

CREATE OR REPLACE PROCEDURE XX_SUPP_DEMO_EXTRACT_PKG
(p_directory_name IN VARCHAR2)-- Directory Name 'OUTGOING_DIRECTORY'
AS
--DECLARE 
  CURSOR cur_vendor
  IS
 select aps.segment1 supplier_Number,
 aps.vendor_name,
 aps.vendor_name_alt,
 aps.vendor_type_lookup_code,
 apsa.vendor_site_code,
 apsa.address_line1,
 apsa.address_line2,
 apsa.address_line3,
 apsa.city,
 apsa.country,
 apsa.purchasing_site_flag,
 apsa.pay_site_flag,
 apsa.payment_method_lookup_code
from ap_suppliers aps,
 ap_supplier_sites_all apsa
where aps.vendor_id=apsa.vendor_id
and aps.end_date_active is null;
  
v_utlfile UTL_FILE.file_type;
v_filename VARCHAR2 (30) := 'APSupplierOutFile.dat';
BEGIN
dbms_output.put_line('Before Opening UTL File');
v_utlfile := UTL_FILE.fopen (p_directory_name,v_filename ,'W' );
    UTL_FILE.put_line (v_utlfile ,
        ('supplier_Number'  )||'|'
    ||  ('vendor_name' )||'|'
    ||  ('vendor_name_alt' )||'|'
    ||  ('vendor_type_lookup_code' )||'|'
    ||  ('vendor_site_code' )||'|'
    ||  ('address_line1'  )||'|'
    ||  ('address_line2'  )||'|'
    ||  ('address_line3'  )||'|'
    ||  ('city'  )||'|'
    ||  ('country' )||'|'
    ||  ('purchasing_site_flag'  )||'|'
    ||  ('pay_site_flag' )||'|'
    ||  ('payment_method_lookup_code' ));
FOR rec_vendor IN cur_vendor
LOOP
     UTL_FILE.put_line (v_utlfile ,
        (rec_vendor.supplier_Number)||'|'
    ||  (rec_vendor.vendor_name)||'|'
    ||  (rec_vendor.vendor_name_alt)||'|'
    ||  (rec_vendor.vendor_type_lookup_code)||'|'
    ||  (rec_vendor.vendor_site_code)||'|'
    ||  (rec_vendor.address_line1)||'|'
    ||  (rec_vendor.address_line2)||'|'
    ||  (rec_vendor.address_line3)||'|'
    ||  (rec_vendor.city)||'|'
    ||  (rec_vendor.country)||'|'
    ||  (rec_vendor.purchasing_site_flag)||'|'
    ||  (rec_vendor.pay_site_flag)||'|'
    ||  (rec_vendor.payment_method_lookup_code));
END LOOP;
dbms_output.put_line ('..After Cursor cur_vendor');
 UTL_FILE.fclose (v_utlfile);
EXCEPTION
WHEN UTL_FILE.invalid_path THEN
  dbms_output.put_line ('Supplier_extract-Invalid Output Path: '  || ' - ' || SQLCODE || ' - ' || SQLERRM);
  dbms_output.put_line (' No OutPut File is Generated..... ');
  UTL_FILE.fclose (v_utlfile);
WHEN UTL_FILE.invalid_mode THEN
  dbms_output.put_line ('Supplier_extract-INVALID_MODE: ' || SQLCODE || ' - ' || SQLERRM);
  dbms_output.put_line (' No OutPut File is Generated..... ');
  UTL_FILE.fclose (v_utlfile);
WHEN UTL_FILE.invalid_filehandle THEN
  dbms_output.put_line ('Supplier_extract-INVALID_FILEHANDLE: ' || SQLCODE || ' - ' || SQLERRM);
  dbms_output.put_line (' No OutPut File is Generated..... ');
  UTL_FILE.fclose (v_utlfile);
WHEN UTL_FILE.invalid_operation THEN
  dbms_output.put_line ('Supplier_extract-INVALID_OPERATION: ' || SQLCODE || ' - ' || SQLERRM);
  dbms_output.put_line (' No OutPut File is Generated..... ');
  UTL_FILE.fclose (v_utlfile);
WHEN UTL_FILE.write_error THEN
  dbms_output.put_line ('Supplier_extract-An error occured writing data into output file: ' || SQLCODE || ' - ' || SQLERRM);
  dbms_output.put_line (' No OutPut File is Generated..... ');
  UTL_FILE.fclose (v_utlfile);
WHEN OTHERS THEN
  dbms_output.put_line ('Unexpected Error in Procedure XX_SUPP_DEMO_EXTRACT_PKG' || ' - ' || SQLERRM);
  UTL_FILE.fclose (v_utlfile);
END XX_SUPP_DEMO_EXTRACT_PKG;