Oracle R12 Customer Bank Account Conversion

1) API to create Bank

 l_extbank_rec.object_version_number := 1.0;
          l_extbank_rec.bank_name             := rec_cust_bank.bank_name;
          l_extbank_rec.bank_number           := rec_cust_bank.bank_number;
          l_extbank_rec.country_code          := rec_cust_bank.country_code;
          l_extbank_rec.description           := 'Created via API';
       
      
       
          IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK
                    (p_api_version        => 1.0,
                     p_init_msg_list      => fnd_api.g_true,
                     p_ext_bank_rec       => l_extbank_rec,
                     x_bank_id            => x_bank_id,
                     x_return_status      => x_return_status,
                     x_msg_count          => x_msg_count,
                     x_msg_data           => x_msg_data,
                     x_response           => x_response
                    );

2. API to Create Bank Branch

 l_ext_bank_branch_rec.bank_party_id := x_bank_id ;
          l_ext_bank_branch_rec.bch_object_version_number := 1.0;
          l_ext_bank_branch_rec.typ_object_version_number := 1.0;
          l_ext_bank_branch_rec.rfc_object_version_number := 1.0;
          l_ext_bank_branch_rec.eft_object_version_number := 1.0;                
          l_ext_bank_branch_rec.branch_name   := rec_cust_bank.branch_name;
          l_ext_bank_branch_rec.branch_number := rec_cust_bank.branch_number;
          l_ext_bank_branch_rec.branch_type   := rec_cust_bank.branch_type;
          l_ext_bank_branch_rec.bic           := rec_cust_bank.bic;
                              
                                    
          IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH
                    (p_api_version              => 1.0,
                     p_init_msg_list            => fnd_api.g_true,
                     p_ext_bank_branch_rec      => l_ext_bank_branch_rec,
                     x_branch_id                => x_branch_id,
                     x_return_status            => x_return_status,
                     x_msg_count                => x_msg_count,
                     x_msg_data                 => x_msg_data,
                     x_response                 => x_response
                    );

3. API to Create Branch Location


  p_location_rec.country  := rec_cust_bank.country_code;
        p_location_rec.address1 := rec_cust_bank.address1;
        p_location_rec.city        := rec_cust_bank.city;
        p_location_rec.postal_code := rec_cust_bank.postal_code;
        p_location_rec.state       := rec_cust_bank.state;
        p_location_rec.province    := rec_cust_bank.province;
        p_location_rec.created_by_module := 'CE';      
 
        hz_location_v2pub.create_location( p_init_msg_list    => fnd_api.g_true
                                         , p_location_rec     => p_location_rec
                                         , x_location_id      => x_location_id
                                         , x_return_status    => x_return_status
                                         , x_msg_count        => x_msg_count
                                         , x_msg_data         => x_msg_data );



4. API to Create Party Site to link Location and Branch id   

  p_party_site_rec.party_id                 := x_branch_id;
        p_party_site_rec.location_id              := x_location_id;
        p_party_site_rec.identifying_address_flag := 'N';
        p_party_site_rec.created_by_module        := 'CE';
             
        DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_site_v2pub.create_party_site');
             
        HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE(
          p_init_msg_list     => FND_API.G_TRUE,
          p_party_site_rec    => p_party_site_rec,
          x_party_site_id     => x_party_site_id,
          x_party_site_number => x_party_site_number,
          x_return_status     => x_return_status,
          x_msg_count         => x_msg_count,
          x_msg_data          => x_msg_data);           

       5.  API to Create Bank Account


  l_bank_acct_rec.object_version_number    := 1.0;
        l_bank_acct_rec.branch_id                := x_branch_id ;
        l_bank_acct_rec.bank_id                  := x_bank_id;
        l_bank_acct_rec.acct_owner_party_id      := rec_cust_bank.acct_owner_party_id;
        l_bank_acct_rec.bank_account_name        := rec_cust_bank.bank_account_name;
        l_bank_acct_rec.bank_account_num         := rec_cust_bank.bank_account_num;
        l_bank_acct_rec.iban                     := rec_cust_bank.iban;
        l_bank_acct_rec.country_code             := rec_cust_bank.country_code;
        l_bank_acct_rec.currency                 := rec_cust_bank.acct_currency;
        l_bank_acct_rec.foreign_payment_use_flag := rec_cust_bank.foreign_payment_use_flag;
        l_bank_acct_rec.start_date               := SYSDATE;
        l_bank_acct_rec.status                   := 'A';


  IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT ( p_api_version       => 1.0,
                                                      p_init_msg_list     => FND_API.G_TRUE,
                                                      p_ext_bank_acct_rec => l_bank_acct_rec,
                                                      x_acct_id           => l_acct,
                                                      x_return_status     => x_return_status,
                                                      x_msg_count         => x_msg_count,
                                                      x_msg_data          => x_msg_data,
                                                      x_response          => x_response );


6. API to create  joint account if more than one party own the Bank Account

 IBY_EXT_BANKACCT_PUB.CHECK_BANK_ACCT_OWNER (
              p_api_version        => 1.0,
              p_init_msg_list       => fnd_api.g_true,
              p_bank_acct_id        => NVL(l_acct_id,l_acct),
              p_acct_owner_party_id => rec_cust_bank.acct_owner_party_id,
              x_return_status       => x_return_status,
              x_msg_count           => x_msg_count,
              x_msg_data            => x_msg_data,
              x_response            => x_response
              );
        IF x_return_status = fnd_api.g_ret_sts_error THEN
          fnd_file.put_line(fnd_file.LOG,'Current Party is not an owner');     
             
          IBY_EXT_BANKACCT_PUB.ADD_JOINT_ACCOUNT_OWNER(p_api_version         => 1.0,
                                                       p_init_msg_list       => fnd_api.g_true,
                                                       p_bank_account_id     => NVL(l_acct_id,l_acct),
                                                       p_acct_owner_party_id => rec_cust_bank.acct_owner_party_id,
                                                       x_joint_acct_owner_id => x_joint_owner_id,
                                                       x_return_status       => x_return_status,
                                                       x_msg_count           => x_msg_count,
                                                       x_msg_data            => x_msg_data,
                                                       x_response            => x_response);    


API to set Payer Instruction

  l_instrument.Instrument_Id      := NVL(l_acct_id,l_acct);
      l_instrument.Instrument_Type    := NVL(rec_cust_bank.Instrument_Type,'BANKACCOUNT'); --'BANKACCOUNT';
      l_assignment_attribs.priority   := 1;
      l_assignment_attribs.start_date := TO_DATE('01-JAN-2000');
      l_assignment_attribs.Instrument := l_instrument;
      fnd_file.put_line(fnd_file.LOG, 'Before calling api Set_Payer_Instr_Assignment');
             
      IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment ( p_api_version             => 1.0,
                                                        p_init_msg_list       => FND_API.G_TRUE,
                                                        p_commit              => FND_API.G_TRUE,
                                                        x_return_status       => x_return_status,
                                                        x_msg_count           => x_msg_count,
                                                        x_msg_data            => x_msg_data,
                                                        p_payer               => l_payer,
                                                        p_assignment_attribs  => l_assignment_attribs,
                                                        x_assign_id           => l_assign_id,
                                                        x_response            => x_response);

7. API to Create debit authorization
Debit cards: The authorization process validates the debit card information and debits the third-party payer's bank account immediately. The first party payee may receive the funds at this time. Some payment systems require a separate settlement step to move funds to the first party payee

  BEGIN
           
            SELECT  hcsua.site_use_id,
                    hca.account_number
            INTO    l_site_use_id,
                    l_cust_acct_number
            FROM    hz_cust_accounts hca,
                    hz_cust_acct_sites_all hcasa,
                    hz_cust_site_uses_all hcsua
            WHERE   hca.orig_system_reference   = rec_cust_bank.orig_system_customer_ref
            AND     hcasa.cust_account_id       = hca.cust_account_id
            AND     hcasa.orig_system_reference = rec_cust_bank.orig_system_address_ref
            AND     hcasa.cust_acct_site_id     = hcsua.cust_acct_site_id
            AND     hcsua.orig_system_reference = rec_cust_bank.orig_system_address_ref
            AND     hcsua.site_use_code         = 'BILL_TO';
        EXCEPTION
            WHEN OTHERS THEN
                l_site_use_id := NULL;
                l_cust_acct_number :=NULL;
                l_err_flag    := 'Y';
                fnd_file.put_line(fnd_file.log,'Error While getting bill_to site use id in Customer Direct Debit Authorization.');
        END;
       
        fnd_file.put_line(fnd_file.log,'l_site_use_id : '||l_site_use_id);
       
        BEGIN
            SELECT DISTINCT ood.legal_entity
            INTO   l_legal_entity_id
            FROM  org_organization_definitions ood,
                  hr_operating_units           hou
            WHERE  1=1
            AND   ood.operating_unit = hou.organization_id
            AND   UPPER(hou.name) = UPPER(rec_cust_bank.operating_unit_name) ;
           
        EXCEPTION
            WHEN OTHERS THEN
                l_legal_entity_id := NULL;
                l_err_flag    := 'Y';
                fnd_file.put_line(fnd_file.log,'Error While getting Legal Entity Id');
        END;
       
        IF l_legal_entity_id IS NOT NULL THEN
            BEGIN
           
                SELECT  entityprofileeo.legal_entity_id,
                        entityprofileeo.name,
                        reg.registration_number
                INTO    l_legal_entity_id,
                        l_le_name,                    
                        l_le_registration_no
                FROM    xle_entity_profiles entityprofileeo,
                        hz_geographies hg,
                        hz_parties hp,
                        xle_registrations reg,
                        hr_locations loc,
                        xle_jurisdictions_b jur,
                        fnd_territories_vl ft,
                        xle_lookups lkp
                WHERE   1=1
                AND     hg.geography_id   = entityprofileeo.geography_id
                AND     hp.party_id         = entityprofileeo.party_id
                AND     reg.source_id       = entityprofileeo.legal_entity_id
                AND     reg.source_table    = 'XLE_ENTITY_PROFILES'
                AND     reg.location_id     = loc.location_id
                AND     reg.jurisdiction_id = jur.jurisdiction_id
                AND     ft.territory_code   = loc.country
                AND     lkp.lookup_type     = 'XLE_YES_NO'
                AND     lkp.lookup_code     = entityprofileeo.transacting_entity_flag
                AND     entityprofileeo.legal_entity_id = l_legal_entity_id;
            EXCEPTION
                WHEN OTHERS THEN
                    l_legal_entity_id       := NULL;
                    l_le_name               := NULL;
                    l_le_registration_no    := NULL;
                    l_err_flag              := 'Y';
                    fnd_file.put_line(fnd_file.log,'Error While getting Legal Entity Details');
            END;
        END IF;  --- end if l_legal_entity_id is not null
       
        BEGIN
            SELECT  COUNT(*)
            INTO    l_debit_auth_cnt
            FROM    iby_debit_authorizations ida
            WHERE   1=1
            AND     ida.cust_addr_id                  = l_site_use_id
            AND     ida.external_bank_account_use_id  = l_assign_id;
        EXCEPTION
            WHEN OTHERS THEN
                l_debit_auth_cnt := NULL;
                fnd_file.put_line(fnd_file.log,'Error While Checking exiting Debit Authorization');   
        END;   
       
        BEGIN
            SELECT IBY_DEBIT_AUTHORIZATIONS_S.NEXTVAL
            INTO   l_debit_auth_id
            FROM   DUAL;
        EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Error While Getting Max debit Auth Id');
            l_debit_auth_id := NULL;
            l_err_flag      := 'Y';
        END;
       
       
        IF l_err_flag = 'N' AND l_debit_auth_cnt = 0 THEN
           
           
            BEGIN
            iby_fndcpt_setup_pub.Create_Debit_Authorization
                                                          ( p_debit_auth_id              => l_debit_auth_id,
                                                            p_bank_use_id                => l_assign_id,
                                                            p_auth_ref_number            => l_cust_acct_number,
                                                            p_initial_debit_auth_id      => l_debit_auth_id,
                                                            p_auth_rev_number            => 1,
                                                            p_payment_code               => 'REC',
                                                            p_amend_readon_code          => NULL,
                                                            p_auth_sign_date             => SYSDATE,
                                                            p_auth_cancel_date           => NULL,
                                                            p_debit_auth_method          => NULL,
                                                            p_pre_notif_flag             => 'N',
                                                            p_creditor_id                => l_legal_entity_id,                   --- IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LEGAL_ENTITY_ID%TYPE,
                                                            p_creditor_name              => l_le_name, --IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LE_NAME%TYPE,
                                                            p_debit_auth_begin           => SYSDATE,                 --IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_BEGIN%TYPE,
                                                            p_cust_addr_id               =>  l_site_use_id,            --IN IBY_DEBIT_AUTHORIZATIONS.CUST_ADDR_ID%TYPE,
                                                            p_debit_auth_flag            => 'Y',                     --IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_FLAG%TYPE,
                                                            p_debit_auth_ref             => NULL,                    --IN  IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_REFERENCE%TYPE,
                                                            p_cust_id_code               => NULL,             --IN IBY_DEBIT_AUTHORIZATIONS.CUST_IDENTIFICATION_CODE%TYPE,
                                                            p_creditor_identifer         => 'ES75ZZZB84265172',       --IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_IDENTIFIER%TYPE,
                                                            p_debit_auth_end             => NULL,                    --IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_END%TYPE,
                                                            p_mandate_file               => NULL,                    --IN IBY_DEBIT_AUTHORIZATIONS.MANDATE_FILE%TYPE,
                                                            x_result                     => x_msg_count             --OUT NOCOPY NUMBER
                                                            );
                COMMIT;                                           
            EXCEPTION
                WHEN OTHERS THEN
                    l_err_flag := 'Y';
                    fnd_file.put_line(fnd_file.log,'Error While Creating Direct Debit Authorization');
            END;
       
        END IF;
       
   
    END IF ; --- END IF MAIN DEBIT AUTHORIZATION  

Oracle APPS Customer Tables

SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME = '';





---For Customer Accounts:


SELECT * FROM HZ_CUST_ACCOUNTS hca

WHERE hca.ACCOUNT_NAME = '' ;




--Account Number:

--Account Id:

--Customer Accounts Sites:



SELECT * FROM HZ_CUST_ACCT_SITES_ALL hcsa

WHERE hcsa.CUST_ACCOUNT_ID = '' ;




--Customer Account Site Id: 10554 - TESTCUSTS_1_2

--10558 - TESTCUSTS_1_1



SELECT * FROM HZ_CUST_SITE_USES_ALL ;

SELECT * FROM HZ_PARTY_SITES

WHERE PARTY_ID = '' -- Check Party Site Name




--There are two Id need to get, one is subject id and other is object id

--1.Subject Id is related to Organization that is customer Party id here the party id is



SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME LIKE 'TESTCUST_1'



　

　
SELECT * FROM HZ_RELATIONSHIPS hr

WHERE hr.SUBJECT_ID = 'Person Party id'

and hr.OBJECT_ID = 'Org Party_id'



　

　

SELECT * FROM HZ_ORG_CONTACTS hoc

WHERE hoc.PARTY_RELATIONSHIP_ID = '' -- Relatioship Id




  SELECT * FROM HZ_CONTACT_POINTS hp --HZ_CUST_CONTACT_POINTS hcp

WHERE hp.OWNER_TABLE_ID = '' ; --Party Site Id of the Contact



　

SELECT * FROM HZ_CUST_ACCOUNT_ROLES hcr

WHERE hcr.CUST_ACCT_SITE_ID ='' ;

Customer Conversion Oracle R12

Introduction to Oracle Trading Community Architecture
Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them.
This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information. See: Using Oracle Trading Community Architecture.
The key entities in TCA include:

• Parties: Entities of type Person or Organization that can enter into business relationships. Parties can also be of type Relationship. For example, Joe as himself is a party of type Person, but Joe as a contact for Vision Corporation is a party of type Relationship. Every party in the TCA Registry has a unique Registry ID.
  TCA includes an extensive variety of information for parties, for example party name, addresses, contacts, and contact points. Joe as a person can have a personal phone number that differs from the phone number for the relationship of Joe as a contact.
• Party sites: Addresses that parties use for specific purposes, or uses.
• Customers: Parties with whom you have a selling relationship.
• Customer accounts: The business relationships between you and your customers.
• Customer account sites: Party sites used in the context of customer accounts for specific purposes, or uses, for example ship-to and bill-to account sites.
• Locations: Geospatial points, usually defined by an address.
• Contacts: People who have a contact or employment relationship with an organization or person.
• Contact points: Means of contact, for example, phone and e-mail address.

TCA also includes conceptual functionality that helps you manage and understand your trading community. For example, you can use relationships to model the roles that parties play with respect to one another, and classifications to classify entities.

1. API  to create Organization/Party

 x_return_status                      := fnd_api.g_ret_sts_success;
          p_organization_rec.organization_name := cust_api_tbl(i).party_name;
          p_organization_rec.party_rec.category_code         := UPPER (cust_api_tbl(i).customer_prospect_code);
          p_organization_rec.party_rec.orig_system_reference := cust_api_tbl(i).orig_system_party_ref;
          p_organization_rec.party_rec.attribute_category    := cust_api_tbl(i).customer_attribute_category;
     p_organization_rec.created_by_module               := G_CREATED_BY_MODULE;
hz_party_v2pub.create_organization( p_init_msg_list           => fnd_api.g_true
                        , p_organization_rec        => p_organization_rec
                        , x_return_status           => x_return_status
                        , x_msg_count               => x_msg_count
                        , x_msg_data                => x_msg_data
                        , x_party_id                => x_party_id
                        , x_party_number            => x_party_number
                        , x_profile_id              => x_org_profile_id );

2. API  to create Customer Account

 p_customer_profile_rec.profile_class_id            := l_cust_profile_api.profile_class_id;
          p_customer_profile_rec.account_status              := l_cust_profile_api.account_status;
          p_customer_profile_rec.auto_rec_incl_disputed_flag := l_cust_profile_api.auto_rec_incl_disputed_flag;
          p_customer_profile_rec.credit_balance_statements   := l_cust_profile_api.credit_balance_statements;
          p_customer_profile_rec.credit_checking             := l_cust_profile_api.credit_checking;
          p_customer_profile_rec.credit_hold                 := l_cust_profile_api.credit_hold;
          p_customer_profile_rec.discount_terms              := l_cust_profile_api.discount_terms;
          p_customer_profile_rec.disputed_transactions_flag  := l_cust_profile_api.disputed_transactions_flag;
          p_customer_profile_rec.dunning_letters             := l_cust_profile_api.dunning_letters;
          p_customer_profile_rec.override_terms              := l_cust_profile_api.override_terms;
          p_customer_profile_rec.percent_collectable         := l_cust_profile_api.percent_collectable;
          p_customer_profile_rec.statement_cycle_id          := l_cust_profile_api.statement_cycle_id;
          p_customer_profile_rec.send_statements             := l_cust_profile_api.statements;
          p_customer_profile_rec.tax_printing_option         := l_cust_profile_api.tax_printing_option;
          p_customer_profile_rec.standard_terms              := l_cust_profile_api.standard_term_id;
          p_cust_account_rec.account_number        := cust_api_tbl(i).customer_number;         
          p_cust_account_rec.account_name          := cust_api_tbl(i).customer_name;
          p_cust_account_rec.orig_system_reference := cust_api_tbl(i).orig_system_customer_ref;
          p_cust_account_rec.sales_channel_code    := cust_api_tbl(i).sales_channel_code;
          p_cust_account_rec.freight_term          := cust_api_tbl(i).freight_term_code;
          p_cust_account_rec.price_list_id         := cust_api_tbl(i).price_list_id;
          p_cust_account_rec.date_type_preference  := upper(cust_api_tbl(i).request_date_type);
          p_cust_account_rec.ship_via              := cust_api_tbl(i).cust_ship_via_code;
          p_cust_account_rec.status                := cust_api_tbl(i).customer_status;
          p_cust_account_rec.customer_class_code   := cust_api_tbl(i).customer_class_code; --- 'INDIRECT';
          p_cust_account_rec.customer_type         := cust_api_tbl(i).customer_type;
          p_cust_account_rec.created_by_module     := G_CREATED_BY_MODULE;
          p_organization_rec.party_rec.party_id    := cust_api_tbl(i).party_id;
          p_organization_rec.created_by_module     := G_CREATED_BY_MODULE;
          IF NVL(cust_api_tbl(i).status_flag,'I')  <> 'C' THEN
              hz_cust_account_v2pub.create_cust_account ( p_init_msg_list           => fnd_api.g_true
                            , p_cust_account_rec        => p_cust_account_rec
                            , p_organization_rec        => p_organization_rec
                            , p_customer_profile_rec    => p_customer_profile_rec
                            , p_create_profile_amt      => fnd_api.g_false
                            , x_cust_account_id         => x_cust_account_id
                            , x_account_number          => x_account_number
                            , x_party_id                => x_party_id
                            , x_party_number            => x_party_number
                            , x_profile_id              => x_profile_id
                            , x_return_status           => x_return_status
                            , x_msg_count               => x_msg_count
                            , x_msg_data                => x_msg_data );

3. API to create Customer Profile

 p_cust_profile_amt_rec_type.currency_code        := l_cust_profile_api.currency_code;
          p_cust_profile_amt_rec_type.overall_credit_limit := l_cust_profile_api.overall_credit_limit;
          p_cust_profile_amt_rec_type.trx_credit_limit     := l_cust_profile_api.trx_credit_limit;
 
          hz_customer_profile_v2pub.create_cust_profile_amt ( p_init_msg_list            => fnd_api.g_true
                                                            , p_check_foreign_key        => x_cust_account_id
                                                            , p_cust_profile_amt_rec     => p_cust_profile_amt_rec_type
                                                            , x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id
                                                            , x_return_status            => x_return_status
                                                            , x_msg_count                => x_msg_count
                                                            , x_msg_data                 => x_msg_data ) ;


4. API to create HZ location

 p_location_rec.country     := cust_site_api_tbl(i).country_code;
 
         p_location_rec.city        := cust_site_api_tbl(i).city;
          p_location_rec.postal_code := NVL(get_lookup_county_postcode ( cust_site_api_tbl(i).postal_code,'POSTAL_CODE_MAPPING'),cust_site_api_tbl(i).postal_code);
          p_location_rec.state       := cust_site_api_tbl(i).state;
          p_location_rec.province    := cust_site_api_tbl(i).province;
          p_location_rec.language    := cust_site_api_tbl(i).language;   
          p_location_rec.county      := NVL(get_lookup_county_postcode ( cust_site_api_tbl(i).county,'COUNTY_MAPPING'),cust_site_api_tbl(i).county);
          p_location_rec.created_by_module := G_CREATED_BY_MODULE;
        --p_location_rec.sales_tax_inside_city_limits := cust_site_api_tbl(i).sales_tax_inside_city_limits;
          hz_location_v2pub.create_location( p_init_msg_list    => fnd_api.g_true
                                           , p_location_rec     => p_location_rec
                                           , x_location_id      => x_location_id
                                           , x_return_status    => x_return_status
                                           , x_msg_count        => x_msg_count
                                           , x_msg_data         => x_msg_data );

5. API to Create Party Site
 p_party_site_rec.party_id    := cust_site_api_tbl(i).party_id;
              p_party_site_rec.location_id := cust_site_api_tbl(i).location_id;
              p_party_site_rec.language    := cust_site_api_tbl(i).language;    
              p_party_site_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
              p_party_site_rec.created_by_module := G_CREATED_BY_MODULE;
             p_party_site_rec.identifying_address_flag := cust_site_api_tbl(i).identifying_address_flag;
             p_party_site_rec.party_site_number := cust_site_api_tbl(i).party_site_number;
              hz_party_site_v2pub.create_party_site( p_init_msg_list        => fnd_api.g_true
                         , p_party_site_rec     => p_party_site_rec
                         , x_party_site_id      => x_party_site_id
                         , x_party_site_number  => x_party_site_number
                         , x_return_status      => x_return_status
                         , x_msg_count          => x_msg_count
                         , x_msg_data           => x_msg_data );


6. API to create party site use

 p_party_site_use_rec.site_use_type    := cust_site_api_tbl(i).site_use_code;
              p_party_site_use_rec.party_site_id    := cust_site_api_tbl(i).party_site_id;
              p_party_site_use_rec.primary_per_type := cust_site_api_tbl(i).primary_site_use_flag;
              p_party_site_use_rec.created_by_module := G_CREATED_BY_MODULE;
              p_party_site_use_rec.status            := cust_site_api_tbl(i).cust_site_uses_status;
              hz_party_site_v2pub.create_party_site_use( p_init_msg_list        => fnd_api.g_true
                            , p_party_site_use_rec      => p_party_site_use_rec
                            , x_party_site_use_id       => x_party_site_use_id
                            , x_return_status       => x_return_status
                            , x_msg_count           => x_msg_count
                            , x_msg_data            => x_msg_data );


7. API to create Customer Account Site



  p_cust_acct_site_rec.org_id          := cust_site_api_tbl(i).org_id;
              p_cust_acct_site_rec.cust_account_id := cust_site_api_tbl(i).cust_account_id;
              p_cust_acct_site_rec.party_site_id   := cust_site_api_tbl(i).party_site_id;
              p_cust_acct_site_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
              p_cust_acct_site_rec.attribute_category    := cust_site_api_tbl(i).address_attribute_category;
              p_cust_acct_site_rec.attribute1            := cust_site_api_tbl(i).address_attribute1;
              --p_cust_acct_site_rec.language              := cust_site_api_tbl(i).language; --language column is obsolete so no mapping required
              p_cust_acct_site_rec.ece_tp_location_code  := cust_site_api_tbl(i).edi_location;
            --p_cust_acct_site_rec.status                := cust_site_api_tbl(i).cust_acct_sites_status;
            --p_cust_acct_site_rec.territory             := cust_site_api_tbl(i).territory; -- Business asked to ignore this on 16th Sep 2013
              p_cust_acct_site_rec.created_by_module     := G_CREATED_BY_MODULE;
              hz_cust_account_site_v2pub.create_cust_acct_site( p_init_msg_list             => fnd_api.g_true
                                  , p_cust_acct_site_rec    => p_cust_acct_site_rec
                                  , x_cust_acct_site_id     => x_cust_acct_site_id
                                  , x_return_status         => x_return_status
                                  , x_msg_count             => x_msg_count
                                  , x_msg_data              => x_msg_data );


8. API to Create Cust Account Site Use


 p_su_customer_profile_rec.profile_class_id   := l_cust_site_profile_api.profile_class_id;
              p_su_customer_profile_rec.account_status     := l_cust_site_profile_api.account_status;
              p_su_customer_profile_rec.credit_balance_statements  := l_cust_site_profile_api.credit_balance_statements;
              p_su_customer_profile_rec.credit_checking            := l_cust_site_profile_api.credit_checking;
              p_su_customer_profile_rec.credit_hold                := l_cust_site_profile_api.credit_hold;
              p_su_customer_profile_rec.discount_terms             := l_cust_site_profile_api.discount_terms;
              p_su_customer_profile_rec.disputed_transactions_flag := l_cust_site_profile_api.disputed_transactions_flag;
              p_su_customer_profile_rec.dunning_letters            := l_cust_site_profile_api.dunning_letters;
              p_su_customer_profile_rec.override_terms             := l_cust_site_profile_api.override_terms;
              p_su_customer_profile_rec.percent_collectable        := l_cust_site_profile_api.percent_collectable;
              p_su_customer_profile_rec.statement_cycle_id         := l_cust_site_profile_api.statement_cycle_id;
              p_su_customer_profile_rec.send_statements            := l_cust_site_profile_api.statements;
              p_su_customer_profile_rec.tax_printing_option        := l_cust_site_profile_api.tax_printing_option;
              p_su_customer_profile_rec.standard_terms             := l_cust_site_profile_api.standard_term_id;
              p_cust_site_use_rec.tax_reference             := cust_site_api_tbl(i).cust_tax_reference;
              p_cust_site_use_rec.org_id                := cust_site_api_tbl(i).org_id;
              p_cust_site_use_rec.cust_acct_site_id     := cust_site_api_tbl(i).cust_acct_site_id;
              p_cust_site_use_rec.site_use_code         := cust_site_api_tbl(i).site_use_code;
              p_cust_site_use_rec.primary_flag          := cust_site_api_tbl(i).primary_site_use_flag;
              p_cust_site_use_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
              p_cust_site_use_rec.freight_term          := cust_site_api_tbl(i).freight_term_code;
              p_cust_site_use_rec.price_list_id         := l_site_price_list_id; ---cust_site_api_tbl(i).price_list_id; Added By Sachin On 11-SEP-2014
              p_cust_site_use_rec.demand_class_code     := cust_site_api_tbl(i).demand_class_code;
              p_cust_site_use_rec.status                := 'A' ;
              p_cust_site_use_rec.LOCATION              := cust_site_api_tbl(i).LOCATION ;
              p_cust_site_use_rec.territory_id          := cust_site_api_tbl(i).territory_id ;
              p_cust_site_use_rec.warehouse_id          := cust_site_api_tbl(i).warehouse_id ;  
              p_cust_site_use_rec.primary_salesrep_id   := cust_site_api_tbl(i).salesrep_id ;        
     
              p_cust_site_use_rec.created_by_module     := G_CREATED_BY_MODULE;
              hz_cust_account_site_v2pub.create_cust_site_use( p_init_msg_list          => fnd_api.g_true
                             , p_cust_site_use_rec          => p_cust_site_use_rec
                             , p_customer_profile_rec       => p_su_customer_profile_rec
                             , p_create_profile             => fnd_api.g_true
                             , p_create_profile_amt         => fnd_api.g_false
                             , x_site_use_id                => x_site_use_id
                             , x_return_status              => x_return_status
                             , x_msg_count                  => x_msg_count
                             , x_msg_data                   => x_msg_data );

9.   API to create Customer Profile


p_cust_profile_amt_rec_type.currency_code            := l_cust_site_profile_api.currency_code;
              p_cust_profile_amt_rec_type.overall_credit_limit     := l_cust_site_profile_api.overall_credit_limit;
              p_cust_profile_amt_rec_type.trx_credit_limit         := l_cust_site_profile_api.trx_credit_limit;
              p_cust_profile_amt_rec_type.attribute4               := l_cust_site_profile_api.amount_attribute4;
              p_cust_profile_amt_rec_type.cust_account_profile_id  := l_cust_account_profile_id;
              p_cust_profile_amt_rec_type.cust_account_id          := cust_site_api_tbl(i).cust_account_id;
              hz_customer_profile_v2pub.create_cust_profile_amt (p_init_msg_list => fnd_api.g_true,
                                p_check_foreign_key                  => cust_site_api_tbl(i).cust_account_id,
                                p_cust_profile_amt_rec               =>  p_cust_profile_amt_rec_type,
                                x_cust_acct_profile_amt_id           => x_cust_acct_profile_amt_id,
                                x_return_status                      => x_return_status,
                                x_msg_count                          => x_msg_count,
                                x_msg_data                           => x_msg_data
                                  )  ;

10. API to create Customer Payment Method

p_rec_mtd_rec.cust_account_id   := cust_site_api_tbl(i).cust_account_id;
              p_rec_mtd_rec.receipt_method_id := cust_site_api_tbl(i).payment_method_id;
              p_rec_mtd_rec.primary_flag      := cust_site_api_tbl(i).pay_mthd_primary_flag ;
              p_rec_mtd_rec.start_date        := cust_site_api_tbl(i).pay_mthd_start_date;
              p_rec_mtd_rec.site_use_id       := cust_site_api_tbl(i).cust_site_use_id;
              hz_payment_method_pub.create_payment_method( p_init_msg_list          => fnd_api.g_true
                             , p_payment_method_rec     => p_rec_mtd_rec
                             , x_cust_receipt_method_id => x_cust_receipt_method_id
                             , x_return_status          => x_return_status
                             , x_msg_count              => x_msg_count
                             , x_msg_data               => x_msg_data);

11. APIs to create Customer Contact


  -- Initialize parameters for hz_party_v2pub.create_person API
                p_person_rec.person_first_name := cust_cont_api_tbl(i).contact_first_name;
                p_person_rec.person_last_name  := cust_cont_api_tbl(i).contact_last_name;
                p_person_rec.created_by_module := G_CREATED_BY_MODULE;
                hz_party_v2pub.create_person( p_init_msg_list   => fnd_api.g_true
                        , p_person_rec          => p_person_rec
                        , x_return_status       => x_return_status
                        , x_msg_count           => x_msg_count
                        , x_msg_data            => x_msg_data
                        , x_party_id            => x_person_party_id
                        , x_party_number        => x_person_party_number
                        , x_profile_id          => x_person_profile_id );

 p_org_contact_rec.job_title_code                   := cust_cont_api_tbl(i).contact_job_title_code;
            p_org_contact_rec.job_title                        := cust_cont_api_tbl(i).contact_job_title;
            p_org_contact_rec.party_rel_rec.subject_id         := cust_cont_api_tbl(i).person_party_id;
            p_org_contact_rec.party_rel_rec.subject_type       := 'PERSON';
            p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
            p_org_contact_rec.party_rel_rec.object_id          := x_cust_party_id; --- cust_cont_api_tbl(i).party_id;
            p_org_contact_rec.party_rel_rec.object_type        := 'ORGANIZATION';
            p_org_contact_rec.party_rel_rec.object_table_name  := 'HZ_PARTIES';
            p_org_contact_rec.party_rel_rec.relationship_code  := 'CONTACT_OF';
            p_org_contact_rec.party_rel_rec.relationship_type  := 'CONTACT';
            p_org_contact_rec.party_rel_rec.start_date         := SYSDATE;
            p_org_contact_rec.created_by_module                := G_CREATED_BY_MODULE;
            hz_party_contact_v2pub.create_org_contact( p_init_msg_list          => fnd_api.g_true
                             , p_org_contact_rec    => p_org_contact_rec
                             , x_return_status      => x_return_status
                             , x_msg_count          => x_msg_count
                             , x_msg_data           => x_msg_data
                             , x_org_contact_id     => x_org_contact_id
                             , x_party_rel_id       => x_party_rel_id
                             , x_party_id           => x_org_party_id
                             , x_party_number       => x_org_party_number );


 p_cust_account_role_rec.party_id          := get_relation_party_id(cust_cont_api_tbl(i).party_rel_id);
            p_cust_account_role_rec.cust_account_id   := cust_cont_api_tbl(i).cust_account_id;
            p_cust_account_role_rec.cust_acct_site_id := l_new_cust_acct_site_id;
            p_cust_account_role_rec.role_type         := 'CONTACT';           
            p_cust_account_role_rec.created_by_module := G_CREATED_BY_MODULE;
            IF (cust_cont_api_tbl(i).rank           = '1ST'
             OR cust_cont_api_tbl(i).rank           = '1') then
              p_cust_account_role_rec.primary_flag := 'Y';
            ELSE
              p_cust_account_role_rec.primary_flag := NULL;
            END IF;
            hz_cust_account_role_v2pub.create_cust_account_role( p_init_msg_list        => fnd_api.g_true
                                   , p_cust_account_role_rec    => p_cust_account_role_rec
                                   , x_return_status            => x_return_status
                                   , x_msg_count                => x_msg_count
                                   , x_msg_data                 => x_msg_data
                                   , x_cust_account_role_id     => x_cust_account_role_id );


  p_role_responsibility_rec.cust_account_role_id := cust_cont_api_tbl(i).cust_account_role_id ;
            p_role_responsibility_rec.responsibility_type  := cust_cont_api_tbl(i).contact_role_code ;           
            p_role_responsibility_rec.created_by_module := G_CREATED_BY_MODULE;
            hz_cust_account_role_v2pub.create_role_responsibility( p_init_msg_list        => fnd_api.g_true
                                   , p_role_responsibility_rec  => p_role_responsibility_rec
                                   , x_return_status            => x_return_status
                                   , x_msg_count                => x_msg_count
                                   , x_msg_data                 => x_msg_data
                                   , x_responsibility_id        => x_responsibility_id );


12. Create Phone Contact Point API


p_contact_point_rec.contact_point_type := 'PHONE';
              p_phone_rec.phone_area_code            := cust_cont_api_tbl(i).telephone_area_code;
              p_phone_rec.phone_number               := cust_cont_api_tbl(i).telephone;
              p_phone_rec.phone_extension            := cust_cont_api_tbl(i).telephone_extension;
              p_phone_rec.phone_line_type := 'GEN';
              hz_contact_point_v2pub.create_contact_point ( p_init_msg_list         => fnd_api.g_true
                              , p_contact_point_rec     => p_contact_point_rec
                              , p_phone_rec             => p_phone_rec
                              , p_email_rec             => p_email_rec
                              , x_return_status         => x_return_status
                              , x_msg_count             => x_msg_count
                              , x_msg_data              => x_msg_data
                              , x_contact_point_id      => x_contact_point_id );