AR Customer Interface R12

AR Customer Interface R12

The Customer Interface Data flows to Receivables as shown in the above figure.

To import the Customer information, following Interface Tables have to be populated with mentioned Mandatory columns.

RA_CUSTOMERS_INTERFACE

To import a customer, address, or business purpose, populate the following mandatory columns of RA_CUSTOMERS_INTERFACE:

• ORIG_SYSTEM_CUSTOMER_REF

• INSERT_UPDATE_FLAG

• CUSTOMER_NAME

• CUSTOMER_NUMBER (if you are not using Automatic Customer Numbering)

• CUSTOMER_STATUS

• LAST_UPDATED_BY

• LAST_UPDATE_DATE

• CREATED_BY

• CREATION_DATE

If you are importing an address and a business purpose, you must also populate the following tables:

• PRIMARY_SITE_USE_FLAG (if you are inserting an address)

• LOCATION (if you are not using Automatic Site Numbering)

• SITE_USE_CODE (if you are inserting an address)

• ADDRESS1

Receivable requires that you separate your city, state, and postal codes, whereas your current system may not. To save time, separate these components in your current system before importing customers into Receivables.

• ORIG_SYSTEM_ADDRESS_REF

You must enter values for the columns you reference in your Tax Location Flexfield if you are calculating sales tax and your Address Validation option is set to 'Error.' (You define this option in the System Options window, Tax alternative region.)

• COUNTRY

RA_CUSTOMER_PROFILES_INTERFACE

A customer level profile must exist in RA_CUSTOMER_PROFILES_INTERFACE for new customers and each Bill-To Business Purpose.

• ORIG_SYSTEM_CUSTOMER_REF

• INSERT_UPDATE_FLAG

• CUSTOMER_PROFILE_CLASS_NAME

If you did not pass a value in this column, you must enter values in the following columns:

• COLLECTOR_NAME

• CREDIT_BALANCE_STATEMENTS

• CREDIT_CHECKING

• AUTO_REC_INCL_DISPUTED_FLAG

• DISCOUNT_TERMS

• DUNNING_LETTERS (if 'Y,' you must also enter a value in DUNNING_LETTER_SET_NAME)

• INTEREST_CHARGES (if 'Y,' you must also enter values in INTEREST_PERIOD_DAYS and CHARGE_ON_FINANCE_CHARGE_FLAG

• STATEMENTS (if 'Y,' you must also enter a value in STATEMENT_CYCLE_NAME)

• TOLERANCE

• TAX_PRINTING_OPTION

• OVERRIDE_TERMS

• GROUPING_RULE_NAME

• CREDIT_HOLD

• LAST_UPDATED_BY

• LAST_UPDATE_DATE

• CREATED_BY

• CREATION_DATE

If you are entering a profile for a customer address, you must also enter a Bill-To site in ORIG_SYSTEM_ADDRESS_REF.

RA_CONTACT_PHONES_INTERFACE

To import telephone numbers for customers, addresses, and contacts, populate the following mandatory columns of RA_CONTACT_PHONES_INTERFACE:

• ORIG_SYSTEM_CUSTOMER_REF

• ORIG_SYSTEM_TELEPHONE_REF

• INSERT_UPDATE_FLAG

• TELEPHONE

• TELEPHONE_TYPE

• LAST_UPDATED_BY

• LAST_UPDATE_DATE

• CREATED_BY

• CREATION_DATE

If you are entering a telephone number for an address, you must also enter a value in ORIG_SYSTEM_ADDRESS_REF.

If you are entering a telephone number for a contact, you must also enter a value in ORIG_SYSTEM_CONTACT_REF and CONTACT_LAST_NAME.
RA_CUSTOMER_BANKS_INTERFACE
To import banks for customers and Bill-To business purposes, use the above table.
RA_CUST_PAY_METHOD_INTERFACE
To import payment methods for customers and Bill-To business purposes, use the above table.

--->:====================================================================

--->: SAMPLE TECHNICAL INPUTS TO RUN THE CUSTOMER INTERFACE.

--->:====================================================================

TRUNCATE TABLE ar.ra_customers_interface_all;

INSERT INTO ar.ra_customers_interface_all

  (

    orig_system_customer_ref,

    customer_name,

    orig_system_address_ref,

    address1,

    address2,

    address3,

    address4,

    city,

    county,

    state,

    province,

    country,

    postal_code,

    site_use_code,

    primary_site_use_flag,

    customer_status,

    insert_update_flag,

    last_updated_by,

    last_update_date,

    created_by,

    creation_date,

    customer_number,

    customer_name_phonetic,

    customer_category_code,

    location,

    org_id

  )

  VALUES

  (

    '01-00-01',      --->: orig_system_reference

    'CUSTOMERNAME0', --->: customer_name

    '01-00-01',      --->: orig_system_address_ref, change it if you put another address

    'ADDRESS0',      --->: address1

    NULL,            --->: address2

    NULL,            --->: address3

    NULL,            --->: address4

    'San Mateo',     --->: city

    'San Mateo',     --->: county

    'CA',            --->: state

    NULL,            --->: provice

    'US',            --->: country

    '94401',         --->: postal_code

    'BILL_TO',       --->: SITE USE

    'Y',             --->: primary_site_use_flag

    'A',             --->: customer_staus

    'I',             --->: insert_update_flag

    -1,              --->: last_updated_by

    sysdate,         --->: last_update_date

    -1,              --->: created_by

    sysdate,         --->: creation_date

    NULL,            --->: customer_number if automatic customer number is no

    'CUSTOMERNAME0', --->: customer_name_phonetic

    'CUSTOMER',      --->: customer_category_code

    NULL,            --->: location if autositenumber is no

    204              --->: org_id

  );

--->:====================================================================

--->: INSERT THE CUSTOMER PROFILE INFORMATION

--->:====================================================================

TRUNCATE TABLE ar.ra_customer_profiles_int_all;

INSERT INTO ar.ra_customer_profiles_int_all

  (

    orig_system_customer_ref,

    insert_update_flag,

    customer_profile_class_name,

    credit_hold,

    last_updated_by,

    last_update_date,

    created_by,

    creation_date,

    org_id

  )

  VALUES

  (

    '01-00-01', --->: orig_system_customer_ref

    'I',        --->: insert_update_flag

    'DEFAULT',  --->: Should be valid profile class

    'N',        --->: This can be 'Y','N' not null.

    -1,         --->: last_updated_by

    sysdate,    --->: last_update_date

    -1,         --->: created_by

    sysdate,    --->: created_by

    204         --->: org_id

  );

--->:====================================================================

--->: INSERT THE CUSTOMER PHONES & CONTACT INFORMATION

--->:====================================================================

INSERT

INTO

  ra_contact_phones_int_all

  (

    orig_system_contact_ref,

    orig_system_telephone_ref,

    orig_system_customer_ref,

    orig_system_address_ref,

    insert_update_flag,

    contact_first_name,

    contact_last_name,

    contact_title,

    contact_job_title,

    telephone,

    telephone_extension,

    telephone_type,

    telephone_area_code,

    last_updated_by,

    last_update_date,

    created_by,

    creation_date,

    last_update_login,

    sex_code,

    salutation,

    email_address,

    mail_stop,

    contact_key,

    org_id,

    contact_job_title_code,

    contact_point_type

  )

  VALUES

  (

    '01-00-01',--->:orig_system_contact_ref,

    '01-00-01',--->:orig_system_telephone_ref,

    '01-00-01',--->:orig_system_customer_ref,

    '01-00-01',--->:orig_system_address_ref,

    'I',

    'CUSTOMERFirstNAME0', --->:contact_first_name,

    'CUSTOMERlastNAME0',  --->:contact_last_name,

    NULL,                 --->:contact_title,

    NULL,                 --->:contact_job_title,

    40986341,             --->:telephone,

    6341,                 --->:telephone_extension,

    Land line,            --->:telephone_type,

    91-80,                --->:telephone_area_code,

    fnd_global.user_id,

    SYSDATE,

    fnd_global.user_id,

    SYSDATE,

    fnd_global.login_id,

    Male,                    --->:sex_code,

    NULL,                    --->:salutation,

    Customername@company.com,--->:email_address,

    NULL,                    --->:mail_stop,

    NULL,                    --->:contact_key,

    v_org_id,

    Customer,    --->:contact_job_title_code,

    Email/Phone ,--->:contact_point_type

  );

 commit;

--->:====================================================================

--->: RUN CUSTOMER INTERFACE PROGRAM FROM THE APPLICATION.

--->:====================================================================

V_REQUEST_ID := APPS.FND_REQUEST.SUBMIT_REQUEST

                                            ( 'AR'

                                              , 'RACUST'

                                              , ''

                                              , ''

                                              , FALSE

                                              , P_CREATE_RECIPROCAL_FLAG

                                              , P_ORG_ID

                                              , CHR(0)--->: End of parameters

                                            );

--->:====================================================================
 

Customer Interface transfers customer data from the interface tables into the following tables:

• AR_CUSTOMER_PROFILES

• AR_CUSTOMER_PROFILE_AMOUNTS

• RA_ADDRESSES

• RA_CONTACTS

• RA_CUSTOMERS

• RA_CUSTOMER_RELATIONSHIPS

• RA_CUST_RECEIPT_METHODS

• RA_PHONES

• RA_SITE_USES

• AP_BANK_ACCOUNT_USES

• AP_BANK_ACCOUNTS

• AP_BANK_BRANCHES

The Customer Interface program will not allow updates to the following tables:

• RA_SITE_USES

• RA_CUSTOMER_RELATIONSHIPS

• RA_CUST_RECEIPT_METHODS

• AP_BANK_BRANCHES

• AP_BANK_ACCOUNTS

• AP_BANK_ACCOUNT_USES

Caution: The Customer Interface Transfer Report will not display errors for records attempting to update these tables; the records will simply not be processed.

GL Interface

GL Interface

Complete GL Interface

 Pre-Requisites:
    --------------
      a) set of books should be defined
      b) Current Conversion rates and accounting periods need to be defines
      c) Source and Category Name Should be defined


    Interface Tables:
    ----------------
      GL_INTERFACE
  
    Base Tables:
    -----------
       GL_JE_HEADERS
       GL_JE_LINES
       GL_JE_BATCHES


 Standard Program:
 Go to General Ledger Vision Operations(U.S.A)
   Run =>Import
 Here Give the Source name and Save.
While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there...........


   Validation Columns:
   ------------------
       Source       period_name   currency_code   set_of_books_id
       je_source    je_catregory  accounting_date entered_dr , entered_cr
       accounted_cr accounted_dr  encumberance_type_id 


    Source = 'NEW'
    period need to be open status in  gl_period_statuses
    souce_name defined in gl_je_source table
    category_name defines  gl_je_Category
    currency available in fnd_Currencies
    accounted_cr and accounted_dr total should be same.

Control file for GL_Interface:

LOAD DATA

INFILE *

TRUNCATE INTO TABLE GL_INTERFACE_TEMP

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(STATUS,

 SET_OF_BOOKS_ID,

 ACCOUNTING_DATE,

 CURRENCY_CODE,

 DATE_CREATED,

 CREATED_BY,

 ACTUAL_FLAG,

 USER_JE_CATEGORY_NAME,

 USER_JE_SOURCE_NAME,

 SEGMENT1,

 SEGMENT2,

 SEGMENT3,

 SEGMENT4,

 SEGMENT5,

 ENTERED_DR,

 ENTERED_CR,  

 ACCOUNTED_DR,

 ACCOUNTED_CR,

 GROUP_ID)

BEGINDATA

NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11

NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11

Script

sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl'    log='/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log'

exit 0

GL Interface Package:

CREATE OR REPLACE package body APPS.xx_gl_int_pkg

is

procedure dis_log(p_msg in varchar2)

is 

begin

fnd_file.put_line(fnd_file.log,p_msg);

end;

procedure main(errbuf out varchar2,

               retcode out varchar2

               )

 is

 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;

 v_gl_int    gl_interface%rowtype;

 v_process_flag    varchar2(10);

 v_error_msg   varchar2(100);

 v_tot_err_msg   varchar2(1000);

 begin

  

   dis_log('before entering the loop');

   

  

 for i in c1 loop

                v_error_msg :=null;

                v_process_flag:='S';

                v_tot_err_msg:=null;

                v_gl_int:=null;

              --currency_code validation

                begin

                select  currency_code into v_gl_int.currency_code

                                      from fnd_currencies

                                     where currency_code=i.currency_code;

                 exception

                 when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid Currency Code =>'||i.currency_code;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                 when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at Currency Code =>'||i.currency_code;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                end;      

                

                

                --user_je_source_name validation

                

                begin

                

                  select user_je_source_name into v_gl_int.user_je_source_name

                                             from gl_je_sources

                                            where user_je_source_name=i.user_je_source_name;

                  exception

                 when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid Sourec Name =>'||i.user_je_source_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                 when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at Sourec Name =>'||i.user_je_source_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                end;      

                

                --category_name  validation

                begin

                     select user_je_category_name into v_gl_int.user_je_category_name

                     from gl_je_categories

                     where user_je_category_name=i.user_je_category_name;

                  exception

                  when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid category_name =>'||i.user_je_category_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                  when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at category_name =>'||i.user_je_category_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;    

                  

                end;

                

                 --user id validation 

                 

                begin

                     select user_id into v_gl_int.created_by from fnd_user

                                   where  user_id = i.created_by;

                  exception

                  when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid user id =>'||i.created_by;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                  when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at user id =>'||i.created_by;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ; 

                    

                end;

                

                 -- set of books id validation

                 

                begin

                 

                      SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id 

                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;

                   exception

                  when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid set of books id =>'||i.set_of_books_id;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                  when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception atset of books id =>'||i.set_of_books_id;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ; 

                end;

              

                         v_gl_int.status                    :=i.status;

                        -- v_gl_int.set_of_books_id           :=i.set_of_books_id;

                         v_gl_int.accounting_date           :=i.accounting_date;

                        -- v_gl_int.currency_code             :=i.currency_code;

                         v_gl_int.date_created              :=i.date_created;

                         --v_gl_int.created_by                :=i.created_by;

                         v_gl_int.actual_flag               :=i.actual_flag ;

                         --v_gl_int.user_je_category_name     :=i.user_je_category_name;

                        --v_gl_int.user_je_source_name       :=i.user_je_source_name;

                         v_gl_int.segment1                  :=i.segment1;

                         v_gl_int.segment2                  :=i.segment2;

                         v_gl_int.segment3                  :=i.segment3;

                         v_gl_int.segment4                  :=i.segment4;

                         v_gl_int.segment5                  :=i.segment5 ;

                         v_gl_int.entered_dr                :=i.entered_dr;

                         v_gl_int.entered_cr                :=i.entered_cr;

                         v_gl_int.accounted_dr               :=i.accounted_dr;

                         v_gl_int.accounted_cr              :=i.accounted_cr;

                         v_gl_int.group_id                  :=i.group_id;

                         

               

                 

                 dis_log('before inserting the loop');   

                 

                   if v_process_flag = 'S' then      

                

                    insert into gl_interface values v_gl_int;

                 

                   end if;

           update GL_INTERFACE_TEMP set process_flag=v_process_flag,

                                           error_message=v_tot_err_msg

                       where rowid=i.row_id;

                  

                 dis_log('after inserting the loop');      

 end loop;

 exception

 when others then

 dis_log('exception occured at main loop');

 end main;

 end xx_gl_int_pkg;