1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help ending my alert once probation initiation has started

Discussion in 'SQL PL/SQL' started by moha, Sep 26, 2017.

  1. moha

    moha Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    kenya
    Hi all i created one alert that picks employees that have probation end date before one month and mail is sent to supervisor and it works fine but a new requirement is that when the probation initiation begins it should pick from hr_api_transactions table and stop the alert from sending afte probation confirmation please help. below is the query

    SELECT papf.employee_number,
    papf.full_name employee_name,
    pjt.NAME job,
    pp.NAME position_name,
    papf.start_date,
    paaf.date_probation_end,
    paaf.probation_period, notice_period,
    DECODE (probation_unit,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) probation_unit,
    DECODE (notice_period_uom,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) notice_period_uom,
    hl_freq.meaning, paaf.effective_start_date, paaf.effective_end_date,
    mg.full_name supervisor_name, mg.email_address
    supervisor_email
    FROM per_all_assignments_f paaf,
    per_jobs_tl pjt,
    per_grades_tl pgt,
    per_all_people_f papf,
    hr_lookups hl_freq,
    per_all_positions pp,
    per_all_people_f mg
    WHERE 1 = 1
    AND paaf.person_id = papf.person_id
    AND paaf.business_group_id = papf.business_group_id
    AND pgt.grade_id(+) = paaf.grade_id
    AND pjt.job_id = paaf.job_id
    AND paaf.frequency = hl_freq.lookup_code
    AND papf.business_group_id = pp.business_group_id
    AND pp.position_id = paaf.position_id
    AND paaf.person_id = papf.person_id
    AND paaf.job_id = pp.job_id
    AND paaf.supervisor_id = mg.person_id(+)
    AND TRUNC (SYSDATE) BETWEEN TRUNC (mg.effective_start_date)
    AND TRUNC (mg.effective_end_date)
    AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND hl_freq.lookup_type = 'FREQUENCY'
    AND paaf.probation_period IS NOT NULL
    AND TRUNC (paaf.date_probation_end) BETWEEN paaf.effective_start_date
    AND NVL (paaf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (paaf.date_probation_end) BETWEEN papf.effective_start_date
    AND NVL (papf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TO_CHAR (papf.original_date_of_hire, 'yyyy') =
    TO_CHAR (SYSDATE, 'yyyy')
    AND TO_DATE (SYSDATE, 'DD-MM-RRRR') =
    TO_DATE (paaf.date_probation_end, 'DD-MM-RRRR')
    - 30
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And where are the table create statements and sample data? A query is only as good as the tables it uses and if they aren't available how can anyone test and offer suggestions?
     
  3. moha

    moha Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    kenya
    Which create statement zargon can u please clarify.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You post a query using tables no one here has access to and you expect us to know why your query isn't performing as you desire. Is that logical? You need to provide MUCH more than just your query if you want someone here to actually be able to assist you. As I clearly stated before you need to post create table statements and provide sample data. We can do nothing without those items.