SELECT hou.name ou_name,

prh.segment1 requisition_num, prh.creation_date, prh.created_by, poh.agent_id, poh.po_header_id, poh.segment1 po_num, ppx.full_name Requestor_Name, prh.description Req_Description, auth_status.displayed_field authorization_status, prh.org_id, prh.note_to_authorizer, req_type.displayed_field type_lookup_code, prl.catalog_type, prl.blanket_po_header_id, prl.blanket_po_line_num, prl.catalog_type, prl.line_num, plt.order_type_lookup_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, COALESCE(prd.req_line_quantity,prl.quantity) quantity, (prl.unit_price * COALESCE(prd.req_line_quantity, prl.quantity ) ) line_amount, prl.quantity_delivered, prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date, pla.cancel_reason, glcc.concatenated_segments charge_account, aps.vendor_id, aps.vendor_name, aps.segment1 vendor_number, assa.vendor_site_code, plc.displayed_field po_status, hr.location_code deliver_to_location, prl.urgent_flag, (SELECT segment1 FROM po_headers_all pha WHERE pha.po_header_id = prl.blanket_po_header_id ) bpa_number, (SELECT (CASE WHEN SYSDATE BETWEEN pha.start_date AND NVL(pha.end_date,SYSDATE+1) THEN 'ACTIVE' ELSE 'INACTIVE' END ) FROM po_headers_all pha WHERE pha.po_header_id = prl.blanket_po_header_id ) bpa_status, prl.blanket_po_line_num, (SELECT (CASE WHEN NVL((pl.expiration_date), (SYSDATE)) >= (SYSDATE) THEN 'ACTIVE' ELSE 'INACTIVE' END ) FROM po_lines_all pl WHERE pl.po_header_id = prl.blanket_po_header_id AND pl.line_num = prl.blanket_po_line_num ) bpa_line_status, prl.suggested_vendor_product_code vendor_item_number, prl.rate_date exchange_rate_date, prl.rate exchange_rate, prl.currency_unit_price FROM po_requisition_headers_all prh, hr_all_organization_units hou, hr_organization_information hoi, po_requisition_lines_all prl, po_req_distributions_all prd, gl_code_combinations_kfv glcc, per_people_x ppx, po_headers_all poh, po_lines_all pla, ap_suppliers aps, ap_supplier_sites_all assa, po_distributions_all pda, hr_locations hr, po_lookup_codes plc, po_lookup_codes auth_status, po_lookup_codes req_type, po_line_types plt WHERE prh.requisition_header_id = prl.requisition_header_id AND hou.organization_id = prh.org_id AND hou.organization_id = hoi.organization_id AND hoi.org_information_context = 'Operating Unit Information' AND ppx.person_id = prh.preparer_id AND prh.type_lookup_code = 'PURCHASE' AND prd.requisition_line_id = prl.requisition_line_id AND pda.req_distribution_id = prd.distribution_id AND glcc.code_combination_id = prd.code_combination_id AND pda.po_line_id = pla.po_line_id AND pla.po_header_id = poh.po_header_id AND pda.po_header_id = poh.po_header_id AND pda.deliver_to_location_id = hr.location_id AND poh.vendor_id = aps.vendor_id AND poh.vendor_site_id = assa.vendor_site_id AND aps.vendor_id = assa.vendor_id AND prl.line_type_id = plt.line_type_id AND plc.lookup_type = 'DOCUMENT STATE' AND plc.lookup_code = poh.closed_code AND auth_status.lookup_type = 'AUTHORIZATION STATUS' AND auth_status.lookup_code = prh.authorization_status AND req_type.lookup_type = 'REQUISITION TYPE' AND req_type.lookup_code = prh.type_lookup_code

 

SELECT fcr.request_id ,

  fcpt.user_concurrent_program_name

  || NVL2(fcr.description, ' ('

  || fcr.description

  || ')', NULL) conc_prog ,

  fu.user_name requestor ,

  fu.description requested_by ,

  fu.email_address ,

  frt.responsibility_name requested_by_resp ,

  TRIM(fl.meaning) STATUS ,

  fcr.phase_code ,

  fcr.status_code ,

  fcr.argument_text "PARAMETERS" ,

  TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested ,

  TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start ,

  TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time ,

  DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold ,

  CASE

    WHEN fcr.hold_flag = 'Y'

    THEN SUBSTR( fu.description , 0 , 40 )

  END last_update_by ,

  CASE

    WHEN fcr.hold_flag = 'Y'

    THEN fcr.last_update_date

  END last_update_date ,

  fcr.increment_dates ,

  CASE

    WHEN fcrc.CLASS_INFO IS NULL

    THEN 'Yes: '

      || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')

    ELSE 'n/a'

  END run_once ,

  CASE

    WHEN fcrc.class_type = 'P'

    THEN 'Repeat every '

      || SUBSTR(fcrc.class_info, 1, instr(fcrc.class_info, ':')           - 1)

      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months', 'H', ' hours', 'D', ' days')

      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C', ' from the completion of the prior run')

    ELSE 'n/a'

  END set_days_of_week ,

  CASE

    WHEN fcrc.class_type                         = 'S'

    AND instr(SUBSTR(fcrc.class_info, 33),'1',1) > 0

    THEN 'Days of week: '

      || DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun, ')

      || DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ')

      || DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ')

      || DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ')

      || DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ')

      || DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ')

      || DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat ')

    ELSE 'n/a'

  END days_of_week

FROM apps.fnd_concurrent_requests fcr ,

  apps.fnd_user fu ,

  apps.fnd_concurrent_programs fcp ,

  apps.fnd_concurrent_programs_tl fcpt ,

  apps.fnd_printer_styles_tl fpst ,

  apps.fnd_conc_release_classes fcrc ,

  apps.fnd_responsibility_tl frt ,

  apps.fnd_lookups fl

WHERE fcp.application_id       = fcpt.application_id

AND fcr.requested_by           = fu.user_id

AND fcr.concurrent_program_id  = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND fcr.concurrent_program_id  = fcpt.concurrent_program_id

AND fcr.responsibility_id      = frt.responsibility_id

AND fcr.print_style            = fpst.printer_style_name(+)

AND fcr.release_class_id       = fcrc.release_class_id(+)

AND fcr.status_code            = fl.lookup_code

AND fl.lookup_type             = 'CP_STATUS_CODE'

AND fcr.phase_code             = 'P'

AND frt.language               = 'US'

AND fpst.language              = 'US'

AND fcpt.language              = 'US'

ORDER BY Fu.Description,

  Fcr.Requested_Start_Date ASC

 

SELECT s.inst_id,
NVL (s.username, 'Internal') "User Name",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",

fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;

Here below is the detail sql query to find the item category value in oracle apps.

select MSB.segment1 ITEM_CODE,

  MSB.DESCRIPTION ITEM_DESCRIPTION,

     MCST.category_set_name,

       MCK.CONCATENATED_SEGMENTS CATEGORY

  from apps.mtl_system_items_b MSB,

       apps.mtl_categories_kfv MCK,

       apps.mtl_item_categories MIC,

        apps.mtl_category_sets_tl MCST

       where MIC.inventory_item_id = MSB.inventory_item_id

       and MIC.category_id = MCK.category_id

      and MSB.organization_id =MIC.organization_id

     and MSB.organization_id = :P_INV_ORGANIZATION

     and MCST.category_set_id=MIC.category_set_id

hr_employee_api.create_employee API is used to create new employee with other important information as per the requirement.

hr_employee_api.create_employee

(p_hire_date                      => TO_DATE (’12-AUG-2023′),

p_business_group_id=>fnd_profile.value_specific(‘PER_BUSINESS_GROUP_ID’)

p_last_name                      => ‘ P_EMP_LAST_NAME ‘,

p_first_name                     => ‘ P_EMP_FIRST_NAME ‘,

p_middle_names                   => NULL,

p_sex                            => ‘M’,

p_national_identifier            => ‘256-54-575′,

p_date_of_birth                  => TO_DATE (’05-NOV-1990’),

p_known_as                       => ‘ P_DUMMY ‘,

p_employee_number                => lc_employee_number,

p_person_id                      => ln_person_id,

p_assignment_id                  => ln_assignment_id,

p_per_object_version_number      => ln_object_ver_number,

p_asg_object_version_number      => ln_asg_ovn,

p_per_effective_start_date       => ld_per_effective_start_date,

p_per_effective_end_date         => ld_per_effective_end_date,

p_full_name                      => lc_full_name,

p_per_comment_id                 => ln_per_comment_id,

p_assignment_sequence            => ln_assignment_sequence,

p_assignment_number              => lc_assignment_number,

p_name_combination_warning       => lb_name_combination_warning,

p_assign_payroll_warning         => lb_assign_payroll_warning,

p_orig_hire_warning              => lb_orig_hire_warning);

 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.