eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.BEN_CWB_PP_SUM_DWLD_V

Source

View Text - Preformatted


SELECT prsum."BUSINESS_GROUP_ID",prsum."BUSINESS_GROUP_NAME",prsum."TOTAL_EMPLOYEES_PROCD",prsum."ERRED_EMPLOYEES",prsum."SUCCESSFUL_EMPLOYEES",prsum."WARNED_EMPLOYEES",prsum."ELIG_EMPLOYEES",prsum."INELIG_EMPLOYEES",prsum."STARTED",prsum."PROCESSED", errsum.message_number, errsum.error_or_warning_text, errsum.employee_count message_emp_count, perfsum.employees_rated, asgsum.total "TOTAL_ASG_CHANGES", asgsum.proposed_job, asgsum.proposed_position, asgsum.proposed_grade, asgsum.proposed_group, asgsum.proposed_flex1, asgsum.proposed_flex2, asgsum.proposed_flex3, asgsum.proposed_flex4, asgsum.proposed_flex5, asgsum.proposed_flex6, asgsum.proposed_flex7, asgsum.proposed_flex8, asgsum.proposed_flex9, asgsum.proposed_flex10, asgsum.proposed_flex11, asgsum.proposed_flex12, asgsum.proposed_flex13, asgsum.proposed_flex14, asgsum.proposed_flex15, asgsum.proposed_flex16, asgsum.proposed_flex17, asgsum.proposed_flex18, asgsum.proposed_flex19, asgsum.proposed_flex20, asgsum.proposed_flex21, asgsum.proposed_flex22, asgsum.proposed_flex23, asgsum.proposed_flex24, asgsum.proposed_flex25, asgsum.proposed_flex26, asgsum.proposed_flex27, asgsum.proposed_flex28, asgsum.proposed_flex29, asgsum.proposed_flex30, compsum.eligible_count, compsum.paid_count ,(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 (SELECT MAX (business_group_id) business_group_id, MAX (business_group_name) business_group_name, MAX (cnt_all) total_employees_procd, NVL (MAX (DECODE (status_cd, 'E', cnt_status_cd, NULL)), 0 ) erred_employees, NVL (MAX (DECODE (status_cd, 'SC', cnt_status_cd, NULL)), 0 ) successful_employees, 0 warned_employees, NVL (MAX (DECODE (eligibility, 'Y', cnt_eligibility, NULL)), 0 ) elig_employees, NVL (MAX (DECODE (eligibility, 'N', cnt_eligibility, NULL)), 0 ) inelig_employees, NVL (MAX (DECODE (per_in_ler_stat_cd, 'STRTD', cnt_per_in_ler_stat_cd, NULL ) ), 0 ) started, NVL (MAX (DECODE (per_in_ler_stat_cd, 'PROCD', cnt_per_in_ler_stat_cd, NULL ) ), 0 ) processed FROM (SELECT rpt.oipl_id, rpt.business_group_id, rpt.business_group_name, rpt.status_cd, COUNT (*) OVER (PARTITION BY rpt.business_group_id, rpt.status_cd) cnt_status_cd, rpt.eligibility, COUNT (*) OVER (PARTITION BY rpt.business_group_id, rpt.eligibility) cnt_eligibility, pil.per_in_ler_stat_cd, COUNT (*) OVER (PARTITION BY rpt.business_group_id, pil.per_in_ler_stat_cd) cnt_per_in_ler_stat_cd, COUNT (*) OVER (PARTITION BY rpt.business_group_id) cnt_all FROM ben_cwb_rpt_detail rpt, ben_per_in_ler pil WHERE rpt.benefit_action_id = FND_PROFILE.value('BEN_CWB_PP_BEN_ACTION_ID') AND rpt.oipl_id = -1 AND pil.per_in_ler_id = rpt.group_per_in_ler_id) GROUP BY business_group_id) prsum, (SELECT MAX (dtl.business_group_id) business_group_id, (business_group_name) bg_name, COUNT (dtl.person_id) employees_rated FROM ben_cwb_rpt_detail dtl WHERE dtl.benefit_action_id = FND_PROFILE.value('BEN_CWB_PP_BEN_ACTION_ID') AND dtl.performance_rating IS NOT NULL GROUP BY dtl.business_group_name) perfsum, (SELECT dtl.business_group_id, MAX (business_group_name) business_group_name, COUNT (dtl.person_id) total, COUNT (proposed_job) proposed_job, COUNT (proposed_position) proposed_position, COUNT (proposed_grade) proposed_grade, COUNT (proposed_group) proposed_group, COUNT (proposed_flex1) proposed_flex1, COUNT (proposed_flex2) proposed_flex2, COUNT (proposed_flex3) proposed_flex3, COUNT (proposed_flex4) proposed_flex4, COUNT (proposed_flex5) proposed_flex5, COUNT (proposed_flex6) proposed_flex6, COUNT (proposed_flex7) proposed_flex7, COUNT (proposed_flex8) proposed_flex8, COUNT (proposed_flex9) proposed_flex9, COUNT (proposed_flex10) proposed_flex10, COUNT (proposed_flex11) proposed_flex11, COUNT (proposed_flex12) proposed_flex12, COUNT (proposed_flex13) proposed_flex13, COUNT (proposed_flex14) proposed_flex14, COUNT (proposed_flex15) proposed_flex15, COUNT (proposed_flex16) proposed_flex16, COUNT (proposed_flex17) proposed_flex17, COUNT (proposed_flex18) proposed_flex18, COUNT (proposed_flex19) proposed_flex19, COUNT (proposed_flex20) proposed_flex20, COUNT (proposed_flex21) proposed_flex21, COUNT (proposed_flex22) proposed_flex22, COUNT (proposed_flex23) proposed_flex23, COUNT (proposed_flex24) proposed_flex24, COUNT (proposed_flex25) proposed_flex25, COUNT (proposed_flex26) proposed_flex26, COUNT (proposed_flex27) proposed_flex27, COUNT (proposed_flex28) proposed_flex28, COUNT (proposed_flex29) proposed_flex29, COUNT (proposed_flex30) proposed_flex30 FROM ben_cwb_rpt_detail dtl WHERE benefit_action_id = FND_PROFILE.value('BEN_CWB_PP_BEN_ACTION_ID') AND assignment_changed_flag = 'Y' GROUP BY dtl.business_group_id) asgsum, (SELECT business_group_id, MAX (eligible_count) eligible_count, MAX (paid_count) paid_count FROM (SELECT dtl.business_group_id, dtl.group_oipl_id, COUNT (*) OVER (PARTITION BY dtl.business_group_id, group_oipl_id) eligible_count, COUNT (DECODE (amount, 0, NULL, amount)) OVER (PARTITION BY dtl.business_group_id, group_oipl_id) paid_count FROM ben_cwb_rpt_detail dtl WHERE benefit_action_id = FND_PROFILE.value('BEN_CWB_PP_BEN_ACTION_ID') AND eligibility = 'Y') GROUP BY business_group_id) compsum, (SELECT business_group_id, fnd_number.canonical_to_number (SUBSTR (error_or_warning_text, 1, INSTR (error_or_warning_text, ' ') ) ) message_number, MAX (SUBSTR (error_or_warning_text, INSTR (error_or_warning_text, ' ') + 1 ) ) error_or_warning_text, DECODE (MAX (status_cd), 'E', 'Error', 'Warning') status, NVL (COUNT (dtl.person_id), 0) employee_count FROM ben_cwb_rpt_detail dtl WHERE status_cd IN ('E', 'W') AND benefit_action_id = FND_PROFILE.value('BEN_CWB_PP_BEN_ACTION_ID') AND oipl_id = -1 GROUP BY business_group_id, fnd_number.canonical_to_number (SUBSTR (error_or_warning_text, 1, INSTR (error_or_warning_text, ' ' ) ) )) errsum , ben_benefit_actions actn, ben_cwb_pl_dsgn dsgn WHERE prsum.business_group_id = perfsum.business_group_id(+) AND prsum.business_group_id = asgsum.business_group_id(+) AND prsum.business_group_id = compsum.business_group_id(+) AND prsum.business_group_id = errsum.business_group_id(+) and actn.benefit_action_id = FND_PROFILE.value('BEN_CWB_PP_BEN_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

View Text - HTML Formatted

SELECT PRSUM."BUSINESS_GROUP_ID"
, PRSUM."BUSINESS_GROUP_NAME"
, PRSUM."TOTAL_EMPLOYEES_PROCD"
, PRSUM."ERRED_EMPLOYEES"
, PRSUM."SUCCESSFUL_EMPLOYEES"
, PRSUM."WARNED_EMPLOYEES"
, PRSUM."ELIG_EMPLOYEES"
, PRSUM."INELIG_EMPLOYEES"
, PRSUM."STARTED"
, PRSUM."PROCESSED"
, ERRSUM.MESSAGE_NUMBER
, ERRSUM.ERROR_OR_WARNING_TEXT
, ERRSUM.EMPLOYEE_COUNT MESSAGE_EMP_COUNT
, PERFSUM.EMPLOYEES_RATED
, ASGSUM.TOTAL "TOTAL_ASG_CHANGES"
, ASGSUM.PROPOSED_JOB
, ASGSUM.PROPOSED_POSITION
, ASGSUM.PROPOSED_GRADE
, ASGSUM.PROPOSED_GROUP
, ASGSUM.PROPOSED_FLEX1
, ASGSUM.PROPOSED_FLEX2
, ASGSUM.PROPOSED_FLEX3
, ASGSUM.PROPOSED_FLEX4
, ASGSUM.PROPOSED_FLEX5
, ASGSUM.PROPOSED_FLEX6
, ASGSUM.PROPOSED_FLEX7
, ASGSUM.PROPOSED_FLEX8
, ASGSUM.PROPOSED_FLEX9
, ASGSUM.PROPOSED_FLEX10
, ASGSUM.PROPOSED_FLEX11
, ASGSUM.PROPOSED_FLEX12
, ASGSUM.PROPOSED_FLEX13
, ASGSUM.PROPOSED_FLEX14
, ASGSUM.PROPOSED_FLEX15
, ASGSUM.PROPOSED_FLEX16
, ASGSUM.PROPOSED_FLEX17
, ASGSUM.PROPOSED_FLEX18
, ASGSUM.PROPOSED_FLEX19
, ASGSUM.PROPOSED_FLEX20
, ASGSUM.PROPOSED_FLEX21
, ASGSUM.PROPOSED_FLEX22
, ASGSUM.PROPOSED_FLEX23
, ASGSUM.PROPOSED_FLEX24
, ASGSUM.PROPOSED_FLEX25
, ASGSUM.PROPOSED_FLEX26
, ASGSUM.PROPOSED_FLEX27
, ASGSUM.PROPOSED_FLEX28
, ASGSUM.PROPOSED_FLEX29
, ASGSUM.PROPOSED_FLEX30
, COMPSUM.ELIGIBLE_COUNT
, COMPSUM.PAID_COUNT
, (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 (SELECT MAX (BUSINESS_GROUP_ID) BUSINESS_GROUP_ID
, MAX (BUSINESS_GROUP_NAME) BUSINESS_GROUP_NAME
, MAX (CNT_ALL) TOTAL_EMPLOYEES_PROCD
, NVL (MAX (DECODE (STATUS_CD
, 'E'
, CNT_STATUS_CD
, NULL))
, 0 ) ERRED_EMPLOYEES
, NVL (MAX (DECODE (STATUS_CD
, 'SC'
, CNT_STATUS_CD
, NULL))
, 0 ) SUCCESSFUL_EMPLOYEES
, 0 WARNED_EMPLOYEES
, NVL (MAX (DECODE (ELIGIBILITY
, 'Y'
, CNT_ELIGIBILITY
, NULL))
, 0 ) ELIG_EMPLOYEES
, NVL (MAX (DECODE (ELIGIBILITY
, 'N'
, CNT_ELIGIBILITY
, NULL))
, 0 ) INELIG_EMPLOYEES
, NVL (MAX (DECODE (PER_IN_LER_STAT_CD
, 'STRTD'
, CNT_PER_IN_LER_STAT_CD
, NULL ) )
, 0 ) STARTED
, NVL (MAX (DECODE (PER_IN_LER_STAT_CD
, 'PROCD'
, CNT_PER_IN_LER_STAT_CD
, NULL ) )
, 0 ) PROCESSED
FROM (SELECT RPT.OIPL_ID
, RPT.BUSINESS_GROUP_ID
, RPT.BUSINESS_GROUP_NAME
, RPT.STATUS_CD
, COUNT (*) OVER (PARTITION BY RPT.BUSINESS_GROUP_ID
, RPT.STATUS_CD) CNT_STATUS_CD
, RPT.ELIGIBILITY
, COUNT (*) OVER (PARTITION BY RPT.BUSINESS_GROUP_ID
, RPT.ELIGIBILITY) CNT_ELIGIBILITY
, PIL.PER_IN_LER_STAT_CD
, COUNT (*) OVER (PARTITION BY RPT.BUSINESS_GROUP_ID
, PIL.PER_IN_LER_STAT_CD) CNT_PER_IN_LER_STAT_CD
, COUNT (*) OVER (PARTITION BY RPT.BUSINESS_GROUP_ID) CNT_ALL
FROM BEN_CWB_RPT_DETAIL RPT
, BEN_PER_IN_LER PIL
WHERE RPT.BENEFIT_ACTION_ID = FND_PROFILE.VALUE('BEN_CWB_PP_BEN_ACTION_ID')
AND RPT.OIPL_ID = -1
AND PIL.PER_IN_LER_ID = RPT.GROUP_PER_IN_LER_ID) GROUP BY BUSINESS_GROUP_ID) PRSUM
, (SELECT MAX (DTL.BUSINESS_GROUP_ID) BUSINESS_GROUP_ID
, (BUSINESS_GROUP_NAME) BG_NAME
, COUNT (DTL.PERSON_ID) EMPLOYEES_RATED
FROM BEN_CWB_RPT_DETAIL DTL
WHERE DTL.BENEFIT_ACTION_ID = FND_PROFILE.VALUE('BEN_CWB_PP_BEN_ACTION_ID')
AND DTL.PERFORMANCE_RATING IS NOT NULL GROUP BY DTL.BUSINESS_GROUP_NAME) PERFSUM
, (SELECT DTL.BUSINESS_GROUP_ID
, MAX (BUSINESS_GROUP_NAME) BUSINESS_GROUP_NAME
, COUNT (DTL.PERSON_ID) TOTAL
, COUNT (PROPOSED_JOB) PROPOSED_JOB
, COUNT (PROPOSED_POSITION) PROPOSED_POSITION
, COUNT (PROPOSED_GRADE) PROPOSED_GRADE
, COUNT (PROPOSED_GROUP) PROPOSED_GROUP
, COUNT (PROPOSED_FLEX1) PROPOSED_FLEX1
, COUNT (PROPOSED_FLEX2) PROPOSED_FLEX2
, COUNT (PROPOSED_FLEX3) PROPOSED_FLEX3
, COUNT (PROPOSED_FLEX4) PROPOSED_FLEX4
, COUNT (PROPOSED_FLEX5) PROPOSED_FLEX5
, COUNT (PROPOSED_FLEX6) PROPOSED_FLEX6
, COUNT (PROPOSED_FLEX7) PROPOSED_FLEX7
, COUNT (PROPOSED_FLEX8) PROPOSED_FLEX8
, COUNT (PROPOSED_FLEX9) PROPOSED_FLEX9
, COUNT (PROPOSED_FLEX10) PROPOSED_FLEX10
, COUNT (PROPOSED_FLEX11) PROPOSED_FLEX11
, COUNT (PROPOSED_FLEX12) PROPOSED_FLEX12
, COUNT (PROPOSED_FLEX13) PROPOSED_FLEX13
, COUNT (PROPOSED_FLEX14) PROPOSED_FLEX14
, COUNT (PROPOSED_FLEX15) PROPOSED_FLEX15
, COUNT (PROPOSED_FLEX16) PROPOSED_FLEX16
, COUNT (PROPOSED_FLEX17) PROPOSED_FLEX17
, COUNT (PROPOSED_FLEX18) PROPOSED_FLEX18
, COUNT (PROPOSED_FLEX19) PROPOSED_FLEX19
, COUNT (PROPOSED_FLEX20) PROPOSED_FLEX20
, COUNT (PROPOSED_FLEX21) PROPOSED_FLEX21
, COUNT (PROPOSED_FLEX22) PROPOSED_FLEX22
, COUNT (PROPOSED_FLEX23) PROPOSED_FLEX23
, COUNT (PROPOSED_FLEX24) PROPOSED_FLEX24
, COUNT (PROPOSED_FLEX25) PROPOSED_FLEX25
, COUNT (PROPOSED_FLEX26) PROPOSED_FLEX26
, COUNT (PROPOSED_FLEX27) PROPOSED_FLEX27
, COUNT (PROPOSED_FLEX28) PROPOSED_FLEX28
, COUNT (PROPOSED_FLEX29) PROPOSED_FLEX29
, COUNT (PROPOSED_FLEX30) PROPOSED_FLEX30
FROM BEN_CWB_RPT_DETAIL DTL
WHERE BENEFIT_ACTION_ID = FND_PROFILE.VALUE('BEN_CWB_PP_BEN_ACTION_ID')
AND ASSIGNMENT_CHANGED_FLAG = 'Y' GROUP BY DTL.BUSINESS_GROUP_ID) ASGSUM
, (SELECT BUSINESS_GROUP_ID
, MAX (ELIGIBLE_COUNT) ELIGIBLE_COUNT
, MAX (PAID_COUNT) PAID_COUNT
FROM (SELECT DTL.BUSINESS_GROUP_ID
, DTL.GROUP_OIPL_ID
, COUNT (*) OVER (PARTITION BY DTL.BUSINESS_GROUP_ID
, GROUP_OIPL_ID) ELIGIBLE_COUNT
, COUNT (DECODE (AMOUNT
, 0
, NULL
, AMOUNT)) OVER (PARTITION BY DTL.BUSINESS_GROUP_ID
, GROUP_OIPL_ID) PAID_COUNT
FROM BEN_CWB_RPT_DETAIL DTL
WHERE BENEFIT_ACTION_ID = FND_PROFILE.VALUE('BEN_CWB_PP_BEN_ACTION_ID')
AND ELIGIBILITY = 'Y') GROUP BY BUSINESS_GROUP_ID) COMPSUM
, (SELECT BUSINESS_GROUP_ID
, FND_NUMBER.CANONICAL_TO_NUMBER (SUBSTR (ERROR_OR_WARNING_TEXT
, 1
, INSTR (ERROR_OR_WARNING_TEXT
, ' ') ) ) MESSAGE_NUMBER
, MAX (SUBSTR (ERROR_OR_WARNING_TEXT
, INSTR (ERROR_OR_WARNING_TEXT
, ' ') + 1 ) ) ERROR_OR_WARNING_TEXT
, DECODE (MAX (STATUS_CD)
, 'E'
, 'ERROR'
, 'WARNING') STATUS
, NVL (COUNT (DTL.PERSON_ID)
, 0) EMPLOYEE_COUNT
FROM BEN_CWB_RPT_DETAIL DTL
WHERE STATUS_CD IN ('E'
, 'W')
AND BENEFIT_ACTION_ID = FND_PROFILE.VALUE('BEN_CWB_PP_BEN_ACTION_ID')
AND OIPL_ID = -1 GROUP BY BUSINESS_GROUP_ID
, FND_NUMBER.CANONICAL_TO_NUMBER (SUBSTR (ERROR_OR_WARNING_TEXT
, 1
, INSTR (ERROR_OR_WARNING_TEXT
, ' ' ) ) )) ERRSUM
, BEN_BENEFIT_ACTIONS ACTN
, BEN_CWB_PL_DSGN DSGN
WHERE PRSUM.BUSINESS_GROUP_ID = PERFSUM.BUSINESS_GROUP_ID(+)
AND PRSUM.BUSINESS_GROUP_ID = ASGSUM.BUSINESS_GROUP_ID(+)
AND PRSUM.BUSINESS_GROUP_ID = COMPSUM.BUSINESS_GROUP_ID(+)
AND PRSUM.BUSINESS_GROUP_ID = ERRSUM.BUSINESS_GROUP_ID(+)
AND ACTN.BENEFIT_ACTION_ID = FND_PROFILE.VALUE('BEN_CWB_PP_BEN_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