Outbound Interface in Oracle Apps

Outbound Interface in Oracle Apps

Hi Friends, We are going to talk about Outbound Interface in Oracle Apps. Outbound interface in Oracle apps is the process , in which we do extract and share the Oracle apps data with other application outside oracle system. In Outbound interface, We do integrate with outside application but we don't accept any data from external application but we send the data to external application. 

What is Interface ?

When we want to integrate one application with other application , we do integrate with the help of interface. Interface is the mechanism in oracle apps , which helps to link/integrate one application with other application.

Types of Interface in Oracle Apps

In Oracle apps , we have two types of Interfaces.

1. Inbound Interface 

2.Outbound Interface

Inbound Interface :- Inbound interface is the process , In which we accept the data from External application into Oracle apps application. In inbound interface , we consume the data from external application and process in oracle apps.

Outbound Interface :-  Outbound interface is the process , in which we send the data from Oracle apps application into External application. In outbound interface , we share the data with external application and external application consumes this data.

How to design the Outbound interface in Oracle Apps

In Outbound interface , We have to share the oracle apps data with external Application. We have to decide the strategies using which we can extract and share the data. We have multiple options to design the Outbound Interface in Oracle apps. 

Outbound Interface in Oracle Apps

Outbound Interface in Oracle Apps

Options to design the Outbound Interface in Oracle Apps

1. Using Oracle Reports.

2. Using PLSQL Stored Procedure (utl_file Method).

Oracle Reports to design the Outbound Interface in Oracle Apps

If we have to share the Data with External Application in Excel File,  then we can design the Oracle Reports(XML Publisher Report) and then transfer this report output in the FTP server to the destination application.

Step1:-  Design the Oracle Reports (XML Report).

Step2:- Now Schedule the Report and in the Delivery Options enter the SFTP details to transfer the report output in the Destination server.

PLSQL Stored Procedure (utl_file Method) to design the Outbound Interface in Oracle Apps

PROCEDURE  XX_PAYMENT_EXTRACT(p_err_message OUT VARCHAR2,

                             p_err_code OUT NUMBER)

is

CURSOR payment_cur

      IS

      select

pv.segment1 ,

aps.vendor_site_code ,

a1.INVOICE_NUM  ,

to_char(aca.check_number) check_number,

aca.check_date ,

aca.amount ,

a2.amount payment_amount

from apps.ap_invoices_all a1,ap_invoice_payments_all a2,ap_checks_all aca,po_vendors pv,ap_supplier_sites_all aps

where 1=1

and a1.invoice_id=a2.invoice_id

and a2.check_id=aca.check_id

and a1.vendor_id=pv.vendor_id

and a1.vendor_site_id=aps.vendor_site_id;

v_row_count           NUMBER                                    := 0;

      v_instance_name       v$database.name%TYPE;

      v_user_name           fnd_user.user_name%TYPE;

      v_organization_id     hr_operating_units.organization_id%TYPE;

      v_organization_name   hr_operating_units.NAME%TYPE;

      v_sysdate             DATE;

      v_utl_dir             VARCHAR2 (2000);

      v_utl_filename        VARCHAR2 (2000);

      v_utl_mode            VARCHAR2 (1)                              := 'W';

      v_filehandle          UTL_FILE.file_type;

      v_buffer              VARCHAR2 (4000)                           := NULL;

      v_profile_value       VARCHAR2 (240);

      v_len                 NUMBER;

      v_instr               NUMBER;

      payment_rec          payment_cur%ROWTYPE;

 BEGIN

 

      fnd_file.put_line (fnd_file.LOG, 'Determining current system time...');

      SELECT SYSDATE

      INTO   v_sysdate

      FROM   DUAL;

      fnd_file.put_line (fnd_file.LOG, 'Current system time is: '

                          || TO_CHAR (v_sysdate, 'yyyy-mm-dd-hh24-mi-ss'));

      v_utl_filename    :='FILENAME'

                      || '_'

                      || TO_CHAR (v_sysdate, 'DDMMYYYY-hh24-mi-ss')

                      || '.xlsx;

 fnd_profile.get ('XX_UTL_OUTFILE', v_profile_value);

      v_utl_dir         :=  v_profile_value;

     

       fnd_file.put_line (fnd_file.LOG, 'Writing to directory '

                          || v_utl_dir);

      fnd_file.put_line (fnd_file.LOG, 'Writing file '

                          || v_utl_filename);

      fnd_file.put_line (fnd_file.LOG, 'UTL_FILE mode is '

                          || v_utl_mode);

      fnd_file.put_line (fnd_file.LOG, 'Opening file with above parameters...');

     

     

       v_filehandle      := UTL_FILE.fopen ('XX_UTL_OUTFILE',

                                           v_utl_filename,

                                           v_utl_mode

                                          );

                                         

                                         

fnd_file.put_line (fnd_file.LOG, 'Finished opening file');

 v_buffer       :=(   '"'

                            || 'VENDORCUSTOMERID'

                            || '"'

                            || ','

                            || '"'

                            || 'Vendor Site Code'

                            || '"'

                            || ','

                            || '"'

                            || 'Vendor Invoice Number'

                            || '"'

                            || ','

                            || '"'

                            || 'Check/Payment Document'

                            || '"'

                            || ','

                            || '"'

                            || 'Payment Date'

                            || '"'

                            || ','

                            || '"'

                            || 'Check Amount'

                            || '"'

                            || ','

                            || '"'

                            || 'Invoice Amount Paid'

                            || '"'

                            );

                           

      fnd_file.put_line (fnd_file.LOG, 'Preparing to write header record...');

    UTL_FILE.put_line (v_filehandle, v_buffer);

    fnd_file.put_line (fnd_file.LOG, 'Finished writing header record');

   

     OPEN payment_cur;

fnd_file.put_line (fnd_file.LOG, 'Building extract file...');

      LOOP

--    v_len   := length(invoices_cur.inv_amt);

--    v_instr := instr(invoices_cur,'.');

         FETCH payment_cur

         INTO  payment_rec;

         IF payment_cur%NOTFOUND

         THEN

            EXIT;

         END IF;

        

        v_buffer          := (   '"'

                            || payment_rec.segment1

                            || '"'

                            || ','

                            || '"'

                            || payment_rec.vendor_site_code

                            || '"'

                            || ','

                            || '"'

                            || payment_rec.INVOICE_NUM

                            || '"'

                            || ','

                            || '"'

                            || payment_rec.check_number

                            || '"'

                            || ','

                            || '"'

                            || payment_rec.check_date

                            || '"'

                            || ','

                            || '"'

                            || payment_rec.amount

                            || '"'

                            || ','

                            || '"'

                            || payment_rec.PAYMENT_AMOUNT

                            || '"'

                            );

                                       

            UTL_FILE.put_line (v_filehandle, v_buffer);

           

             END LOOP;

            

               fnd_file.put_line (fnd_file.LOG, 'Finished building extract file');

      fnd_file.put_line (fnd_file.LOG, 'Building trailer record...');

   

    end;     

end;

/

Outbound Interface in Oracle Apps

Outbound Interface in Oracle Apps