VIEW: APPS.HRI_CS_PRSNTYP_V

Source

View Text - Preformatted

SELECT ppt.system_person_type wkth_wktyp_sk_fk ,hri_bpl_person_type.get_wkth_lvl1_sk_fk (ppt.person_type_id, ppt.system_person_type, ppt.user_person_type, ppt.business_group_id, prm_flg.lookup_code, emp_cat.lookup_code, asg_typ.lookup_code) wkth_lvl1_sk_fk ,hri_bpl_person_type.get_wkth_lvl2_sk_fk (ppt.person_type_id, ppt.system_person_type, ppt.user_person_type, ppt.business_group_id, prm_flg.lookup_code, emp_cat.lookup_code, asg_typ.lookup_code) wkth_lvl2_sk_fk ,ppt.system_person_type wkth_wktyp_code ,hri_bpl_person_type.get_wkth_lvl1_code (ppt.person_type_id, ppt.system_person_type, ppt.user_person_type, ppt.business_group_id, prm_flg.lookup_code, emp_cat.lookup_code, asg_typ.lookup_code) wkth_lvl1_code ,hri_bpl_person_type.get_wkth_lvl2_code (ppt.person_type_id, ppt.system_person_type, ppt.user_person_type, ppt.business_group_id, prm_flg.lookup_code, emp_cat.lookup_code, asg_typ.lookup_code) wkth_lvl2_code ,ppt.person_type_id person_type_id ,prm_flg.lookup_code primary_flag_code ,asg_typ.lookup_code assignment_type_code ,emp_cat.lookup_code employment_category_code ,hri_bpl_person_type.get_include_flag (ppt.person_type_id, ppt.system_person_type, ppt.user_person_type, ppt.business_group_id, prm_flg.lookup_code, emp_cat.lookup_code, asg_typ.lookup_code) include_flag_code FROM per_person_types ppt ,hr_organization_information hoi ,hr_lookups prm_flg ,hr_lookups asg_typ ,(SELECT flv.lookup_code ,CASE WHEN SUM(DECODE(flv.tag, NULL, 1, 0)) > 0 THEN NULL WHEN COUNT(DISTINCT tag) = 1 THEN MAX(flv.tag) ELSE NULL END tag ,DECODE(flv.lookup_type, 'EMP_CAT' ,'E', 'C') assignment_type FROM fnd_lookup_values_vl flv WHERE flv.lookup_type IN ('EMP_CAT', 'CWK_ASG_CATEGORY') AND flv.enabled_flag = 'Y' GROUP BY flv.lookup_code ,flv.lookup_type UNION ALL SELECT 'NA_EDW' ,null ,'E' FROM dual UNION ALL SELECT 'NA_EDW' ,null ,'C' FROM dual) emp_cat WHERE ppt.system_person_type IN ('EMP','CWK') AND ppt.business_group_id = hoi.organization_id AND hoi.org_information_context = 'Business Group Information' AND prm_flg.lookup_type = 'YES_NO' AND emp_cat.assignment_type = asg_typ.lookup_code AND DECODE(emp_cat.tag, NULL, 'Y', DECODE(SUBSTR(emp_cat.tag, 1, 1), '+', DECODE(SIGN(INSTR(emp_cat.tag, hoi.org_information9)), 1, 'Y', 'N'), '-', DECODE(SIGN(INSTR(emp_cat.tag, hoi.org_information9)), 1, 'N', 'Y'), 'Y')) = 'Y' AND asg_typ.lookup_type = 'EMP_APL' AND asg_typ.lookup_code = SUBSTR(ppt.system_person_type, 1, 1)

View Text - HTML Formatted

SELECT PPT.SYSTEM_PERSON_TYPE WKTH_WKTYP_SK_FK
, HRI_BPL_PERSON_TYPE.GET_WKTH_LVL1_SK_FK (PPT.PERSON_TYPE_ID
, PPT.SYSTEM_PERSON_TYPE
, PPT.USER_PERSON_TYPE
, PPT.BUSINESS_GROUP_ID
, PRM_FLG.LOOKUP_CODE
, EMP_CAT.LOOKUP_CODE
, ASG_TYP.LOOKUP_CODE) WKTH_LVL1_SK_FK
, HRI_BPL_PERSON_TYPE.GET_WKTH_LVL2_SK_FK (PPT.PERSON_TYPE_ID
, PPT.SYSTEM_PERSON_TYPE
, PPT.USER_PERSON_TYPE
, PPT.BUSINESS_GROUP_ID
, PRM_FLG.LOOKUP_CODE
, EMP_CAT.LOOKUP_CODE
, ASG_TYP.LOOKUP_CODE) WKTH_LVL2_SK_FK
, PPT.SYSTEM_PERSON_TYPE WKTH_WKTYP_CODE
, HRI_BPL_PERSON_TYPE.GET_WKTH_LVL1_CODE (PPT.PERSON_TYPE_ID
, PPT.SYSTEM_PERSON_TYPE
, PPT.USER_PERSON_TYPE
, PPT.BUSINESS_GROUP_ID
, PRM_FLG.LOOKUP_CODE
, EMP_CAT.LOOKUP_CODE
, ASG_TYP.LOOKUP_CODE) WKTH_LVL1_CODE
, HRI_BPL_PERSON_TYPE.GET_WKTH_LVL2_CODE (PPT.PERSON_TYPE_ID
, PPT.SYSTEM_PERSON_TYPE
, PPT.USER_PERSON_TYPE
, PPT.BUSINESS_GROUP_ID
, PRM_FLG.LOOKUP_CODE
, EMP_CAT.LOOKUP_CODE
, ASG_TYP.LOOKUP_CODE) WKTH_LVL2_CODE
, PPT.PERSON_TYPE_ID PERSON_TYPE_ID
, PRM_FLG.LOOKUP_CODE PRIMARY_FLAG_CODE
, ASG_TYP.LOOKUP_CODE ASSIGNMENT_TYPE_CODE
, EMP_CAT.LOOKUP_CODE EMPLOYMENT_CATEGORY_CODE
, HRI_BPL_PERSON_TYPE.GET_INCLUDE_FLAG (PPT.PERSON_TYPE_ID
, PPT.SYSTEM_PERSON_TYPE
, PPT.USER_PERSON_TYPE
, PPT.BUSINESS_GROUP_ID
, PRM_FLG.LOOKUP_CODE
, EMP_CAT.LOOKUP_CODE
, ASG_TYP.LOOKUP_CODE) INCLUDE_FLAG_CODE
FROM PER_PERSON_TYPES PPT
, HR_ORGANIZATION_INFORMATION HOI
, HR_LOOKUPS PRM_FLG
, HR_LOOKUPS ASG_TYP
, (SELECT FLV.LOOKUP_CODE
, CASE WHEN SUM(DECODE(FLV.TAG
, NULL
, 1
, 0)) > 0 THEN NULL WHEN COUNT(DISTINCT TAG) = 1 THEN MAX(FLV.TAG) ELSE NULL END TAG
, DECODE(FLV.LOOKUP_TYPE
, 'EMP_CAT'
, 'E'
, 'C') ASSIGNMENT_TYPE
FROM FND_LOOKUP_VALUES_VL FLV
WHERE FLV.LOOKUP_TYPE IN ('EMP_CAT'
, 'CWK_ASG_CATEGORY')
AND FLV.ENABLED_FLAG = 'Y' GROUP BY FLV.LOOKUP_CODE
, FLV.LOOKUP_TYPE UNION ALL SELECT 'NA_EDW'
, NULL
, 'E'
FROM DUAL UNION ALL SELECT 'NA_EDW'
, NULL
, 'C'
FROM DUAL) EMP_CAT
WHERE PPT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'CWK')
AND PPT.BUSINESS_GROUP_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'BUSINESS GROUP INFORMATION'
AND PRM_FLG.LOOKUP_TYPE = 'YES_NO'
AND EMP_CAT.ASSIGNMENT_TYPE = ASG_TYP.LOOKUP_CODE
AND DECODE(EMP_CAT.TAG
, NULL
, 'Y'
, DECODE(SUBSTR(EMP_CAT.TAG
, 1
, 1)
, '+'
, DECODE(SIGN(INSTR(EMP_CAT.TAG
, HOI.ORG_INFORMATION9))
, 1
, 'Y'
, 'N')
, '-'
, DECODE(SIGN(INSTR(EMP_CAT.TAG
, HOI.ORG_INFORMATION9))
, 1
, 'N'
, 'Y')
, 'Y')) = 'Y'
AND ASG_TYP.LOOKUP_TYPE = 'EMP_APL'
AND ASG_TYP.LOOKUP_CODE = SUBSTR(PPT.SYSTEM_PERSON_TYPE
, 1
, 1)