Supplier Interface Sample Code in oracle apps

After getting the data from client we should Create Staging tables then we have to write an interface program to dump that data from staging tables to interface tables then we have to run standard program Suppliers open interface import to load data from interface tables to Base tables. This is the interface process. So here i am providing Sample code to do the Supplier interface.

CREATE OR REPLACE PACKAGE XXST_AP_SUPP_INT_PKG AS
/**********************************************************************************************************************
* Name            :  Rajashekar
* Purpose        : The Package is Used for Data Interface for Vendors Additions
* C and I Number        :
* Arguments        :
* Script Location     : $XXST/sql
* Notes            :1.This Package Specification defines the Procedure Defined in the Package
* Change History
*       Date            Name        Ver    Description
* ----------------------------------------------------------------------------------------------------------------------
*     27-Mar-2013       Rajashekar  1.0    Created for Data Interface for Vendor Additions
*                                                  from stage table to R/12
* **********************************************************************************************************************/
  gn_processed_rec            NUMBER:=0;  -- Count of no of records processed
  gn_errored_rec              NUMBER:=0;  -- Count of Error records
  gn_uploaded_rec             NUMBER:=0;  -- Count of Uploaded Records
  pn_batch_no                 NUMBER:=0;  -- User given batch no for Global
  count_new_ven_records       NUMBER:=0;  -- Count of Vendors
  count_ins_ven_records       NUMBER:=0;  -- Count of inserted vendors
  count_new_sites_records     NUMBER:=0;  -- Count of New Sites
  count_ins_sites_records     NUMBER:=0;  -- Count of inserted Sites
  count_new_cont_records      NUMBER:=0;  -- Count of New Contacts
  count_ins_cont_records      NUMBER:=0;  -- Count of Inserted Contacts
  count_new_addl_records      NUMBER:=0;  -- Count of Additional information for Localization
  count_ins_addl_records      NUMBER:=0;  -- Count of Inserted information for Localization
  g_pn_batch_no1              VARCHAR2(10);
  -- This is default procedure from this procedure we can call all procedures and It is used to insert Vendors information. 
 PROCEDURE ap_vendor_creation (errBuff  OUT NOCOPY VARCHAR2,
                               retCode  OUT NOCOPY NUMBER,
                               p_batch_no IN VARCHAR2);
  -- This procedure inserts the Sites information.                                         
 PROCEDURE ap_supp_sites_creation(errbuf     OUT VARCHAR2,
                                  retcode    OUT NUMBER,
                                  p_batch_no IN VARCHAR2) ;
  -- This procedure insertes Contacts information                          
 PROCEDURE ap_supp_contacts_creation(errbuf     OUT VARCHAR2,
                                     retcode    OUT NUMBER,
                                     p_batch_no IN VARCHAR2);
  -- This procedure inserts the Localization Tables information
 PROCEDURE ap_localization_data_creation (errBuff  OUT NOCOPY VARCHAR2,
                                          retCode  OUT NOCOPY NUMBER,
                                          p_batch_no IN VARCHAR2);
  -- This procedure updates the Error Information in Staging table.
 PROCEDURE ap_error_update;
 ---- This function to get the field from csv file
 FUNCTION get_field(p_line IN VARCHAR2, p_delimiter IN VARCHAR2, p_field_no IN NUMBER) RETURN VARCHAR2;
 --- This procedure to load the data from csv to staging table
 PROCEDURE ap_supplier_upload(Errbuf OUT VARCHAR2,Retcode  OUT NUMBER,p_file_name IN VARCHAR2,P_LINE_NUM IN NUMBER);
 END XXST_AP_SUPP_INT_PKG;
/




For Staging table creation  Click here.



For Error table creation  Click Here.



for Utility Package creation Click here


For Supplier Interface Package Body CLICK HERE