The requirement was to add phone details ( Work phone number, work mobile number) a list of employees.

We have implemented this using hr_phone_api

Process Steps:

1. Create a temp table

CREATE TABLE load_phone_temp

AS

SELECT parent_id,phone_number work_phone,phone_number mobile_phone

  FROM per_phones

 WHERE 1=2;

2.Load data from excel workbook to temp table using sql loader. Sample control file below.

LOAD DATA

 INFILE *

 REPLACE INTO TABLE load_phone_temp

 FIELDS TERMINATED BY "," 

 ( parent_id, work_phone,mobile_phone )

 begindata

111,2222222,33333333

sqlldr control = "control_file_path" user_id=userid/password@DBServicename

3.Run the script below which picks up records from temp table and creates contact details for employees

/* Formatted on 2014/06/23 21:48 (Formatter Plus v4.8.8) */

CONNECT apps/&1

SET serveroutput on

DECLARE

   ln_work_phone_id           per_phones.phone_id%TYPE;

   ln_mobile_phone_id         per_phones.phone_id%TYPE;

   ln_object_version_number   per_phones.object_version_number%TYPE;

   CURSOR c_phone_tmp

   IS

      SELECT *

        FROM load_phone_temp;

   l_person_id                load_phone_temp.parent_id%TYPE;

   l_count                    NUMBER                                  := 0;

   l_loc                      NUMBER                                  := 0;

BEGIN

   l_loc := 1;

   FOR r_phone_tmp IN c_phone_tmp

   LOOP

      l_loc := 2;

      DBMS_OUTPUT.put_line (' Employee Number ' || r_phone_tmp.parent_id);

      l_count := l_count + 1;

      l_loc := 2;

      BEGIN

         SELECT person_id

           INTO l_person_id

           FROM per_all_people_f

          WHERE employee_number = r_phone_tmp.parent_id

            AND TRUNC (SYSDATE) BETWEEN effective_start_date

                                    AND effective_end_date;

         l_loc := 3;

         BEGIN

            IF r_phone_tmp.work_phone IS NOT NULL

            THEN

               --DBMS_OUTPUT.put_line ('Inside work phone');

               -- Create or Update Employee Phone Detail

-- -----------------------------------------------------------

               l_loc := 4;

               hr_phone_api.create_phone

                         (                   -- Input data elements

                                             -- -----------------------------

                          p_date_from                  => TRUNC (SYSDATE),

                          p_phone_type                 => 'W1',

                          p_phone_number               => r_phone_tmp.work_phone,

                          p_parent_id                  => l_person_id,

                          p_parent_table               => 'PER_ALL_PEOPLE_F',

                          p_effective_date             => TRUNC (SYSDATE),

-- Output data elements

-- --------------------------------

                          p_phone_id                   => ln_work_phone_id,

                          p_object_version_number      => ln_object_version_number

                         );

               DBMS_OUTPUT.put_line ('work phone id ' || ln_work_phone_id);

            END IF;

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.put_line (   'Employee Number -> '

                                     || r_phone_tmp.parent_id

                                     || 'Error->'

                                     || SQLERRM

                                    );

         END;

         l_loc := 5;

         BEGIN

            IF r_phone_tmp.mobile_phone IS NOT NULL

            THEN

         --DBMS_OUTPUT.put_line ('Inside work mobile phone');

-- Create or Update Employee Phone Detail

-- -----------------------------------------------------------

               l_loc := 6;

               hr_phone_api.create_phone

                         (                   -- Input data elements

                                             -- -----------------------------

                          p_date_from                  => TRUNC (SYSDATE),

                          p_phone_type                 => 'WM',

                          p_phone_number               => r_phone_tmp.mobile_phone,

                          p_parent_id                  => l_person_id,

                          p_parent_table               => 'PER_ALL_PEOPLE_F',

                          p_effective_date             => TRUNC (SYSDATE),

-- Output data elements

-- --------------------------------

                          p_phone_id                   => ln_mobile_phone_id,

                          p_object_version_number      => ln_object_version_number

                         );

               DBMS_OUTPUT.put_line (   'Location '

                                     || l_loc

                                     || 'mobile phone id '

                                     || ln_mobile_phone_id

                                    );

            END IF;

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.put_line (   'Employee Number -> '

                                     || r_phone_tmp.parent_id

                                     || 'Error->'

                                     || SQLERRM

                                    );

         END;

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (   'Employee Number -> '

                                  || r_phone_tmp.parent_id

                                  || 'Error->'

                                  || SQLERRM

                                 );

      END;

   END LOOP;

   DBMS_OUTPUT.put_line ('Total No Of records processed -> ' || l_count);

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      ROLLBACK;

      DBMS_OUTPUT.put_line (l_loc || ' ' || SQLERRM);

END;

/

SHOW ERR;

To check different phone types an employee can have, please check HR_LOOKUPS table for lookup type ‘PHONE_TYPE’

4. Word of caution – Though I have tested and implemented this, would suggest you test thoroughly before implementing it in LIVE.

 

Date tracking in Oracle E-business is a mechanism to store data based on dates. This helps storage of historical data along with current data in the system.

The date tracked tables in Oracle end with ‘_F’ and have columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. These columns along with PERSON_ID form the composite key to identify a person’s record at a given point of time.
For example, to get the person’s record as on current date –

SELECT *

  FROM per_all_people_f

 WHERE sysdate BETWEEN effective_start_date AND effective_end_date;

Modes of Date Tracking:

• Purge: This removes the entire record from the database
• End Date: This updates EFFECTIVE_END_DATE of current active row to today’s date.
• Correction:This mode will update the column. No record history will be retained.
• Update: This will add a new row in the table with the EFFECTIVE_START_DATE as today’s date and EFFECTIVE_END_DATE as EOT(31-DEC-4712 and update current active record with EFFECTIVE_END_DATE as yesterday’s date

If Update mode selected, system will check if there are any future updates entered for the record being updated. If it has been updated in future, system will prompt for two more modes

• UPDATE_CHANGE_INSERT: The changes that the user makes remain in effect until the effective end date of the current record. At that point the future scheduled changes take effect.
• UPDATE_OVERRIDE:The user’s changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.

 

All persons related data are stored in table PER_ALL_PEOPLE_F. This table stores the historic and current information of the employee based on the Date track mode used during the record updates.

Tables below present the same data, but in a different ways :

• PER_ALL_PEOPLE_F: Stores the Person data with Date Track
• PER_PEOPLE_F: a view over PER_ALL_PEOPLE_F with additional security on records. Like, which user can see what all records?
• PER_PEOPLE_X: shows up only the currently active record as of SYSDATE.
• PER_PEOPLE_V: a view used by E-Biz forms to show the data with additional security using security profiles.
• PER_ALL_PEOPLE_D: a view that shows the date track history.

Below are the tables which are linked to PER_ALL_PEOPLE_F using person_id.

• PER_ADDRESSES: stores the address of a person. It’s a DATED table.
• PER_PHONES: stores the Phone numbers of a person.
• PER_CONTACT_RELATIONSHIPS: stores the contacts of a person.
• PER_DISABILITIES_F: stores the disability information of a person.
• PER_PERSON_TYPE_USAGES_F: stores the person type of a person (example: Employee, Ex-employee, Beneficiary etc.)
• PER_QUALIFICATIONS: stores the qualification of a person.

 

SELECT
ooha.order_number,
wdd.DELIVERY_DETAIL_ID ,
wda.delivery_detail_id,
wnd.delivery_id
from
oe_order_headers_all ooha,
oe_order_lines_all oola
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,wsh_delivery_details wdd
where wdd.SOURCE_HEADER_ID=ooha.HEADER_ID
AND wdd.source_line_id = ool.line_id
and ooha.header_id=oola.HEADER_ID
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
and ooha.ORDER_NUMBER=:ORDER_NUMBER

 DECLARE

    -- Declare variables for the script

    l_vendor_id           NUMBER;

    l_vendor_site_id      NUMBER;

    l_item_id             NUMBER;

    l_category_id         NUMBER;

    l_currency_code       VARCHAR2(3);

    l_org_id              NUMBER;

    l_bpo_header_id       NUMBER;

    l_bpo_line_id         NUMBER;

    l_start_date          DATE := SYSDATE;

    l_end_date            DATE := ADD_MONTHS(SYSDATE, 12);

BEGIN

    -- Set variable values as per your requirements

    l_vendor_id := <vendor_id>;

    l_vendor_site_id := <vendor_site_id>;

    l_item_id := <item_id>;

    l_category_id := <category_id>;

    l_currency_code := 'USD';

    l_org_id := <org_id>;

    -- Create Blanket Purchase Order Header

    l_bpo_header_id := po_headers_s.nextval;

    INSERT INTO po_headers_all

        (po_header_id, segment1, type_lookup_code, supplier_id, supplier_site_id,

         currency_code, rate_date, rate_type, agreement_id, agreement_line_id,

         authorization_status, org_id, start_date, end_date, created_by, creation_date,

         last_updated_by, last_update_date, last_update_login, object_version_number)

    VALUES

        (l_bpo_header_id, 'BPO-' || to_char(SYSDATE, 'YYYYMMDD') || '-' || l_bpo_header_id,

         'BLANKET', l_vendor_id, l_vendor_site_id, l_currency_code, SYSDATE,

         'Corporate', NULL, NULL, 'INCOMPLETE', l_org_id, l_start_date, l_end_date,

         fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,

         fnd_global.login_id, 1);

    -- Create Blanket Purchase Order Line

    l_bpo_line_id := po_lines_s.nextval;

    INSERT INTO po_lines_all

        (po_header_id, po_line_id, line_num, item_id, category_id, quantity,

         unit_price, unit_meas_lookup_code, need_by_date, promise_date, org_id,

         created_by, creation_date, last_updated_by, last_update_date, last_update_login,

         object_version_number)

    VALUES

        (l_bpo_header_id, l_bpo_line_id, 1, l_item_id, l_category_id, 100,

         10, 'EACH', l_start_date, l_end_date, l_org_id,

         fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id, 1);

    -- Commit the transaction

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Blanket Purchase Order created successfully with Header ID ' || l_bpo_header_id || ' and Line ID ' || l_bpo_line_id);

EXCEPTION

    WHEN OTHERS THEN

        -- Rollback the transaction in case of any exception

        ROLLBACK;

        DBMS_OUTPUT.PUT_LINE('Error creating Blanket Purchase Order: ' || SQLERRM);

END;

 /*NEW PUBLIC API - INV_ABC_ASSIGNMENTS_PUB .CREATE_ABC_ASSIGNMENTS IS ADDED TO INSERT/UPDATE ABC ASSIGNMENTS

IF THE ITEM ALREADY EXISTS IT WILL BE UPDATED/ASSIGNED TO THE NEW CLASS, ELSE WILL BE ADDED TO THE CLASS PASSED AS PARAMETER.*/

/* public api PLS file INVPAASS.pls ,INVPAASB.pls  */ 

-- organization V1( master organization, M1 (child org )

-- created item(s) ABC_API_1, ABC_API_2, ABC_API_3

-- This data is from the table MTL_SYSTEM_ITEMS_B

--SEGMENT1                                 INVENTORY_ITEM_ID

---------------------------------------- -----------------

--ABC_API_1                                           24535205

-- created abc group ABC_API_EX   in M1

-- This data is from the table MTL_ABC_ASSIGNMENT_GROUPS

--ASSIGNMENT_GROUP_NAME                    ASSIGNMENT_GROUP_ID

---------------------------------------- -------------------

-- ABC_API_EX                                             10250 

-- These are there clases used in  abc group ABC_API_EX  

-- This data is from  MTL_ABC_ASSGN_GROUP_CLASSES 

--ABC_CLASS_NAME                           ABC_CLASS_ID

---------------------------------------- ------------

--Class A                                            56 

--CLASS B                                            57

--Class C                                            58 

-- In the demo accept statements will be use to get  the 

-- three required . 

SET SERVEROUTPUT ON ;

ACCEPT 1 NUMBER PROMPT 'Please enter the  inventory_item_id :'

ACCEPT 2 NUMBER PROMPT 'Please enter the  assignment_group_id :'

ACCEPT 3 NUMBER PROMPT 'Please enter the  abc_class_id :'

DECLARE

ITEM_ID NUMBER ;

ASSIGN_GRP_ID NUMBER;

ABC_CLS_ID  NUMBER;

Cursor ITEM IS 

select msi.inventory_item_id

from apps.mtl_system_items_b msi,

     apps.mtl_abc_assignments maa,

     apps.mtl_abc_assignment_groups maag,

     apps.mtl_abc_classes mac,

     apps.mtl_abc_compile_headers mach

where msi.organization_id = :P_SOURCE_ORG_ID

and maa.inventory_item_id = msi.inventory_item_id

and maa.assignment_group_id = maag.assignment_group_id

and maag.organization_id = :P_SOURCE_ORG_ID

and maag.compile_id = mach.compile_id

and maa.abc_class_id = mac.abc_class_id 

and exists (select 1

            from apps.mtl_system_items_b msi2

            where msi2.organization_id = :P_DESTINATION_ORG_ID

            and msi2.inventory_item_id = msi.inventory_item_id);

Cursor TGT IS

  select maag.assignment_group_id,mac.abc_class_id

from apps.mtl_system_items_b msi,

     apps.mtl_abc_assignments maa,

     apps.mtl_abc_assignment_groups maag,

     apps.mtl_abc_classes mac,

     apps.mtl_abc_compile_headers mach

where msi.organization_id = :P_DESTINATION_ORG_ID

and maa.inventory_item_id = msi.inventory_item_id

and maa.assignment_group_id = maag.assignment_group_id

and maag.organization_id = :P_DESTINATION_ORG_ID

and maag.compile_id = mach.compile_id

and maa.abc_class_id = mac.abc_class_id;

-- These varible are to capture the success of the api 

-- and print the failure messages 

LSTATUS VARCHAR2(1); -- return status 

LMSG_COUNT NUMBER;   -- message count 

LMSG_DATA VARCHAR2(240); -- message data 

V_MSG VARCHAR2(4000);

I NUMBER ;

j NUMBER ;

ABC_ITEM  INV_ABC_Assignments_PUB.ABC_ASSIGNMENTS_REC_TYPE;

--------------------------------------------------------------------------------

--  abc_assignments_rec_type record type

--  Record type to hold a abc assignment required columns

--                  inventory_item_id

--                  assignmentg_group_id

--                  abc_class_id

BEGIN 

-- these next three line will need to be modified

-- useed in a larger program to add a large number of items programaticly

for i in ITEM LOOP

  For J in TGT LOOP

ABC_ITEM.INVENTORY_ITEM_ID := i.inventory_item_id;

ABC_ITEM.ASSIGNMENT_GROUP_ID := j.assignment_group_id;

ABC_ITEM.abc_class_id := j.abc_class_id ;

 INV_ABC_Assignments_PUB.CREATE_ABC_ASSIGNMENTS( FND_API.G_TRUE,

                                                FND_API.G_TRUE,

                                                1.0,

                                                LSTATUS,

                                                LMSG_COUNT,

                                                LMSG_DATA ,

                                                ABC_ITEM);

                                                

                                                

        DBMS_OUTPUT.PUT_LINE ( 'Status :' || LSTATUS);

         

          IF (lstatus is null  or lstatus = 'S'  ) THEN -- changed for bug 22065914  

              DBMS_OUTPUT.PUT_LINE ( 'SUCCESS');

              COMMIT;

              DBMS_OUTPUT.PUT_LINE ( 'Item_id  ' || ITEM_ID || ' add to group ' || ASSIGN_GRP_ID || ' and Class id  ' || ABC_CLS_ID   );

              ELSE

              DBMS_OUTPUT.PUT_LINE ( 'FAILED, with Status :' || LSTATUS);

              DBMS_OUTPUT.PUT_LINE ( 'message count :' || LMSG_COUNT);

              dbms_output.put_line ( 'message :' || lmsg_data);

          IF ( lmsg_count > 1 ) THEN

             FOR i IN 1 .. lmsg_count

             LOOP

                fnd_msg_pub.get ( p_msg_index => i , p_encoded =>FND_API.G_FALSE,

                p_data => v_msg , p_msg_index_out => j ) ;

                dbms_output.put_line ( 'message :' || v_msg);

             END LOOP ;

           end if;

        END IF;

END LOOP;

 

 END LOOP;

END;