Outbound Interface using PL/SQL
Follow the steps
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;
/
