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.  

Concurrent & Responsibilities query

Salam Alaikum,

   I share with you guys this query that i use to get report information, it can also give you all the reports available for a specific user and by which responsibilities. And it can be used to get all the reports attached to a certain responsibility via its request group.

SELECT a.user_concurrent_program_name,
       b.request_group_name,
       e.responsibility_name
  FROM fnd_concurrent_programs_tl a,
       fnd_request_groups b,
       fnd_request_group_units c,
       fnd_responsibility d,
       fnd_responsibility_tl e,
       fnd_user_resp_groups_direct f,
       fnd_user g,
       fnd_concurrent_programs h
 WHERE     a.concurrent_program_id = c.request_unit_id
       AND a.concurrent_program_id = h.concurrent_program_id
       AND b.request_group_id = c.request_group_id
       AND b.request_group_id = d.request_group_id
       AND d.responsibility_id = e.responsibility_id
       AND b.application_id = c.application_id
       AND d.application_id = e.application_id
       AND f.user_id = g.user_id
       AND f.responsibility_id = e.responsibility_id
       AND UPPER (a.user_concurrent_program_name) LIKE
            '%' || UPPER (:p_concurrent_program_name) || '%'
       AND g.user_name = NVL (UPPER (:p_user_name), g.user_name)
       AND e.language = NVL (UPPER (:p_language), USERENV ('LANG'))
       AND SYSDATE BETWEEN NVL (
                            f.start_date,
                            fnd_date.canonical_to_date (
                             '0001/01/01 00:00:00'))
                       AND NVL (
                            f.end_date,
                            fnd_date.canonical_to_date (
                             '4000/12/31 23:59:59'));

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

Query to get all Business Group's Mapped with thier Legal Entities

Salam Alaikum,

   I share with you guys this query that can used to get Business group information, along with their Legal entities.

SELECT DISTINCT hrl.country,
                hroutl_bg.NAME bg,
                hroutl_bg.organization_id,
                lep.legal_entity_id,
                lep.NAME legal_entity,
                hroutl_ou.NAME ou_name,
                hroutl_ou.organization_id org_id,
                hrl.location_id,
                hrl.location_code,
                glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE     lep.transacting_entity_flag = 'Y'
       AND lep.party_id = hzp.party_id
       AND lep.legal_entity_id = reg.source_id
       AND reg.source_table = 'XLE_ENTITY_PROFILES'
       AND hrl.location_id = reg.location_id
       AND reg.identifying_flag = 'Y'
       AND ter.territory_code = hrl.country
       AND lep.legal_entity_id = hro.default_legal_context_id
       AND gloperatingunitseo.organization_id = hro.organization_id
       AND hroutl_bg.organization_id = hro.business_group_id
       AND hroutl_ou.organization_id = hro.organization_id
       AND glev.legal_entity_id = lep.legal_entity_id;

Note: the query might need mild modification based on the setup you have.

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.

Lock on table (Oracle Apps)

Salam Alaikum,

   Many times we face the issue of lock on a table on the database.

   This could be huge problem, specially on Oracle Apps. As it can stop business process.

   I provide a part of code i use to know which is the table that locked, and additional information like session id (SID) thats locking the table. And other information like PID.

   This query gets information about the lock currently on the table: 

  SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
      ,vlocked.session_id
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
and vs.type <> 'BACKGROUND'         -- very important to exclude background sessions
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status,'XX') != 'KILLED';

  
   And  then you can share the query output with your DBA (recommended), or you can kill the session that's locking the table by yourself (You have to be extra careful with the kill session command; it can cause an instance CRASH!!!).

   The code to kill a session is:

ALTER SYSTEM KILL SESSION '&SID,&serial#';

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