AP Invoice Distribution Account LOV Check

select
x.ledger,
x.operating_unit,
nvl(x.concatenated_segments,:account_string) account_string,
x.description,
x.start_date_active,
x.end_date_active,
x.enabled_flag,
x.summary_flag,
x.detail_posting_allowed,
x.gl_security,
x.code_combination_id,
nvl(
substr(
case when x.concatenated_segments is null then chr(10) || 'Account does not exist in the Ledgers COA' end ||
case when x.concatenated_segments is not null and not nvl(x.gl_security,'FALSE') = 'TRUE' then chr(10) || 'GL Security is preventing the Account from being selected from this responsibility' end ||
case when not trunc(sysdate) between nvl(x.start_date_active,trunc(sysdate)) and nvl(x.end_date_active,trunc(sysdate)) then chr(10) || 'The Account is not active' end ||
case when not x.enabled_flag='Y' then chr(10) || 'The account is disabled' end ||
case when not x.summary_flag='N' then chr(10) || 'The account is a summary account' end ||
case when not x.detail_posting_allowed='Y' then chr(10) || 'Detailed Posting is not permitted to this account' end
,2,4000),
'Good') lov_status
from
(
select
gl.name ledger,
hou.name operating_unit,
gcck.concatenated_segments,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description,
gcck.start_date_active,
gcck.end_date_active,
gcck.enabled_flag,
gcck.summary_flag,
gcck.detail_posting_allowed,
(select
 gl_security_pkg.validate_segval(fifsgv.segment_num,null,gcck.segment1)
 from
 fnd_id_flex_segments_vl fifsgv
 where
 fifsgv.application_id=101 and
 fifsgv.id_flex_code='GL#' and
 fifsgv.enabled_flag='Y' and
 fifsgv.application_column_name='SEGMENT1' and
 fifsgv.id_flex_num=gl.chart_of_accounts_id
) gl_security,
gcck.code_combination_id
from
hr_operating_units hou,
gl_ledgers gl,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
hou.name=:operating_unit and
gcck.concatenated_segments(+)=:account_string and
to_number(hou.set_of_books_id)=gl.ledger_id and
gl.chart_of_accounts_id=gcck.chart_of_accounts_id(+) and
flvv.lookup_type(+)='ACCOUNT TYPE' and
flvv.view_application_id(+)=101 and
flvv.lookup_code(+)=gcck.gl_account_type
) x

select x.ledger, x.operating_unit, nvl(x.concatenated_segments,:account_string) account_string, x.description, x.start_date_active, x.end_date_active, x.enabled_flag, x.summary_flag, x.detail_posting_allowed, x.gl_security, x.code_combination_id, nvl( substr( case when x.concatenated_segments is null then chr(10) || 'Account does not exist in the Ledgers COA' end || case when x.concatenated_segments is not null and not nvl(x.gl_security,'FALSE') = 'TRUE' then chr(10) || 'GL Security is preventing the Account from being selected from this responsibility' end || case when not trunc(sysdate) between nvl(x.start_date_active,trunc(sysdate)) and nvl(x.end_date_active,trunc(sysdate)) then chr(10) || 'The Account is not active' end || case when not x.enabled_flag='Y' then chr(10) || 'The account is disabled' end || case when not x.summary_flag='N' then chr(10) || 'The account is a summary account' end || case when not x.detail_posting_allowed='Y' then chr(10) || 'Detailed Posting is not permitted to this account' end ,2,4000), 'Good') lov_status from ( select gl.name ledger, hou.name operating_unit, gcck.concatenated_segments, xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description, gcck.start_date_active, gcck.end_date_active, gcck.enabled_flag, gcck.summary_flag, gcck.detail_posting_allowed, (select gl_security_pkg.validate_segval(fifsgv.segment_num,null,gcck.segment1) from fnd_id_flex_segments_vl fifsgv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT1' and fifsgv.id_flex_num=gl.chart_of_accounts_id ) gl_security, gcck.code_combination_id from hr_operating_units hou, gl_ledgers gl, gl_code_combinations_kfv gcck, fnd_lookup_values_vl flvv where hou.name=:operating_unit and gcck.concatenated_segments(+)=:account_string and to_number(hou.set_of_books_id)=gl.ledger_id and gl.chart_of_accounts_id=gcck.chart_of_accounts_id(+) and flvv.lookup_type(+)='ACCOUNT TYPE' and flvv.view_application_id(+)=101 and flvv.lookup_code(+)=gcck.gl_account_type ) x