APPS.BEN_EGD_LER SQL Statements

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

Line: 21

      SELECT   fs.effective_date
      FROM     fnd_sessions fs
      WHERE    fs.session_id = USERENV('SESSIONID');

Line: 26

      SELECT   ler.ler_id,
               ler.ocrd_dt_det_cd
      FROM     ben_ler_f ler
      WHERE    ler.business_group_id = p_new.business_group_id
      AND      l_session_date BETWEEN ler.effective_start_date
                   AND ler.effective_end_date
      AND      (
                    (
                          EXISTS
                          (SELECT   1
                           FROM     ben_per_info_chg_cs_ler_f psl,
                                    ben_ler_per_info_cs_ler_f lpl
                           WHERE    psl.source_table = 'BEN_ELIG_DPNT'
                           AND      psl.per_info_chg_cs_ler_id =
                                                     lpl.per_info_chg_cs_ler_id
                           AND      l_session_date BETWEEN psl.effective_start_date
                                        AND psl.effective_end_date
                           AND      lpl.ler_id = ler.ler_id
                           AND      l_session_date BETWEEN lpl.effective_start_date
                                        AND lpl.effective_end_date)
                      )
                 OR (
                          EXISTS
                          (SELECT   1
                           FROM     ben_ler_rltd_per_cs_ler_f lrp,
                                    ben_rltd_per_chg_cs_ler_f rpc
                           WHERE    rpc.source_table = 'BEN_ELIG_DPNT'
                           AND      l_session_date BETWEEN rpc.effective_start_date
                                        AND rpc.effective_end_date
                           AND      rpc.rltd_per_chg_cs_ler_id =
                                                     lrp.rltd_per_chg_cs_ler_id
                           AND      lrp.ler_id = ler.ler_id
                           AND      l_session_date BETWEEN lrp.effective_start_date
                                        AND lrp.effective_end_date)
                      )
                );

Line: 65

      SELECT   psl.source_column,
               psl.new_val,
               psl.old_val,
               'P',
               psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
      FROM     ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
      WHERE    lpl.ler_id = p_ler_id
      AND      lpl.business_group_id = p_new.business_group_id
      AND      lpl.business_group_id = psl.business_group_id
      AND      l_session_date BETWEEN psl.effective_start_date
                   AND psl.effective_end_date
      AND      l_session_date BETWEEN lpl.effective_start_date
                   AND lpl.effective_end_date
      AND      psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
      AND      source_table = 'BEN_ELIG_DPNT'
      UNION ALL
      SELECT   rpc.source_column,
               rpc.new_val,
               rpc.old_val,
               'R',
               rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
      FROM     ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
      WHERE    lrp.ler_id = p_ler_id
      AND      lrp.business_group_id = p_new.business_group_id
      AND      lrp.business_group_id = rpc.business_group_id
      AND      l_session_date BETWEEN rpc.effective_start_date
                   AND rpc.effective_end_date
      AND      l_session_date BETWEEN lrp.effective_start_date
                   AND lrp.effective_end_date
      AND      rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
      AND      source_table = 'BEN_ELIG_DPNT'
      order by 1;

Line: 102

      SELECT   'Y'
      FROM     ben_ptnl_ler_for_per bp
      WHERE    bp.person_id = p_person_id
      AND      bp.ler_id = p_ler_id
      AND      bp.ptnl_ler_for_per_stat_cd = 'DTCTD'
      AND      bp.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;

Line: 110

      SELECT   pcr.contact_person_id
      FROM     per_contact_relationships pcr
      WHERE    pcr.person_id = p_person_id
      AND      pcr.business_group_id = p_new.business_group_id
      AND      l_session_date BETWEEN NVL(pcr.date_start, l_session_date)
                   AND NVL(pcr.date_end, l_session_date);

Line: 119

      select person_id
      from ben_per_in_ler
      where per_in_ler_id = p_new.per_in_ler_id
      and per_in_ler_stat_cd not in ('VOIDD','BCKDT')
      and business_Group_id = p_new.business_group_id;