PLSQL Programs

 

1. AR Invoice creation
2. Supplier creation
3. Customer creation
4. HRMS API’s for data anonymization/ masking/ scrambling


1. AR Invoice creation

DECLARE
  l_return_status           VARCHAR2(1);
  l_msg_count               NUMBER;
  l_msg_data                VARCHAR2(2000);
  l_batch_id                NUMBER;
  l_batch_source_rec        ar_invoice_api_pub.batch_source_rec_type;
  l_trx_header_tbl          ar_invoice_api_pub.trx_header_tbl_type;
  l_trx_lines_tbl           ar_invoice_api_pub.trx_line_tbl_type;
  l_trx_dist_tbl            ar_invoice_api_pub.trx_dist_tbl_type;
  l_trx_salescredits_tbl    ar_invoice_api_pub.trx_salescredits_tbl_type;
  l_trx_contingencies_tbl   ar_invoice_api_pub.trx_contingencies_tbl_type;
  v_line_no                 NUMBER := 0;
  v_line_dist_no            NUMBER := 0;
  CURSOR cbatch IS SELECT customer_trx_id FROM ra_customer_trx_all WHERE batch_id = l_batch_id;

  CURSOR cvalidtxn IS SELECT trx_header_id
                      FROM ar_trx_header_gt
                      WHERE trx_header_id NOT IN (
    SELECT trx_header_id FROM ar_trx_errors_gt
  );

BEGIN
/*Applications context set. Initialize the session before calling any public or private APIs. This is recommended practice*/
  fnd_global.apps_initialize(
    user_id        => fnd_profile.value('USER_ID')
    ,resp_id        => fnd_profile.value('RESP_ID')
    ,resp_appl_id   => fnd_profile.value('RESP_APPL_ID')
  );

  mo_global.init('AR');
  mo_global.set_policy_context('S',fnd_profile.value('ORG_ID'));
  fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));

/*Populate batch source information*/
  l_batch_source_rec.batch_source_id                   := 5027;

/*Populate header information*/
  l_trx_header_tbl(1).trx_header_id                    := 2764315;
  l_trx_header_tbl(1).bill_to_customer_id              := 2651;
  l_trx_header_tbl(1).ship_to_customer_id              := 1035;
  l_trx_header_tbl(1).cust_trx_type_id                 := 1026;
  l_trx_header_tbl(1).trx_date                         := SYSDATE;
  l_trx_header_tbl(1).term_id                          := 1000;
  l_trx_header_tbl(1).primary_salesrep_id              := -3;
  l_trx_header_tbl(1).trx_currency                     := 'GBP';
  l_trx_header_tbl(1).exchange_rate_type               := NULL;
  l_trx_header_tbl(1).exchange_date                    := NULL;
  l_trx_header_tbl(1).exchange_rate                    := NULL;
  l_trx_header_tbl(1).attribute5                       := 'ABC123';
  l_trx_header_tbl(1).purchase_order                   := 'DEF456';
  l_trx_header_tbl(1).purchase_order_date              := (SYSDATE - 5);
  l_trx_header_tbl(1).trx_class                        := 'INV';

/*Populate line information*/
  v_line_no                                            := v_line_no + 1;/*Fetch line records and LOOP them*/
  l_trx_lines_tbl(v_line_no).trx_header_id             := 2764315;
  l_trx_lines_tbl(v_line_no).trx_line_id               := 3003327;
  l_trx_lines_tbl(v_line_no).line_number               := 1;
  l_trx_lines_tbl(v_line_no).quantity_invoiced         := 10;
  l_trx_lines_tbl(v_line_no).unit_selling_price        := 100;
  l_trx_lines_tbl(v_line_no).line_type                 := 'LINE';
  l_trx_lines_tbl(v_line_no).description               := 'Goods1@STANDARD Rate';
  l_trx_lines_tbl(v_line_no).tax_classification_code   := 'MIXED'; /*'ZERO RATED' or 'MIXED' or 'STANDARD'*/

/*Populate distribution information*/
  v_line_dist_no                                       := v_line_dist_no + 1; /*Fetch line records and LOOP them*/
  l_trx_dist_tbl(v_line_dist_no).trx_dist_id           := 3382546;
  l_trx_dist_tbl(v_line_dist_no).trx_line_id           := 3003327;
  l_trx_dist_tbl(v_line_dist_no).trx_header_id         := 2764315;
  l_trx_dist_tbl(v_line_dist_no).account_class         := 'REV';
  l_trx_dist_tbl(v_line_dist_no).percent               := 100;
  l_trx_dist_tbl(v_line_dist_no).code_combination_id   := 2173791;
  l_trx_dist_tbl(v_line_dist_no).amount                := 1000;

/*Populate line tax information*/
  v_line_no                                            := v_line_no + 1;
  l_trx_lines_tbl(v_line_no).taxable_flag              := 'N';
  l_trx_lines_tbl(v_line_no).trx_header_id             := 2764315;
  l_trx_lines_tbl(v_line_no).trx_line_id               := 3003328;
  l_trx_lines_tbl(v_line_no).link_to_trx_line_id       := 3003327;
  l_trx_lines_tbl(v_line_no).line_number               := 1;
  l_trx_lines_tbl(v_line_no).line_type                 := 'TAX';
  l_trx_lines_tbl(v_line_no).tax_regime_code           := 'GB VAT';
  l_trx_lines_tbl(v_line_no).tax                       := 'GB VAT';
  l_trx_lines_tbl(v_line_no).tax_jurisdiction_code     := 'GB VAT UK';
  l_trx_lines_tbl(v_line_no).tax_status_code           := 'MIXED'; /*'ZERO RATED' or 'MIXED' or 'STANDARD'*/
  l_trx_lines_tbl(v_line_no).tax_rate_code             := 'MIXED'; /*'ZERO RATED' or 'MIXED' or 'STANDARD'*/
  l_trx_lines_tbl(v_line_no).tax_rate                  := 20;
  l_trx_lines_tbl(v_line_no).amount                    := 200;

/*Call the invoice api to create multiple invoices in a batch*/
  ar_invoice_api_pub.create_invoice(
    p_api_version             => 1.0
    ,p_batch_source_rec        => l_batch_source_rec
    ,p_trx_header_tbl          => l_trx_header_tbl
    ,p_trx_lines_tbl           => l_trx_lines_tbl
    ,p_trx_dist_tbl            => l_trx_dist_tbl
    ,p_trx_salescredits_tbl    => l_trx_salescredits_tbl
    ,p_trx_contingencies_tbl   => l_trx_contingencies_tbl
    ,x_return_status           => l_return_status
    ,x_msg_count               => l_msg_count
    ,x_msg_data                => l_msg_data
  );

  IF
    l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error
  THEN
    dbms_output.put_line('unexpected errors found!');
  ELSE
    FOR cvalidtxnrec IN cvalidtxn LOOP
      IF
        ( ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL )
      THEN
        dbms_output.put_line('Invoice(s) suceessfully created!');
        dbms_output.put_line('Batch ID: ' || ar_invoice_api_pub.g_api_outputs.batch_id);
        l_batch_id   := ar_invoice_api_pub.g_api_outputs.batch_id;
        FOR cbatchrec IN cbatch LOOP
          dbms_output.put_line('Cust Trx Id ' || cbatchrec.customer_trx_id);
        END LOOP;

      ELSE
        dbms_output.put_line('Errors found!');
      END IF;
    END LOOP;
  END IF;

  COMMIT;
END;

2. Supplier creation

CREATE OR REPLACE PACKAGE BODY xxab_supplier_conversion AS
/*************************************************************************
* TYPE : Package Body *
* NAME : xxab_supplier_conversion *
************************************************************************/
------------------------------
-- Global Variable Declaration
------------------------------
  g_return_status              VARCHAR2(2000);
  g_msg_count                  NUMBER;
  g_msg_data                   VARCHAR2(2000);
  g_msg_index_out              NUMBER;

------------------------------
-- * Procedure Name : xxab_create_vendor_site
-- * Description : This Procedure creates the Supplier sites
------------------------------
  PROCEDURE xxab_create_vendor_site (
    p_vendor_id IN NUMBER
  ) IS
    l_vendor_site_rec        apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
    l_vendor_cont_rec        apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
    p_party_site_use_rec     hz_party_site_v2pub.party_site_use_rec_type;
    p_contact_point_rec      hz_contact_point_v2pub.contact_point_rec_type;
    p_edi_rec                hz_contact_point_v2pub.edi_rec_type;
    p_email_rec              hz_contact_point_v2pub.email_rec_type;
    p_phone_rec              hz_contact_point_v2pub.phone_rec_type;
    p_telex_rec              hz_contact_point_v2pub.telex_rec_type;
    p_web_rec                hz_contact_point_v2pub.web_rec_type;
    x_contact_point_id       NUMBER;
    x_party_site_use_id      NUMBER;
    x_vendor_site_id         NUMBER;
    x_party_site_id          NUMBER;
    x_location_id            NUMBER;
    lv_supplier_number       VARCHAR2(100);
    lv_supplier_name         VARCHAR2(1000);
    ln_count                 NUMBER;
    l_vendor_site_org_id     VARCHAR2(50);
    lv_user_id               NUMBER;
    lv_responsibility_id     NUMBER;
    lv_application_id        NUMBER;
    lv_organization_id       NUMBER;
    ln_user_id               NUMBER;
    ln_responsibility_id     NUMBER;
    ln_application_id        NUMBER;
    ln_organization_id       NUMBER;
    lv_unit                  VARCHAR2(20);
    l_party_tax_profile_id   VARCHAR2(100);
    CURSOR cur_sup_tab (
      p_vendor_id NUMBER
    ) IS SELECT segment1
               ,vendor_name
         FROM ap_suppliers
         WHERE vendor_id = p_vendor_id;
    CURSOR cur_stg_tab (
      p_supplier_number VARCHAR2
      ,p_supplier_name VARCHAR2
    ) IS SELECT *
         FROM xxab_supp_stg_tab
         WHERE 1 = 1
               AND   vendor_number = p_supplier_number
               AND   vendor_name = p_supplier_name;
    l_vendor_sites           cur_stg_tab%rowtype;
    CURSOR cur_org_id (
      p_unit VARCHAR2
    ) IS SELECT organization_id
         FROM hr_all_organization_units
         WHERE name = DECODE(
      p_unit
      ,'USABC'
      ,'ABCAAA'
      ,'USDEF'
      ,'ABCAAA'
      ,'USXYZ'
      ,'ABC'
    );
  BEGIN
    OPEN cur_sup_tab(p_vendor_id);
    FETCH cur_sup_tab INTO lv_supplier_number,lv_supplier_name;
    CLOSE cur_sup_tab;
    OPEN cur_stg_tab(
      lv_supplier_number
      ,lv_supplier_name
    );
    FETCH cur_stg_tab INTO l_vendor_sites;
    IF
      l_vendor_sites.unit = 'USXYZ'
    THEN
      lv_user_id                                           := NULL;
      lv_responsibility_id                                 := NULL;
      lv_application_id                                    := NULL;
      BEGIN
        SELECT fu.user_id
              ,frt.responsibility_id
              ,frt.application_id INTO
          lv_user_id,lv_responsibility_id,lv_application_id
        FROM fnd_user fu
            ,fnd_responsibility_tl frt
        WHERE fu.user_name = 'SYSADMIN'
              AND   frt.responsibility_name = 'Payables AB Supplier Setup';
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
      fnd_global.apps_initialize(
        lv_user_id
        ,lv_responsibility_id
        ,lv_application_id
      );
      mo_global.init('SQLAP');
      OPEN cur_org_id(l_vendor_sites.unit);
      FETCH cur_org_id INTO l_vendor_site_org_id;
      CLOSE cur_org_id;
      fnd_client_info.set_org_context(l_vendor_site_org_id);
      l_vendor_site_rec.last_update_date                   := SYSDATE;
      l_vendor_site_rec.last_updated_by                    := fnd_global.user_id;
      l_vendor_site_rec.vendor_id                          := p_vendor_id;
      l_vendor_site_rec.vendor_site_code                   := l_vendor_sites.vendor_site_code;
      l_vendor_site_rec.country                            := l_vendor_sites.vendor_site_country;
      l_vendor_site_rec.address_line1                      := l_vendor_sites.vendor_site_add1;
      l_vendor_site_rec.address_line2                      := l_vendor_sites.vendor_site_add2;
      l_vendor_site_rec.address_line3                      := l_vendor_sites.vendor_site_add3;
      l_vendor_site_rec.address_line4                      := l_vendor_sites.vendor_site_add4;
      l_vendor_site_rec.city                               := l_vendor_sites.vendor_site_city;
      l_vendor_site_rec.zip                                := l_vendor_sites.vendor_site_zip;
      l_vendor_site_rec.org_id                             := l_vendor_site_org_id;
      l_vendor_site_rec.area_code                          := l_vendor_sites.vendor_phone_area_code;
      l_vendor_site_rec.phone                              := l_vendor_sites.vendor_phone_number;
      l_vendor_site_rec.email_address                      := l_vendor_sites.vendor_email_address;
      l_vendor_site_rec.vat_registration_num               := l_vendor_sites.vat_registration_num;
      l_vendor_site_rec.ap_tax_rounding_rule               := 'NEAREST';
      l_vendor_site_rec.vat_code                           := 'STANDARD';
      l_vendor_site_rec.auto_tax_calc_flag                 := 'Y';
      l_vendor_site_rec.supplier_notif_method              := 'PRINT';
      l_vendor_site_rec.purchasing_site_flag               := 'Y';
      l_vendor_site_rec.pay_site_flag                      := 'Y';
      l_vendor_site_rec.pay_group_lookup_code              := l_vendor_sites.pay_group_lookup_code;
      l_vendor_site_rec.hold_unmatched_invoices_flag       := 'Y';
      l_vendor_site_rec.hold_reason                        := 'UNMATCHED INVOICES';
      l_vendor_site_rec.ext_payee_rec.default_pmt_method   := 'EFT';
      l_vendor_site_rec.remittance_email                   := l_vendor_sites.remittance_email_address; --v0.2
      IF
        l_vendor_sites.remittance_email_address IS NOT NULL
      THEN
        l_vendor_site_rec.remit_advice_delivery_method   := 'EMAIL';
      END IF;
-- Call the Vendor site API
      ap_vendor_pub_pkg.create_vendor_site(
        p_api_version        => 1.0
        ,p_init_msg_list      => fnd_api.g_true
        ,p_commit             => fnd_api.g_true
        ,p_validation_level   => fnd_api.g_valid_level_full
        ,x_return_status      => g_return_status
        ,x_msg_count          => g_msg_count
        ,x_msg_data           => g_msg_data
        ,p_vendor_site_rec    => l_vendor_site_rec
        ,x_vendor_site_id     => x_vendor_site_id
        ,x_party_site_id      => x_party_site_id
        ,x_location_id        => x_location_id
      );
      IF
        g_return_status <> fnd_api.g_ret_sts_success
      THEN
        FOR v_index IN 1..g_msg_count LOOP
          fnd_msg_pub.get(
            p_msg_index       => v_index
            ,p_encoded         => 'F'
            ,p_data            => g_msg_data
            ,p_msg_index_out   => g_msg_index_out
          );
          g_msg_data   := substr(
            g_msg_data
            ,1
            ,200
          );
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Error at Vendor site creation :'
            || g_msg_data
          );
          ROLLBACK;
        END LOOP;
      ELSE
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Vendor site: '
          || l_vendor_site_rec.vendor_site_code
          || ' created for vendor: '
          || lv_supplier_number
        );
/*API to update Communication details under Address Book section*/ --v0.4
        IF
          l_vendor_sites.vendor_phone_area_code IS NOT NULL OR l_vendor_sites.vendor_phone_number IS NOT NULL OR l_vendor_sites.vendor_email_address IS NOT NULL
        THEN
-- Initializing the Mandatory API parameters
          p_contact_point_rec.contact_point_type   := 'PHONE';
          p_contact_point_rec.owner_table_name     := 'HZ_PARTY_SITES';
          p_contact_point_rec.owner_table_id       := x_party_site_id;
          p_contact_point_rec.primary_flag         := 'Y';
          p_contact_point_rec.created_by_module    := 'POS_SUPPLIER_MGMT';
          p_phone_rec.phone_area_code              := l_vendor_sites.vendor_phone_area_code;
          p_phone_rec.phone_number                 := l_vendor_sites.vendor_phone_number;
          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_edi_rec             => p_edi_rec
            ,p_email_rec           => p_email_rec
            ,p_phone_rec           => p_phone_rec
            ,p_telex_rec           => p_telex_rec
            ,p_web_rec             => p_web_rec
            ,x_contact_point_id    => x_contact_point_id
            ,x_return_status       => g_return_status
            ,x_msg_count           => g_msg_count
            ,x_msg_data            => g_msg_data
          );
          IF
            g_return_status <> fnd_api.g_ret_sts_success
          THEN
            FOR i IN 1..g_msg_count LOOP
              g_msg_data   := fnd_msg_pub.get(
                p_msg_index   => i
                ,p_encoded     => 'F'
              );
              dbms_output.put_line(i
              || ') '
              || g_msg_data);
            END LOOP;
          END IF;
-- Initializing the Mandatory API parameters
          p_contact_point_rec.contact_point_type   := 'EMAIL';
          p_contact_point_rec.owner_table_name     := 'HZ_PARTY_SITES';
          p_contact_point_rec.owner_table_id       := x_party_site_id;
          p_contact_point_rec.primary_flag         := 'Y';
          p_contact_point_rec.created_by_module    := 'POS_SUPPLIER_MGMT';
          p_email_rec.email_address                := l_vendor_sites.vendor_email_address;
          hz_contact_point_v2pub.create_contact_point(
            p_init_msg_list       => fnd_api.g_true
            ,p_contact_point_rec   => p_contact_point_rec
            ,p_edi_rec             => p_edi_rec
            ,p_email_rec           => p_email_rec
            ,p_phone_rec           => p_phone_rec
            ,p_telex_rec           => p_telex_rec
            ,p_web_rec             => p_web_rec
            ,x_contact_point_id    => x_contact_point_id
            ,x_return_status       => g_return_status
            ,x_msg_count           => g_msg_count
            ,x_msg_data            => g_msg_data
          );
          IF
            g_return_status <> fnd_api.g_ret_sts_success
          THEN
            FOR i IN 1..g_msg_count LOOP
              g_msg_data   := fnd_msg_pub.get(
                p_msg_index   => i
                ,p_encoded     => 'F'
              );
              dbms_output.put_line(i
              || ') '
              || g_msg_data);
            END LOOP;
          END IF;
        END IF;
/*API to update Address Purpose to Purchasing*/
        p_party_site_use_rec.site_use_type       := 'PURCHASING';
        p_party_site_use_rec.party_site_id       := x_party_site_id;
        p_party_site_use_rec.created_by_module   := 'POS_SUPPLIER_MGMT';
        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        => g_return_status
          ,x_msg_count            => g_msg_count
          ,x_msg_data             => g_msg_data
        );
/*API to update Address Purpose to Payments*/
        p_party_site_use_rec.site_use_type       := 'PAY';
        p_party_site_use_rec.party_site_id       := x_party_site_id;
        p_party_site_use_rec.created_by_module   := 'POS_SUPPLIER_MGMT';
        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        => g_return_status
          ,x_msg_count            => g_msg_count
          ,x_msg_data             => g_msg_data
        );
        BEGIN
          SELECT party_tax_profile_id INTO
            l_party_tax_profile_id
          FROM zx_party_tax_profile
          WHERE party_id = x_party_site_id
                AND   party_type_code = 'THIRD_PARTY_SITE'
                AND   ROWNUM = 1;
        EXCEPTION
          WHEN OTHERS THEN
            l_party_tax_profile_id   := NULL;
        END;
        IF
          l_vendor_sites.vat_registration_num IS NOT NULL
        THEN
/*API to update Tax Registration Number at Supplier site level under Tax Details section*/
          zx_registrations_pkg.insert_row(
            p_request_id                  => NULL
            ,p_attribute1                  => NULL
            ,p_attribute2                  => NULL
            ,p_attribute3                  => NULL
            ,p_attribute4                  => NULL
            ,p_attribute5                  => NULL
            ,p_attribute6                  => NULL
            ,p_rounding_rule_code          => 'NEAREST'
            ,p_validation_rule             => NULL
            ,p_tax_jurisdiction_code       => NULL
            ,p_self_assess_flag            => NULL
            ,p_registration_status_code    => NULL
            ,p_registration_source_code    => NULL
            ,p_registration_reason_code    => NULL
            ,p_tax                         => 'GB VAT'
            ,p_tax_regime_code             => 'GB VAT'
            ,p_inclusive_tax_flag          => NULL
            ,p_effective_from              => SYSDATE
            ,p_effective_to                => NULL
            ,p_rep_party_tax_name          => NULL
            ,p_default_registration_flag   => NULL
            ,p_bank_account_num            => NULL
            ,p_record_type_code            => NULL
            ,p_legal_location_id           => NULL
            ,p_tax_authority_id            => NULL
            ,p_rep_tax_authority_id        => NULL
            ,p_coll_tax_authority_id       => NULL
            ,p_registration_type_code      => NULL
            ,p_registration_number         => l_vendor_sites.vat_registration_num
            ,p_party_tax_profile_id        => l_party_tax_profile_id
            ,p_legal_registration_id       => NULL
            ,p_bank_id                     => NULL
            ,p_bank_branch_id              => NULL
            ,p_account_site_id             => NULL
            ,p_attribute14                 => NULL
            ,p_attribute15                 => NULL
            ,p_attribute_category          => NULL
            ,p_program_login_id            => NULL
            ,p_account_id                  => NULL
            ,p_tax_classification_code     => NULL
            ,p_attribute7                  => NULL
            ,p_attribute8                  => NULL
            ,p_attribute9                  => NULL
            ,p_attribute10                 => NULL
            ,p_attribute11                 => NULL
            ,p_attribute12                 => NULL
            ,p_attribute13                 => NULL
            ,x_return_status               => g_return_status
          );
/*API to update Tax Registration Number at Supplier site level under Tax and Reporting section*/
          FOR v_site IN ( SELECT pvsa.*
                                ,pvsa.rowid
                          FROM po_vendor_sites_all pvsa
                          WHERE party_site_id = x_party_site_id
          ) LOOP
            ap_vendor_sites_pkg.update_row(
              x_rowid                          => v_site.rowid
              ,x_vendor_site_id                 => v_site.vendor_site_id
              ,x_last_update_date               => v_site.last_update_date
              ,x_last_updated_by                => v_site.last_updated_by
              ,x_vendor_id                      => v_site.vendor_id
              ,x_vendor_site_code               => v_site.vendor_site_code
              ,x_last_update_login              => v_site.last_update_login
              ,x_creation_date                  => v_site.creation_date
              ,x_created_by                     => v_site.created_by
              ,x_purchasing_site_flag           => v_site.purchasing_site_flag
              ,x_rfq_only_site_flag             => v_site.rfq_only_site_flag
              ,x_pay_site_flag                  => v_site.pay_site_flag
              ,x_attention_ar_flag              => v_site.attention_ar_flag
              ,x_address_line1                  => v_site.address_line1
              ,x_address_line2                  => v_site.address_line2
              ,x_address_line3                  => v_site.address_line3
              ,x_city                           => v_site.city
              ,x_state                          => v_site.state
              ,x_zip                            => v_site.zip
              ,x_province                       => v_site.province
              ,x_country                        => v_site.country
              ,x_area_code                      => v_site.area_code
              ,x_phone                          => v_site.phone
              ,x_customer_num                   => v_site.customer_num
              ,x_ship_to_location_id            => v_site.ship_to_location_id
              ,x_bill_to_location_id            => v_site.bill_to_location_id
              ,x_ship_via_lookup_code           => v_site.ship_via_lookup_code
              ,x_freight_terms_lookup_code      => v_site.freight_terms_lookup_code
              ,x_fob_lookup_code                => v_site.fob_lookup_code
              ,x_inactive_date                  => v_site.inactive_date
              ,x_fax                            => v_site.fax
              ,x_fax_area_code                  => v_site.fax_area_code
              ,x_telex                          => v_site.telex
              ,x_bank_account_name              => v_site.bank_account_name
              ,x_bank_account_num               => v_site.bank_account_num
              ,x_bank_num                       => v_site.bank_num
              ,x_bank_account_type              => v_site.bank_account_type
              ,x_terms_date_basis               => v_site.terms_date_basis
              ,x_current_catalog_num            => v_site.current_catalog_num
              ,x_distribution_set_id            => v_site.distribution_set_id
              ,x_accts_pay_ccid                 => v_site.accts_pay_code_combination_id
              ,x_future_dated_payment_ccid      => v_site.future_dated_payment_ccid
              ,x_prepay_code_combination_id     => v_site.prepay_code_combination_id
              ,x_pay_group_lookup_code          => v_site.pay_group_lookup_code
              ,x_payment_priority               => v_site.payment_priority
              ,x_terms_id                       => v_site.terms_id
              ,x_invoice_amount_limit           => v_site.invoice_amount_limit
              ,x_pay_date_basis_lookup_code     => v_site.pay_date_basis_lookup_code
              ,x_always_take_disc_flag          => v_site.always_take_disc_flag
              ,x_invoice_currency_code          => v_site.invoice_currency_code
              ,x_payment_currency_code          => v_site.payment_currency_code
              ,x_hold_all_payments_flag         => v_site.hold_all_payments_flag
              ,x_hold_future_payments_flag      => v_site.hold_future_payments_flag
              ,x_hold_reason                    => v_site.hold_reason
              ,x_hold_unmatched_invoices_flag   => v_site.hold_unmatched_invoices_flag
              ,x_match_option                   => v_site.match_option
              ,x_create_debit_memo_flag         => v_site.create_debit_memo_flag
              ,x_tax_reporting_site_flag        => v_site.tax_reporting_site_flag
              ,x_attribute_category             => v_site.attribute_category
              ,x_attribute1                     => v_site.attribute1
              ,x_attribute2                     => v_site.attribute2
              ,x_attribute3                     => v_site.attribute3
              ,x_attribute4                     => v_site.attribute4
              ,x_attribute5                     => v_site.attribute5
              ,x_attribute6                     => v_site.attribute6
              ,x_attribute7                     => v_site.attribute7
              ,x_attribute8                     => v_site.attribute8
              ,x_attribute9                     => v_site.attribute9
              ,x_attribute10                    => v_site.attribute10
              ,x_attribute11                    => v_site.attribute11
              ,x_attribute12                    => v_site.attribute12
              ,x_attribute13                    => v_site.attribute13
              ,x_attribute14                    => v_site.attribute14
              ,x_attribute15                    => v_site.attribute15
              ,x_validation_number              => v_site.validation_number
              ,x_exclude_freight_from_disc      => v_site.exclude_freight_from_discount
              ,x_vat_registration_num           => l_vendor_sites.vat_registration_num
              ,–v_site.vat_registration_num
              ,x_check_digits                   => v_site.check_digits
              ,x_bank_number                    => v_site.bank_number
              , --v0.3
              x_address_line4                  => v_site.address_line4
              ,x_county                         => v_site.county
              ,x_address_style                  => v_site.address_style
              ,x_language                       => v_site.language
              ,x_allow_awt_flag                 => v_site.allow_awt_flag
              ,x_awt_group_id                   => v_site.awt_group_id
              ,x_pay_awt_group_id               => v_site.pay_awt_group_id
              ,x_pay_on_code                    => v_site.pay_on_code
              ,x_default_pay_site_id            => v_site.default_pay_site_id
              ,x_pay_on_receipt_summary_code    => v_site.pay_on_receipt_summary_code
              ,x_bank_branch_type               => v_site.bank_branch_type
              ,x_edi_id_number                  => v_site.edi_id_number
              ,x_vendor_site_code_alt           => v_site.vendor_site_code_alt
              ,x_address_lines_alt              => v_site.address_lines_alt
              ,x_global_attribute_category      => v_site.global_attribute_category
              ,x_global_attribute1              => v_site.global_attribute1
              ,x_global_attribute2              => v_site.global_attribute2
              ,x_global_attribute3              => v_site.global_attribute3
              ,x_global_attribute4              => v_site.global_attribute4
              ,x_global_attribute5              => v_site.global_attribute5
              ,x_global_attribute6              => v_site.global_attribute6
              ,x_global_attribute7              => v_site.global_attribute7
              ,x_global_attribute8              => v_site.global_attribute8
              ,x_global_attribute9              => v_site.global_attribute9
              ,x_global_attribute10             => v_site.global_attribute10
              ,x_global_attribute11             => v_site.global_attribute11
              ,x_global_attribute12             => v_site.global_attribute12
              ,x_global_attribute13             => v_site.global_attribute13
              ,x_global_attribute14             => v_site.global_attribute14
              ,x_global_attribute15             => v_site.global_attribute15
              ,x_global_attribute16             => v_site.global_attribute16
              ,x_global_attribute17             => v_site.global_attribute17
              ,x_global_attribute18             => v_site.global_attribute18
              ,x_global_attribute19             => v_site.global_attribute19
              ,x_global_attribute20             => v_site.global_attribute20
              ,x_bank_charge_bearer             => v_site.bank_charge_bearer
              ,x_ece_tp_location_code           => v_site.ece_tp_location_code
              ,x_pcard_site_flag                => v_site.pcard_site_flag
              ,x_country_of_origin_code         => v_site.country_of_origin_code
              ,x_calling_sequence               => 'SQL SCRIPT – RFC7917'
              ,x_shipping_location_id           => NULL
              , --v_Site.shipping_location_id, -- do not update shipping location information
              x_supplier_notif_method          => v_site.supplier_notif_method
              ,x_email_address                  => v_site.email_address
              ,x_primary_pay_site_flag          => v_site.primary_pay_site_flag
              ,x_org_id                         => v_site.org_id
            );
          END LOOP;
        END IF;
      END IF;
    ELSE
      lv_user_id                                           := NULL;
      lv_responsibility_id                                 := NULL;
      lv_application_id                                    := NULL;
      BEGIN
        SELECT fu.user_id
              ,frt.responsibility_id
              ,frt.application_id INTO
          lv_user_id,lv_responsibility_id,lv_application_id
        FROM fnd_user fu
            ,fnd_responsibility_tl frt
        WHERE fu.user_name = 'SYSADMIN'
              AND   frt.responsibility_name = 'ABC Payables Supplier Setup';
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
      fnd_global.apps_initialize(
        lv_user_id
        ,lv_responsibility_id
        ,lv_application_id
      );
      mo_global.init('SQLAP');
      OPEN cur_org_id(l_vendor_sites.unit);
      FETCH cur_org_id INTO l_vendor_site_org_id;
      CLOSE cur_org_id;
      fnd_client_info.set_org_context(l_vendor_site_org_id);
      l_vendor_site_rec.last_update_date                   := SYSDATE;
      l_vendor_site_rec.last_updated_by                    := fnd_global.user_id;
      l_vendor_site_rec.vendor_id                          := p_vendor_id;
      l_vendor_site_rec.vendor_site_code                   := l_vendor_sites.vendor_site_code;
      l_vendor_site_rec.country                            := l_vendor_sites.vendor_site_country;
      l_vendor_site_rec.address_line1                      := l_vendor_sites.vendor_site_add1;
      l_vendor_site_rec.address_line2                      := l_vendor_sites.vendor_site_add2;
      l_vendor_site_rec.address_line3                      := l_vendor_sites.vendor_site_add3;
      l_vendor_site_rec.address_line4                      := l_vendor_sites.vendor_site_add4;
      l_vendor_site_rec.city                               := l_vendor_sites.vendor_site_city;
      l_vendor_site_rec.zip                                := l_vendor_sites.vendor_site_zip;
      l_vendor_site_rec.org_id                             := l_vendor_site_org_id;
      l_vendor_site_rec.area_code                          := l_vendor_sites.vendor_phone_area_code;
      l_vendor_site_rec.phone                              := l_vendor_sites.vendor_phone_number;
      l_vendor_site_rec.email_address                      := l_vendor_sites.vendor_email_address;
      l_vendor_site_rec.vat_registration_num               := l_vendor_sites.vat_registration_num;
      l_vendor_site_rec.ap_tax_rounding_rule               := 'NEAREST';
      l_vendor_site_rec.vat_code                           := 'STANDARD';
      l_vendor_site_rec.auto_tax_calc_flag                 := 'Y';
      –l_vendor_site_rec.tolerance_name                    := 'ABC – Tolerance';
      l_vendor_site_rec.supplier_notif_method              := 'PRINT';
      l_vendor_site_rec.purchasing_site_flag               := 'Y';
      l_vendor_site_rec.pay_site_flag                      := 'Y';
      l_vendor_site_rec.pay_group_lookup_code              := l_vendor_sites.pay_group_lookup_code;
      l_vendor_site_rec.hold_unmatched_invoices_flag       := 'Y';
      l_vendor_site_rec.hold_reason                        := 'UNMATCHED INVOICES';
      l_vendor_site_rec.ext_payee_rec.default_pmt_method   := 'EFT';
      l_vendor_site_rec.remittance_email                   := l_vendor_sites.remittance_email_address; --v0.2
      IF
        l_vendor_sites.remittance_email_address IS NOT NULL
      THEN
        l_vendor_site_rec.remit_advice_delivery_method   := 'EMAIL';
      END IF;
-- Call the API
      ap_vendor_pub_pkg.create_vendor_site(
        p_api_version        => 1.0
        ,p_init_msg_list      => fnd_api.g_true
        ,p_commit             => fnd_api.g_true
        ,p_validation_level   => fnd_api.g_valid_level_full
        ,x_return_status      => g_return_status
        ,x_msg_count          => g_msg_count
        ,x_msg_data           => g_msg_data
        ,p_vendor_site_rec    => l_vendor_site_rec
        ,x_vendor_site_id     => x_vendor_site_id
        ,x_party_site_id      => x_party_site_id
        ,x_location_id        => x_location_id
      );
      IF
        g_return_status <> fnd_api.g_ret_sts_success
      THEN
        FOR v_index IN 1..g_msg_count LOOP
          fnd_msg_pub.get(
            p_msg_index       => v_index
            ,p_encoded         => 'F'
            ,p_data            => g_msg_data
            ,p_msg_index_out   => g_msg_index_out
          );
          g_msg_data   := substr(
            g_msg_data
            ,1
            ,200
          );
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Error at Vendor site creation :'
            || g_msg_data
          );
          ROLLBACK;
        END LOOP;
      ELSE
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Vendor site: '
          || l_vendor_site_rec.vendor_site_code
          || ' created for vendor: '
          || lv_supplier_number
        );
/*API to update Communication details under Address Book section*/ --v0.4
        IF
          l_vendor_sites.vendor_phone_area_code IS NOT NULL OR l_vendor_sites.vendor_phone_number IS NOT NULL OR l_vendor_sites.vendor_email_address IS NOT NULL
        THEN
-- Initializing the Mandatory API parameters
          p_contact_point_rec.contact_point_type   := 'PHONE';
          p_contact_point_rec.owner_table_name     := 'HZ_PARTY_SITES';
          p_contact_point_rec.owner_table_id       := x_party_site_id;
          p_contact_point_rec.primary_flag         := 'Y';
          p_contact_point_rec.created_by_module    := 'POS_SUPPLIER_MGMT';
          p_phone_rec.phone_area_code              := l_vendor_sites.vendor_phone_area_code;
          p_phone_rec.phone_number                 := l_vendor_sites.vendor_phone_number;
          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_edi_rec             => p_edi_rec
            ,p_email_rec           => p_email_rec
            ,p_phone_rec           => p_phone_rec
            ,p_telex_rec           => p_telex_rec
            ,p_web_rec             => p_web_rec
            ,x_contact_point_id    => x_contact_point_id
            ,x_return_status       => g_return_status
            ,x_msg_count           => g_msg_count
            ,x_msg_data            => g_msg_data
          );
          IF
            g_return_status <> fnd_api.g_ret_sts_success
          THEN
            FOR i IN 1..g_msg_count LOOP
              g_msg_data   := fnd_msg_pub.get(
                p_msg_index   => i
                ,p_encoded     => 'F'
              );
              dbms_output.put_line(i
              || ') '
              || g_msg_data);
            END LOOP;
          END IF;
-- Initializing the Mandatory API parameters
          p_contact_point_rec.contact_point_type   := 'EMAIL';
          p_contact_point_rec.owner_table_name     := 'HZ_PARTY_SITES';
          p_contact_point_rec.owner_table_id       := x_party_site_id;
          p_contact_point_rec.primary_flag         := 'Y';
          p_contact_point_rec.created_by_module    := 'POS_SUPPLIER_MGMT';
          p_email_rec.email_address                := l_vendor_sites.vendor_email_address;
          hz_contact_point_v2pub.create_contact_point(
            p_init_msg_list       => fnd_api.g_true
            ,p_contact_point_rec   => p_contact_point_rec
            ,p_edi_rec             => p_edi_rec
            ,p_email_rec           => p_email_rec
            ,p_phone_rec           => p_phone_rec
            ,p_telex_rec           => p_telex_rec
            ,p_web_rec             => p_web_rec
            ,x_contact_point_id    => x_contact_point_id
            ,x_return_status       => g_return_status
            ,x_msg_count           => g_msg_count
            ,x_msg_data            => g_msg_data
          );
          IF
            g_return_status <> fnd_api.g_ret_sts_success
          THEN
            FOR i IN 1..g_msg_count LOOP
              g_msg_data   := fnd_msg_pub.get(
                p_msg_index   => i
                ,p_encoded     => 'F'
              );
              dbms_output.put_line(i
              || ') '
              || g_msg_data);
            END LOOP;
          END IF;
        END IF;
/*API to update Address Purpose to Purchasing*/
        p_party_site_use_rec.site_use_type       := 'PURCHASING';
        p_party_site_use_rec.party_site_id       := x_party_site_id;
        p_party_site_use_rec.created_by_module   := 'POS_SUPPLIER_MGMT';
        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        => g_return_status
          ,x_msg_count            => g_msg_count
          ,x_msg_data             => g_msg_data
        );
/*API to update Address Purpose to Payments*/
        p_party_site_use_rec.site_use_type       := 'PAY';
        p_party_site_use_rec.party_site_id       := x_party_site_id;
        p_party_site_use_rec.created_by_module   := 'POS_SUPPLIER_MGMT';
        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        => g_return_status
          ,x_msg_count            => g_msg_count
          ,x_msg_data             => g_msg_data
        );
        BEGIN
          SELECT party_tax_profile_id INTO
            l_party_tax_profile_id
          FROM zx_party_tax_profile
          WHERE party_id = x_party_site_id
                AND   party_type_code = 'THIRD_PARTY_SITE'
                AND   ROWNUM = 1;
        EXCEPTION
          WHEN OTHERS THEN
            l_party_tax_profile_id   := NULL;
        END;
        IF
          l_vendor_sites.vat_registration_num IS NOT NULL
        THEN
/*API to update Tax Registration Number at Supplier site level under Tax Details section*/
          zx_registrations_pkg.insert_row(
            p_request_id                  => NULL
            ,p_attribute1                  => NULL
            ,p_attribute2                  => NULL
            ,p_attribute3                  => NULL
            ,p_attribute4                  => NULL
            ,p_attribute5                  => NULL
            ,p_attribute6                  => NULL
            ,p_rounding_rule_code          => 'NEAREST'
            ,p_validation_rule             => NULL
            ,p_tax_jurisdiction_code       => NULL
            ,p_self_assess_flag            => NULL
            ,p_registration_status_code    => NULL
            ,p_registration_source_code    => NULL
            ,p_registration_reason_code    => NULL
            ,p_tax                         => 'GB VAT'
            ,p_tax_regime_code             => 'GB VAT'
            ,p_inclusive_tax_flag          => NULL
            ,p_effective_from              => SYSDATE
            ,p_effective_to                => NULL
            ,p_rep_party_tax_name          => NULL
            ,p_default_registration_flag   => NULL
            ,p_bank_account_num            => NULL
            ,p_record_type_code            => NULL
            ,p_legal_location_id           => NULL
            ,p_tax_authority_id            => NULL
            ,p_rep_tax_authority_id        => NULL
            ,p_coll_tax_authority_id       => NULL
            ,p_registration_type_code      => NULL
            ,p_registration_number         => l_vendor_sites.vat_registration_num
            ,p_party_tax_profile_id        => l_party_tax_profile_id
            ,p_legal_registration_id       => NULL
            ,p_bank_id                     => NULL
            ,p_bank_branch_id              => NULL
            ,p_account_site_id             => NULL
            ,p_attribute14                 => NULL
            ,p_attribute15                 => NULL
            ,p_attribute_category          => NULL
            ,p_program_login_id            => NULL
            ,p_account_id                  => NULL
            ,p_tax_classification_code     => NULL
            ,p_attribute7                  => NULL
            ,p_attribute8                  => NULL
            ,p_attribute9                  => NULL
            ,p_attribute10                 => NULL
            ,p_attribute11                 => NULL
            ,p_attribute12                 => NULL
            ,p_attribute13                 => NULL
            ,x_return_status               => g_return_status
          );
/*API to update Tax Registration Number at Supplier site level under Tax and Reporting section*/
          FOR v_site IN ( SELECT pvsa.*
                                ,pvsa.rowid
                          FROM po_vendor_sites_all pvsa
                          WHERE party_site_id = x_party_site_id
          ) LOOP
            ap_vendor_sites_pkg.update_row(
              x_rowid                          => v_site.rowid
              ,x_vendor_site_id                 => v_site.vendor_site_id
              ,x_last_update_date               => v_site.last_update_date
              ,x_last_updated_by                => v_site.last_updated_by
              ,x_vendor_id                      => v_site.vendor_id
              ,x_vendor_site_code               => v_site.vendor_site_code
              ,x_last_update_login              => v_site.last_update_login
              ,x_creation_date                  => v_site.creation_date
              ,x_created_by                     => v_site.created_by
              ,x_purchasing_site_flag           => v_site.purchasing_site_flag
              ,x_rfq_only_site_flag             => v_site.rfq_only_site_flag
              ,x_pay_site_flag                  => v_site.pay_site_flag
              ,x_attention_ar_flag              => v_site.attention_ar_flag
              ,x_address_line1                  => v_site.address_line1
              ,x_address_line2                  => v_site.address_line2
              ,x_address_line3                  => v_site.address_line3
              ,x_city                           => v_site.city
              ,x_state                          => v_site.state
              ,x_zip                            => v_site.zip
              ,x_province                       => v_site.province
              ,x_country                        => v_site.country
              ,x_area_code                      => v_site.area_code
              ,x_phone                          => v_site.phone
              ,x_customer_num                   => v_site.customer_num
              ,x_ship_to_location_id            => v_site.ship_to_location_id
              ,x_bill_to_location_id            => v_site.bill_to_location_id
              ,x_ship_via_lookup_code           => v_site.ship_via_lookup_code
              ,x_freight_terms_lookup_code      => v_site.freight_terms_lookup_code
              ,x_fob_lookup_code                => v_site.fob_lookup_code
              ,x_inactive_date                  => v_site.inactive_date
              ,x_fax                            => v_site.fax
              ,x_fax_area_code                  => v_site.fax_area_code
              ,x_telex                          => v_site.telex
              ,x_bank_account_name              => v_site.bank_account_name
              ,x_bank_account_num               => v_site.bank_account_num
              ,x_bank_num                       => v_site.bank_num
              ,x_bank_account_type              => v_site.bank_account_type
              ,x_terms_date_basis               => v_site.terms_date_basis
              ,x_current_catalog_num            => v_site.current_catalog_num
              ,x_distribution_set_id            => v_site.distribution_set_id
              ,x_accts_pay_ccid                 => v_site.accts_pay_code_combination_id
              ,x_future_dated_payment_ccid      => v_site.future_dated_payment_ccid
              ,x_prepay_code_combination_id     => v_site.prepay_code_combination_id
              ,x_pay_group_lookup_code          => v_site.pay_group_lookup_code
              ,x_payment_priority               => v_site.payment_priority
              ,x_terms_id                       => v_site.terms_id
              ,x_invoice_amount_limit           => v_site.invoice_amount_limit
              ,x_pay_date_basis_lookup_code     => v_site.pay_date_basis_lookup_code
              ,x_always_take_disc_flag          => v_site.always_take_disc_flag
              ,x_invoice_currency_code          => v_site.invoice_currency_code
              ,x_payment_currency_code          => v_site.payment_currency_code
              ,x_hold_all_payments_flag         => v_site.hold_all_payments_flag
              ,x_hold_future_payments_flag      => v_site.hold_future_payments_flag
              ,x_hold_reason                    => v_site.hold_reason
              ,x_hold_unmatched_invoices_flag   => v_site.hold_unmatched_invoices_flag
              ,x_match_option                   => v_site.match_option
              ,x_create_debit_memo_flag         => v_site.create_debit_memo_flag
              ,x_tax_reporting_site_flag        => v_site.tax_reporting_site_flag
              ,x_attribute_category             => v_site.attribute_category
              ,x_attribute1                     => v_site.attribute1
              ,x_attribute2                     => v_site.attribute2
              ,x_attribute3                     => v_site.attribute3
              ,x_attribute4                     => v_site.attribute4
              ,x_attribute5                     => v_site.attribute5
              ,x_attribute6                     => v_site.attribute6
              ,x_attribute7                     => v_site.attribute7
              ,x_attribute8                     => v_site.attribute8
              ,x_attribute9                     => v_site.attribute9
              ,x_attribute10                    => v_site.attribute10
              ,x_attribute11                    => v_site.attribute11
              ,x_attribute12                    => v_site.attribute12
              ,x_attribute13                    => v_site.attribute13
              ,x_attribute14                    => v_site.attribute14
              ,x_attribute15                    => v_site.attribute15
              ,x_validation_number              => v_site.validation_number
              ,x_exclude_freight_from_disc      => v_site.exclude_freight_from_discount
              ,x_vat_registration_num           => l_vendor_sites.vat_registration_num
              ,--v_Site.vat_registration_num,
              x_check_digits                   => v_site.check_digits
              ,x_bank_number                    => v_site.bank_number
              , --v0.3
              x_address_line4                  => v_site.address_line4
              ,x_county                         => v_site.county
              ,x_address_style                  => v_site.address_style
              ,x_language                       => v_site.language
              ,x_allow_awt_flag                 => v_site.allow_awt_flag
              ,x_awt_group_id                   => v_site.awt_group_id
              ,x_pay_awt_group_id               => v_site.pay_awt_group_id
              ,x_pay_on_code                    => v_site.pay_on_code
              ,x_default_pay_site_id            => v_site.default_pay_site_id
              ,x_pay_on_receipt_summary_code    => v_site.pay_on_receipt_summary_code
              ,x_bank_branch_type               => v_site.bank_branch_type
              ,x_edi_id_number                  => v_site.edi_id_number
              ,x_vendor_site_code_alt           => v_site.vendor_site_code_alt
              ,x_address_lines_alt              => v_site.address_lines_alt
              ,x_global_attribute_category      => v_site.global_attribute_category
              ,x_global_attribute1              => v_site.global_attribute1
              ,x_global_attribute2              => v_site.global_attribute2
              ,x_global_attribute3              => v_site.global_attribute3
              ,x_global_attribute4              => v_site.global_attribute4
              ,x_global_attribute5              => v_site.global_attribute5
              ,x_global_attribute6              => v_site.global_attribute6
              ,x_global_attribute7              => v_site.global_attribute7
              ,x_global_attribute8              => v_site.global_attribute8
              ,x_global_attribute9              => v_site.global_attribute9
              ,x_global_attribute10             => v_site.global_attribute10
              ,x_global_attribute11             => v_site.global_attribute11
              ,x_global_attribute12             => v_site.global_attribute12
              ,x_global_attribute13             => v_site.global_attribute13
              ,x_global_attribute14             => v_site.global_attribute14
              ,x_global_attribute15             => v_site.global_attribute15
              ,x_global_attribute16             => v_site.global_attribute16
              ,x_global_attribute17             => v_site.global_attribute17
              ,x_global_attribute18             => v_site.global_attribute18
              ,x_global_attribute19             => v_site.global_attribute19
              ,x_global_attribute20             => v_site.global_attribute20
              ,x_bank_charge_bearer             => v_site.bank_charge_bearer
              ,x_ece_tp_location_code           => v_site.ece_tp_location_code
              ,x_pcard_site_flag                => v_site.pcard_site_flag
              ,x_country_of_origin_code         => v_site.country_of_origin_code
              ,x_calling_sequence               => 'SQL SCRIPT – RFC7917'
              ,x_shipping_location_id           => NULL
              , --v_Site.shipping_location_id, -- do not update shipping location information
              x_supplier_notif_method          => v_site.supplier_notif_method
              ,x_email_address                  => v_site.email_address
              ,x_primary_pay_site_flag          => v_site.primary_pay_site_flag
              ,x_org_id                         => v_site.org_id
            );
          END LOOP;
        END IF;
      END IF;
    END IF;
    CLOSE cur_stg_tab;
  END xxab_create_vendor_site;
--------------------------------------------------
-- * Procedure Name : xxab_create_vendor_contact
-- * Description : This Procedure creates the Supplier site
-- * contacts
--------------------------------------------------
  PROCEDURE xxab_create_vendor_contact (
    p_vendor_id        IN NUMBER
    ,p_vendor_site_id   IN NUMBER
  ) IS
    l_vendor_contact_rec   apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
    x_vendor_contact_id    NUMBER;
    x_per_party_id         NUMBER;
    x_rel_party_id         NUMBER;
    x_rel_id               NUMBER;
    x_org_contact_id       NUMBER;
    x_party_site_id        NUMBER;
    lv_vendor_number       VARCHAR2(100);
    lv_vendor_site_code    VARCHAR2(100);
    l_vendor_site_org_id   VARCHAR2(50);
    CURSOR cur_sup_tab (
      p_vendor_id NUMBER
    ) IS SELECT segment1 FROM ap_suppliers WHERE vendor_id = p_vendor_id;
    CURSOR cur_sup_site_tab (
      p_vendor_number VARCHAR2
    ) IS SELECT vendor_site_code FROM ap_supplier_sites_all WHERE vendor_id = p_vendor_id;
    CURSOR cur_stg_tab (
      p_vendor_number VARCHAR2
      ,p_vendor_site_code VARCHAR2
    ) IS SELECT *
         FROM xxab_supp_stg_tab
         WHERE 1 = 1
               AND   vendor_number = p_vendor_number
               AND   vendor_site_code = p_vendor_site_code;
    l_site_contacts        cur_stg_tab%rowtype;
    CURSOR cur_org_id (
      p_unit VARCHAR2
    ) IS SELECT organization_id
         FROM hr_all_organization_units
         WHERE name = DECODE(
      p_unit
      ,'USABC'
      ,'ABCAAA'
      ,'USDEF'
      ,'ABCAAA'
      ,'USXYZ'
      ,'ABC'
    );
  BEGIN
    OPEN cur_sup_tab(p_vendor_id);
    FETCH cur_sup_tab INTO lv_vendor_number;
    CLOSE cur_sup_tab;
    OPEN cur_sup_site_tab(lv_vendor_number);
    FETCH cur_sup_site_tab INTO lv_vendor_site_code;
    CLOSE cur_sup_site_tab;
    OPEN cur_stg_tab(
      lv_vendor_number
      ,lv_vendor_site_code
    );
    LOOP
      FETCH cur_stg_tab INTO l_site_contacts;
      EXIT WHEN cur_stg_tab%notfound;
      OPEN cur_org_id(l_site_contacts.unit);
      FETCH cur_org_id INTO l_vendor_site_org_id;
      CLOSE cur_org_id;
-- Assign Contact Details
      l_vendor_contact_rec.vendor_id           := p_vendor_id;
      l_vendor_contact_rec.vendor_site_id      := p_vendor_site_id;
      l_vendor_contact_rec.person_first_name   := l_site_contacts.vendor_contact_first_name;
      l_vendor_contact_rec.person_last_name    := l_site_contacts.vendor_contact_last_name;
      l_vendor_contact_rec.email_address       := l_site_contacts.vendor_contact_email;
      l_vendor_contact_rec.org_id              := l_vendor_site_org_id;
      l_vendor_contact_rec.person_title        := l_site_contacts.person_title;
      IF
        ( l_site_contacts.vendor_contact_first_name IS NOT NULL OR l_site_contacts.vendor_contact_last_name IS NOT NULL OR l_site_contacts.vendor_contact_email IS NOT NULL )
      THEN
        ap_vendor_pub_pkg.create_vendor_contact(
          p_api_version          => 1.0
          ,p_init_msg_list        => fnd_api.g_true
          ,p_commit               => fnd_api.g_true
          ,p_validation_level     => fnd_api.g_valid_level_full
          ,x_return_status        => g_return_status
          ,x_msg_count            => g_msg_count
          ,x_msg_data             => g_msg_data
          ,p_vendor_contact_rec   => l_vendor_contact_rec
          ,x_vendor_contact_id    => x_vendor_contact_id
          ,x_per_party_id         => x_per_party_id
          ,x_rel_party_id         => x_rel_party_id
          ,x_rel_id               => x_rel_id
          ,x_org_contact_id       => x_org_contact_id
          ,x_party_site_id        => x_party_site_id
        );
        IF
          g_return_status <> fnd_api.g_ret_sts_success
        THEN
          FOR v_index IN 1..fnd_msg_pub.count_msg LOOP
            fnd_msg_pub.get(
              p_msg_index       => v_index
              ,p_encoded         => 'F'
              ,p_data            => g_msg_data
              ,p_msg_index_out   => g_msg_index_out
            );
            g_msg_data   := substr(
              g_msg_data
              ,1
              ,200
            );
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error at Vendor site contacts :'
              || g_msg_data
            );
            ROLLBACK;
          END LOOP;
        ELSE
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Vendor site contact '
            || l_site_contacts.vendor_contact_first_name
            || ' '
            || l_site_contacts.vendor_contact_last_name
            || ' created for vendor: '
            || lv_vendor_number
          );
        END IF;
      ELSE
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Missing Information – Vendor contact details does not exist for vendor :'
          || lv_vendor_number
          || '..!'
        );
      END IF;
    END LOOP;
    CLOSE cur_stg_tab;
  END xxab_create_vendor_contact;
--------------------------------------------------------
-- * Procedure Name : xxab_create_vendor_tax
-- * Description : This Procedure creates the Supplier Tax
--------------------------------------------------------
  PROCEDURE xxab_create_vendor_tax (
    p_vendor_party_id IN NUMBER
  ) IS
    lv_party_tax_profile_id      VARCHAR2(50);
    lv_tax_registration_number   VARCHAR2(50);
    lv_vendor_number             VARCHAR2(50);
    CURSOR cur_ven_tax IS SELECT party_tax_profile_id FROM zx_party_tax_profile WHERE party_id = p_vendor_party_id;
  BEGIN
    OPEN cur_ven_tax;
    FETCH cur_ven_tax INTO lv_party_tax_profile_id;
    CLOSE cur_ven_tax;
    zx_party_tax_profile_pkg.update_row(
      p_party_tax_profile_id           => lv_party_tax_profile_id
      ,p_collecting_authority_flag      => NULL
      ,p_provider_type_code             => NULL
      ,p_create_awt_dists_type_code     => NULL
      ,p_create_awt_invoices_type_cod   => NULL
      ,p_tax_classification_code        => NULL
      ,p_self_assess_flag               => NULL
      ,p_allow_offset_tax_flag          => NULL
      ,p_rep_registration_number        => NULL
      ,p_effective_from_use_le          => NULL
      ,p_record_type_code               => NULL
      ,p_request_id                     => NULL
      ,p_attribute1                     => NULL
      ,p_attribute2                     => NULL
      ,p_attribute3                     => NULL
      ,p_attribute4                     => NULL
      ,p_attribute5                     => NULL
      ,p_attribute6                     => NULL
      ,p_attribute7                     => NULL
      ,p_attribute8                     => NULL
      ,p_attribute9                     => NULL
      ,p_attribute10                    => NULL
      ,p_attribute11                    => NULL
      ,p_attribute12                    => NULL
      ,p_attribute13                    => NULL
      ,p_attribute14                    => NULL
      ,p_attribute15                    => NULL
      ,p_attribute_category             => NULL
      ,p_party_id                       => NULL
      ,p_program_login_id               => NULL
      ,p_party_type_code                => NULL
      ,p_supplier_flag                  => NULL
      ,p_customer_flag                  => NULL
      ,p_site_flag                      => NULL
      ,p_process_for_applicability_fl   => NULL
      ,p_rounding_level_code            => NULL
      ,p_rounding_rule_code             => 'NEAREST'
      ,p_withholding_saaat_date         => NULL
      ,p_inclusive_tax_flag             => NULL
      ,p_allow_awt_flag                 => NULL
      ,p_use_le_as_subscriber_flag      => NULL
      ,p_legal_establishment_flag       => NULL
      ,p_first_party_le_flag            => NULL
      ,p_reporting_authority_flag       => NULL
      ,x_return_status                  => g_return_status
      ,p_registration_type_code         => NULL
      ,p_country_code                   => NULL
    );
    IF
      g_return_status <> fnd_api.g_ret_sts_success
    THEN
      FOR v_index IN 1..fnd_msg_pub.count_msg LOOP
        fnd_msg_pub.get(
          p_msg_index       => v_index
          ,p_encoded         => 'F'
          ,p_data            => g_msg_data
          ,p_msg_index_out   => g_msg_index_out
        );
        g_msg_data   := substr(
          g_msg_data
          ,1
          ,200
        );
        dbms_output.put_line('Error at Vendor tax creation :'
        || g_msg_data);
        ROLLBACK;
      END LOOP;
    ELSE
      dbms_output.put_line('Tax details updated succesfully');
    END IF;
  END xxab_create_vendor_tax;
----------------------------------------------------------
-- * Procedure Name : xxab_bank_account_creation
-- * Description : This Procedure creates the Supplier bank
-- * account
----------------------------------------------------------
  PROCEDURE xxab_bank_account_creation (
    p_vendor_id IN NUMBER
  ) IS
    x_response            iby_fndcpt_common_pub.result_rec_type;
    p_ext_bank_acct_rec   iby_ext_bankacct_pub.extbankacct_rec_type;
    v_supplier_party_id   NUMBER;
    x_acct_id             NUMBER;
    p_count               NUMBER;
    lv_supplier_number    VARCHAR2(100);
    lv_supplier_name      VARCHAR2(1000);
    ln_party_id           NUMBER;
    ln_bank_party_id      NUMBER;
    ln_branch_party_id    NUMBER;
    CURSOR cur_sup_tab (
      p_vendor_id NUMBER
    ) IS SELECT segment1
               ,party_id
               ,vendor_name
         FROM ap_suppliers
         WHERE vendor_id = p_vendor_id;
    CURSOR cur_stg_tab (
      p_supplier_number VARCHAR2
      ,p_supplier_name VARCHAR2
    ) IS SELECT *
         FROM xxab_supp_stg_tab
         WHERE 1 = 1
               AND   vendor_number = p_supplier_number
               AND   vendor_name = p_supplier_name;
    l_vendor_bank         cur_stg_tab%rowtype;
  BEGIN
    OPEN cur_sup_tab(p_vendor_id);
    FETCH cur_sup_tab INTO lv_supplier_number,ln_party_id,lv_supplier_name;
    CLOSE cur_sup_tab;
    OPEN cur_stg_tab(
      lv_supplier_number
      ,lv_supplier_name
    );
    FETCH cur_stg_tab INTO l_vendor_bank;
    IF
      ( l_vendor_bank.bank_name IS NOT NULL AND l_vendor_bank.bank_number IS NOT NULL ) AND ( l_vendor_bank.bank_account_number IS NOT NULL AND l_vendor_bank.bank_account_name IS NOT NULL )
    THEN
      BEGIN
        SELECT bank_party_id INTO
          ln_bank_party_id
        FROM iby_ext_banks_v
        WHERE 1 = 1
              AND   bank_name = l_vendor_bank.bank_name
              AND   bank_number = l_vendor_bank.bank_number;
      EXCEPTION
        WHEN OTHERS THEN
          ln_bank_party_id   := NULL;
      END;
      IF
        nvl(
          ln_bank_party_id
          ,1
        ) <> 1
      THEN
        BEGIN
          SELECT branch_party_id INTO
            ln_branch_party_id
          FROM iby_ext_bank_branches_v
          WHERE bank_party_id = ln_bank_party_id;
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;
        p_ext_bank_acct_rec.acct_owner_party_id        := ln_party_id;
        p_ext_bank_acct_rec.bank_account_name          := l_vendor_bank.bank_account_name;
        p_ext_bank_acct_rec.bank_account_num           := l_vendor_bank.bank_account_number;
        p_ext_bank_acct_rec.bank_id                    := ln_bank_party_id;
        p_ext_bank_acct_rec.branch_id                  := ln_branch_party_id;
        p_ext_bank_acct_rec.saaat_date                 := SYSDATE;
        p_ext_bank_acct_rec.country_code               := 'GB';
        p_ext_bank_acct_rec.currency                   := 'GBP';
        p_ext_bank_acct_rec.foreign_payment_use_flag   := 'N';
        p_ext_bank_acct_rec.payment_factor_flag        := 'N';
        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   => p_ext_bank_acct_rec
          ,x_acct_id             => x_acct_id
          ,x_return_status       => g_return_status
          ,x_msg_count           => g_msg_count
          ,x_msg_data            => g_msg_data
          ,x_response            => x_response
        );
        IF
          g_return_status <> fnd_api.g_ret_sts_success
        THEN
          LOOP
            p_count      := p_count + 1;
            g_msg_data   := fnd_msg_pub.get(
              fnd_msg_pub.g_next
              ,fnd_api.g_false
            );
            IF
              g_msg_data IS NULL
            THEN
              EXIT;
            END IF;
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error at bank account creation '
              || p_count
              || ' —'
              || g_msg_data
            );
            ROLLBACK;
          END LOOP;
        ELSE
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Vendor site bank account created for vendor: '
            || lv_supplier_number
          );
        END IF;
      ELSE
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Missing Information – Bank details provided are invalid for vendor: '
          || lv_supplier_number
          || '. Please enter manually in application..!'
        );
      END IF;
    ELSE
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Missing Information – Bank details are not provided for vendor: '
        || lv_supplier_number
        || '. Please enter manually in application..!'
      );
    END IF;
  END xxab_bank_account_creation;
----------------------------------------------------------
-- * Procedure Name : xxab_bank_account_site_assg
-- * Description : This Procedure assigns the Supplier bank
-- * account at Site level
----------------------------------------------------------
  PROCEDURE xxab_bank_account_site_assg (
    p_vendor_id IN NUMBER
  ) IS
--Setting payee Instrument Assignment varibales
    lr_payee_rec             iby_disbursement_setup_pub.payeecontext_rec_type;
    lr_assg_attr             iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
    ln_assg_id               NUMBER;
    lr_response              iby_fndcpt_common_pub.result_rec_type;
    ln_acct_id               NUMBER;
    l_ext_bank_account_id    NUMBER;
    lv_bank_name             VARCHAR2(200);
    lv_bank_number           VARCHAR2(200);
    ln_bank_account_number   VARCHAR2(200);
    CURSOR cur_sup_tab (
      p_vendor_id NUMBER
    ) IS SELECT a.party_id
               ,b.org_id
               ,b.party_site_id
               ,b.vendor_site_id
               ,a.segment1
               ,a.vendor_name
         FROM ap_suppliers a
             ,ap_supplier_sites_all b
         WHERE a.vendor_id = p_vendor_id
               AND   a.vendor_id = b.vendor_id;
    l_vendor_bank_acnt       cur_sup_tab%rowtype;
  BEGIN
    OPEN cur_sup_tab(p_vendor_id);
    FETCH cur_sup_tab INTO l_vendor_bank_acnt;
    BEGIN
      SELECT bank_name
            ,bank_number
            ,bank_account_number INTO
        lv_bank_name,lv_bank_number,ln_bank_account_number
      FROM xxab_supp_stg_tab
      WHERE 1 = 1
            AND   vendor_number = l_vendor_bank_acnt.segment1
            AND   vendor_name = l_vendor_bank_acnt.vendor_name
            AND   ROWNUM = 1;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    IF
      lv_bank_name IS NOT NULL AND lv_bank_number IS NOT NULL AND ln_bank_account_number IS NOT NULL
    THEN
      BEGIN
        SELECT ext_bank_account_id INTO
          l_ext_bank_account_id
        FROM iby_ext_bank_accounts
        WHERE bank_id = ( SELECT bank_party_id
                          FROM iby_ext_banks_v
                          WHERE 1 = 1
                                AND   bank_name = lv_bank_name
                                AND   bank_number = lv_bank_number
                                AND   bank_account_num = ln_bank_account_number
        );
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
      lr_payee_rec.payment_function             := 'PAYABLES_DISB';
      lr_payee_rec.party_id                     := l_vendor_bank_acnt.party_id;
      lr_payee_rec.org_type                     := 'OPERATING_UNIT';
      lr_payee_rec.org_id                       := l_vendor_bank_acnt.org_id;
      lr_payee_rec.party_site_id                := l_vendor_bank_acnt.party_site_id;
      lr_payee_rec.supplier_site_id             := l_vendor_bank_acnt.vendor_site_id;
      lr_assg_attr.instrument.instrument_type   := 'BANKACCOUNT';
      lr_assg_attr.instrument.instrument_id     := l_ext_bank_account_id;
      lr_assg_attr.priority                     := 1;
      lr_assg_attr.saaat_date                   := SYSDATE;
      lr_assg_attr.end_date                     := NULL;
      iby_disbursement_setup_pub.set_payee_instr_assignment(
        p_api_version          => 1.0
        ,p_init_msg_list        => fnd_api.g_true
        ,p_commit               => fnd_api.g_true
        ,x_return_status        => g_return_status
        ,x_msg_count            => g_msg_count
        ,x_msg_data             => g_msg_data
        ,p_payee                => lr_payee_rec
        ,p_assignment_attribs   => lr_assg_attr
        ,x_assign_id            => ln_assg_id
        ,x_response             => lr_response
      );
      IF
        g_msg_count = 0
      THEN
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Vendor site bank account assigned to vendor :'
          || l_vendor_bank_acnt.segment1
        );
      END IF;
    END IF;
    CLOSE cur_sup_tab;
  EXCEPTION
    WHEN OTHERS THEN
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Error in SET_PAYEE_INSTR_ASSIGNMENT: '
        || sqlerrm
      );
  END xxab_bank_account_site_assg;
  ------------------------------
-- * Procedure Name : xxab_create_vendor
-- * Description : This main Procedure creates the Supplier
-- * and call procedures internally to
-- * create Sites, Contacts and Tax details
------------------------------
  PROCEDURE xxab_create_vendor (
    p_errbuf    OUT NOCOPY VARCHAR2
    ,p_retcode   OUT NOCOPY VARCHAR2
  ) IS
    l_vendor_rec             apps.ap_vendor_pub_pkg.r_vendor_rec_type;
    x_vendor_id              NUMBER;
    x_party_id               NUMBER;
    x_party_site_id          NUMBER;
    lv_vendor_number         VARCHAR2(100);
    lv_vendor_name           VARCHAR2(1000);
    lv_registration_number   VARCHAR2(50);
    l_rowcount               NUMBER;
    v_resp_name              VARCHAR2(50);
    p_unit                   VARCHAR2(100);
    CURSOR cur_stg_tab (
      p_unit VARCHAR2
    ) IS SELECT DISTINCT vendor_number
                        ,vendor_name
                        ,company_registration_number
                        ,primary_category
                        ,hold_from_payment
                        ,organization_type
                        ,payment_hold_reason
                        ,invoice_currency_code
                        ,payment_currency_code
                        ,payment_priority
                        ,inspection_required_flag
                        ,receipt_required_flag
                        ,qty_rcv_tolerance
                        ,qty_rcv_exception_code
                        ,enforce_ship_to_location_code
                        ,days_early_receipt_allowed
                        ,days_late_receipt_allowed
                        ,receipt_days_exception_code
                        ,receiving_routing_id
                        ,terms_name
                        ,vat_registration_num
         FROM xxab_supp_stg_tab
         WHERE unit = p_unit
    ORDER BY vendor_number;
    l_vendors                cur_stg_tab%rowtype;
  BEGIN
    v_resp_name                                     := fnd_profile.value('RESP_NAME');
    fnd_global.resp_appl_id;
    IF
      v_resp_name = 'ABC Payables Supplier Setup'
    THEN
      p_unit   := ( 'USABC, USDEF' );
    ELSIF v_resp_name = 'Payables AB Supplier Setup' THEN
      p_unit   := 'USXYZ';
    ELSE
      p_retcode   := 2;
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Please select valid responsibility to execute Supplier conversion'
      );
    END IF;
    FOR cur_unit IN ( SELECT TRIM(regexp_substr(
      upper(p_unit)
      ,'[^,]+'
      ,1
      ,level
    ) ) unit
                      FROM dual CONNECT BY
      regexp_substr(
        upper(p_unit)
        ,'[^,]+'
        ,1
        ,level
      ) IS NOT NULL
    ) LOOP
      OPEN cur_stg_tab(cur_unit.unit);
      LOOP
        l_rowcount                                      := cur_stg_tab%rowcount;
        FETCH cur_stg_tab INTO l_vendors;
        EXIT WHEN cur_stg_tab%notfound;
        fnd_file.put_line(
          fnd_file.log
          ,'————————————————————'
        );
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Fetching data for Vendor: '
          || l_vendors.vendor_number
        );
        lv_vendor_number                                := NULL;
        lv_vendor_name                                  := NULL;
        lv_registration_number                          := NULL;
        BEGIN
          SELECT segment1 INTO
            lv_vendor_number
          FROM ap_suppliers
          WHERE segment1 = TO_CHAR(
            l_vendors.vendor_number
          );
        EXCEPTION
          WHEN no_data_found THEN
            lv_vendor_number   := 'A';
          WHEN OTHERS THEN
            lv_vendor_number   := 'A';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,sqlerrm
            );
        END;
        BEGIN
          SELECT vendor_name INTO
            lv_vendor_name
          FROM ap_suppliers
          WHERE vendor_name = TO_CHAR(
            l_vendors.vendor_name
          );
        EXCEPTION
          WHEN no_data_found THEN
            lv_vendor_name   := 'A';
          WHEN OTHERS THEN
            lv_vendor_name   := 'A';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,sqlerrm
            );
        END;
        BEGIN
          SELECT rep_registration_number INTO
            lv_registration_number
          FROM zx_party_tax_profile
          WHERE rep_registration_number = l_vendors.vat_registration_num
                AND   ROWNUM = 1;
        EXCEPTION
          WHEN no_data_found THEN
            lv_registration_number   := 'A';
          WHEN OTHERS THEN
            lv_registration_number   := 'A';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,sqlerrm
            );
        END;
        IF
          nvl(
            lv_vendor_number
            ,'A'
          ) <> l_vendors.vendor_number AND nvl(
            lv_registration_number
            ,'A'
          ) <> nvl(
            l_vendors.vat_registration_num
            ,'B'
          ) AND nvl(
            lv_vendor_name
            ,'A'
          ) <> l_vendors.vendor_name
        THEN
          l_vendor_rec.vendor_id                          := NULL;
          l_vendor_rec.segment1                           := l_vendors.vendor_number;
          l_vendor_rec.vendor_name                        := l_vendors.vendor_name;
          l_vendor_rec.attribute2                         := l_vendors.company_registration_number;
          l_vendor_rec.attribute3                         := l_vendors.primary_category;
          l_vendor_rec.hold_unmatched_invoices_flag       := l_vendors.hold_from_payment;
          l_vendor_rec.vendor_type_lookup_code            := l_vendors.organization_type;
          l_vendor_rec.hold_reason                        := l_vendors.payment_hold_reason;
          l_vendor_rec.invoice_currency_code              := l_vendors.invoice_currency_code;
          l_vendor_rec.payment_currency_code              := l_vendors.payment_currency_code;
          l_vendor_rec.payment_priority                   := l_vendors.payment_priority;
          l_vendor_rec.inspection_required_flag           := l_vendors.inspection_required_flag;
          l_vendor_rec.receipt_required_flag              := l_vendors.receipt_required_flag;
          l_vendor_rec.qty_rcv_tolerance                  := l_vendors.qty_rcv_tolerance;
          l_vendor_rec.qty_rcv_exception_code             := l_vendors.qty_rcv_exception_code;
          l_vendor_rec.enforce_ship_to_location_code      := l_vendors.enforce_ship_to_location_code;
          l_vendor_rec.days_early_receipt_allowed         := l_vendors.days_early_receipt_allowed;
          l_vendor_rec.days_late_receipt_allowed          := l_vendors.days_late_receipt_allowed;
          l_vendor_rec.receipt_days_exception_code        := l_vendors.receipt_days_exception_code;
          l_vendor_rec.receiving_routing_id               := l_vendors.receiving_routing_id;
          l_vendor_rec.terms_name                         := l_vendors.terms_name;
          l_vendor_rec.ext_payee_rec.default_pmt_method   := 'EFT';

 
-- Call the API
          ap_vendor_pub_pkg.create_vendor(
            p_api_version        => 1.0
            ,p_init_msg_list      => fnd_api.g_true
            ,p_commit             => fnd_api.g_true
            ,p_validation_level   => fnd_api.g_valid_level_full
            ,x_return_status      => g_return_status
            ,x_msg_count          => g_msg_count
            ,x_msg_data           => g_msg_data
            ,p_vendor_rec         => l_vendor_rec
            ,x_vendor_id          => x_vendor_id
            ,x_party_id           => x_party_id
          );
          IF
            ( g_return_status <> fnd_api.g_ret_sts_success )
          THEN
            FOR i IN 1..fnd_msg_pub.count_msg LOOP
              fnd_msg_pub.get(
                p_msg_index       => i
                ,p_encoded         => 'F'
                ,p_data            => g_msg_data
                ,p_msg_index_out   => g_msg_index_out
              );
              g_msg_data   := substr(
                g_msg_data
                ,1
                ,200
              );
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'Error at Vendor creation :'
                || g_msg_data
              );
            END LOOP;
          ELSE
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Vendor: '
              || l_vendors.vendor_number
              || ' created'
            );
            xxab_create_vendor_site(x_vendor_id);
            xxab_create_vendor_contact(
              x_vendor_id
              ,x_party_site_id
            );
            xxab_create_vendor_tax(x_party_id);
            xxab_update_vendor(x_vendor_id);
            xxab_bank_account_creation(x_vendor_id);
            xxab_bank_account_site_assg(x_vendor_id);
            COMMIT;
          END IF;
        ELSE
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Vendor Name/ Number or Tax registration number already exists for :'
            || l_vendors.vendor_number
          );
          p_retcode   := 1;
        END IF;
        COMMIT;
      END LOOP;
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Total number of records fetched for :'
        || cur_unit.unit
        || ' are '
        || l_rowcount
      );
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'*************************************************************'
      );
      IF
        l_rowcount = 0
      THEN
        p_retcode   := 1;
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Please check the data file..!'
        );
      END IF;
      CLOSE cur_stg_tab;
    END LOOP;
  END xxab_create_vendor;
END xxab_supplier_conversion;
/*
—————————————————————————————————————–
Alternate approach of Supplier creation through interface tables:
Supplier Creation
Maintain Data in Excel and upload it in interfaces table as mentioned below either using SQL Loader or WinSCP
Supplier Interface Table: AP_SUPPLIERS_INT
Mandatory Columns: VENDOR_INTERFACE_ID & VENDOR_NAME
Once data is uploaded in staging tables, use below mentioned requests to upload it
Request Name for Supplier: Supplier Open Interface Import
Supplier Site Creation
Supplier Site Interface Table: AP_SUPPLIER_SITES_INT
Mandatory Columns: VENDOR_SITE_INTERFACE_ID, VENDOR_SITE_CODE & VENDOR_ID
(Vendor ID created in AP_SUPPLIERS table after submission of Supplier Open Interface Import request.)
Once data is uploaded in staging tables, use below mentioned requests to upload it
Request Name for Supplier: Supplier Open Interface Import
Request Name for Supplier Sites: Supplier Sites Open Interface Import*/

3. Customer creation

CREATE OR REPLACE PACKAGE BODY xxar_xx_create_customer AS
/************************************************************************
* TYPE : Package *
* NAME : xxar_xx_create_customer *
* INPUT Parameters : operatting unit *
* OUTPUT Parametrs : out_errbuf,out_retcode *
* PURPOSE : xxtar and xx Customer Creation *
* *
* Author Date Ver Description *
* ——– ———- —- ————————————- *
* ABC 25/05/2018 1.0 Initial *
************************************************************************/
--------------------------------
-- Global Variable Declaration —
--------------------------------
  g_return_status             VARCHAR2(2000);
  g_msg_count                 NUMBER;
  g_msg_data                  VARCHAR2(2000);
--------------------------------
-- * Procedure Name : CREATE_CUST_ACCOUNT
-- * Description : This main Procedure creates the customer
-- * account and call procedures internally to
-- * create location and customer sites
--------------------------------
  PROCEDURE create_cust_account (
    x_errbuff          OUT VARCHAR2
    ,x_retcode          OUT VARCHAR2
    ,p_operating_unit   IN VARCHAR2
  ) IS
--------------------------------
-- Local Variable Declaration —
--------------------------------
    l_cust_account_rec          hz_cust_account_v2pub.cust_account_rec_type;
    l_organization_rec          hz_party_v2pub.organization_rec_type;
    l_customer_profile_rec      hz_customer_profile_v2pub.customer_profile_rec_type;
    l_cust_profile_amt_rec      hz_customer_profile_v2pub.cust_profile_amt_rec_type;
    l_error_flag                VARCHAR2(2000) := 'N';
    l_error_status              VARCHAR2(2000) := 'N';
    l_child_status              VARCHAR2(2000) := 'SUCCESS';
    l_location_id               hz_locations.location_id%TYPE;
    l_party_site_id             hz_party_sites.party_site_id%TYPE;
    l_cust_acc_site_id          hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
    l_site_use_id               hz_cust_site_uses_all.site_use_id%TYPE;
    l_collector_id              ar_collectors.collector_id%TYPE;
    l_application_id            fnd_application.application_id%TYPE;
    l_responsibility_id         fnd_responsibility.responsibility_id%TYPE;
    l_cust_account_id           hz_cust_accounts.cust_account_id%TYPE;
    l_account_number            hz_cust_accounts.account_number%TYPE;
    l_party_id                  hz_parties.party_id%TYPE;
    l_party_number              hz_parties.party_number%TYPE;
    l_profile_id                NUMBER;
    l_profile_class_id          NUMBER;
    l_status                    VARCHAR2(2000);
    l_msg_count                 VARCHAR2(2000);
    l_msg_data                  VARCHAR2(2000);
    l_cust_act_prof_amt_id      VARCHAR2(2000);
    l_cust_account_profile_id   NUMBER(15);
    l_customer_count            NUMBER;

--Cursor to get customer data
    CURSOR cur_data_file (
      p_operating_unit IN VARCHAR2
    ) IS SELECT DISTINCT party_name
                        ,account_name
                        ,payment_term_id
                        ,site_use_code
                        ,nvl(
      identifying_address_flag
      ,'Y'
    ) identifying_address_flag
                        ,collector
                        ,credit_rating
                        ,credit_classification
                        ,review_cycle
                        ,account_status
                        ,nvl(
      created_by_module
      ,'HZ_CPUI'
    ) created_by_module
                        ,profile_class
                        ,currency_code
                        ,overall_credit_limit
                        ,nvl(
      min_dunning_amount
      ,'25'
    ) min_dunning_amount
                        ,nvl(
      min_dunning_invoice_amount
      ,'25'
    ) min_dunning_invoice_amount
                        ,nvl(
      min_statement_amount
      ,'25'
    ) min_statement_amount
                        ,bu
         FROM xxar_xx_customer
         WHERE DECODE(
      bu
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
    ) = p_operating_unit;
--Cursor to get contact details
    CURSOR cur_create_contact (
      l_party_name IN VARCHAR2
    ) IS SELECT DISTINCT party_name
                        ,first_name
                        ,last_name
         FROM xxar_xx_customer
         WHERE party_name = l_party_name
               AND   DECODE(
      bu
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
    ) = p_operating_unit;
--Cursor to get address details
    CURSOR cur_address_data (
      l_party_name IN VARCHAR2
    ) IS SELECT DISTINCT party_name
                        ,address1
                        ,address2
                        ,address3
                        ,city
                        ,country
                        ,postal_code
         FROM xxar_xx_customer
         WHERE party_name = l_party_name
               AND   DECODE(
      bu
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
      ,'xx'
    ) = p_operating_unit;
  BEGIN
    IF
      p_operating_unit NOT IN (
        'xx'
        ,'xx'
      )
    THEN
      x_retcode   := '2';
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Error: Invalid Operating Unit'
      );
    END IF;
    BEGIN
      SELECT application_id INTO
        l_application_id
      FROM fnd_application
      WHERE application_short_name LIKE 'AR';
    EXCEPTION
      WHEN OTHERS THEN
        x_retcode   := '2';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error: Unable to fetch application id'
        );
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error: '
          || sqlerrm
        );
    END;
--Get values for new customer
    FOR rec_data_file IN cur_data_file(p_operating_unit) LOOP
      EXIT WHEN cur_data_file%notfound;
      l_error_flag                                        := 'N';
      l_child_status                                      := 'SUCCESS';
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'*********************************************************************'
      );
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Customer: '
        || rec_data_file.party_name
      );
      BEGIN
        SELECT COUNT(1) INTO
          l_customer_count
        FROM hz_parties
        WHERE party_name = rec_data_file.party_name;
      EXCEPTION
        WHEN OTHERS THEN
          l_error_flag   := 'Y';
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Error: While checking duplicate customer'
          );
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Error: '
            || sqlerrm
          );
      END;
      IF
        l_customer_count > 0
      THEN
        l_error_flag   := 'Y';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error: Customer with this name already exists'
        );
      END IF;
      IF
        rec_data_file.collector IS NOT NULL
      THEN
        BEGIN
          SELECT collector_id INTO
            l_collector_id
          FROM ar_collectors
          WHERE name = rec_data_file.collector;
        EXCEPTION
          WHEN OTHERS THEN
            l_error_flag   := 'Y';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error: Unable to fetch collector id'
            );
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error: '
              || sqlerrm
            );
        END;
      END IF;
      IF
        rec_data_file.profile_class IS NOT NULL
      THEN
        BEGIN
          SELECT profile_class_id INTO
            l_profile_class_id
          FROM hz_cust_profile_classes
          WHERE name = rec_data_file.profile_class;
        EXCEPTION
          WHEN OTHERS THEN
            l_error_flag   := 'Y';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error: Unable to fetch profile class'
            );
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error: '
              || sqlerrm
            );
        END;
      END IF;
      l_cust_account_rec.created_by_module                := rec_data_file.created_by_module;
      l_cust_account_rec.customer_type                    := 'R';
      l_cust_account_rec.account_name                     := rec_data_file.account_name;
      l_organization_rec.organization_name                := rec_data_file.party_name;
      l_organization_rec.created_by_module                := rec_data_file.created_by_module;
      l_customer_profile_rec.profile_class_id             := l_profile_class_id;
      l_customer_profile_rec.created_by_module            := rec_data_file.created_by_module;
      l_customer_profile_rec.collector_id                 := l_collector_id;
      l_customer_profile_rec.standard_terms               := rec_data_file.payment_term_id;
      l_customer_profile_rec.application_id               := l_application_id;
      l_customer_profile_rec.status                       := 'A';
      l_customer_profile_rec.credit_rating                := rec_data_file.credit_rating;
      l_customer_profile_rec.credit_classification        := rec_data_file.credit_classification;
      l_customer_profile_rec.review_cycle                 := rec_data_file.review_cycle;
      l_customer_profile_rec.account_status               := rec_data_file.account_status;

--Calling API to create customer account
      IF
        l_error_flag = 'N'
      THEN
        BEGIN
          hz_cust_account_v2pub.create_cust_account(
            p_init_msg_list          => 'T'
            ,p_cust_account_rec       => l_cust_account_rec
            ,p_organization_rec       => l_organization_rec
            ,p_customer_profile_rec   => l_customer_profile_rec
            ,p_create_profile_amt     => 'F'
            ,x_cust_account_id        => l_cust_account_id
            ,x_account_number         => l_account_number
            ,x_party_id               => l_party_id
            ,x_party_number           => l_party_number
            ,x_profile_id             => l_profile_id
            ,x_return_status          => g_return_status
            ,x_msg_count              => g_msg_count
            ,x_msg_data               => g_msg_data
          );
          BEGIN
            SELECT cust_account_profile_id INTO
              l_cust_account_profile_id
            FROM hz_customer_profiles
            WHERE cust_account_id = l_cust_account_id;
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'l_cust_account_id: '
              || l_cust_account_id
            );
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'l_cust_account_profile_id: '
              || l_cust_account_profile_id
            );
          EXCEPTION
            WHEN OTHERS THEN
              l_error_flag   := 'Y';
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'Error: Unable to fetch Customer account profile id'
              );
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'Error: '
                || sqlerrm
              );
          END;
          IF
            g_return_status = fnd_api.g_ret_sts_success AND l_error_flag = 'N'
          THEN
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Party Id : '
              || l_party_id
            );
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Party Number : '
              || l_party_number
            );
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Customer Account Created Sucessfully'
            );

--Create Profile Amount
            l_cust_profile_amt_rec.cust_account_profile_id      := l_cust_account_profile_id;
            l_cust_profile_amt_rec.cust_account_id              := l_cust_account_id;
            l_cust_profile_amt_rec.currency_code                := rec_data_file.currency_code;
            l_cust_profile_amt_rec.overall_credit_limit         := rec_data_file.overall_credit_limit;
            l_cust_profile_amt_rec.min_dunning_amount           := rec_data_file.min_dunning_amount;
            l_cust_profile_amt_rec.min_statement_amount         := rec_data_file.min_statement_amount;
            l_cust_profile_amt_rec.min_dunning_invoice_amount   := rec_data_file.min_dunning_invoice_amount;
            l_cust_profile_amt_rec.created_by_module            := rec_data_file.created_by_module;
            hz_customer_profile_v2pub.create_cust_profile_amt(
              p_init_msg_list              => 'T'
              ,p_check_foreign_key          => fnd_api.g_true
              ,p_cust_profile_amt_rec       => l_cust_profile_amt_rec
              ,x_cust_acct_profile_amt_id   => l_cust_act_prof_amt_id
              ,x_return_status              => l_status
              ,x_msg_count                  => l_msg_count
              ,x_msg_data                   => l_msg_data
            );
            IF
              l_status = fnd_api.g_ret_sts_success
            THEN
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'Customer Profile Amount Created Sucessfully'
              );
            ELSE
              l_error_flag     := 'Y';
              l_error_status   := 'Y';
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'ERROR: Customer Profile Amount not created'
              );
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'ERROR: '
                || l_msg_data
              );
            END IF;
          ELSE
            l_error_flag     := 'Y';
            l_error_status   := 'Y';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error: Unable to create Customer '
              || rec_data_file.party_name
            );
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error: '
              || g_msg_data
            );
            IF
              g_msg_count > 1
            THEN
              FOR i IN 1..g_msg_count LOOP
                apps.fnd_file.put_line(
                  apps.fnd_file.log
                  ,i
                  || '.'
                  || substr(
                    fnd_msg_pub.get(p_encoded   => fnd_api.g_false)
                    ,1
                    ,255
                  )
                );
              END LOOP;
            END IF;
          END IF;
        EXCEPTION
          WHEN OTHERS THEN
            l_error_flag     := 'Y';
            l_error_status   := 'Y';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error in Creating Customer: '
              || sqlerrm
            );
        END;
      END IF;
      IF
        g_return_status = fnd_api.g_ret_sts_success AND l_error_flag = 'N'
      THEN
        FOR rec_address_data IN cur_address_data(rec_data_file.party_name) LOOP
          EXIT WHEN cur_address_data%notfound;
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,'Calling the procedure to create location'
          );
          create_location(
            rec_address_data.country
            ,rec_address_data.address1
            ,rec_address_data.address2
            ,rec_address_data.address3
            ,rec_address_data.city
            ,rec_address_data.postal_code
            ,rec_data_file.created_by_module
            ,l_child_status
            ,l_location_id
          );
          IF
            l_child_status != 'ERROR' AND l_error_flag = 'N'
          THEN
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Calling the procedure to create party site'
            );
            create_party_site(
              l_party_id
              ,l_location_id
              ,rec_data_file.identifying_address_flag
              ,rec_data_file.created_by_module
              ,l_application_id
              ,l_child_status
              ,l_party_site_id
            );
            IF
              l_child_status != 'ERROR' AND l_error_flag = 'N'
            THEN
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'Calling the procedure to create customer account site'
              );
              create_cust_acct_site(
                l_cust_account_id
                ,l_party_site_id
                ,rec_data_file.created_by_module
                ,l_child_status
                ,l_cust_acc_site_id
              );
              IF
                l_child_status != 'ERROR' AND l_error_flag = 'N'
              THEN
                apps.fnd_file.put_line(
                  apps.fnd_file.log
                  ,'Calling the procedure to create customer account site use'
                );
                create_cust_site_use(
                  l_cust_acc_site_id
                  ,'BILL_TO'
                  ,rec_data_file.created_by_module
                  ,l_child_status
                  ,l_site_use_id
                );
                create_cust_site_use(
                  l_cust_acc_site_id
                  ,'INV'
                  ,rec_data_file.created_by_module
                  ,l_child_status
                  ,l_site_use_id
                );
                IF
                  l_child_status = 'ERROR' AND l_error_flag = 'N'
                THEN
                  l_error_status   := 'Y';
                  l_error_flag     := 'Y';
                  apps.fnd_file.put_line(
                    apps.fnd_file.log
                    ,'Error: Unable to create Customer Site Use'
                  );
                END IF;
              ELSE
                l_error_status   := 'Y';
                l_error_flag     := 'Y';
                apps.fnd_file.put_line(
                  apps.fnd_file.log
                  ,'Error: Unable to create Customer Account Site'
                );
              END IF;
            ELSE
              l_error_status   := 'Y';
              l_error_flag     := 'Y';
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'Error: Unable to create Party Site'
              );
            END IF;
          ELSE
            l_error_status   := 'Y';
            l_error_flag     := 'Y';
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Error: Unable to create Location'
            );
          END IF;
        END LOOP;
      END IF;
--create person
      IF
        l_child_status != 'ERROR' AND l_error_flag = 'N'
      THEN
        FOR rec_create_contact IN cur_create_contact(rec_data_file.party_name) LOOP
          EXIT WHEN cur_create_contact%notfound;
          IF
            rec_create_contact.first_name IS NOT NULL OR rec_create_contact.last_name IS NOT NULL
          THEN
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,'Calling procedure to create customer contact details'
            );
            create_person(
              rec_create_contact.first_name
              ,rec_create_contact.last_name
              ,rec_data_file.created_by_module
              ,l_party_id
              ,l_cust_account_id
              ,l_child_status
            );
            IF
              l_child_status = 'ERROR'
            THEN
              l_error_status   := 'Y';
              l_error_flag     := 'Y';
              apps.fnd_file.put_line(
                apps.fnd_file.log
                ,'Error: Unable to create customer contact'
              );
            END IF;
          END IF;
        END LOOP;
      ELSE
        l_error_status   := 'Y';
      END IF;
      IF
        l_error_flag = 'Y'
      THEN
        ROLLBACK;
      ELSE
        COMMIT;
      END IF;
    END LOOP;
    apps.fnd_file.put_line(
      apps.fnd_file.log
      ,'*********************************************************************'
    );
    IF
      l_error_status = 'Y'
    THEN
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'ERROR: One or more customer has not been created due to errors'
      );
    ELSE
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'SUCCESS: All Customers have been created successfully'
      );
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Error: '
        || sqlerrm
      );
  END create_cust_account;
----------------------------
-- * Procedure Name : CREATE_LOCATION
-- * Description : This Procedure creates the Location
----------------------------
  PROCEDURE create_location (
    p_country             IN VARCHAR2
    ,p_address1            IN VARCHAR2
    ,p_address2            IN VARCHAR2
    ,p_address3            IN VARCHAR2
    ,p_city                IN VARCHAR2
    ,p_postal_code         IN VARCHAR2
    ,p_created_by_module   IN VARCHAR2
    ,p_return              OUT VARCHAR2
    ,p_location_id         OUT VARCHAR2
  ) IS
    l_location_rec   hz_location_v2pub.location_rec_type;
    l_location_id    hz_locations.location_id%TYPE;
  BEGIN
    l_location_rec.country             := p_country;
    l_location_rec.address1            := p_address1;
    l_location_rec.address2            := p_address2;
    l_location_rec.address3            := p_address3;
    l_location_rec.city                := p_city;
    l_location_rec.postal_code         := p_postal_code;
    l_location_rec.created_by_module   := p_created_by_module;
    apps.fnd_file.put_line(
      apps.fnd_file.log
      ,'Calling the API hz_location_v2pub.create_location'
    );
    BEGIN
      hz_location_v2pub.create_location(
        p_init_msg_list   => fnd_api.g_true
        ,p_location_rec    => l_location_rec
        ,x_location_id     => p_location_id
        ,x_return_status   => g_return_status
        ,x_msg_count       => g_msg_count
        ,x_msg_data        => g_msg_data
      );
      IF
        g_return_status = fnd_api.g_ret_sts_success
      THEN
        p_return   := 'SUCCESS';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Creation of Location is Successful'
        );
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Location Id: '
          || p_location_id
        );
      ELSE
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error: Creation of Location failed: '
          || g_msg_data
        );
        p_return     := 'ERROR';
        FOR i IN 1..g_msg_count LOOP
          g_msg_data   := oe_msg_pub.get(
            p_msg_index   => i
            ,p_encoded     => 'F'
          );
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,i
            || ') '
            || g_msg_data
          );
        END LOOP;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        p_return   := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error in location: '
          || sqlerrm
        );
    END;
  END create_location;
----------------------------------------
-- * Procedure Name : CREATE_PARTY_SITE
-- * Description : This Procedure creates the party sites for the customer
----------------------------------------
  PROCEDURE create_party_site (
    p_party_id                   IN VARCHAR2
    ,p_location_id                IN VARCHAR2
    ,p_identifying_address_flag   IN VARCHAR2
    ,p_created_by_module          IN VARCHAR2
    ,p_application_id             IN VARCHAR2
    ,p_return                     OUT VARCHAR2
    ,p_party_site_id              OUT VARCHAR2
  ) IS
    l_party_site_rec      hz_party_site_v2pub.party_site_rec_type;
    l_party_site_id       hz_party_sites.party_site_id%TYPE;
    l_party_site_number   hz_party_sites.party_site_number%TYPE;
  BEGIN
    l_party_site_rec.party_id                   := p_party_id;
    l_party_site_rec.location_id                := p_location_id;
    l_party_site_rec.identifying_address_flag   := p_identifying_address_flag;
    l_party_site_rec.created_by_module          := p_created_by_module;
    l_party_site_rec.status                     := 'A';
    l_party_site_rec.application_id             := p_application_id;
    apps.fnd_file.put_line(
      apps.fnd_file.log
      ,'Calling the API hz_party_site_v2pub.create_party_site'
    );
    BEGIN
      hz_party_site_v2pub.create_party_site(
        p_init_msg_list       => fnd_api.g_true
        ,p_party_site_rec      => l_party_site_rec
        ,x_party_site_id       => p_party_site_id
        ,x_party_site_number   => l_party_site_number
        ,x_return_status       => g_return_status
        ,x_msg_count           => g_msg_count
        ,x_msg_data            => g_msg_data
      );
      IF
        g_return_status = fnd_api.g_ret_sts_success
      THEN
        p_return   := 'SUCCESS';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Creation of Party Site is Successful'
        );
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Party Site Id: '
          || p_party_site_id
        );
      ELSE
        p_return     := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'ERROR: Creation of Party Site failed: '
          || g_msg_data
        );
        FOR i IN 1..g_msg_count LOOP
          g_msg_data   := fnd_msg_pub.get(
            p_msg_index   => i
            ,p_encoded     => 'F'
          );
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,i
            || ') '
            || g_msg_data
          );
        END LOOP;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        p_return   := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error: '
          || sqlerrm
        );
    END;
  END create_party_site;
--------------------------------
-- * Procedure Name : CREATE_CUST_ACCT_SITE
-- * Description : This Procedure creates the customer site account
--------------------------------
  PROCEDURE create_cust_acct_site (
    p_cust_account_id     IN VARCHAR2
    ,p_party_site_id       IN VARCHAR2
    ,p_created_by_module   IN VARCHAR2
    ,p_return              OUT VARCHAR2
    ,p_cust_acc_site_id    OUT VARCHAR2
  ) IS
    l_cust_acct_site_rec   hz_cust_account_site_v2pub.cust_acct_site_rec_type;
  BEGIN
    l_cust_acct_site_rec.cust_account_id     := p_cust_account_id;
    l_cust_acct_site_rec.party_site_id       := p_party_site_id;
    l_cust_acct_site_rec.created_by_module   := p_created_by_module;
    l_cust_acct_site_rec.territory           := 'UK Default';
    l_cust_acct_site_rec.territory_id        := '1001';
    l_cust_acct_site_rec.status              := 'A';
    apps.fnd_file.put_line(
      apps.fnd_file.log
      ,'Calling the API hz_cust_account_site_v2pub.create_cust_acct_site'
    );
    BEGIN
      hz_cust_account_site_v2pub.create_cust_acct_site(
        'T'
        ,l_cust_acct_site_rec
        ,p_cust_acc_site_id
        ,g_return_status
        ,g_msg_count
        ,g_msg_data
      );
      IF
        g_return_status = fnd_api.g_ret_sts_success
      THEN
        p_return   := 'SUCCESS';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Creation of Customer Account Site is Successful'
        );
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Customer Account Site Id is = '
          || TO_CHAR(p_cust_acc_site_id)
        );
      ELSE
        p_return   := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'ERROR: Creation of Customer Account Site: '
          || g_msg_data
        );
        IF
          g_msg_count > 1
        THEN
          FOR i IN 1..g_msg_count LOOP
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,i
              || '.'
              || substr(
                fnd_msg_pub.get(p_encoded   => fnd_api.g_false)
                ,1
                ,255
              )
            );
          END LOOP;
        END IF;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        p_return   := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error in Creating Customer Account Site: '
          || sqlerrm
        );
    END;
  END create_cust_acct_site;
--------------------------------
-- * Procedure Name : CREATE_CUST_SITE_USE
-- * Description : This Procedure creates the customer site uses
--------------------------------
  PROCEDURE create_cust_site_use (
    p_cust_acct_site_id   IN VARCHAR2
    ,p_site_use_code       IN VARCHAR2
    ,p_created_by_module   IN VARCHAR2
    ,p_return              OUT VARCHAR2
    ,p_site_use_id         OUT VARCHAR2
  ) IS
    l_cust_site_use_rec      hz_cust_account_site_v2pub.cust_site_use_rec_type;
    l_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
    l_site_use_id            hz_cust_site_uses_all.site_use_id%TYPE;
  BEGIN
    l_cust_site_use_rec.cust_acct_site_id   := p_cust_acct_site_id;
    l_cust_site_use_rec.site_use_code       := p_site_use_code;
    l_cust_site_use_rec.created_by_module   := p_created_by_module;
    l_cust_site_use_rec.primary_flag        := 'Y';
    apps.fnd_file.put_line(
      apps.fnd_file.log
      ,'Calling the API hz_cust_account_site_v2pub.create_cust_site_use'
    );
    BEGIN
      hz_cust_account_site_v2pub.create_cust_site_use(
        p_init_msg_list          => 'T'
        ,p_cust_site_use_rec      => l_cust_site_use_rec
        ,p_customer_profile_rec   => l_customer_profile_rec
        ,p_create_profile         => ”
        ,p_create_profile_amt     => ”
        ,x_site_use_id            => p_site_use_id
        ,x_return_status          => g_return_status
        ,x_msg_count              => g_msg_count
        ,x_msg_data               => g_msg_data
      );
      IF
        g_return_status = fnd_api.g_ret_sts_success
      THEN
        p_return   := 'SUCCESS';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Creation of Customer Account Site Use is Successful'
        );
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Site Use Id = '
          || TO_CHAR(p_site_use_id)
        );
      ELSE
        p_return   := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'ERROR: Creation of Customer Account Site Use: '
          || g_msg_data
        );
        IF
          g_msg_count > 1
        THEN
          FOR i IN 1..g_msg_count LOOP
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,i
              || '.'
              || substr(
                fnd_msg_pub.get(p_encoded   => fnd_api.g_false)
                ,1
                ,255
              )
            );
          END LOOP;
        END IF;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        p_return   := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error: '
          || sqlerrm
        );
    END;
  END create_cust_site_use;
--------------------------------
-- * Procedure Name : create_person
-- * Description : This Procedure creates the contact person and party relation
--------------------------------
  PROCEDURE create_person (
    p_person_first_name   IN VARCHAR2
    ,p_person_last_name    IN VARCHAR2
    ,p_created_by_module   IN VARCHAR2
    ,p_party_id            IN VARCHAR2
    ,p_cust_acc_id         IN VARCHAR2
    ,p_return              OUT VARCHAR2
  ) IS
    l_person_rec               hz_party_v2pub.person_rec_type;
    l_contact_point_rec        hz_contact_point_v2pub.contact_point_rec_type;
    l_phone_rec                hz_contact_point_v2pub.phone_rec_type;
    l_party_id                 hz_parties.party_id%TYPE;
    l_party_number             hz_parties.party_number%TYPE;
    l_profile_id               hz_customer_profiles.cust_account_profile_id%TYPE;
    l_org_contact_rec          hz_party_contact_v2pub.org_contact_rec_type;
    l_org_contact_id           NUMBER;
    l_party_rel_id             NUMBER;
    l_rel_party_id             NUMBER;
    l_rel_party_number         VARCHAR2(2000);
    l_cr_cust_acc_role_rec     hz_cust_account_role_v2pub.cust_account_role_rec_type;
    l_cust_account_role_id     VARCHAR2(2000);
    l_contact_point_party_id   hz_contact_points.contact_point_id%TYPE;
    l_status                   VARCHAR2(2000);
    l_msg_count                VARCHAR2(2000);
    l_msg_data                 VARCHAR2(2000);
  BEGIN
    l_person_rec.person_first_name                       := p_person_first_name;
    l_person_rec.person_last_name                        := p_person_last_name;
    l_person_rec.created_by_module                       := p_created_by_module;
    apps.fnd_file.put_line(
      apps.fnd_file.log
      ,'Calling the API HZ_PARTY_V2PUB.CREATE_PERSON'
    );
    hz_party_v2pub.create_person(
      p_init_msg_list   => 'T'
      ,p_person_rec      => l_person_rec
      ,x_party_id        => l_party_id
      ,x_party_number    => l_party_number
      ,x_profile_id      => l_profile_id
      ,x_return_status   => g_return_status
      ,x_msg_count       => g_msg_count
      ,x_msg_data        => g_msg_data
    );
    IF
      g_return_status = fnd_api.g_ret_sts_success
    THEN
      p_return                                             := 'SUCCESS';
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Creation of Customer Contact Person is Successful'
      );
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Person Party Id = '
        || TO_CHAR(l_party_id)
      );
--Call create org contact
      l_org_contact_rec.created_by_module                  := p_created_by_module;
      l_org_contact_rec.party_rel_rec.subject_id           := l_party_id; --Person party id
      l_org_contact_rec.party_rel_rec.subject_type         := ( 'PERSON' );
      l_org_contact_rec.party_rel_rec.subject_table_name   := 'HZ_PARTIES';
      l_org_contact_rec.party_rel_rec.object_table_name    := 'HZ_PARTIES';
      l_org_contact_rec.party_rel_rec.object_id            := p_party_id; --Organization Party Id
      l_org_contact_rec.party_rel_rec.object_type          := ( 'ORGANIZATION' );
      l_org_contact_rec.party_rel_rec.relationship_code    := ( 'CONTACT_OF' ); --EMPLOYEE_OF
      l_org_contact_rec.party_rel_rec.relationship_type    := ( 'CONTACT' ); --EMPLOYEE
      l_org_contact_rec.party_rel_rec.relationship_type    := ( 'CONTACT' );
      l_org_contact_rec.party_rel_rec.start_date           := SYSDATE;
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Calling the API hz_party_contact_v2pub.create_org_contact'
      );
      hz_party_contact_v2pub.create_org_contact(
        p_init_msg_list     => 'T'
        ,p_org_contact_rec   => l_org_contact_rec
        ,x_org_contact_id    => l_org_contact_id
        ,x_party_rel_id      => l_party_rel_id
        ,x_party_id          => l_rel_party_id
        ,x_party_number      => l_rel_party_number
        ,x_return_status     => g_return_status
        ,x_msg_count         => g_msg_count
        ,x_msg_data          => g_msg_data
      );
      IF
        g_return_status = fnd_api.g_ret_sts_success
      THEN
        p_return                                   := 'SUCCESS';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Creation of Customer party relation is Successful'
        );
        l_cr_cust_acc_role_rec.party_id            := l_rel_party_id;
        l_cr_cust_acc_role_rec.cust_account_id     := p_cust_acc_id;
        l_cr_cust_acc_role_rec.role_type           := 'CONTACT';
        l_cr_cust_acc_role_rec.created_by_module   := p_created_by_module;
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Calling the API hz_cust_account_role_v2pub.create_cust_account_role'
        );
        hz_cust_account_role_v2pub.create_cust_account_role(
          'T'
          ,l_cr_cust_acc_role_rec
          ,l_cust_account_role_id
          ,g_return_status
          ,g_msg_count
          ,g_msg_data
        );
      ELSE
        p_return   := 'ERROR';
        apps.fnd_file.put_line(
          apps.fnd_file.log
          ,'Error: Creation of Customer party relation: '
          || substr(
            g_msg_data
            ,1
            ,255
          )
        );
        IF
          g_msg_count > 1
        THEN
          FOR i IN 1..g_msg_count LOOP
            apps.fnd_file.put_line(
              apps.fnd_file.log
              ,i
              || '.'
              || substr(
                fnd_msg_pub.get(p_encoded   => fnd_api.g_false)
                ,1
                ,255
              )
            );
          END LOOP;
        END IF;
      END IF;
    ELSE
      p_return   := 'ERROR';
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'ERROR: Creation of Customer Contact'
        || g_msg_data
      );
      IF
        g_msg_count > 1
      THEN
        FOR i IN 1..g_msg_count LOOP
          apps.fnd_file.put_line(
            apps.fnd_file.log
            ,i
            || '.'
            || substr(
              fnd_msg_pub.get(p_encoded   => fnd_api.g_false)
              ,1
              ,255
            )
          );
        END LOOP;
      END IF;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      p_return   := 'ERROR';
      apps.fnd_file.put_line(
        apps.fnd_file.log
        ,'Error in Creating Customer Contact: '
        || sqlerrm
      );
  END create_person;
END xxar_xx_create_customer;

 


4. HRMS API’s for data anonymization/ masking/ scrambling

DECLARE
  CURSOR cur_name IS SELECT * FROM dba_objects;
  TYPE lv_cur_name_typ IS
    TABLE OF cur_name%rowtype;
  lv_cur_name_tab   lv_cur_name_typ := lv_cur_name_typ ();
  ln_api_error      NUMBER;
  lv_error EXCEPTION;
BEGIN
  OPEN cur_name;
  LOOP
    FETCH cur_name BULK COLLECT INTO lv_cur_name_tab LIMIT 200;
    EXIT WHEN lv_cur_name_tab.count = 0;
    FOR i IN 1..lv_cur_name_tab.count LOOP
      BEGIN
        BEGIN
          FOR j IN ( SELECT * FROM all_objects
          ) LOOP
--API call or action
            NULL;
          END LOOP;
        EXCEPTION
          WHEN OTHERS THEN
            RAISE lv_error;
            ln_api_error   := ln_api_error + 1;
        END;
        BEGIN
          FOR j IN ( SELECT * FROM all_objects
          ) LOOP
--API call or action
            NULL;
          END LOOP;
        EXCEPTION
          WHEN OTHERS THEN
            RAISE lv_error;
            ln_api_error   := ln_api_error + 1;
        END;
        BEGIN
          FOR j IN ( SELECT * FROM all_objects
          ) LOOP
--API call or action
            NULL;
          END LOOP;
        EXCEPTION
          WHEN OTHERS THEN
            RAISE lv_error;
            ln_api_error   := ln_api_error + 1;
        END;
      EXCEPTION
        WHEN lv_error THEN
          ROLLBACK;
      END;
    END LOOP;
  END LOOP;
  CLOSE cur_name;
EXCEPTION
  WHEN OTHERS THEN
    sqlerrm;
END;
CREATE OR REPLACE PACKAGE BODY xxab_hrms_data_anonymization AS
/***********************************************************************************
* TYPE : Package Body *
* NAME : xxab_hrms_data_anonymization *
* PURPOSE : HRMS Data Anonymization *
* Author Date Ver Description *
* --------- ----------------- ---- --------------------------------- *
* ABC 27-May-2019 1.0 Initial *
**********************************************************************************/
-------------------------------------------------------------------------------------------------------
-- * Procedure Name : xxab_hrms_data_scrambling
-- * INPUT Parameters : p_from_address, p_to_address
-- * OUTPUT Parametrs : p_errbuf, p_retcode
-- * Description : This Procedure anonymize the fields of Employees
-- * Address, Phone numbers, Payment Method and
-- * Elements Entry Values
-------------------------------------------------------------------------------------------------------
  PROCEDURE xxab_hrms_data_scrambling (
    p_errbuf            OUT NOCOPY VARCHAR2
    ,p_retcode           OUT NOCOPY VARCHAR2
    ,p_from_date         IN VARCHAR2
    ,p_to_date           IN VARCHAR2
    ,p_employee_number   IN VARCHAR2
  ) IS
    CURSOR cur_emp IS SELECT /*+ PARALLEL */ person_id
                            ,employee_number
                      FROM per_all_people_f papf
                      WHERE trunc(
      papf.creation_date
    ) BETWEEN trunc(
      TO_DATE(
        p_from_date
        ,'DD-MON-RRRR HH24:MI:SS'
      )
    ) AND trunc(
      TO_DATE(
        p_to_date
        ,'DD-MON-RRRR HH24:MI:SS'
      )
    )
                            AND   papf.employee_number IN ( SELECT TRIM(regexp_substr(
      upper(
        nvl(
          p_employee_number
          ,papf.employee_number
        )
      )
      ,'[^,]+'
      ,1
      ,level
    ) ) employee_number
                                                          FROM dual CONNECT BY
      regexp_substr(
        upper(
          nvl(
            p_employee_number
            ,papf.employee_number
          )
        )
        ,'[^,]+'
        ,1
        ,level
      ) IS NOT NULL
    )
                      GROUP BY person_id
                              ,employee_number;
    TYPE lv_emp_typ IS
      TABLE OF cur_emp%rowtype;
    l_emp_tab                       lv_emp_typ := lv_emp_typ ();
    lb_update_warning               BOOLEAN;
    ln_start_time                   NUMBER;
    ln_comment_id                   NUMBER;
    ln_api_error                    NUMBER;
    ld_effective_start_date         DATE;
    ld_effective_end_date           DATE;
    lv_full_name                    per_all_people_f.full_name%TYPE;
    lb_name_combination_warning     BOOLEAN;
    lb_assign_payroll_warning       BOOLEAN;
    lb_orig_hire_warning            BOOLEAN;
    ln_cagr_grade_def_id            NUMBER;
    lv_cagr_concatenated_segments   VARCHAR2(240);
    lv_concatenated_segments        VARCHAR2(240);
    ln_soft_coding_keyflex_id       NUMBER;
    lb_no_managers_warning          BOOLEAN;
    lb_other_manager_warning        BOOLEAN;
    lb_hourly_salaried_warning      BOOLEAN;
    lv_gsp_post_process_warning     VARCHAR2(240);
    lv_group_name                   VARCHAR2(240);
    ln_people_group_id              NUMBER;
    ln_rowcount                     NUMBER;
    lv_error EXCEPTION;
    lv_phone_type                   VARCHAR2(10);
  BEGIN
    ln_start_time                   := dbms_utility.get_time;
    ln_api_error                    := 0;
    ln_rowcount                     := 0;
    OPEN cur_emp;
    LOOP
      FETCH cur_emp BULK COLLECT INTO l_emp_tab LIMIT 1000;
      EXIT WHEN l_emp_tab.count = 0;
      FOR j IN 1..l_emp_tab.count LOOP
        BEGIN
          BEGIN
            FOR i IN ( SELECT papf.employee_number
                             ,papf.object_version_number
                             ,papf.effective_start_date
                       FROM per_all_people_f papf
                       WHERE papf.person_id = l_emp_tab(j).person_id
                       GROUP BY papf.employee_number
                               ,papf.object_version_number
                               ,papf.effective_start_date
            ) LOOP
              ln_rowcount                   := ln_rowcount + 1;
              hr_person_api.update_gb_person(
                p_effective_date             => TO_DATE(
                  i.effective_start_date
                  ,'DD-MON-RRRR HH24:MI:SS'
                )
                ,p_datetrack_update_mode      => 'CORRECTION'
                ,p_person_id                  => l_emp_tab(j).person_id
                ,p_object_version_number      => i.object_version_number
                ,p_date_of_birth              => '01-JAN-1980'
                ,p_ethnic_origin              => NULL
                ,p_ni_number                  => NULL
                ,p_marital_status             => NULL
                ,p_attribute1                 => NULL
                ,p_attribute2                 => NULL
                ,p_attribute15                => NULL
                ,p_nationality                => NULL
                ,p_registered_disabled_flag   => NULL
                ,p_known_as                   => NULL
                ,p_attribute16                => NULL
                ,p_attribute10                => NULL
                ,p_employee_number            => i.employee_number
                ,p_effective_start_date       => i.effective_start_date
                ,p_effective_end_date         => ld_effective_end_date
                ,p_full_name                  => lv_full_name
                ,p_comment_id                 => ln_comment_id
                ,p_name_combination_warning   => lb_name_combination_warning
                ,p_assign_payroll_warning     => lb_assign_payroll_warning
                ,p_orig_hire_warning          => lb_orig_hire_warning
              );
              ld_effective_start_date       := NULL;
              ld_effective_end_date         := NULL;
              lv_full_name                  := NULL;
              ln_comment_id                 := NULL;
              lb_name_combination_warning   := NULL;
              lb_assign_payroll_warning     := NULL;
              lb_orig_hire_warning          := NULL;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_person_api.update_gb_person for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          BEGIN
            FOR i IN ( SELECT paaf.assignment_id
                             ,paaf.object_version_number
                             ,paaf.effective_start_date
                       FROM per_all_assignments_f paaf
                       WHERE 1 = 1
                             AND   paaf.person_id = l_emp_tab(j).person_id
                             AND   paaf.assignment_type = 'E'
                             AND   paaf.primary_flag = 'Y'
                             AND   paaf.assignment_status_type_id = 1
                       GROUP BY paaf.assignment_id
                               ,paaf.object_version_number
                               ,paaf.effective_start_date
            ) LOOP
              ln_rowcount   := ln_rowcount + 1;
              hr_assignment_api.update_gb_emp_asg(
                p_validate                     => false
                ,p_effective_date               => TO_DATE(
                  i.effective_start_date
                  ,'DD-MON-RRRR HH24:MI:SS'
                )
                ,p_datetrack_update_mode        => 'CORRECTION'
                ,p_assignment_id                => i.assignment_id
                ,p_object_version_number        => i.object_version_number
                ,p_normal_hours                 => 0
                ,p_frequency                    => 'W'
                ,p_ass_attribute6               => NULL
                ,p_ass_attribute28              => NULL
                ,p_ass_attribute1               => NULL
                ,p_ass_attribute4               => NULL
                ,p_ass_attribute5               => NULL
                ,p_ass_attribute7               => NULL
                ,p_ass_attribute14              => NULL
                ,p_ass_attribute18              => NULL
                ,p_ass_attribute19              => NULL
                ,p_ass_attribute23              => NULL
                ,p_ass_attribute24              => NULL
                ,p_ass_attribute2               => NULL
                ,p_ass_attribute17              => NULL
                ,p_ass_attribute25              => NULL
                ,p_ass_attribute26              => NULL
                ,p_ass_attribute27              => NULL
                ,p_ass_attribute29              => NULL
                ,p_ass_attribute11              => NULL
                ,p_cagr_grade_def_id            => ln_cagr_grade_def_id
                ,p_cagr_concatenated_segments   => lv_cagr_concatenated_segments
                ,p_concatenated_segments        => lv_concatenated_segments
                ,p_soft_coding_keyflex_id       => ln_soft_coding_keyflex_id
                ,p_comment_id                   => ln_comment_id
                ,p_effective_start_date         => ld_effective_start_date
                ,p_effective_end_date           => ld_effective_end_date
                ,p_no_managers_warning          => lb_no_managers_warning
                ,p_other_manager_warning        => lb_other_manager_warning
                ,p_hourly_salaried_warning      => lb_hourly_salaried_warning
              );
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_assignment_api.update_gb_emp_asg for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          BEGIN
            FOR i IN ( SELECT paaf.assignment_id
                             ,paaf.object_version_number
                             ,paaf.effective_start_date
                             ,paaf.effective_end_date
                       FROM per_all_assignments_f paaf
                       WHERE 1 = 1
                             AND   paaf.person_id = l_emp_tab(j).person_id
                             AND   paaf.assignment_type = 'A'
                       GROUP BY paaf.assignment_id
                               ,paaf.object_version_number
                               ,paaf.effective_start_date
                               ,paaf.effective_end_date
            ) LOOP
              ln_rowcount                     := ln_rowcount + 1;
              hr_assignment_api.update_apl_asg(
                p_validate                     => false
                ,p_effective_date               => TO_DATE(
                  i.effective_start_date
                  ,'DD-MON-RRRR HH24:MI:SS'
                )
                ,p_datetrack_update_mode        => 'CORRECTION'
                ,p_assignment_id                => i.assignment_id
                ,p_object_version_number        => i.object_version_number
                ,p_normal_hours                 => 0
                ,p_frequency                    => 'W'
                ,p_ass_attribute6               => NULL
                ,p_ass_attribute28              => NULL
                ,p_ass_attribute1               => NULL
                ,p_ass_attribute4               => NULL
                ,p_ass_attribute5               => NULL
                ,p_ass_attribute7               => NULL
                ,p_ass_attribute14              => NULL
                ,p_ass_attribute18              => NULL
                ,p_ass_attribute19              => NULL
                ,p_ass_attribute23              => NULL
                ,p_ass_attribute24              => NULL
                ,p_ass_attribute2               => NULL
                ,p_ass_attribute17              => NULL
                ,p_ass_attribute25              => NULL
                ,p_ass_attribute26              => NULL
                ,p_ass_attribute27              => NULL
                ,p_ass_attribute29              => NULL
                ,p_ass_attribute11              => NULL
                ,p_cagr_grade_def_id            => ln_cagr_grade_def_id
                ,p_cagr_concatenated_segments   => lv_cagr_concatenated_segments
                ,p_concatenated_segments        => lv_concatenated_segments
                ,p_group_name                   => lv_group_name
                ,p_comment_id                   => ln_comment_id
                ,p_people_group_id              => ln_people_group_id
                ,p_soft_coding_keyflex_id       => ln_soft_coding_keyflex_id
                ,p_effective_start_date         => ld_effective_start_date
                ,p_effective_end_date           => ld_effective_end_date
              );
              ln_cagr_grade_def_id            := NULL;
              lv_cagr_concatenated_segments   := NULL;
              lv_concatenated_segments        := NULL;
              ln_soft_coding_keyflex_id       := NULL;
              lv_group_name                   := NULL;
              ln_comment_id                   := NULL;
              ln_people_group_id              := NULL;
              ld_effective_start_date         := NULL;
              ld_effective_end_date           := NULL;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_assignment_api.update_apl_asg for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          BEGIN
            FOR i IN ( SELECT pa.address_id
                             ,pa.object_version_number
                       FROM per_addresses pa
                       WHERE pa.person_id = l_emp_tab(j).person_id
                       GROUP BY pa.address_id
                               ,pa.object_version_number
            ) LOOP
              ln_rowcount   := ln_rowcount + 1;
              hr_person_address_api.update_person_address(
                p_effective_date          => SYSDATE
                ,p_address_id              => i.address_id
                ,p_object_version_number   => i.object_version_number
                ,p_address_line1           => 'AAAAAA'
                ,p_address_line2           => 'BBBBBB'
                ,p_address_line3           => 'CCCCCC'
                ,p_postal_code             => 'EC2N 1GJ'
              );
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_person_address_api.update_person_address for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
          END;
          BEGIN
            FOR i IN ( SELECT pp.phone_id
                             ,pp.object_version_number
                             ,pp.phone_type
                       FROM per_phones pp
                       WHERE 1 = 1
                             AND   pp.parent_table = 'PER_ALL_PEOPLE_F'
                             AND   pp.parent_id = l_emp_tab(j).person_id
                       GROUP BY pp.phone_id
                               ,pp.object_version_number
                               ,pp.phone_type
            ) LOOP
              ln_rowcount     := ln_rowcount + 1;
              IF
                i.phone_type = 'M'
              THEN
                lv_phone_type   := 'M';
              ELSIF i.phone_type = 'H1' THEN
                lv_phone_type   := 'H1';
              ELSE
                lv_phone_type   := 'W1';
              END IF;
              hr_phone_api.update_phone(
                p_phone_id                => i.phone_id
                ,p_phone_type              => lv_phone_type
                ,p_phone_number            => 9999999999
                ,p_object_version_number   => i.object_version_number
                ,p_effective_date          => SYSDATE
              );
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_phone_api.update_phone for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
          END;
          BEGIN
            FOR i IN ( SELECT pppmf.personal_payment_method_id
                             ,pppmf.object_version_number
                             ,pppmf.external_account_id
                             ,pppmf.percentage
                             ,pppmf.priority
                             ,pppmf.effective_start_date
                             ,pppmf.effective_end_date
                             ,pea.territory_code
                       FROM pay_personal_payment_methods_f pppmf
                           ,pay_external_accounts pea
                           ,per_all_assignments_f paaf
                       WHERE paaf.assignment_id = pppmf.assignment_id
                             AND   pppmf.external_account_id = pea.external_account_id
                             AND   paaf.person_id = l_emp_tab(j).person_id
                       GROUP BY pppmf.personal_payment_method_id
            ,pppmf.object_version_number
            ,pppmf.external_account_id
            ,pppmf.percentage
            ,pppmf.priority
            ,pppmf.effective_start_date
            ,pppmf.effective_end_date
            ,pea.territory_code
            ) LOOP
              ln_rowcount     := ln_rowcount + 1;
              hr_personal_pay_method_api.update_gb_personal_pay_method(
                p_effective_date                => TO_DATE(
                  i.effective_start_date
                  ,'DD-MON-RRRR HH24:MI:SS'
                )
                ,p_datetrack_update_mode         => 'CORRECTION'
                ,p_personal_payment_method_id    => i.personal_payment_method_id
                ,p_object_version_number         => i.object_version_number
                ,p_account_name                  => 'BANK_ACC_ANON'
                ,p_account_number                => 11111111
                ,p_sort_code                     => 999999
                ,p_bank_name                     => 'BANK_DATA_ANON'
                ,p_account_type                  => NULL
                ,p_bank_branch                   => 'BRANCH_DATA_ANON'
                ,p_bank_branch_location          => NULL
                ,p_bldg_society_account_number   => 'ABCD1234'
                ,p_segment10                     => 'BANK_DATA_ANON'
                ,p_percentage                    => i.percentage
                ,p_priority                      => i.priority
                ,p_territory_code                => i.territory_code
                ,p_comment_id                    => ln_comment_id
                ,p_external_account_id           => i.external_account_id
                ,p_effective_start_date          => i.effective_start_date
                ,p_effective_end_date            => i.effective_start_date
              );
              ln_comment_id   := NULL;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_personal_pay_method_api.update_gb_personal_pay_method for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          BEGIN
            FOR i IN ( SELECT petf.element_name
                             ,peef.element_entry_id
                             ,peef.object_version_number
                             ,peef.effective_start_date
                             ,peef.effective_end_date
                             ,pivf.input_value_id
                             ,pivf.name
                       FROM pay_element_types_f petf
                           ,pay_element_links_f pelf
                           ,pay_input_values_f pivf
                           ,pay_element_entries_f peef
                           ,per_all_assignments_f paaf
                       WHERE 1 = 1
                             AND   pelf.element_type_id = petf.element_type_id
                             AND   pivf.element_type_id = petf.element_type_id
                             AND   peef.element_type_id = petf.element_type_id
                             AND   peef.element_link_id = pelf.element_link_id
                             AND   paaf.assignment_id = peef.assignment_id
                             AND   pivf.name IN (
              'Annual Amount'
              ,'Annual Pay Value'
              ,'Hourly Amount'
              ,'Hourly Rate'
              ,'Pay Value'
              ,'Periodic Amount'
              ,'Weekly Rate'
            )
                             AND   paaf.person_id = l_emp_tab(j).person_id
                       GROUP BY petf.element_name
            ,peef.element_entry_id
            ,peef.object_version_number
            ,peef.effective_start_date
            ,peef.effective_end_date
            ,pivf.input_value_id
            ,pivf.name
            ORDER BY 4 ) LOOP
              ln_rowcount               := ln_rowcount + 1;
              pay_element_entry_api.update_element_entry(
                p_validate                => false
                ,p_datetrack_update_mode   => 'CORRECTION'
                ,p_effective_date          => TO_DATE(
                  i.effective_start_date
                  ,'DD-MON-RRRR HH24:MI:SS'
                )
                ,p_business_group_id       => fnd_profile.value('PER_BUSINESS_GROUP_ID')
                ,p_element_entry_id        => i.element_entry_id
                ,p_object_version_number   => i.object_version_number
                ,p_input_value_id1         => i.input_value_id
                ,p_entry_value1            => 9999
                ,p_effective_start_date    => ld_effective_start_date
                ,p_effective_end_date      => ld_effective_end_date
                ,p_update_warning          => lb_update_warning
              );
              ld_effective_start_date   := NULL;
              ld_effective_end_date     := NULL;
              lb_update_warning         := NULL;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in pay_element_entry_api.update_element_entry for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          BEGIN
            FOR i IN ( SELECT petf.element_name
                             ,peef.element_entry_id
                             ,peef.object_version_number
                             ,peef.effective_start_date
                             ,peef.effective_end_date
                             ,pivf.input_value_id
                             ,pivf.name
                       FROM pay_element_types_f petf
                           ,pay_element_links_f pelf
                           ,pay_input_values_f pivf
                           ,pay_element_entries_f peef
                           ,per_all_assignments_f paaf
                       WHERE 1 = 1
                             AND   pelf.element_type_id = petf.element_type_id
                             AND   pivf.element_type_id = petf.element_type_id
                             AND   peef.element_type_id = petf.element_type_id
                             AND   peef.element_link_id = pelf.element_link_id
                             AND   paaf.assignment_id = peef.assignment_id
                             AND   pivf.name = 'Weekly Amount'
                             AND   paaf.person_id = l_emp_tab(j).person_id
                       GROUP BY petf.element_name
            ,peef.element_entry_id
            ,peef.object_version_number
            ,peef.effective_start_date
            ,peef.effective_end_date
            ,pivf.input_value_id
            ,pivf.name
            ORDER BY 4 ) LOOP
              ln_rowcount               := ln_rowcount + 1;
              pay_element_entry_api.update_element_entry(
                p_validate                => false
                ,p_datetrack_update_mode   => 'CORRECTION'
                ,p_effective_date          => TO_DATE(
                  i.effective_start_date
                  ,'DD-MON-RRRR HH24:MI:SS'
                )
                ,p_business_group_id       => fnd_profile.value('PER_BUSINESS_GROUP_ID')
                ,p_element_entry_id        => i.element_entry_id
                ,p_object_version_number   => i.object_version_number
                ,p_input_value_id1         => i.input_value_id
                ,p_entry_value1            => 9999
                ,p_effective_start_date    => ld_effective_start_date
                ,p_effective_end_date      => ld_effective_end_date
                ,p_update_warning          => lb_update_warning
              );
              ld_effective_start_date   := NULL;
              ld_effective_end_date     := NULL;
              lb_update_warning         := NULL;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in pay_element_entry_api.update_element_entry Weekly Amount for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          BEGIN
            FOR i IN ( SELECT ppos.object_version_number
                             ,ppos.period_of_service_id
                       FROM per_periods_of_service ppos
                       WHERE ppos.actual_termination_date IS NOT NULL
                             AND   ppos.person_id = l_emp_tab(j).person_id
                       GROUP BY ppos.object_version_number
                               ,ppos.period_of_service_id
            ) LOOP
              ln_rowcount   := ln_rowcount + 1;
              hr_ex_employee_api.update_term_details_emp(
                p_effective_date          => SYSDATE
                ,p_period_of_service_id    => i.period_of_service_id
                ,p_object_version_number   => i.object_version_number
                ,p_leaving_reason          => 50
              );
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_ex_employee_api.update_term_details_emp for employee '
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          BEGIN
            FOR i IN ( SELECT papf.employee_number
                             ,papf.object_version_number
                             ,papf.effective_start_date
                       FROM per_all_people_f papf
                           ,per_periods_of_service ppos
                       WHERE papf.person_id = ppos.person_id
                             AND   ppos.actual_termination_date IS NOT NULL
                             AND   papf.person_id = l_emp_tab(j).person_id
                       GROUP BY papf.employee_number
                               ,papf.object_version_number
                               ,papf.effective_start_date
            ) LOOP
              ln_rowcount                   := ln_rowcount + 1;
              hr_person_api.update_gb_person(
                p_effective_date             => TO_DATE(
                  i.effective_start_date
                  ,'DD-MON-RRRR HH24:MI:SS'
                )
                ,p_datetrack_update_mode      => 'CORRECTION'
                ,p_person_id                  => l_emp_tab(j).person_id
                ,p_object_version_number      => i.object_version_number
                ,p_email_address              => NULL
                ,p_employee_number            => i.employee_number
                ,p_effective_start_date       => i.effective_start_date
                ,p_effective_end_date         => ld_effective_end_date
                ,p_full_name                  => lv_full_name
                ,p_comment_id                 => ln_comment_id
                ,p_name_combination_warning   => lb_name_combination_warning
                ,p_assign_payroll_warning     => lb_assign_payroll_warning
                ,p_orig_hire_warning          => lb_orig_hire_warning
              );
              ld_effective_start_date       := NULL;
              ld_effective_end_date         := NULL;
              lv_full_name                  := NULL;
              ln_comment_id                 := NULL;
              lb_name_combination_warning   := NULL;
              lb_assign_payroll_warning     := NULL;
              lb_orig_hire_warning          := NULL;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              ln_api_error   := ln_api_error + 1;
              apps.fnd_file.put_line(
                apps.fnd_file.output
                ,'Error in hr_person_api.update_gb_person to update email address for terminated employee'
                || l_emp_tab(j).employee_number
                || '. The error is '
                || sqlerrm
              );
              RAISE lv_error;
          END;
          COMMIT;
        EXCEPTION
          WHEN lv_error THEN
            ROLLBACK;
        END;
      END LOOP;
    END LOOP;
    IF
      ln_api_error <> 0
    THEN
      apps.fnd_file.put_line(
        apps.fnd_file.output
        ,'Total number of records for '
        || cur_emp%rowcount
        || ' employees are '
        || ln_rowcount
      );
      apps.fnd_file.put_line(
        apps.fnd_file.output
        ,'Total number of processed records are '
        || (ln_rowcount - ln_api_error)
      );
      apps.fnd_file.put_line(
        apps.fnd_file.output
        ,'Total number of error records are '
        || ln_api_error
      );
    ELSE
      apps.fnd_file.put_line(
        apps.fnd_file.output
        ,'All records are anonymized successfully..!!'
      );
      apps.fnd_file.put_line(
        apps.fnd_file.output
        ,'Total number of records for '
        || cur_emp%rowcount
        || ' employees are '
        || ln_rowcount
      );
    END IF;
    CLOSE cur_emp;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      apps.fnd_file.put_line(
        apps.fnd_file.output
        ,'Error in xxab_hrms_data_scrambling procedure '
        || sqlerrm
      );
  END xxab_hrms_data_scrambling;
END xxab_hrms_data_anonymization;