APPS.HR_AUTH_BRIDGE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 15

    SELECT prv.cmcd_rt_val ||' '||
           HR_GENERAL.DECODE_LOOKUP('BEN_ENRT_INFO_RT_FREQ',
                                     prv.cmcd_ref_perd_cd)
    FROM   ben_prtt_rt_val prv
    WHERE  prv.prtt_enrt_rslt_id  = p_prtt_enrt_rslt_id
    AND    prv.per_in_ler_id      = p_per_in_ler_id
    AND    prv.acty_typ_cd        = p_acty_typ_cd
    AND    prv.prtt_rt_val_stat_cd IS NULL
    AND    prv.dsply_on_enrt_flag = 'Y';

Line: 26

    SELECT prv.cmcd_rt_val ||' '||
           HR_GENERAL.DECODE_LOOKUP('BEN_ENRT_INFO_RT_FREQ',
                                     prv.cmcd_ref_perd_cd)
    FROM   ben_prtt_rt_val prv
    WHERE  prv.prtt_enrt_rslt_id  = p_prtt_enrt_rslt_id
    AND    prv.per_in_ler_id      = p_per_in_ler_id
    AND    prv.acty_typ_cd        = p_acty_typ_cd
    AND    prv.prtt_rt_val_stat_cd IS NULL;

Line: 86

    SELECT ppf.full_name ||' '||
           DECODE(pbn.pct_dsgd_num,NULL,TO_CHAR(pbn.amt_dsgd_val),TO_CHAR(pbn.pct_dsgd_num)||'%') beneficiary
    FROM   ben_pl_bnf_f pbn,
           per_all_people_f ppf
    WHERE  pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
    AND    pbn.per_in_ler_id     = p_per_in_ler_id
    AND    p_effective_date BETWEEN pbn.effective_start_date AND pbn.effective_end_date
    AND    pbn.prmry_cntngnt_cd  = p_prmry_cntngnt_cd
    AND    pbn.bnf_person_id     = ppf.person_id
    AND    p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date;

Line: 145

    SELECT pcp.name ||' '||
           HR_GENERAL.DECODE_LOOKUP('BEN_PRMRY_CARE_PRVDR_TYP',pcp.prmry_care_prvdr_typ_cd) primary_care_provider
    FROM   ben_prmry_care_prvdr_f pcp
    WHERE  pcp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
    AND    pcp.business_group_id = p_business_group_id
    AND    p_effective_date BETWEEN pcp.effective_start_date AND pcp.effective_end_date;

Line: 199

      SELECT rplcs_sspndd_rslt_id
      FROM   ben_prtt_enrt_rslt_f pen
      WHERE  pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
      AND    pen.business_group_id    = p_business_group_id
      AND    p_effective_date BETWEEN pen.effective_start_date AND pen.effective_end_date;

Line: 253

     SELECT HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type) contact_type
     FROM   per_contact_relationships pcr
     WHERE  pcr.person_id         = p_person_id
     AND    pcr.contact_person_id = p_contact_person_id
     AND    p_effective_date BETWEEN NVL(pcr.date_start,p_effective_date) AND NVL(pcr.date_end,p_effective_date)
     ORDER BY DECODE(pcr.personal_flag,'Y',1),pcr.contact_type;

Line: 305

     SELECT TO_CHAR(ppp.proposed_salary_n)
     FROM   per_pay_proposals ppp
     WHERE  ppp.pay_proposal_id = (SELECT MAX(ppp1.pay_proposal_id)
                                   FROM   per_pay_proposals ppp1
                                   WHERE  ppp1.assignment_id = p_assignment_id
                                   AND    ppp1.approved = 'Y'
                                   AND    ppp1.change_date <= p_effective_date);

Line: 354

     SELECT TO_CHAR(ppp.change_date,'MM/DD/YYYY')
     FROM   per_pay_proposals ppp
     WHERE  ppp.pay_proposal_id = (SELECT MAX(ppp1.pay_proposal_id)
                                   FROM   per_pay_proposals ppp1
                                   WHERE  ppp1.assignment_id = p_assignment_id
                                   AND    ppp1.approved = 'Y'
                                   AND    ppp1.change_date <= p_effective_date);

Line: 403

     SELECT ppr.performance_rating
     FROM   per_performance_reviews ppr
     WHERE  ppr.performance_review_id = (SELECT MAX(ppr1.performance_review_id)
                                         FROM   per_performance_reviews ppr1
                                         WHERE  ppr1.person_id = p_person_id
                                         AND    ppr1.review_date <= p_effective_date);

Line: 460

    SELECT pps.date_start
    FROM   per_periods_of_service pps
    WHERE  pps.period_of_service_id = p_period_of_service_id
    AND    pps.person_id = p_person_id;

Line: 519

     SELECT NVL(pps.actual_termination_date,TO_DATE('12/31/4712','MM/DD/YYYY'))
     FROM   per_periods_of_service pps
     WHERE  pps.period_of_service_id = p_period_of_service_id
     AND    pps.person_id = p_person_id;

Line: 549

     SELECT pas.per_system_status
     FROM   per_assignment_status_types pas
     WHERE  pas.assignment_status_type_id = p_assignment_status_type_id;

Line: 573

   SELECT paf.assignment_id
   FROM   per_all_assignments_f paf
   WHERE  paf.person_id = p_person_id
   AND    TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
   AND    hr_auth_bridge.get_per_system_status(paf.assignment_status_type_id) = 'ACTIVE_ASSIGN'
   AND    paf.primary_flag = 'Y'
   AND    paf.assignment_type IN ('E','B')
   ORDER BY paf.assignment_type DESC;