VIEW: APPS.HXC_TK_GROUP_QUERY_CRITERIA_V

Source

View Text - Preformatted

SELECT /*+ORDERED*/ tkgqc.rowid row_id ,tkgqc.TK_GROUP_QUERY_CRITERIA_ID ,tkgqc.TK_GROUP_QUERY_ID ,tkgqc.CRITERIA_TYPE ,tkgqc.CRITERIA_ID ,tkgq.GROUP_QUERY_NAME ,tkgq.TK_GROUP_ID ,tkgq.INCLUDE_EXCLUDE ,tkgq.SYSTEM_USER ,p.full_name criteria_display1 ,nvl(p.employee_number,p.npw_number ) criteria_display2 ,hr_person_type_usage_info.get_user_person_type(sysdate,p.person_id) criteria_display_type ,hxc_tk_grp_query_criteria_api.get_tc_period ( tkgqc.criteria_id ) tc_period_name ,hxc_tk_grp_query_criteria_api.check_audit_enabled ( tkgqc.criteria_id ) audit_enabled ,tkgqc.OBJECT_VERSION_NUMBER ,p.business_group_id ,tkgqc.created_by ,tkgqc.creation_date ,tkgqc.last_updated_by ,tkgqc.last_update_date ,tkgqc.last_update_login FROM hxc_tk_group_queries tkgq, hxc_tk_group_query_criteria tkgqc, per_people p, per_person_type_usages ptu, per_person_types ppt, per_assignments_f asg, FND_SESSIONS SES WHERE tkgq.tk_group_query_id = tkgqc.tk_group_query_id and tkgqc.criteria_type = 'PERSON' and TO_NUMBER(tkgqc.criteria_id) = p.person_id and ptu.person_type_id = ppt.person_type_id and ppt.system_person_type in ('EMP', 'EMP_APL','CWK','EX_EMP') and p.person_id = ptu.person_id and asg.person_id = p.person_id and asg.primary_flag = 'Y' and asg.assignment_type in ( 'E','C') and SES.SESSION_ID=USERENV('sessionid') and asg.EFFECTIVE_START_DATE <= SES.EFFECTIVE_DATE AND Decode(asg.assignment_type, 'C', Decode( (Select MAX(nvl(Final_process_date, hr_general.end_of_time)) from per_periods_of_placement where person_id = asg.person_id),asg.EFFECTIVE_END_DATE ,asg.EFFECTIVE_END_DATE +nvl(fnd_profile.value('HXC_CWK_TK_FPD'),0) ,asg.EFFECTIVE_END_DATE ) ,asg.EFFECTIVE_END_DATE) >= SES.EFFECTIVE_DATE

View Text - HTML Formatted

SELECT /*+ORDERED*/ TKGQC.ROWID ROW_ID
, TKGQC.TK_GROUP_QUERY_CRITERIA_ID
, TKGQC.TK_GROUP_QUERY_ID
, TKGQC.CRITERIA_TYPE
, TKGQC.CRITERIA_ID
, TKGQ.GROUP_QUERY_NAME
, TKGQ.TK_GROUP_ID
, TKGQ.INCLUDE_EXCLUDE
, TKGQ.SYSTEM_USER
, P.FULL_NAME CRITERIA_DISPLAY1
, NVL(P.EMPLOYEE_NUMBER
, P.NPW_NUMBER ) CRITERIA_DISPLAY2
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SYSDATE
, P.PERSON_ID) CRITERIA_DISPLAY_TYPE
, HXC_TK_GRP_QUERY_CRITERIA_API.GET_TC_PERIOD ( TKGQC.CRITERIA_ID ) TC_PERIOD_NAME
, HXC_TK_GRP_QUERY_CRITERIA_API.CHECK_AUDIT_ENABLED ( TKGQC.CRITERIA_ID ) AUDIT_ENABLED
, TKGQC.OBJECT_VERSION_NUMBER
, P.BUSINESS_GROUP_ID
, TKGQC.CREATED_BY
, TKGQC.CREATION_DATE
, TKGQC.LAST_UPDATED_BY
, TKGQC.LAST_UPDATE_DATE
, TKGQC.LAST_UPDATE_LOGIN
FROM HXC_TK_GROUP_QUERIES TKGQ
, HXC_TK_GROUP_QUERY_CRITERIA TKGQC
, PER_PEOPLE P
, PER_PERSON_TYPE_USAGES PTU
, PER_PERSON_TYPES PPT
, PER_ASSIGNMENTS_F ASG
, FND_SESSIONS SES
WHERE TKGQ.TK_GROUP_QUERY_ID = TKGQC.TK_GROUP_QUERY_ID
AND TKGQC.CRITERIA_TYPE = 'PERSON'
AND TO_NUMBER(TKGQC.CRITERIA_ID) = P.PERSON_ID
AND PTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EMP_APL'
, 'CWK'
, 'EX_EMP')
AND P.PERSON_ID = PTU.PERSON_ID
AND ASG.PERSON_ID = P.PERSON_ID
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE IN ( 'E'
, 'C')
AND SES.SESSION_ID=USERENV('SESSIONID')
AND ASG.EFFECTIVE_START_DATE <= SES.EFFECTIVE_DATE
AND DECODE(ASG.ASSIGNMENT_TYPE
, 'C'
, DECODE( (SELECT MAX(NVL(FINAL_PROCESS_DATE
, HR_GENERAL.END_OF_TIME))
FROM PER_PERIODS_OF_PLACEMENT
WHERE PERSON_ID = ASG.PERSON_ID)
, ASG.EFFECTIVE_END_DATE
, ASG.EFFECTIVE_END_DATE +NVL(FND_PROFILE.VALUE('HXC_CWK_TK_FPD')
, 0)
, ASG.EFFECTIVE_END_DATE )
, ASG.EFFECTIVE_END_DATE) >= SES.EFFECTIVE_DATE