Oracle Apps Knowledge Sharing

In this post, we have given a sample script to associate an address to existing employee in R12.

Tested Instance: R12.2

API: HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

TABLES:

per_all_people_f

per_addresses

 Query to Get list of defined Address Styles: 

SELECT *
      FROM fnd_descr_flex_contexts
     WHERE descriptive_flexfield_name  = 'Address Structure'
       AND descriptive_flex_context_code = :style
       AND enabled_flag                  = 'Y'
       AND application_id                = 800;
Script: 
SET SERVEROUTPUT ON;
DECLARE 
  lrec_person_details    per_all_people_f%ROWTYPE;
  ln_object_version_num  NUMBER;
  ln_address_id          NUMBER;
  ln_exists              NUMBER;
  lv_employee_num        VARCHAR2(200) DEFAULT '123456';
  
BEGIN 

  SELECT papf.*
    INTO lrec_person_details
    FROM per_all_people_f papf
   WHERE papf.employee_number = lv_employee_num
     AND current_employee_flag = 'Y'
     AND SYSDATE BETWEEN effective_start_date AND effective_end_date; 
     
  HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS
        (p_validate              => FALSE,
         p_effective_date        => lrec_person_details.effective_start_date,
         p_person_id             => lrec_person_details.person_id,
         p_primary_flag          => 'Y',
         p_style                 => 'GENERIC',
         p_date_from             => SYSDATE, 
         p_address_line1         => '11, TEST ADDRESS LINE',
         p_address_line2         => NULL,
         p_address_line3         => NULL,
         p_country               => 'MY',
         p_postal_code           => '40400',
         p_region_1              => 'SELANGOR',
         p_address_id            => ln_address_id,
         p_object_version_number => ln_object_version_num
        );
   DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id); 

   SELECT COUNT(1)
     INTO ln_exists
     FROM per_addresses
    WHERE address_id = ln_address_id
      AND person_id = lrec_person_details.person_id ;
   IF ln_exists > 0
   THEN 
     DBMS_OUTPUT.PUT_LINE('Address successfully attached to person');   
   ELSE 
     DBMS_OUTPUT.PUT_LINE('Address assignment failed'); 
   END IF;
END;

            In this post, we have a given an script to delete the phone record details from Oracle Application. This comes handy during operating unit deactivation and need to delete all sensitive details of the employees from database.

API: HR_PHONE_API.DELETE_PHONE

Tested Instance: R12.2

Table Impacted: PER_PHONES and PER_ALL_PEOPLE_F

Script:

SET SERVEROUTPUT ON;
DECLARE   
  CURSOR cur_phone_details (cv_emp_number  VARCHAR2)
  IS 
  SELECT pp.phone_id,
         pp.object_version_number,
         pp.phone_number
    FROM per_phones pp,
         per_all_people_f papf 
   WHERE papf.person_id = pp.parent_id 
     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
     AND pp.parent_table = 'PER_ALL_PEOPLE_F'
     AND papf.employee_number = cv_emp_number;
  
  lv_employee_number       VARCHAR2(200)  DEFAULT '123456';
  ln_count                 NUMBER;
BEGIN

  FOR phone_rec in cur_phone_details (lv_employee_number)
  LOOP
    ln_count := 0;
    
    HR_PHONE_API.DELETE_PHONE
        (
         p_validate => FALSE,
         p_phone_id => phone_rec.phone_id,
         p_object_version_number => phone_rec.object_version_number
        );
        
    SELECT COUNT(1)
      INTO ln_count
      FROM per_phones pp
     WHERE phone_id = phone_rec.phone_id;

    IF ln_count > 0 
    THEN    
      DBMS_OUTPUT.PUT_LINE('Phone: '||phone_rec.phone_number||' Status: Failed!!');
    ELSE 
      DBMS_OUTPUT.PUT_LINE('Phone: '||phone_rec.phone_number||' Status: Success!!');
    END IF;         
  END LOOP;     
  
END;

                    In this post, we have shared the commonly faced error while trying to run a OAF page from Jdeveloper.

Jdeveloper Version: 10.1.3.5.0.3

Issue Faced: Trying to execute a OAF page on jdeveloper, everything goes smoothly till compilation, OC4J server initiation. But,  no action after the message “20/09/28 12:40:10 Oracle Containers for J2EE 10g (10.1.3.5.0)  initialized”. Post this message, we suppose to see a browser page opened and display our page.

Error Noticed from Log: (SEVERE) Failed to set the internal configuration of the OC4J JMS Server with: XMLJMSServerConfig[file:/C:/oc4j_path/j2ee/home/config/jms.xml]

 Fix:

Deleted all contents from below directory:

<JDEV_HOME>\jdev\system\oracle.j2ee.10.1.3.43.6\embedded-oc4j\persistence\

Possible Reasons (Assumption):

1. Embedded OC4J server was not terminated properly last time. The above mentioned directory contains the files related to last configuration, so upon deleting all details from that directory, I assume it starts fine and work normal.
2. Jms.state file was corrupted due to some reason. The only option to delete it.

FYR:  <JDEV_HOME>\jdev\system\oracle.j2ee.10.1.3.43.6\embedded-oc4j\persistence\ looks like below.

Readers: If you have any better explanation or faced other issues and have solutions for the same, please share the same in comments. We will share to world of OAF developers. It will be useful to someone.