APPS.HRI_BPL_PERSON_TYPE SQL Statements

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

Line: 77

PROCEDURE UPDATE_PERSON_TYPE_GLOBALS(
            p_effective_date              IN    DATE
           ,p_person_id                   IN    NUMBER)
IS

  -- bug 2820666, added support for 'CWK' segments in person type.
  CURSOR csr_person_types
  IS
    SELECT  ttl.user_person_type
           ,DECODE(typ.system_person_type
                    ,'EMP'  ,1
                    ,'CWK'  ,2
                    ,'APL'  ,3
                    ,'EX_EMP',4
                    ,'EX_CWK',5
                    ,'EX_APL',6
                             ,7) order_by
           ,DECODE(typ.system_person_type
                    ,'EMP'
                    ,ttl.user_person_type
                    ,NULL ) EMP_PERSON_TYPE
           ,DECODE(typ.system_person_type
                    ,'CWK'
                    ,ttl.user_person_type
                    ,NULL ) CWK_PERSON_TYPE
           ,DECODE(typ.system_person_type
                    ,'APL'
                    ,ttl.user_person_type
                    ,NULL ) APL_PERSON_TYPE
           ,DECODE(typ.system_person_type
                    ,'EX_EMP'
                    ,ttl.user_person_type
                    ,NULL ) EXEMP_PERSON_TYPE
           ,DECODE(typ.system_person_type
                    ,'EX_CWK'
                    ,ttl.user_person_type
                    ,NULL ) EXCWK_PERSON_TYPE
           ,DECODE(typ.system_person_type
                    ,'EX_APL'
                    ,ttl.user_person_type
                    ,NULL ) EXAPL_PERSON_TYPE
           ,DECODE(typ.system_person_type
                    ,'OTHER'
                    ,ttl.user_person_type
                    ,NULL ) OTHER_PERSON_TYPE
      FROM per_person_types_tl ttl
          ,per_person_types typ
          ,per_person_type_usages_f ptu
    WHERE ttl.language = userenv('LANG')
      AND ttl.person_type_id = typ.person_type_id
      AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
      AND typ.person_type_id = ptu.person_type_id
      AND p_effective_date BETWEEN ptu.effective_start_date
                                AND ptu.effective_end_date
      AND ptu.person_id = p_person_id
  ORDER BY DECODE(typ.system_person_type
                 ,'EMP'   ,1
                 ,'CWK'   ,2
                 ,'APL'   ,3
                 ,'EX_EMP',4
                 ,'EX_CWK',5
                 ,'EX_APL',6
                          ,7
                 );

Line: 193

END UPDATE_PERSON_TYPE_GLOBALS;

Line: 214

      UPDATE_PERSON_TYPE_GLOBALS
        (p_effective_date               => p_effective_date
        ,p_person_id                    => p_person_id
        );

Line: 243

      UPDATE_PERSON_TYPE_GLOBALS
        (p_effective_date               => p_effective_date
        ,p_person_id                    => p_person_id
        );

Line: 271

      UPDATE_PERSON_TYPE_GLOBALS
        (p_effective_date               => p_effective_date
        ,p_person_id                    => p_person_id
        );

Line: 301

      UPDATE_PERSON_TYPE_GLOBALS
        (p_effective_date               => p_effective_date
        ,p_person_id                    => p_person_id
        );

Line: 317

  SELECT
   ppt.system_person_type
  FROM
   per_person_types          ppt
  ,per_person_type_usages_f  ptu
  WHERE ptu.person_id = p_person_id
  AND p_effective_date
    BETWEEN ptu.effective_start_date AND ptu.effective_end_date
  AND ptu.person_type_id = ppt.person_type_id
  AND ppt.system_person_type IN ('EMP','EX_EMP')
  ORDER BY DECODE(ppt.system_person_type,'EMP',1,2);

Line: 387

    SELECT formula_id
    FROM   ff_formulas_f
    WHERE  business_group_id = p_business_group_id
    AND    p_date BETWEEN effective_start_date
                  AND     effective_end_date
    AND    formula_name = p_ff_name;

Line: 513

  SELECT fff.formula_id
  FROM
   ff_formulas_f     fff
  ,ff_formula_types  fft
  WHERE fft.formula_type_name = 'QuickPaint'
  AND trunc(sysdate) between fff.effective_start_date AND fff.effective_end_date
  AND fff.formula_type_id = fft.formula_type_id
  AND fff.formula_name = 'HRI_MAP_WORKER_TYPE'
  AND fff.business_group_id IN (p_business_group_id,0)
  ORDER BY
   fff.business_group_id DESC;

Line: 691

  SELECT
   wkth_wktyp_sk_fk
  ,wkth_wktyp_code
  ,wkth_lvl1_sk_fk
  ,wkth_lvl1_code
  ,wkth_lvl2_sk_fk
  ,wkth_lvl2_code
  FROM
   hri_cs_prsntyp_ct
  WHERE prsntyp_sk_pk = p_prsntyp_sk_pk;

Line: 952

  SELECT
   prsntyp_sk_pk
  ,wkth_wktyp_code
  FROM hri_cs_prsntyp_ct
  WHERE person_type_id = p_person_type_id
  AND employment_category_code = p_employment_category
  AND primary_flag_code = p_primary_flag
  AND assignment_type_code = p_assignment_type;