ANC_PS_ANA_PLANNED_ABS_V

Details

• Schema: FUSION

• Object owner: ANC

• Object type: VIEW

Columns

Name
PERSON_ID ASSIGNMENT_ID TEXT_TITLE TEXT_METRIC TEXT_META BADGE_TEXT BADGE_STATUS PERIOD_CHN_IND_VAL PERIOD_CHN_IND_CMP CHART_TYPE CHART_COLOR CHART_DATA LINK_TEXT

Query

SQL_Statement

SELECT abs.person_id person_id, NULL assignment_id, '{"strKey":"HdrSNextplannedleave"}' text_title, '{"strKey":"PgHIABSTYPEofDUR", "tokens":{"ABS_TYPE":"' || typ.name || '","DUR":"' || abs.duration || ' ' || lower(lok.meaning) || '"}}' text_metric, '{"strKey":"PgHIStartingonDATE", "tokens":{"DATE":"' || hrl_df_util.date_to_char(abs.start_date) || '"}}' text_meta, decode(sign((trunc(abs.start_date) - sysdate) - 7), - 1, 'Info', NULL) badge_text, decode(sign((trunc(abs.start_date) - sysdate) - 7), - 1, 'info', NULL) badge_status, NULL period_chn_ind_val, NULL period_chn_ind_cmp, NULL chart_type, NULL chart_color, NULL chart_data, '?pPersonId=' || abs.person_id || '&pDisplayName=' || pnf.display_name link_text FROM anc_per_abs_entries abs, anc_absence_types_f_tl typ, hcm_lookups lok, per_person_names_f_v pnf WHERE abs.start_date > trunc(sysdate) AND abs.absence_status_cd = 'SUBMITTED' AND abs.approval_status_cd = 'APPROVED' AND typ.absence_type_id = abs.absence_type_id AND trunc(sysdate) BETWEEN typ.effective_start_date AND typ.effective_end_date AND lok.lookup_code = abs.uom AND lok.lookup_type = 'ANC_DURATION_UOM' AND typ.language = sys_context('USERENV', 'LANG') AND abs.person_id = pnf.person_id AND trunc(sysdate) BETWEEN pnf.effective_start_date AND pnf.effective_end_date AND abs.start_date = ( SELECT MIN(abs.start_date) FROM anc_per_abs_entries WHERE absence_status_cd = 'SUBMITTED' AND approval_status_cd = 'APPROVED' AND start_date > trunc(sysdate) )