Sunday, December 14, 2014

Customers API (Complete API)


Salam Alaikum,

   After struggling with the task of uploading for a long time, i share with you guys a complete API that can be used for uploading customers.

   The following API can be used to create the following:
  1. Create Customer (using: HZ_PARTY_V2PUB.create_organization).
  2. Create Customer account (using: HZ_CUST_ACCOUNT_V2PUB.create_cust_account).
  3. Create location (using: HZ_LOCATION_V2PUB.create_location).
  4. Create Customer Site (using: HZ_PARTY_SITE_V2PUB.create_party_site).
  5. Create Customer Account site (using: Z_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site).
  6. Create Customer Account site use (Ship to & Bill to - using: HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use) .
  7. Create Contact Person (using: HZ_PARTY_V2PUB.create_person & HZ_PARTY_CONTACT_V2PUB.create_org_contact).
  8. Add phone number to contact (using: HZ_CONTACT_POINT_V2PUB.create_contact_point).
  9. Create Customer Account Role (using: HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role).
  10. Create External Bank (using: IBY_EXT_BANKACCT_PUB.create_ext_bank).
  11. Create External Branch (using: IBY_EXT_BANKACCT_PUB.create_ext_bank_branch).
  12. Create External Bank Account (using: IBY_EXT_BANKACCT_PUB.create_ext_bank_acct).
  13. Create Bank Account assignment to the customer (using: IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment).

   The Code i am sharing is merging all the api together in one block, but i will share the API's separated once i have them.

DECLARE
 v_return_status         VARCHAR2 (5000);
 v_msg_count             NUMBER;
 v_msg_data              VARCHAR2 (5000);
 -- misc variables
 v_msg_dummy             VARCHAR2 (5000);
 t_output                VARCHAR2 (5000);
 v_cust_account_rec      HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
 v_customer_profile_rec  HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
 v_cust_account_id       NUMBER;
 v_account_number        VARCHAR2 (5000);
 v_party_id              NUMBER;
 v_party_number          VARCHAR2 (5000);
 v_profile_id            NUMBER;
 v_cust_party_id         NUMBER;
 v_cust_party_number     VARCHAR2 (5000);
 v_cust_profile_id       NUMBER;
 v_application_id        NUMBER;
 v_organization_rec      HZ_PARTY_V2PUB.organization_rec_type;
 v_location_id           NUMBER;
 v_location_rec          HZ_LOCATION_V2PUB.location_rec_type;
 v_currency_code         VARCHAR2 (5000);
 v_country_code          VARCHAR2 (5000);
 v_party_site_rec        HZ_PARTY_SITE_V2PUB.party_site_rec_type;
 v_party_site_id         NUMBER;
 v_party_site_number     VARCHAR2 (5000);
 v_cust_acct_site_rec    HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
 v_cust_acct_site_id     NUMBER;
 v_cust_site_use_rec     HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
 v_bill_site_use_id      NUMBER;
 v_ship_site_use_id      NUMBER;
 v_org_id                NUMBER;
 v_person_rec            HZ_PARTY_V2PUB.PERSON_REC_TYPE;
 v_person_profile_id     NUMBER;
 v_person_party_id       NUMBER;
 v_person_party_number   VARCHAR2 (5000);
 v_contact_party_id      NUMBER;
 v_contact_party_number  VARCHAR2 (5000);
 v_org_contact_id        NUMBER;
 v_party_rel_id          NUMBER;
 v_contact_point_id      NUMBER;
 v_org_contact_rec       HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
 v_contact_point_rec     HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
 v_edi_rec               HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
 v_email_rec             HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
 v_phone_rec             HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
 v_telex_rec             HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
 v_web_rec               HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
 v_cust_account_role_id  NUMBER;
 v_cust_account_role_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE;
 v_ext_bank_rec          IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
 v_response              IBY_FNDCPT_COMMON_PUB.Result_rec_type;
 v_bank_id               NUMBER;
 v_bank_exists           NUMBER := 0;
 v_ext_bank_branch_rec   IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
 v_branch_id             NUMBER;
 v_start_date            DATE;
 v_end_date              DATE;
 v_acct_id               NUMBER;
 v_ext_banK_acct_rec     IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
 v_payer_context_rec     IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
 v_assignment_attribs    IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
 v_assign_id             NUMBER;
BEGIN
 -- get application id

 SELECT application_id
   INTO v_application_id
   FROM fnd_application_vl
  WHERE application_name = 'Order Management';

 -- get org details

 SELECT hou.organization_id
   INTO v_org_id
   FROM hr_operating_units hou
  -- hou.name will come from staging table or excel sheet

  WHERE hou.name = 'Sahara Bulk Storage Facilities Limited';

 -- set operating unit

 mo_global.init ('AR');        --fnd_global.apps_initialize(1523, 50758, 222);

 mo_global.set_policy_context ('S', v_org_id); -- get currency, territory details

 SELECT territory_code, currency_code
   INTO v_country_code, v_currency_code
   FROM fnd_territories_tl ftt, fnd_currencies fc
  WHERE     fc.issuing_territory_code = ftt.territory_code -- nigeria comes from excel file
        AND UPPER (ftt.territory_short_name) = UPPER ('Nigeria');

 -- set organization recordv_organization_rec.created_by_module := 'HZ_CPUI';

 v_organization_rec.organization_name := 'Igwe Super Customer';

 v_organization_rec.application_id := v_application_id;

 -- create organization

 HZ_PARTY_V2PUB.create_organization (p_init_msg_list    => FND_API.G_TRUE,
                                     p_organization_rec => v_organization_rec,
                                     x_party_id         => v_party_id,
                                     x_party_number     => v_party_number,
                                     x_profile_id       => v_profile_id,
                                     x_return_status    => v_return_status,
                                     x_msg_count        => v_msg_count,
                                     x_msg_data         => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- set customer record type
 v_cust_account_rec.status := 'A';

 -- lookup is CUSTOMER_TYPE
 v_cust_account_rec.customer_type := 'R';          -- lookup is CUSTOMER CLASS
 v_cust_account_rec.customer_class_code := 'PUBLIC SECTOR COMPANIES';
 v_cust_account_rec.application_id := v_application_id;
 v_cust_account_rec.created_by_module := 'HZ_CPUI';
 v_cust_account_rec.account_name := v_organization_rec.organization_name;

 -- party id
 v_organization_rec.party_rec.party_id := v_party_id;
 v_organization_rec.gsa_indicator_flag := 'N';

 -- create customer
 HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
  p_init_msg_list        => FND_API.G_FALSE,
  p_cust_account_rec     => v_cust_account_rec,
  p_organization_rec     => v_organization_rec,
  p_customer_profile_rec => v_customer_profile_rec,
  p_create_profile_amt   => FND_API.G_FALSE,
  x_cust_account_id      => v_cust_account_id,
  x_account_number       => v_account_number,
  x_party_id             => v_cust_party_id,
  x_party_number         => v_cust_party_number,
  x_profile_id           => v_cust_profile_id,
  x_return_status        => v_return_status,
  x_msg_count            => v_msg_count,
  x_msg_data             => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- create location record
 v_location_rec.address1 := 'Block A, Plot 21 Olamijuyi Avenue';
 v_location_rec.address2 := 'Parkview Estate. Ikoyi';
 v_location_rec.city := 'Lagos';
 v_location_rec.state := 'Lagos';
 v_location_rec.country := v_country_code;
 v_location_rec.created_by_module := 'HZ_CPUI';
 v_location_rec.application_id := v_application_id;
 v_location_rec.address_style := 'Nigeria Address Format';

 -- now create location
 HZ_LOCATION_V2PUB.create_location (p_location_rec  => v_location_rec,
                                    x_location_id   => v_location_id,
                                    x_return_status => v_return_status,
                                    x_msg_count     => v_msg_count,
                                    x_msg_data      => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- populate party site record type
 v_party_site_rec.party_id := v_party_id;
 v_party_site_rec.location_id := v_location_id;
 v_party_site_rec.status := 'A';
 v_party_site_rec.identifying_address_flag := 'Y';
 v_party_site_rec.created_by_module := 'HZ_CPUI';
 v_party_site_rec.application_id := v_application_id;

 -- create party site
 HZ_PARTY_SITE_V2PUB.create_party_site (
  p_party_site_rec    => v_party_site_rec,
  x_party_site_id     => v_party_site_id,
  x_party_site_number => v_party_site_number,
  x_return_status     => v_return_status,
  x_msg_count         => v_msg_count,
  x_msg_data          => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- create account site record type
 v_cust_acct_site_rec.cust_account_id := v_cust_account_id;
 v_cust_acct_site_rec.party_site_id := v_party_site_id;
 v_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
 v_cust_acct_site_rec.application_id := v_application_id;
 v_cust_acct_site_rec.status := 'A';
 v_cust_acct_site_rec.org_id := v_org_id;

 -- create customer account site
 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (
  p_init_msg_list      => FND_API.G_TRUE,
  p_cust_acct_site_rec => v_cust_acct_site_rec,
  x_cust_acct_site_id  => v_cust_acct_site_id,
  x_return_status      => v_return_status,
  x_msg_count          => v_msg_count,
  x_msg_data           => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ' : ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- create account site use record for SHIP_TO
 v_cust_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;
 v_cust_site_use_rec.site_use_code := 'SHIP_TO';
 v_cust_site_use_rec.primary_flag := 'Y';
 v_cust_site_use_rec.status := 'A';

 -- location id
 v_cust_site_use_rec.LOCATION := v_location_id;
 v_cust_site_use_rec.created_by_module := 'HZ_CPUI';
 v_cust_site_use_rec.application_id := v_application_id;
 v_cust_site_use_rec.org_id := v_org_id;

 -- create customer account site use
 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
  p_init_msg_list        => FND_API.G_TRUE,
  p_cust_site_use_rec    => v_cust_site_use_rec,
  p_customer_profile_rec => v_customer_profile_rec,
  p_create_profile       => FND_API.G_TRUE,
  p_create_profile_amt   => FND_API.G_FALSE,
  x_site_use_id          => v_ship_site_use_id,
  x_return_status        => v_return_status,
  x_msg_count            => v_msg_count,
  x_msg_data             => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- create cust site use for bill to
 v_cust_site_use_rec.site_use_code := 'BILL_TO';

 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
  p_init_msg_list        => FND_API.G_TRUE,
  p_cust_site_use_rec    => v_cust_site_use_rec,
  p_customer_profile_rec => v_customer_profile_rec,
  p_create_profile       => FND_API.G_TRUE,
  p_create_profile_amt   => FND_API.G_FALSE,
  x_site_use_id          => v_bill_site_use_id,
  x_return_status        => v_return_status,
  x_msg_count            => v_msg_count,
  x_msg_data             => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- create contact person rec
 v_person_rec.person_first_name := 'Chike';
 v_person_rec.person_last_name := 'Iheomamere';
 v_person_rec.person_title := 'MR.';
 v_person_rec.party_rec.status := 'A';
 v_person_rec.head_of_household_flag := 'N';
 v_person_rec.application_id := v_application_id;
 v_person_rec.created_by_module := 'HZ_CPUI';

 -- create person
 HZ_PARTY_V2PUB.create_person (p_init_msg_list => fnd_api.g_false,
                               p_person_rec    => v_person_rec,
                               x_party_id      => v_person_party_id,
                               x_party_number  => v_person_party_number,
                               x_profile_id    => v_person_profile_id,
                               x_return_status => v_return_status,
                               x_msg_count     => v_msg_count,
                               x_msg_data      => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- create org contact record type
 v_org_contact_rec.job_title := 'Manager';
 v_org_contact_rec.decision_maker_flag := 'N';
 v_org_contact_rec.created_by_module := 'HZ_CPUI';

 -- party_id of created contact
 v_org_contact_rec.party_rel_rec.subject_id := v_person_party_id;
 v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
 v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';

 -- party_id of the organization
 v_org_contact_rec.party_rel_rec.object_id := v_party_id;
 v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
 v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
 v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
 v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
 v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
 v_org_contact_rec.party_rel_rec.status := 'A';

 -- create contact
 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
  p_init_msg_list   => fnd_api.g_false,
  p_org_contact_rec => v_org_contact_rec,
  x_org_contact_id  => v_org_contact_id,
  x_party_rel_id    => v_party_rel_id,
  x_party_id        => v_contact_party_id,
  x_party_number    => v_contact_party_number,
  x_return_status   => v_return_status,
  x_msg_count       => v_msg_count,
  x_msg_data        => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- create phone record type for contact
 v_contact_point_rec.contact_point_type := 'PHONE';
 v_contact_point_rec.owner_table_name := 'HZ_PARTIES';
 v_contact_point_rec.owner_table_id := v_person_party_id;
 v_contact_point_rec.primary_flag := 'Y';
 v_contact_point_rec.contact_point_purpose := 'BUSINESS';
 v_phone_rec.phone_country_code := '234';
 v_phone_rec.phone_area_code := '01';
 v_phone_rec.phone_number := '08023165365';
 v_contact_point_rec.status := 'A';
 v_phone_rec.phone_line_type := 'GEN';
 v_contact_point_rec.created_by_module := 'HZ_CPUI';

 -- add fax if there is one available
 v_telex_rec.telex_number := '04-46879879';
 -- emailv_email_rec.email_address := 'info@acornplc.com';
 -- webv_web_rec.url := 'www.acornplc.com';

 -- add phone number to contact
 HZ_CONTACT_POINT_V2PUB.create_contact_point (
  p_init_msg_list     => fnd_api.g_false,
  p_contact_point_rec => v_contact_point_rec,
  p_edi_rec           => v_edi_rec,
  p_email_rec         => v_email_rec,
  p_phone_rec         => v_phone_rec,
  p_telex_rec         => v_telex_rec,
  p_web_rec           => v_web_rec,
  x_contact_point_id  => v_contact_point_id,
  x_return_status     => v_return_status,
  x_msg_count         => v_msg_count,
  x_msg_data          => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- populate party site record type
 v_cust_account_role_rec.party_id := v_contact_party_id;
 v_cust_account_role_rec.role_type := 'CONTACT';
 v_cust_account_role_rec.cust_account_id := v_cust_account_id;
 v_cust_account_role_rec.cust_acct_site_id := v_cust_acct_site_id;
 v_cust_account_role_rec.primary_flag := 'Y';
 v_cust_account_role_rec.application_id := v_application_id;
 v_cust_account_role_rec.created_by_module := 'HZ_CPUI';
 v_cust_account_role_rec.status := 'A';

 -- create party site
 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
  p_init_msg_list         => FND_API.G_TRUE,
  p_cust_account_role_rec => v_cust_account_role_rec,
  x_cust_account_role_id  => v_cust_account_role_id,
  x_return_status         => v_return_status,
  x_msg_count             => v_msg_count,
  x_msg_data              => v_msg_data);

 --debug section
 IF v_msg_count > 0 THEN
  FOR j IN 1 .. v_msg_count LOOP
   fnd_msg_pub.get (j,
                    FND_API.G_FALSE,
                    v_msg_data,
                    v_msg_dummy);

   t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

   DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
  END LOOP;
 END IF;
 -- end debug

 -- customer banks

 -- create ext bank record type
 v_ext_bank_rec.bank_name := 'Zenith Bank Nigeria Plc';
 v_ext_bank_rec.country_code := v_country_code;

 -- check if bank exists
 IBY_EXT_BANKACCT_PUB.check_bank_exist (
  p_api_version   => 1.0,
  p_init_msg_list => fnd_api.g_true,
  p_country_code  => v_ext_bank_rec.country_code,
  p_bank_name     => v_ext_bank_rec.bank_name,
  p_bank_number   => NULL,
  x_response      => v_response,
  x_bank_id       => v_bank_id,
  x_end_date      => v_end_date,
  x_return_status => v_return_status,
  x_msg_count     => v_msg_count,
  x_msg_data      => v_msg_data);

 -- if bank does not exist then create it

 IF v_bank_id IS NULL THEN
   -- create supplier bank
  IBY_EXT_BANKACCT_PUB.create_ext_bank (p_api_version   => 1.0,
                                        p_init_msg_list => fnd_api.g_true,
                                        p_ext_bank_rec  => v_ext_bank_rec,
                                        x_response      => v_response,
                                        x_bank_id       => v_bank_id,
                                        x_return_status => v_return_status,
                                        x_msg_count     => v_msg_count,
                                        x_msg_data      => v_msg_data);

  --debug section
  IF v_msg_count > 0 THEN
   FOR j IN 1 .. v_msg_count LOOP
    fnd_msg_pub.get (j,
                     FND_API.G_FALSE,
                     v_msg_data,
                     v_msg_dummy);

    t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

    -- raise exception here

    DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
   END LOOP;
  END IF;
 -- end debug

 -- end create bank

 END IF;

 -- set bank branch record
 v_ext_bank_branch_rec.bank_party_id := v_bank_id;

 -- no branch name given in excel sheet
 v_ext_bank_branch_rec.branch_name := 'Default Branch';
 v_ext_bank_branch_rec.branch_type := 'OTHER';

 -- check if branch exists
 IBY_EXT_BANKACCT_PUB.check_ext_bank_branch_exist (
  p_api_version   => 1.0,
  p_init_msg_list => fnd_api.g_true,
  p_bank_id       => v_ext_bank_branch_rec.bank_party_id,
  p_branch_name   => v_ext_bank_branch_rec.branch_name,
  p_branch_number => NULL,
  x_response      => v_response,
  x_branch_id     => v_branch_id,
  x_end_date      => v_end_date,
  x_return_status => v_return_status,
  x_msg_count     => v_msg_count,
  x_msg_data      => v_msg_data);

 -- if branch does not exist then create it
 IF v_branch_id IS NULL THEN
  -- create bank branch
  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 => v_ext_bank_branch_rec,
   x_response            => v_response,
   x_branch_id           => v_branch_id,
   x_return_status       => v_return_status,
   x_msg_count           => v_msg_count,
   x_msg_data            => v_msg_data);

  -- debug section
  IF v_msg_count > 0 THEN
   FOR j IN 1 .. v_msg_count LOOP
    fnd_msg_pub.get (j,
                     FND_API.G_FALSE,
                     v_msg_data,
                     v_msg_dummy);

    t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

    -- raise exception here
    DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
   END LOOP;
  END IF;
 -- end debug
 END IF;
 -- set bank account record

 v_ext_bank_acct_rec.country_code := v_country_code;
 v_ext_bank_acct_rec.branch_id := v_branch_id;
 v_ext_bank_acct_rec.bank_id := v_bank_id;

 -- party id of the customer
 v_ext_bank_acct_rec.acct_owner_party_id := v_party_id;

 -- account name is given in excel sheet
 v_ext_bank_acct_rec.bank_account_name := 'Igwe Super Customer Account';
 v_ext_bank_acct_rec.bank_account_num := '0580034253214';
 v_ext_bank_acct_rec.currency := v_currency_code;

 -- check if branch exists
 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist (
  p_api_version       => 1.0,
  p_init_msg_list     => fnd_api.g_true,
  p_ext_bank_acct_rec => v_ext_bank_acct_rec,
  x_acct_id           => v_acct_id,
  x_start_date        => v_start_date,
  x_end_date          => v_end_date,
  x_return_status     => v_return_status,
  x_msg_count         => v_msg_count,
  x_msg_data          => v_msg_data,
  x_response          => v_response);

 -- if bank account does not exist then create it
 IF v_acct_id IS NULL THEN                               -- create bank branch
  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 => v_ext_bank_acct_rec,
   x_acct_id           => v_acct_id,
   x_response          => v_response,
   x_return_status     => v_return_status,
   x_msg_count         => v_msg_count,
   x_msg_data          => v_msg_data);

  -- debug section
  IF v_msg_count > 0 THEN
   FOR j IN 1 .. v_msg_count LOOP
    fnd_msg_pub.get (j,
                     FND_API.G_FALSE,
                     v_msg_data,
                     v_msg_dummy);

    t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

    -- raise exception here

    DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
   END LOOP;
  END IF;
 -- end debug
 END IF;

 -- payer record type
 /* options for payment function are
 AR_CUSTOMER_REFUNDS - Receivables Customer Refunds
 CUSTOMER_PAYMENT - To collect payment from a customer
 */

 v_payer_context_rec.payment_function := 'CUSTOMER_PAYMENT';
 v_payer_context_rec.party_id := v_party_id;
 v_payer_context_rec.org_type := 'OPERATING_UNIT';
 v_payer_context_rec.org_id := v_org_id;
 v_payer_context_rec.cust_account_id := v_cust_account_id;
 -- this is not actually the account site id
 -- but the account site use id
 v_payer_context_rec.account_site_id := v_bill_site_use_id;

 -- assignment attributes
 v_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';

 -- the external bank account id
 v_assignment_attribs.instrument.instrument_id := v_acct_id;

 --dbms_output.put_line(v_acct_id);
 v_assignment_attribs.priority := 1;
 --v_assignment_attribs.start_date := sysdate;

 -- map account to customer
 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_false,
  x_return_status      => v_return_status,
  x_msg_count          => v_msg_count,
  x_msg_data           => v_msg_data,
  p_payer              => v_payer_context_rec,
  p_assignment_attribs => v_assignment_attribs,
  x_assign_id          => v_assign_id,
  x_response           => v_response);

 -- debug section
 IF v_return_status != 'S' THEN
  IF v_msg_count > 0 THEN
   FOR j IN 1 .. v_msg_count LOOP
    fnd_msg_pub.get (j,
                     FND_API.G_FALSE,
                     v_msg_data,
                     v_msg_dummy);

    t_output    := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);

    -- raise exception here

    DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
   END LOOP;
  END IF;
 END IF;                                                          -- end debug
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.put_line (SQLERRM);
END;

Hope you found it useful guys, feel free to leave a comment if have any note on the subject.

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete