API to Create Position

Salam Alaikum,

   I share with you today the API used to create a position in HR.

   *Please note that the values in the API are hard-coded.

declare

l_true                                                  BOOLEAN     :=false;
l_obj                                                   NUMBER;
l_JOB_ID                                           NUMBER        :=614;  
l_ORGANIZATION_ID                    NUMBER        :=1379;
l_DATE_EFFECTIVE                       DATE        := to_date('01-DEC-2000', 'DD-MON-YYYY');
l_POSITION_ID                                NUMBER;
l_POSITION_DEFINITION_ID       NUMBER;
l_NAME                          VARCHAR2(30);
l_segment1            VARCHAR2(30)    :='TEST POSITION';
l_segment2            VARCHAR2(30)    :='10';

begin

hr_position_api.create_position
(
        p_validate              =>    l_true
,       p_object_version_number        =>    l_obj
,    p_JOB_ID            =>    l_JOB_ID
,    p_ORGANIZATION_ID         =>    l_ORGANIZATION_ID
,    p_DATE_EFFECTIVE        =>    l_DATE_EFFECTIVE
,    p_POSITION_ID            =>    l_POSITION_ID
,     p_POSITION_DEFINITION_ID    =>    l_POSITION_DEFINITION_ID
,     p_NAME                 =>    l_NAME
,    p_segment1            =>    l_segment1
,    p_segment2            =>    l_segment2
,    p_location_id            =>    2694
);
end;

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

API to Create Organization

Salam Alaikum,

   I share with you today the API used to create organiztion in HR.

   *Please note that the values in the API are hard-coded.

DECLARE
l_date                    date           := to_date('01-jan-2000','DD-MON-YYYY');
l_true                    boolean     := false;
l_business_group_id        number        := 626;
l_language_code            varchar2(10)    := 'US';
l_name                    varchar2(80)    := 'NJ TEST ORG';
l_internal_external_flag    varchar2(80)    := 'INT';
l_organization_id         number;
l_object_version_number        number;
l_duplicate_org_warning        boolean;

BEGIN
hr_organization_api.create_organization
   (  p_validate                      =>     l_true
     ,p_effective_date                =>    l_date
     ,p_language_code                 =>    l_language_code
     ,p_business_group_id             =>    l_business_group_id
     ,p_date_from                     =>    l_date
     ,p_name                          =>    l_name
     ,p_internal_external_flag        =>    l_internal_external_flag
     ,p_organization_id               =>    l_organization_id
     ,p_object_version_number         =>    l_object_version_number
     ,P_duplicate_org_warning          =>    l_duplicate_org_warning
);END; 

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

API to Create Person Extra Information record

Salam Alaikum,

   It is one very important and widely used API that i share with you today... It is the API used to upload Extra Information for a person in Oracle HR. It is very simple and straight forward, and as you know each Extra Information has different (attributes/attribute type) so you have to consider getting to know some the dependencies (if exists) between the attributes before uploading.

   That being said, i share one simple API, with three attributes only:

DECLARE
   l_true                       BOOLEAN := FALSE;
   l_PERSON_ID                  NUMBER := 31785;
   l_INFORMATION_TYPE           VARCHAR2 (2000) := 'LAST_NAME_DETAILS';
   l_pei_information_category   VARCHAR2 (2000) := 'LAST_NAME_DETAILS';
   l_PEI_INFORMATION1           VARCHAR2 (2000) := 'Test1';
   l_PEI_INFORMATION2           VARCHAR2 (2000) := 'Test2';
   l_PEI_INFORMATION3           VARCHAR (200) := TO_CHAR (TO_DATE ('01-JAN-1999', 'DD-MON-YYYY'),'YYYY/MM/DD HH24:MI:SS');
   -- Output Variables
   l_PERSON_EXTRA_INFO_ID       NUMBER;
   l_OBJ                        NUMBER;

   CURSOR csr_ovn
   IS
      SELECT MAX (object_version_number)
        FROM per_all_people_f
       WHERE person_id = l_person_id;
BEGIN
   OPEN csr_ovn;
   FETCH csr_ovn INTO l_obj;
   CLOSE csr_ovn;

   HR_PERSON_EXTRA_INFO_API.CREATE_PERSON_EXTRA_INFO (
      p_validate                   => l_true,
      p_person_id                  => l_person_id,
      p_INFORMATION_TYPE           => l_INFORMATION_TYPE,
      p_pei_information_category   => l_pei_information_category,
      p_PEI_INFORMATION1           => l_PEI_INFORMATION1,
      p_PEI_INFORMATION2           => l_PEI_INFORMATION2,
      p_PEI_INFORMATION3           => l_PEI_INFORMATION3,
      p_PERSON_EXTRA_INFO_ID       => l_PERSON_EXTRA_INFO_ID,
      p_object_version_number      => l_obj);
END;

*note: the P_PERSON_EXTRA_INFO_ID and P_OBJECT_VERSION_NUMBER are both out parameters.

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

API code to Create, Release and Receive an OPM Inventory Transfer

Salam Alaikum,

   Following an example API to create, release and transfer in the Oracle Process Manufacturing (OPM). It calls three different API's. Naturally, the first one is to create and the second to release and the third to transfer, based on the Transfer_action.

DECLARE
   x_status         VARCHAR2 (1);
   x_count          NUMBER;
   x_msg_data       VARCHAR2 (2000);
   l_transfer_rec   GMIGXFR.TYPE_XFER_REC;
   x_out_rec        IC_XFER_MST%ROWTYPE;
   l_dummy          NUMBER;
   x_dummy_cnt      NUMBER;
BEGIN
   l_transfer_rec.transfer_action := 1;
   l_transfer_rec.transfer_no := NULL;
   l_transfer_rec.transfer_batch := 'TEST XFER';
   l_transfer_rec.orgn_code := 'PR1';
   l_transfer_rec.item_no := 'JGC_1';
   l_transfer_rec.lot_no := 'JGC_165';
   l_transfer_rec.sublot_no := NULL;
   l_transfer_rec.source_warehouse := 'PR1';
   l_transfer_rec.source_location := '1';
   l_transfer_rec.target_warehouse := 'PR2';
   l_transfer_rec.target_location := '1';
   -- date for testing period
   l_transfer_rec.scheduled_release_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.scheduled_receive_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.actual_release_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.actual_receive_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.cancel_date := NULL;
   l_transfer_rec.release_quantity1 := 1.0;
   l_transfer_rec.release_quantity2 := NULL;
   l_transfer_rec.reason_code := 'ADJ';
   l_transfer_rec.comments := 'test API XFER';
   l_transfer_rec.user_name := 'OPMUK';
   -- Creating
   GMIPXFR.Inventory_Transfer (
      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,
      p_xfer_rec           => l_transfer_rec,
      x_ic_xfer_mst_row    => x_out_rec,
      x_return_status      => x_status,
      x_msg_count          => x_count,
      x_msg_data           => x_msg_data);

   IF x_status != 'S'
   THEN
      DBMS_OUTPUT.put_line ('Error creating');

      IF x_count > 1
      THEN
         FOR l_dummy IN 1 .. x_count
         LOOP
            fnd_msg_pub.get (p_msg_index       => l_dummy,
                             p_data            => x_msg_data,
                             p_encoded         => fnd_api.g_false,
                             p_msg_index_out   => x_dummy_cnt);
            DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
         END LOOP;
      ELSE
         DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
      END IF;
   ELSE                                                          -- if success
      -- RELEASE
      l_transfer_rec.transfer_action := 2;
      l_transfer_rec.transfer_no := x_out_rec.transfer_no;
      DBMS_OUTPUT.put_line ('XFER no:' || l_transfer_rec.transfer_no);
      GMIPXFR.Inventory_Transfer (
         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,
         p_xfer_rec           => l_transfer_rec,
         x_ic_xfer_mst_row    => x_out_rec,
         x_return_status      => x_status,
         x_msg_count          => x_count,
         x_msg_data           => x_msg_data);

      IF x_status != 'S'
      THEN
         DBMS_OUTPUT.put_line ('Error releasing');

         IF x_count > 1
         THEN
            FOR l_dummy IN 1 .. x_count
            LOOP
               fnd_msg_pub.get (p_msg_index       => l_dummy,
                                p_data            => x_msg_data,
                                p_encoded         => fnd_api.g_false,
                                p_msg_index_out   => x_dummy_cnt);
               DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
         END IF;
      ELSE
         -- receive
         l_transfer_rec.transfer_action := 3;
         GMIPXFR.Inventory_Transfer (
            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,
            p_xfer_rec           => l_transfer_rec,
            x_ic_xfer_mst_row    => x_out_rec,
            x_return_status      => x_status,
            x_msg_count          => x_count,
            x_msg_data           => x_msg_data);

         IF x_status != 'S'
         THEN
            DBMS_OUTPUT.put_line ('Error receiving');

            IF x_count > 1
            THEN
               FOR l_dummy IN 1 .. x_count
               LOOP
                  fnd_msg_pub.get (p_msg_index       => l_dummy,
                                   p_data            => x_msg_data,
                                   p_encoded         => fnd_api.g_false,
                                   p_msg_index_out   => x_dummy_cnt);
                  DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
               END LOOP;
            ELSE
               DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
            END IF;
         END IF;
      END IF;
   END IF;
END;
/

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

Inventory Item Upload API

Salam Alaikum,

   I share with you here a very simple API that can be used to upload inventory items to the interface "MTL_SYSTEM_ITEMS_INTERFACE". And please note that after populating the interface you have to run the request "Import Items".

DECLARE
   CURSOR cur
   IS
      SELECT ITEM_CODE,
             ITEM_DESCRIPTION,
             UOM_CODE,
             TEMPLATE_ID
        FROM XXX_ITEMS_TEMP;
BEGIN
   fnd_global.apps_initialize (1150, 20634, 401);

   FOR rec IN cur
   LOOP
      INSERT INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE (SEGMENT1,
                                                   DESCRIPTION,
                                                   ORGANIZATION_ID,
                                                   PRIMARY_UOM_CODE,
                                                   TEMPLATE_ID,
                                                   CREATED_BY,
                                                   PROCESS_FLAG,
                                                   TRANSACTION_TYPE)
           VALUES (rec.ITEM_CODE,
                   rec.ITEM_DESCRIPTION,
                   102,
                   rec.UOM_CODE,
                   rec.TEMPLATE_ID,
                   1150,
                   1,
                   'CREATE');

      INSERT INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE (SEGMENT1,
                                                   DESCRIPTION,
                                                   ORGANIZATION_ID,
                                                   PRIMARY_UOM_CODE,
                                                   TEMPLATE_ID,
                                                   CREATED_BY,
                                                   PROCESS_FLAG,
                                                   TRANSACTION_TYPE)
           VALUES (rec.ITEM_CODE,
                   rec.ITEM_DESCRIPTION,
                   101,
                   rec.UOM_CODE,
                   rec.TEMPLATE_ID,
                   1150,
                   1,
                   'CREATE');

      UPDATE XXX_ITEMS_TEMP2 t
         SET t.data_upload = 'Y'
       WHERE t.ITEM_CODE = rec.ITEM_CODE;
   END LOOP;
END;

Some notes regarding the Concurrent "Import Items":

1. Indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored.
2. Indicate whether to validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they are not processed into Oracle Inventory. (You would choose No if you had previously run the item interface and responded Yes for Validate Items and No for Process Items, and now want to process your items).
3. Indicate whether to process items. If you choose Yes, all qualifying items in the interface table are inserted into Inventory. (You would choose No, along with Yes for Delete Processed Rows, to remove successfully processed rows from the interface table without performing any other processing).
4. Indicate whether to delete successfully processed items from the item interface tables
   (Choose No to leave all rows in the item interface tables for successfully processed items).

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

API To Create Establishment (School/College)

Salam Alaikum,

   Following the API that can be used for creating a Establishment in HR. That Can either be a School or college... etc. Please note that some attributes given in the API may deffer based on the setup.

DECLARE
   CURSOR schools
   IS
      SELECT xx_sch.ROWID,
             xx_sch.*
        FROM xxx_schools_info xx_sch
        WHERE 1 = 1 AND NVL (upload_status, 'N') = 'N';

   l_establishment_id        per_establishments.establishment_id%TYPE;
   l_object_version_number   per_establishments.object_version_number%TYPE;
BEGIN
   FOR i_rec IN schools
   LOOP
      per_est_ins.ins (p_establishment_id           => l_establishment_id,
                       p_name                       => i_rec.establishment_name,
                       p_location                   => i_rec.LOCATION,
                       p_attribute_category         => NULL,
                       p_attribute1                 => i_rec.country,
                       p_attribute2                 => i_rec.area,
                       p_attribute3                 => i_rec.city,
                       p_object_version_number      => l_object_version_number,
                       p_validate                   => FALSE
                      );

      UPDATE xxx_schools_info
         SET upload_status = 'Y',
             establishment_id = l_establishment_id
       WHERE ROWID = i_rec.ROWID;
   END LOOP;
END;

  As always, feel free to leave a comment if have any note on the subject.        

API To Create Employee Qualification

Salam Alaikum,

   I share this API, that creates an employee qualification, but please note that it needs that some prerequisites for this API is needed. Like qualification type should be already created and qualification_type_id should be input to the create_qualification API. As for establishment and establishment_id to be input for the API. And lastly, the employee should be an attendant of the establishment and attendance_id should be input to the API.

The following queries might help to understand the API more clearly:

SELECT qualification_type_id, NAME
  FROM per_qualification_types_tl
 WHERE 1 = 1 AND LANGUAGE = 'US';

SELECT establishment_id,NAME
  FROM per_establishments
 WHERE 1 = 1;

SELECT attendance_id
  FROM per_establishment_attend_v
 WHERE     1 = 1
       AND person_id = 'xx'
       AND establishment_id = 'establishment_id';

   And this is the API:

DECLARE
   l_qualification_id        NUMBER;
   l_object_version_number   NUMBER;
  
   CURSOR per_edu
   IS
      SELECT xx_edu.ROWID,
             xx_edu.*
        FROM xxx_emp_edu_school_info xx_edu
       WHERE 1 = 1 AND NVL (upload_status, 'N') = 'N';

BEGIN
   FOR i_rec IN per_edu
   LOOP
      fnd_profile.put ('PER_PERSON_ID', i_rec.person_id);
      per_qualifications_api.create_qualification
          (p_validate                      => FALSE,
           p_effective_date                => SYSDATE,
           p_qualification_type_id         => i_rec.qualification_type_id,
           p_attendance_id                 => i_rec.attendance_id,
           p_business_group_id             => fnd_profile.VALUE
                                                      ('PER_BUSINESS_GROUP_ID'),
           p_person_id                     => i_rec.person_id,
           p_awarded_date                  => SYSDATE,
           p_start_date                    => SYSDATE,
           p_qua_information_category      => 'SA',
           p_qua_information1              => 'EARENED',
           p_qualification_id              => l_qualification_id,
           p_object_version_number         => l_object_version_number
          );

      UPDATE xxx_emp_edu_school_info
         SET upload_status = 'Y',
             qualification_id = l_qualification_id
       WHERE ROWID = i_rec.ROWID;
      
       commit;
   END LOOP;
END;

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

API to Create External Bank Branch

Salam Alaikum,

   I share with you guys this API, that i use to create External Banks Branches , please note that external bank should exist and bank_id should be passed to this API.

DECLARE
   p_api_version           NUMBER := 1.0;
   p_init_msg_list         VARCHAR2 (1) := 'F';
   x_return_status         VARCHAR2 (2000);
   x_msg_count             NUMBER (5);
   x_msg_data              VARCHAR2 (2000);
   x_response              iby_fndcpt_common_pub.result_rec_type;
   p_ext_bank_branch_rec   iby_ext_bankacct_pub.extbankbranch_rec_type;
   x_branch_id             NUMBER;
   p_count                 NUMBER;

   CURSOR C1
   IS
 SELECT DISTINCT BANK_COUNTRY, BANK_NUMBER, BANK_NAME
                FROM xx_customer_tmp_amm_t
               WHERE     BANK_COUNTRY IS NOT NULL
                     AND BANK_NUMBER IS NOT NULL
                     AND BANK_NAME IS NOT NULL
                     and O_BANK_UPLOAD_STATUS is null;
BEGIN
   FOR REC1 IN C1
   LOOP
      p_ext_bank_branch_rec.bch_object_version_number := 1.0;
      p_ext_bank_branch_rec.branch_name := rec1.BANK_BRANCH_NAME;
      p_ext_bank_branch_rec.branch_type := 'ABA';
      p_ext_bank_branch_rec.branch_number := rec1.BRANCH_NUMBER;
      p_ext_bank_branch_rec.bank_party_id := rec1.NEW_BANK_ID;


      APPS.FND_MSG_PUB.DELETE_MSG (NULL);
      APPS.FND_MSG_PUB.INITIALIZE ();


      IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH (
         p_api_version           => p_api_version,
         p_init_msg_list         => p_init_msg_list,
         p_ext_bank_branch_rec   => p_ext_bank_branch_rec,
         x_branch_id             => x_branch_id,
         x_return_status         => x_return_status,
         x_msg_count             => x_msg_count,
         x_msg_data              => x_msg_data,
         x_response              => x_response);

      DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
      DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
      DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);
      DBMS_OUTPUT.put_line ('x_branch_id = ' || x_branch_id);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Code = ' || x_response.result_code);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Category = ' || x_response.result_category);
      DBMS_OUTPUT.put_line (
         'x_response.Result_Message = ' || x_response.result_message);

      IF x_msg_count = 1
      THEN
         DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
      ELSIF x_msg_count > 1
      THEN
         LOOP
            p_count := p_count + 1;
            x_msg_data :=
               fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

            IF x_msg_data IS NULL
            THEN
               EXIT;
            END IF;

            DBMS_OUTPUT.put_line (
               'Message' || p_count || ' ---' || x_msg_data);
         END LOOP;
      END IF;

      IF (x_return_status = 'S')
      THEN
              --         COMMIT;
      END IF;
   END LOOP;
END;

     And  Here is a Query that you can use to see uploaded bank branches.

SELECT BranchParty.PARTY_ID Branch_Party_Id,
       BranchParty.PARTY_NAME Bank_Branch_Name,
       BranchParty.ORGANIZATION_NAME_PHONETIC Bank_Branch_Name_Alt,
       BranchCA.start_date_active Start_Date,
       BranchCA.end_date_active End_Date
  FROM HZ_PARTIES BranchParty, HZ_CODE_ASSIGNMENTS BranchCA
 WHERE     1 = 1
       AND BranchParty.PARTY_TYPE = 'ORGANIZATION'
       AND BranchParty.status = 'A'
       AND BranchCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
       AND BranchCA.CLASS_CODE IN ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH')
       AND BranchCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
       AND (BranchCA.STATUS = 'A' OR BranchCA.STATUS IS NULL)
       AND BranchCA.OWNER_TABLE_ID = BranchParty.PARTY_ID
       AND BranchParty.created_by = -1;

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

API to Create External Bank

Salam Alaikum,

   I share with you guys this API, that i use to create External Banks that then can be assigned to customers (AR) or Suppliers (AP).

DECLARE
   LC_OUTPUT          VARCHAR2 (3000);
   LC_MSG_DUMMY       VARCHAR2 (3000);
   LC_RETURN_STATUS   VARCHAR2 (3000);
   LC_MSG_DATA        VARCHAR2 (3000);

   LN_BANK_ID         NUMBER;
   LN_MSG_COUNT       NUMBER;
   LR_EXTBANK_REC     APPS.IBY_EXT_BANKACCT_PUB.EXTBANK_REC_TYPE;
   LR_RESPONSE_REC    APPS.IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;

   V_COUNTRY_CODE     VARCHAR2 (100);

   CURSOR C1
   IS
      SELECT DISTINCT BANK_COUNTRY, BANK_NUMBER, BANK_NAME
                FROM xx_customer_tmp_amm_t
               WHERE     BANK_COUNTRY IS NOT NULL
                     AND BANK_NUMBER IS NOT NULL
                     AND BANK_NAME IS NOT NULL
                     and O_BANK_UPLOAD_STATUS is null;

BEGIN
   LC_RETURN_STATUS := '';
   LN_MSG_COUNT := '';
   LC_MSG_DATA := '';


   APPS.FND_MSG_PUB.DELETE_MSG (NULL);
   APPS.FND_MSG_PUB.INITIALIZE ();

   FOR REC1 IN C1
   LOOP
      BEGIN
         SELECT LOOKUP_CODE
           INTO V_COUNTRY_CODE
           FROM FND_LOOKUP_VALUES
          WHERE     LOOKUP_TYPE = 'GHR_US_POSTAL_COUNTRY_CODE'
                AND LANGUAGE = 'US'
                AND MEANING = TRIM (REC1.BANK_COUNTRY);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('Bank_Country name is not Correct ');
      END;

      LR_EXTBANK_REC.BANK_NAME := REC1.BANK_NAME;
      LR_EXTBANK_REC.BANK_NUMBER := REC1.BANK_NUMBER;
      LR_EXTBANK_REC.COUNTRY_CODE := V_COUNTRY_CODE;
      IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK ( -- ------------------------------
         -- Input data elements
         -- ------------------------------
         P_API_VERSION     => 1.0,
         P_INIT_MSG_LIST   => FND_API.G_TRUE,
         P_EXT_BANK_REC    => LR_EXTBANK_REC,
         -- --------------------------------
         -- Output data elements
         -- --------------------------------
         X_BANK_ID         => LN_BANK_ID,
         X_RETURN_STATUS   => LC_RETURN_STATUS,
         X_MSG_COUNT       => LN_MSG_COUNT,
         X_MSG_DATA        => LC_MSG_DATA,
         X_RESPONSE        => LR_RESPONSE_REC);

      LC_OUTPUT := ' ';

      IF (LC_RETURN_STATUS <> 'S')
      THEN
         FOR I IN 1 .. LN_MSG_COUNT
         LOOP
            APPS.FND_MSG_PUB.GET (I,
                                  APPS.FND_API.G_FALSE,
                                  LC_MSG_DATA,
                                  LC_MSG_DUMMY);

            LC_OUTPUT :=
               LC_OUTPUT
               || (TO_CHAR (I) || ': ' || SUBSTR (LC_MSG_DATA, 1, 250));
         END LOOP;

         APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.OUTPUT,
                                 'Error Occured while Creating Bank: ');
      END IF;

      IF (LC_RETURN_STATUS = 'S')
      THEN
              --         COMMIT;
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

   And  Here is a Query that u can use to see uploaded banks.

SELECT BankParty.*
  FROM HZ_PARTIES BankParty, HZ_CODE_ASSIGNMENTS BankCA
 WHERE     BankParty.PARTY_TYPE = 'ORGANIZATION'
       AND NVL (BankParty.status, 'A') = 'A'
       AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
       AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')
       AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
       AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
       AND NVL (BankCA.STATUS, 'A') = 'A'
       AND bankparty.created_by = -1;

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

API to Assign Competency to Employee(s)

Salam Alaikum,

   I share with you guys this API, that i used to delete an Appraisal for employees.It assign the competency to employees. But you can use the same API to assign it (competency) to a specific job or position.

DECLARE
l_competence_element_id number;
l_api_ovn number;

cursor app_data
is
--- note1: profeciency level id
--- note2: person id update
--- note3: competence level id should be added to the competences tables and passed here 
    select a.*,c.competence_id,a.rowid
    from XXX_HR_PER_COMPETENCY a , xxxhrdata.xxx_competencies b ,xxx_competency_levels c
    where 1=1
    and a.COMPETENCY_NAME = b.COMPETENCY_NAME
    and b.COMBINATION = c.COMBINATION;       

BEGIN
    for i_rec in app_data
    loop
   hr_competence_element_api.create_competence_element
      (     
       p_business_group_id                 => fnd_profile.VALUE('PER_BUSINESS_GROUP_ID'),
       p_effective_date                    => TO_DATE ('1900/01/01','YYYY/MM/DD'),        
      
       p_competence_id                     => i_rec.competence_id,
       p_proficiency_level_id              => i_rec.PROFICIENCY_LEVEL_id,
       p_rating_level_id                   => i_rec.LEVEL_NO,
       p_person_id                         => i_rec.person_id,
      
       p_competence_element_id             => l_competence_element_id,
       p_object_version_number             => l_api_ovn
      );
           
      update XXX_HR_PER_COMPETENCY u
      set upload_status = 'Y'
      where i_rec.rowid = u.rowid;
       
     end loop; 
END;

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

API to Create HR Competence

Salam Alaikum,

   I share with you guys this API, that i used to Create a Competence. That then can assigned to an employee or more.

DECLARE
   v_competence_id              NUMBER;
   v_competence_definition_id   NUMBER;
   v_object_version_number      NUMBER;
   v_name                       VARCHAR2;

   CURSOR app_data
   IS
      SELECT *
        FROM xxx_competencies;               ---   per_competences_v 
BEGIN
   FOR i_rec IN app_data
   LOOP
   
     hr_competences_api.create_competence
          (p_effective_date                => TO_CHAR ('1900/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS'),
           p_date_from                     => TO_CHAR ('1900/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS'),
           p_business_group_id             => fnd_profile.VALUE('PER_BUISNESS_GROUP_ID'),
           p_description                   => i_rec.competency_description,
           p_competence_alias              => i_rec.competency_name,
          
           p_competence_id                 => v_competence_id,
           p_competence_definition_id      => v_competence_definition_id,
           p_object_version_number         => v_object_version_number,
           p_name                          => v_name
          );

      UPDATE xxx_competencies cbd
         SET cbd.upload_status = 'Y',
             cbd.competence_id = v_competence_id
       WHERE cbd.ROWID = i_rec.ROWID;

   END LOOP;
END;

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

API to Delete Employee Appraisal

Salam Alaikum,

   I share with you guys this API, that i used to delete an Appraisal for employees.

DECLARE
   v_appraisal_id            NUMBER;
   v_object_version_number   NUMBER;
   l_template_id             NUMBER;
   l_rating_level_id         NUMBER;
   l_appraisal_id            NUMBER;
   v_date_of_hire            DATE;
   v_start_date_var          DATE;
   v_end_date_var            DATE;
   l_object_version_number   NUMBER;

   CURSOR app_data
   IS
      SELECT *
        FROM per_appraisals;     
BEGIN
   FOR i_rec IN app_data
   LOOP

        HR_UTILITY.FND_INSERT(SYSDATE);

      hr_appraisals_api.delete_appraisal
                    (p_appraisal_id                 => i_rec.appraisal_id,
                     p_object_version_number        => i_rec.OBJECT_VERSION_NUMBER
                    );

         commit;
        
   END LOOP;
END;

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

API to Create Appraisal for Employee

Salam Alaikum,

   I share with you guys this API, that i used to create Appraisal for employees.

DECLARE
 v_appraisal_id          NUMBER;
 v_object_version_number NUMBER;
 l_template_id           NUMBER;
 l_rating_level_id       NUMBER;
 l_appraisal_id          NUMBER;
 v_date_of_hire          DATE;
 v_start_date_var        DATE;
 v_end_date_var          DATE;

 l_object_version_number NUMBER;

 CURSOR app_data
 IS
  -- note1: over_all_rating need to be mapped to get performance id
  SELECT t.*,
         k.person_id,
         k.original_date_of_hire,
         t.ROWID,
         ppos.actual_termination_date actual_termination_date
    FROM xxxhrdata.xxx_emp_appraisals_info t,
         per_all_people_f k,
         per_periods_of_service ppos
   WHERE     1 = 1
         AND NVL (t.upload_status, 'N') = 'N'
         AND t.employee_number = k.employee_number
         AND t.over_all_rating IS NOT NULL
         AND EXTRACT (YEAR FROM t.start_date) IN ('2012', '2013')
         AND ppos.person_id = k.person_id;
BEGIN
 FOR i_rec IN app_data LOOP
  FND_PROFILE.PUT ('PER_BUSINESS_GROUP_ID', i_rec.PERSON_ID);
  FND_PROFILE.PUT ('PER_BUSINESS_GROUP_ID', i_rec.MAIN_APPRAISER_EMP_NO);

  HR_UTILITY.FND_INSERT (SYSDATE);

  ---Get the appraisal template
  SELECT appraisal_template_id
    INTO l_template_id
    FROM per_appraisal_templates
   WHERE attribute20 = 'HISTORICALAPPRAISAL';

  ---Get the Rating Level
  SELECT rating_level_id
    INTO l_rating_level_id
    FROM per_rating_levels a, per_rating_scales b
   WHERE     a.rating_scale_id = b.rating_scale_id
         AND b.default_flag = 'Y'
         AND a.step_value = i_rec.OVER_ALL_RATING;

  IF i_rec.ORIGINAL_DATE_OF_HIRE > i_rec.START_DATE THEN
   v_start_date_var := i_rec.ORIGINAL_DATE_OF_HIRE;
  ELSE
   v_start_date_var := i_rec.START_DATE;
  END IF;

  IF i_rec.actual_termination_date BETWEEN i_rec.START_DATE
                                       AND i_rec.end_date THEN
   v_end_date_var := i_rec.actual_termination_date;
  ELSE
   v_end_date_var := i_rec.end_date;
  END IF;

  hr_appraisals_api.create_appraisal (
   p_effective_date               => i_rec.START_DATE,
   p_business_group_id            => fnd_profile.VALUE ('PER_BUISNESS_GROUP_ID'),
   p_appraisal_template_id        => l_template_id,
   p_appraisee_person_id          => i_rec.person_id,              -- Employee
   p_appraiser_person_id          => i_rec.MAIN_APPRAISER_EMP_NO,   -- Manager
   p_appraisal_date               => i_rec.START_DATE,
   p_appraisal_period_start_date  => i_rec.START_DATE,
   p_appraisal_period_end_date    => NVL (i_rec.actual_termination_date,
                                          i_rec.end_date),
   p_overall_performance_level_id => l_rating_level_id,
   p_appraisal_system_status      => 'COMPLETED',
   p_main_appraiser_id            => i_rec.MAIN_APPRAISER_EMP_NO,   -- Manager
   p_open                         => '',
   p_system_type                  => 'MGRSTD',
   p_system_params                => 'pSystemType=MGRSTD',
   --changes based on the setup
   --p_attribute1                   => x,
   --p_attribute2                   => y,

   -- OUT
   p_appraisal_id                 => l_appraisal_id,
   p_object_version_number        => l_object_version_number);

  UPDATE xxx_emp_appraisals_info cbd
     SET cbd.upload_status = 'Y'
   WHERE cbd.ROWID = i_rec.ROWID;
 END LOOP;
END;

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

Customers API (Complete API)

Salam Alaikum,

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

   The following API can be used to create the following:

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

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

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

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

 -- get org details

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

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

 -- set operating unit

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

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

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

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

 v_organization_rec.organization_name := 'Igwe Super Customer';

 v_organization_rec.application_id := v_application_id;

 -- create organization

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 -- customer banks

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

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

 -- if bank does not exist then create it

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

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

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

    -- raise exception here

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

 -- end create bank

 END IF;

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

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

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

 -- if branch does not exist then create it
 IF v_branch_id IS NULL THEN
  -- create bank branch
  IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
   p_api_version         => 1.0,
   p_init_msg_list       => fnd_api.g_true,
   p_ext_bank_branch_rec => v_ext_bank_branch_rec,
   x_response            => v_response,
   x_branch_id           => v_branch_id,
   x_return_status       => v_return_status,
   x_msg_count           => v_msg_count,
   x_msg_data            => v_msg_data);

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

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

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

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

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

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

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

 -- if bank account does not exist then create it
 IF v_acct_id IS NULL THEN                               -- create bank branch
  IBY_EXT_BANKACCT_PUB.create_ext_bank_acct (
   p_api_version       => 1.0,
   p_init_msg_list     => fnd_api.g_true,
   p_ext_bank_acct_rec => v_ext_bank_acct_rec,
   x_acct_id           => v_acct_id,
   x_response          => v_response,
   x_return_status     => v_return_status,
   x_msg_count         => v_msg_count,
   x_msg_data          => v_msg_data);

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

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

    -- raise exception here

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

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

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

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

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

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

 -- map account to customer
 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment (
  p_api_version        => 1.0,
  p_init_msg_list      => fnd_api.g_true,
  p_commit             => fnd_api.g_false,
  x_return_status      => v_return_status,
  x_msg_count          => v_msg_count,
  x_msg_data           => v_msg_data,
  p_payer              => v_payer_context_rec,
  p_assignment_attribs => v_assignment_attribs,
  x_assign_id          => v_assign_id,
  x_response           => v_response);

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

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

    -- raise exception here

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

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