VIEW: APPS.BEN_CWB_PP_ERR_DWLD_V

Source

View Text - Preformatted

SELECT rpt.benefit_action_id, rpt.full_name employee_name, rpt.employee_number employee_number, rpt.business_group_name business_group_name, rpt.manager_name manger_name, info.legislation_code, info.email_address, SUBSTR (rpt.error_or_warning_text, INSTR (rpt.error_or_warning_text, ' ') + 1 ) ERROR_TEXT ,(select pgm.user_concurrent_program_name from ben_batch_proc_info proc, fnd_concurrent_requests req , fnd_concurrent_programs_vl pgm where actn.benefit_action_id = proc.benefit_action_id and actn.request_id = req.request_id and pgm.concurrent_program_id = req.concurrent_program_id) program_name_h ,(actn.process_date) process_date_h ,(select strt_tm from ben_batch_proc_info proc where actn.benefit_action_id = proc.benefit_action_id ) start_time_h ,(select end_tm from ben_batch_proc_info proc where actn.benefit_action_id = proc.benefit_action_id ) end_time_h , dsgn.name plan_name_h , actn.lf_evt_ocrd_dt life_event_occurred_date_h ,(select meaning from hr_lookups where actn.validate_flag = lookup_code(+) and lookup_type(+) = 'BEN_DB_UPD_MD') validate_flag_h ,(select meaning from hr_lookups where actn.audit_log_flag = lookup_code(+) and lookup_type(+) = 'YES_NO') audit_log_flag_h ,(select meaning from hr_lookups where actn.debug_messages_flag = lookup_code(+) and lookup_type(+) = 'BEN_CWB_DEBUG_LEVEL') debug_messages_flag_h ,(select full_name from per_all_people_f p where person_id(+) = actn.person_id and actn.process_date between effective_start_date(+) and effective_end_date(+)) person_name_h ,(select full_name from per_all_people_f where to_char(person_id(+)) = actn.bft_attribute4 and actn.process_date between effective_start_date(+) and effective_end_date(+)) manager_name_h ,(select name from hr_all_organization_units_tl bg where to_char(bg.organization_id(+)) = actn.bft_attribute3 and bg.language(+) = userenv('LANG')) business_group_name_h FROM ben_cwb_rpt_detail rpt, ben_cwb_person_info info, ben_benefit_actions actn, ben_cwb_pl_dsgn dsgn WHERE info.group_per_in_ler_id = rpt.group_per_in_ler_id AND rpt.status_cd = 'E' AND NVL (rpt.person_rate_id, 0) <> -9999 AND rpt.group_oipl_id = -1 AND actn.benefit_action_id = rpt.benefit_action_id and dsgn.pl_id = actn.pl_id and dsgn.group_pl_id = dsgn.pl_id and dsgn.group_oipl_id = -1 and dsgn.oipl_id = dsgn.group_oipl_id AND EXISTS ( SELECT NULL FROM per_people_f secured_people WHERE secured_people.person_id = rpt.person_id AND SYSDATE BETWEEN NVL (secured_people.effective_start_date, SYSDATE ) AND NVL (secured_people.effective_end_date, SYSDATE )) UNION SELECT rpt.benefit_action_id, '(Withheld for security)' employee_name, '(Withheld for security)' employee_number, rpt.business_group_name business_group_name, rpt.manager_name manger_name, info.legislation_code, info.email_address, SUBSTR (rpt.error_or_warning_text, INSTR (rpt.error_or_warning_text, ' ') + 1 ) ERROR_TEXT ,(select pgm.user_concurrent_program_name from ben_batch_proc_info proc, fnd_concurrent_requests req , fnd_concurrent_programs_vl pgm where actn.benefit_action_id = proc.benefit_action_id and actn.request_id = req.request_id and pgm.concurrent_program_id = req.concurrent_program_id) program_name_h ,(actn.process_date) process_date_h ,(select strt_tm from ben_batch_proc_info proc where actn.benefit_action_id = proc.benefit_action_id ) start_time_h ,(select end_tm from ben_batch_proc_info proc where actn.benefit_action_id = proc.benefit_action_id ) end_time_h , dsgn.name plan_name_h , actn.lf_evt_ocrd_dt life_event_occurred_date_h ,(select meaning from hr_lookups where actn.validate_flag = lookup_code(+) and lookup_type(+) = 'BEN_DB_UPD_MD') validate_flag_h ,(select meaning from hr_lookups where actn.audit_log_flag = lookup_code(+) and lookup_type(+) = 'YES_NO') audit_log_flag_h ,(select meaning from hr_lookups where actn.debug_messages_flag = lookup_code(+) and lookup_type(+) = 'BEN_CWB_DEBUG_LEVEL') debug_messages_flag_h ,(select full_name from per_all_people_f where person_id(+) = actn.person_id and actn.process_date between effective_start_date(+) and effective_end_date(+)) person_name_h ,(select full_name from per_all_people_f where to_char(person_id(+)) = actn.bft_attribute4 and actn.process_date between effective_start_date(+) and effective_end_date(+)) manager_name_h ,(select name from hr_all_organization_units_tl where to_char(organization_id(+)) = actn.bft_attribute3 and language(+) = userenv('LANG')) business_group_name_h FROM ben_cwb_rpt_detail rpt, ben_cwb_person_info info, ben_benefit_actions actn, ben_cwb_pl_dsgn dsgn WHERE rpt.status_cd = 'E' AND info.group_per_in_ler_id = rpt.group_per_in_ler_id AND NVL (rpt.person_rate_id, 0) <> -9999 AND rpt.group_oipl_id = -1 AND actn.benefit_action_id = rpt.benefit_action_id and dsgn.pl_id = actn.pl_id and dsgn.group_pl_id = dsgn.pl_id and dsgn.group_oipl_id = -1 and dsgn.oipl_id = dsgn.group_oipl_id AND NOT EXISTS ( SELECT NULL FROM per_people_f secured_people WHERE secured_people.person_id = rpt.person_id AND SYSDATE BETWEEN NVL (secured_people.effective_start_date, SYSDATE ) AND NVL (secured_people.effective_end_date, SYSDATE ))

View Text - HTML Formatted

SELECT RPT.BENEFIT_ACTION_ID
, RPT.FULL_NAME EMPLOYEE_NAME
, RPT.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, RPT.BUSINESS_GROUP_NAME BUSINESS_GROUP_NAME
, RPT.MANAGER_NAME MANGER_NAME
, INFO.LEGISLATION_CODE
, INFO.EMAIL_ADDRESS
, SUBSTR (RPT.ERROR_OR_WARNING_TEXT
, INSTR (RPT.ERROR_OR_WARNING_TEXT
, ' ') + 1 ) ERROR_TEXT
, (SELECT PGM.USER_CONCURRENT_PROGRAM_NAME
FROM BEN_BATCH_PROC_INFO PROC
, FND_CONCURRENT_REQUESTS REQ
, FND_CONCURRENT_PROGRAMS_VL PGM
WHERE ACTN.BENEFIT_ACTION_ID = PROC.BENEFIT_ACTION_ID
AND ACTN.REQUEST_ID = REQ.REQUEST_ID
AND PGM.CONCURRENT_PROGRAM_ID = REQ.CONCURRENT_PROGRAM_ID) PROGRAM_NAME_H
, (ACTN.PROCESS_DATE) PROCESS_DATE_H
, (SELECT STRT_TM
FROM BEN_BATCH_PROC_INFO PROC
WHERE ACTN.BENEFIT_ACTION_ID = PROC.BENEFIT_ACTION_ID ) START_TIME_H
, (SELECT END_TM
FROM BEN_BATCH_PROC_INFO PROC
WHERE ACTN.BENEFIT_ACTION_ID = PROC.BENEFIT_ACTION_ID ) END_TIME_H
, DSGN.NAME PLAN_NAME_H
, ACTN.LF_EVT_OCRD_DT LIFE_EVENT_OCCURRED_DATE_H
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE ACTN.VALIDATE_FLAG = LOOKUP_CODE(+)
AND LOOKUP_TYPE(+) = 'BEN_DB_UPD_MD') VALIDATE_FLAG_H
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE ACTN.AUDIT_LOG_FLAG = LOOKUP_CODE(+)
AND LOOKUP_TYPE(+) = 'YES_NO') AUDIT_LOG_FLAG_H
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE ACTN.DEBUG_MESSAGES_FLAG = LOOKUP_CODE(+)
AND LOOKUP_TYPE(+) = 'BEN_CWB_DEBUG_LEVEL') DEBUG_MESSAGES_FLAG_H
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F P
WHERE PERSON_ID(+) = ACTN.PERSON_ID
AND ACTN.PROCESS_DATE BETWEEN EFFECTIVE_START_DATE(+)
AND EFFECTIVE_END_DATE(+)) PERSON_NAME_H
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE TO_CHAR(PERSON_ID(+)) = ACTN.BFT_ATTRIBUTE4
AND ACTN.PROCESS_DATE BETWEEN EFFECTIVE_START_DATE(+)
AND EFFECTIVE_END_DATE(+)) MANAGER_NAME_H
, (SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL BG
WHERE TO_CHAR(BG.ORGANIZATION_ID(+)) = ACTN.BFT_ATTRIBUTE3
AND BG.LANGUAGE(+) = USERENV('LANG')) BUSINESS_GROUP_NAME_H
FROM BEN_CWB_RPT_DETAIL RPT
, BEN_CWB_PERSON_INFO INFO
, BEN_BENEFIT_ACTIONS ACTN
, BEN_CWB_PL_DSGN DSGN
WHERE INFO.GROUP_PER_IN_LER_ID = RPT.GROUP_PER_IN_LER_ID
AND RPT.STATUS_CD = 'E'
AND NVL (RPT.PERSON_RATE_ID
, 0) <> -9999
AND RPT.GROUP_OIPL_ID = -1
AND ACTN.BENEFIT_ACTION_ID = RPT.BENEFIT_ACTION_ID
AND DSGN.PL_ID = ACTN.PL_ID
AND DSGN.GROUP_PL_ID = DSGN.PL_ID
AND DSGN.GROUP_OIPL_ID = -1
AND DSGN.OIPL_ID = DSGN.GROUP_OIPL_ID
AND EXISTS ( SELECT NULL
FROM PER_PEOPLE_F SECURED_PEOPLE
WHERE SECURED_PEOPLE.PERSON_ID = RPT.PERSON_ID
AND SYSDATE BETWEEN NVL (SECURED_PEOPLE.EFFECTIVE_START_DATE
, SYSDATE )
AND NVL (SECURED_PEOPLE.EFFECTIVE_END_DATE
, SYSDATE )) UNION SELECT RPT.BENEFIT_ACTION_ID
, '(WITHHELD FOR SECURITY)' EMPLOYEE_NAME
, '(WITHHELD FOR SECURITY)' EMPLOYEE_NUMBER
, RPT.BUSINESS_GROUP_NAME BUSINESS_GROUP_NAME
, RPT.MANAGER_NAME MANGER_NAME
, INFO.LEGISLATION_CODE
, INFO.EMAIL_ADDRESS
, SUBSTR (RPT.ERROR_OR_WARNING_TEXT
, INSTR (RPT.ERROR_OR_WARNING_TEXT
, ' ') + 1 ) ERROR_TEXT
, (SELECT PGM.USER_CONCURRENT_PROGRAM_NAME
FROM BEN_BATCH_PROC_INFO PROC
, FND_CONCURRENT_REQUESTS REQ
, FND_CONCURRENT_PROGRAMS_VL PGM
WHERE ACTN.BENEFIT_ACTION_ID = PROC.BENEFIT_ACTION_ID
AND ACTN.REQUEST_ID = REQ.REQUEST_ID
AND PGM.CONCURRENT_PROGRAM_ID = REQ.CONCURRENT_PROGRAM_ID) PROGRAM_NAME_H
, (ACTN.PROCESS_DATE) PROCESS_DATE_H
, (SELECT STRT_TM
FROM BEN_BATCH_PROC_INFO PROC
WHERE ACTN.BENEFIT_ACTION_ID = PROC.BENEFIT_ACTION_ID ) START_TIME_H
, (SELECT END_TM
FROM BEN_BATCH_PROC_INFO PROC
WHERE ACTN.BENEFIT_ACTION_ID = PROC.BENEFIT_ACTION_ID ) END_TIME_H
, DSGN.NAME PLAN_NAME_H
, ACTN.LF_EVT_OCRD_DT LIFE_EVENT_OCCURRED_DATE_H
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE ACTN.VALIDATE_FLAG = LOOKUP_CODE(+)
AND LOOKUP_TYPE(+) = 'BEN_DB_UPD_MD') VALIDATE_FLAG_H
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE ACTN.AUDIT_LOG_FLAG = LOOKUP_CODE(+)
AND LOOKUP_TYPE(+) = 'YES_NO') AUDIT_LOG_FLAG_H
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE ACTN.DEBUG_MESSAGES_FLAG = LOOKUP_CODE(+)
AND LOOKUP_TYPE(+) = 'BEN_CWB_DEBUG_LEVEL') DEBUG_MESSAGES_FLAG_H
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID(+) = ACTN.PERSON_ID
AND ACTN.PROCESS_DATE BETWEEN EFFECTIVE_START_DATE(+)
AND EFFECTIVE_END_DATE(+)) PERSON_NAME_H
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE TO_CHAR(PERSON_ID(+)) = ACTN.BFT_ATTRIBUTE4
AND ACTN.PROCESS_DATE BETWEEN EFFECTIVE_START_DATE(+)
AND EFFECTIVE_END_DATE(+)) MANAGER_NAME_H
, (SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE TO_CHAR(ORGANIZATION_ID(+)) = ACTN.BFT_ATTRIBUTE3
AND LANGUAGE(+) = USERENV('LANG')) BUSINESS_GROUP_NAME_H
FROM BEN_CWB_RPT_DETAIL RPT
, BEN_CWB_PERSON_INFO INFO
, BEN_BENEFIT_ACTIONS ACTN
, BEN_CWB_PL_DSGN DSGN
WHERE RPT.STATUS_CD = 'E'
AND INFO.GROUP_PER_IN_LER_ID = RPT.GROUP_PER_IN_LER_ID
AND NVL (RPT.PERSON_RATE_ID
, 0) <> -9999
AND RPT.GROUP_OIPL_ID = -1
AND ACTN.BENEFIT_ACTION_ID = RPT.BENEFIT_ACTION_ID
AND DSGN.PL_ID = ACTN.PL_ID
AND DSGN.GROUP_PL_ID = DSGN.PL_ID
AND DSGN.GROUP_OIPL_ID = -1
AND DSGN.OIPL_ID = DSGN.GROUP_OIPL_ID
AND NOT EXISTS ( SELECT NULL
FROM PER_PEOPLE_F SECURED_PEOPLE
WHERE SECURED_PEOPLE.PERSON_ID = RPT.PERSON_ID
AND SYSDATE BETWEEN NVL (SECURED_PEOPLE.EFFECTIVE_START_DATE
, SYSDATE )
AND NVL (SECURED_PEOPLE.EFFECTIVE_END_DATE
, SYSDATE ))