Oracle Apps 4u
  • Home
  • About Us
  • Services
    • Consulting
    • Training
    • On Job Support
    • Sample Projects
  • Courses
    • Oracle Apps Technical
    • Oracle DBA
    • Oracle Apps SCM – PO & OM
  • Downloads
  • Interview
    • Interview Questions
    • Assessment Quiz
    • Resume Services
  • Tools
  • Blog
  • Contact

Some Important Oracle HRMS Queries

Posted on June 17, 2014 by admin Posted in Blog
 —————————————————————————————-
      — Cursor to check whether a person SSN already exists in the oracle or not
      —————————————————————————————-
      CURSOR cur_per_ssn (
         p_national_identifier    per_all_people_f.national_identifier%TYPE,
         p_biz_id                 NUMBER)
      IS
         SELECT national_identifier
           FROM apps.per_all_people_f papf
          WHERE papf.national_identifier = p_national_identifier

                AND business_group_id = p_biz_id
                AND pv_effective_date BETWEEN papf.effective_start_date
                                          AND papf.effective_end_date;

      —————————————————————————————-
      — Cursor to get the final process and rehire flag of a person using the SSN
      —————————————————————————————-
      CURSOR cur_rehire_chk (
         p_national_identifier1 per_all_people_f.national_identifier%TYPE)
      IS
         SELECT ppos.final_process_date,
                NVL (ppos.attribute2, ‘N’),
                papf.person_id
           FROM per_all_people_f papf, per_periods_of_service ppos
          WHERE papf.national_identifier = p_national_identifier1
                AND papf.person_id = ppos.person_id
                AND ppos.date_start IN
                       (SELECT MAX (date_start)
                          FROM per_periods_of_service ppos1
                         WHERE ppos1.person_id = ppos.person_id
                               AND ppos1.date_start <= TRUNC (SYSDATE))
                AND pv_effective_date BETWEEN papf.effective_start_date
                                          AND papf.effective_end_date
                AND ROWNUM < 2;

      ——————————————————————————————
      — Cursor to get the person type id of a person to pass to the create emp API
      ——————————————————————————————
      CURSOR cur_per_type (
         p_person_type    per_person_types.user_person_type%TYPE,
         p_biz_id         NUMBER)
      IS
         SELECT person_type_id
           FROM apps.per_person_types
          WHERE UPPER (user_person_type) = UPPER (p_person_type)
                AND business_group_id = p_biz_id;

« How to create a multilingual report in Oracle XML Publisher
How to Copy a Sales order from backend »

Recent Posts

  • Oracle Quizz
  • How to invoke a calendar in a form
  • Oracle DBA Resume
  • JAVA Quizz
  • DBA Quizz

Categories

  • Blog
  • Quizz

Courses List

  • Oracle Apps Technical support
  • ORACLE DBA
  • Oracle SCM – PO & OM

About Us

Oracleapps4u.net is a global provider of oracle application consulting services. We are passionate about helping our clients to adapt and grow in a changing world of technology by focusing on innovation, quality, process, workforce development and re-usable solutions.

Services

Assurance Services BI & Performance Management Business Process Services Consulting Digital Enterprise Enterprise Solutions IT Infrastructure Services IT Services Platform Solutions Supply Chain Management

Events

Enterprise Security & Risk Management Enterprise Solutions iON Small and Medium Business IT Infrastructure Services IT Services Platform Solutions Supply Chain Management

Contact Us

Oracleapps4u.net
ASA Technologies,
Raheja Mindspace IT Park,
Hitech City, Hyderabad.

Phone No: +91-9885101475
Email: info@oracleapps4u.net

CyberChimps WordPress Themes

Copyright © 2015 Oracleapps4u.net