GL Full Journal Entry Report
with allhdr as(
select je_header_id
from (select
gjh.je_header_id
from gl_ledgers gl,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
(SELECT
xglcc.segment1,
xglcc.segment2,
xglcc.segment3,
xglcc.segment4,
xglcc.segment5,
xglcc.segment6,
xglcc.segment7,
xglcc.segment8,
xglcc.segment9,
--xglcc.gl_account_type,
xglcc.code_Combination_id,
xglcc.concatenated_segments
from gl_code_combinations xglcc
WHERE 1=1
AND ( case when xglcc.segment1 in (:P_Co) then 1
when (COALESCE(NULL,:P_Co) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment2 in (:P_Loc) then 1
when (COALESCE(NULL,:P_Loc) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment3 in (:P_Gac) then 1
when (COALESCE(NULL,:P_Gac) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment4 in (:P_Lac) then 1
when (COALESCE(NULL,:P_Lac) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment5 in (:P_Dept) then 1
when (COALESCE(NULL,:P_Dept) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment6 in (:P_BU) then 1
when (COALESCE(NULL,:P_BU) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment7 in (:P_Ico) then 1
when (COALESCE(NULL,:P_Ico) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment8 in (:P_Program) then 1
when (COALESCE(NULL,:P_Program) is NULL ) then 1
end = 1 )
AND ( case when xglcc.concatenated_segments in (:P_ACCT_CD_COMB) then 1
when (COALESCE(NULL,:P_ACCT_CD_COMB) is NULL ) then 1
end = 1 )
) xglcc,
(select gjs.user_je_source_name,
gjs.je_source_name,
gjc.user_je_category_name,
gjc.je_category_name,
nvl(flvv.je_class_rpt_group,
nvl(flvv2.je_class_rpt_group,
flvv3.je_class_rpt_group)) as je_class_rpt_group,
nvl(flvv.je_class_desc,
nvl(flvv2.je_class_desc, flvv3.je_class_desc)) as je_class_desc
from (select flvv.attribute1 user_je_source_name,
flvv.attribute2 user_je_category_name,
flvv.attribute3 je_class_rpt_group,
a.description je_class_desc
from (select flvv.attribute1,
flvv.attribute2,
flvv.attribute3
from fnd_lookup_values_vl flvv
where flvv.lookup_type =
'XXON_GL_JE_CLASSIFICATION_GRP'
and nvl(enabled_flag, 'N') = 'Y'
and nvl(flvv.start_date_active,
trunc(sysdate)) <= trunc(sysdate)
and nvl(flvv.end_date_active, trunc(sysdate)) >=
trunc(sysdate)
and (flvv.attribute1 <> '*' and
flvv.attribute2 <> '*')) flvv,
(select ffvv.flex_value, ffvv.description
from fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id =
ffvv.flex_value_set_id
and ffvs.flex_value_set_name =
'XXON_GL_JE_CLASSIFICATIONS') a
where flvv.attribute3 = a.flex_value(+)) flvv,
(select flvv.attribute1 user_je_source_name,
flvv.attribute2 user_je_category_name,
flvv.attribute3 je_class_rpt_group,
a.description je_class_desc
from (select flvv.attribute1,
flvv.attribute2,
flvv.attribute3
from fnd_lookup_values_vl flvv
where flvv.lookup_type =
'XXON_GL_JE_CLASSIFICATION_GRP'
and nvl(enabled_flag, 'N') = 'Y'
and nvl(flvv.start_date_active,
trunc(sysdate)) <= trunc(sysdate)
and nvl(flvv.end_date_active, trunc(sysdate)) >=
trunc(sysdate)
and (flvv.attribute1 = '*' and
flvv.attribute2 <> '*')) flvv,
(select ffvv.flex_value, ffvv.description
from fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id =
ffvv.flex_value_set_id
and ffvs.flex_value_set_name =
'XXON_GL_JE_CLASSIFICATIONS') a
where flvv.attribute3 = a.flex_value(+)) flvv2,
(select flvv.attribute1 user_je_source_name,
flvv.attribute2 user_je_category_name,
flvv.attribute3 je_class_rpt_group,
a.description je_class_desc
from (select flvv.attribute1,
flvv.attribute2,
flvv.attribute3
from fnd_lookup_values_vl flvv
where flvv.lookup_type =
'XXON_GL_JE_CLASSIFICATION_GRP'
and nvl(enabled_flag, 'N') = 'Y'
and nvl(flvv.start_date_active,
trunc(sysdate)) <= trunc(sysdate)
and nvl(flvv.end_date_active, trunc(sysdate)) >=
trunc(sysdate)
and (flvv.attribute1 <> '*' and
flvv.attribute2 = '*')
order by flvv.lookup_code) flvv,
(select ffvv.flex_value, ffvv.description
from fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id =
ffvv.flex_value_set_id
and ffvs.flex_value_set_name =
'XXON_GL_JE_CLASSIFICATIONS') a
where flvv.attribute3 = a.flex_value(+)) flvv3,
gl_je_sources gjs,
gl_je_categories gjc
where gjs.user_je_source_name = flvv.user_je_source_name(+)
and gjc.user_je_category_name =
flvv.user_je_category_name(+)
and gjc.user_je_category_name =
flvv2.user_je_category_name(+)
and gjs.user_je_source_name = flvv3.user_je_source_name(+)
and (gjs.user_je_source_name IN (:P_Source) OR COALESCE(:P_Source,null) IS Null)
and (gjc.user_je_category_name IN (:P_Category) OR COALESCE(:P_Category,null) IS Null)
) jscg,
Per_Users fu,
gl_lookups gll
WHERE gl.ledger_id = gjh.ledger_id
and gjb.je_batch_id = gjh.je_batch_id
and gjh.je_header_id = gjl.je_header_id
and xglcc.code_combination_id = gjl.code_combination_id
and jscg.je_source_name = gjh.je_source
and jscg.je_category_name = gjh.je_category
AND ( case when gjh.period_name in (:P_Period_name) then 1
when (COALESCE(NULL,:P_Period_name) is NULL ) then 1
end = 1 )
AND ( case when gl.name in (:P_GL_name) then 1
when (COALESCE(NULL,:P_GL_name) is NULL ) then 1
end = 1 )
AND ( case when fu.username in (:P_Created_by) then 1
when (COALESCE(NULL,:P_Created_by) is NULL ) then 1
end = 1 )
AND ( case when gjb.name in (:P_Batch_name) then 1
when (COALESCE(NULL,:P_Batch_name) is NULL ) then 1
end = 1 )
AND ( case when gjh.name in (:P_Ledger_name) then 1
when (COALESCE(NULL,:P_Ledger_name) is NULL ) then 1
end = 1 )
AND ( case when gjb.status in (:P_posted_status) then 1
when (COALESCE(NULL,:P_posted_status) is NULL ) then 1
end = 1 )
AND ( case when gjh.currency_code in (:P_CY_CD) then 1
when (COALESCE(NULL,:P_CY_CD) is NULL ) then 1
end = 1 )
AND ( case when gll.lookup_code in (:P_APPROVAL_STATUS) then 1
when (COALESCE(NULL,:P_APPROVAL_STATUS) is NULL ) then 1
end = 1 )
--and exists
-- (select 1
-- from apps.gl_consolidation sgc
-- where exists
-- (select 1
-- from apps.gl_cons_map_set_v s,
-- apps.gl_cons_set_assign_v a
-- where s.consolidation_set_id = a.consolidation_set_id
-- and a.consolidation_id = sgc.consolidation_id)
-- and sgc.from_ledger_id = gl.ledger_id
-- and sgc.from_currency_code = 'USD'
-- and sgc.name not like 'ZZZ%'
-- and nvl('amp;PARM06', 'N') = 'Y'
-- union
-- select 1
-- from dual
-- where gl.name = 'USD CL'
-- and jscg.user_je_source_name lt;gt; 'Consolidation'
-- and nvl('amp;PARM06', 'N') = 'Y'
-- union all
-- select 1
-- from dual
-- where nvl('amp;PARM06', 'N') = 'N')
and fu.username = gjb.created_by
and gll.lookup_type = 'JE_BATCH_APPROVAL_STATUS'
and gll.lookup_code = gjb.approval_status_code)
where 1=1
-- %IF ' amp;PARM17' = '' %THENDO;
-- %ELSE;
-- AND (je_class_rpt_group amp;SPARM17)
-- %ENDIF;
)
select *
from (select gjb.je_batch_id,
gjb.name je_batch_name,
gjh.je_header_id,
gjh.name journal_name,
gjb.status posted_status,
gll.meaning approval_status,
gl.name ledger_name,
-- pxglcc.rpt_type rpt_type,
-- pxglcc.fs_cat_1 fs_cat_1,
-- pxglcc.fs_cat_2 fs_cat_2,
-- pxglcc.fs_cat_3 fs_cat_3,
jscg.user_je_source_name source,
jscg.user_je_category_name category,
gjh.attribute9 je_class,
nvl(y.je_class_desc,
nvl(nvl(jscg.je_class_desc, jscg.je_class_rpt_group),
'Not Defined')) je_class_rpt_group,
-- COALESCE(wd.wd,
-- (case
-- when TRUNC(gjb.posted_date) lt; COALESCE(wd.fiscal_date,
-- TRUNC(gjb.posted_date) + 1) and
-- TRUNC(gjb.posted_date) lt;=
-- (select max(a.period_end_date)
-- from apps.xxon_gl_closing_calendar a
-- where a.period_name = Gjh.Period_Name) THEN
-- 'Prior WD01'
-- WHEN TRUNC(gjb.posted_date) =
-- COALESCE(wd.fiscal_date, TRUNC(gjb.posted_date) - 1) THEN
-- Wd.Wd
-- else
-- 'Post Period Closing'
-- end)) wd,
-- gjb.default_effective_date acctg_dt,
TO_CHAR(gjb.default_effective_date, 'MM/DD/YYYY HH:MI:SS PM') acctg_dt,
--gjh.creation_Date date_created,
TO_CHAR(gjh.creation_Date, 'MM/DD/YYYY HH:MI:SS PM') date_created,
TO_CHAR(gjb.posted_date, 'MM/DD/YYYY HH:MI:SS PM') date_posted,
-- gjb.posted_date date_posted,
fu.username created_by,
xev.display_name created_by_desc,
(select
pu.username from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
where rownum=1
and gjal.je_batch_id=gjb.je_batch_id
and gjal.user_id=pu.username(+)
and pu.person_id=papf.person_id(+)
and Action_code in('SENT_APPROVAL_W_POSTING')
-- and gjal.action_date=(select max(glall.action_date) FROM
-- GL_je_action_log glall
-- where glall.je_batch_id=gjb.je_batch_id)
)notify_to,
(select
papf.display_name from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
where rownum=1
and gjal.je_batch_id=gjb.je_batch_id
and gjal.user_id=pu.username(+)
and pu.person_id=papf.person_id(+)
and Action_code in('SENT_APPROVAL_W_POSTING')
-- and gjal.action_date=(select max(glall.action_date) FROM
-- GL_je_action_log glall
-- where glall.je_batch_id=gjb.je_batch_id)
)notify_to_desc,
(select
pu.username from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
where 1=1
and gjal.user_id=pu.username(+)
and pu.person_id=papf.person_id(+)
and Action_code in('APPROVED')
and gjal.action_date=(select max(glall.action_date) FROM
GL_je_action_log glall
where glall.je_batch_id=gjb.je_batch_id)
) Approved_by,
(select
papf.display_name from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
where 1=1--gjal.je_batch_id=
and gjal.user_id=pu.username(+)
and pu.person_id=papf.person_id(+)
and Action_code in('APPROVED')
and gjal.action_date=(select max(glall.action_date) FROM
GL_je_action_log glall
where glall.je_batch_id=gjb.je_batch_id)
) Approved_Desc,
gjh.actual_flag,
gdc.user_conversion_type conv_type,
-- gjh.currency_conversion_date conv_date,
TO_CHAR(gjh.currency_conversion_date, 'MM/DD/YYYY HH:MI:SS PM') conv_date,
gjh.currency_conversion_rate conv_rate,
gjl.je_line_num je_line,
xglcc.segment1 co,
xglcc.segment2 loc,
xglcc.segment3 gac,
xglcc.segment4 lac,
xglcc.segment5 dept,
xglcc.segment6 bu,
xglcc.segment7 ico,
xglcc.segment8 proj,
xglcc.segment9 fut,
xglcc.concatenated_segments acct_cd_comb,
(select ffvv.description
FROM fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv
WHERE ffvs.flex_value_set_name = 'XXON_GLS3_GAC'
and ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvv.flex_value = xglcc.segment3) gac_desc,
--xglcc.gl_account_type gac_type,
(select ffvv.description
FROM fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv
WHERE ffvs.flex_value_set_name = 'XXON_GLS4_LAC'
and ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvv.flex_value = xglcc.segment4
AND Ffvv.Enabled_Flag = 'Y'
AND Ffvv.Summary_Flag = 'N'
AND ROWNUM = 1) lac_desc,
(select ffvv.description
FROM fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv
WHERE ffvs.flex_value_set_name = 'XXON_GL_PROJ'
and ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvv.flex_value = xglcc.segment8) proj_desc,
gjh.currency_code cy_cd,
nvl(gjl.entered_dr, 0) ent_dr,
nvl(gjl.entered_cr, 0) ent_cr,
nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0) ent_net,
nvl(gjl.accounted_dr, 0) acct_dr,
nvl(gjl.accounted_cr, 0) acct_cr,
nvl(gjl.accounted_dr, 0) - nvl(gjl.accounted_cr, 0) acct_net,
gjl.description,
gjh.accrual_rev_status reversal_flag,
gjh.accrual_rev_period_name reversal_period,
gjh.period_name period,
gjh.doc_sequence_value doc_sequence,
gjl.tax_code,
-- gjl.Invoice_Date,
TO_CHAR(gjl.Invoice_Date, 'MM/DD/YYYY HH:MI:SS PM') Invoice_Date,
gjl.Invoice_Identifier,
gjl.Invoice_Amount,
gjh.attribute10 afrm_link,
gjb.parent_je_batch_id
from gl_ledgers gl,
gl_daily_conversion_types gdc,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
--xxon_employee_view xev,
PER_PERSON_NAMES_F xev,
(SELECT
xglcc.segment1,
xglcc.segment2,
xglcc.segment3,
xglcc.segment4,
xglcc.segment5,
xglcc.segment6,
xglcc.segment7,
xglcc.segment8,
xglcc.segment9,
--xglcc.gl_account_type,
xglcc.code_Combination_id,
xglcc.concatenated_segments
from gl_code_combinations xglcc
WHERE 1=1
) xglcc,
-- (SELECT 'BSHEET' rpt_type,
-- bxglcc.fs_cat_1,
-- bxglcc.fs_cat_2,
-- bxglcc.fs_cat_3,
-- bxglcc.code_Combination_id
-- from apps.xxon_gl_fs_glcc_attr_bsheet bxglcc
-- WHERE bxglcc.gl_account_type IN ('A','L','O')
-- UNION ALL
-- SELECT 'PLOPR' rpt_type,
-- pxglcc.fs_cat_1,
-- pxglcc.fs_cat_2,
-- pxglcc.fs_cat_3,
-- pxglcc.code_Combination_id
-- FROM
-- apps.xxon_gl_fs_glcc_attr_plopr pxglcc
-- WHERE pxglcc.gl_account_type IN ('E','R')) pxglcc,
(select gjs.user_je_source_name,
gjs.je_source_name,
gjc.user_je_category_name,
gjc.je_category_name,
nvl(flvv.je_class_rpt_group,
nvl(flvv2.je_class_rpt_group,
flvv3.je_class_rpt_group)) as je_class_rpt_group,
nvl(flvv.je_class_desc,
nvl(flvv2.je_class_desc, flvv3.je_class_desc)) as je_class_desc
from (select flvv.attribute1 user_je_source_name,
flvv.attribute2 user_je_category_name,
flvv.attribute3 je_class_rpt_group,
a.description je_class_desc
from (select flvv.attribute1,
flvv.attribute2,
flvv.attribute3
from fnd_lookup_values_vl flvv
where flvv.lookup_type =
'XXON_GL_JE_CLASSIFICATION_GRP'
and nvl(enabled_flag, 'N') = 'Y'
and nvl(flvv.start_date_active,
trunc(sysdate)) <= trunc(sysdate)
and nvl(flvv.end_date_active, trunc(sysdate)) >=
trunc(sysdate)
and (flvv.attribute1 <> '*' and
flvv.attribute2 <> '*')) flvv,
(select ffvv.flex_value, ffvv.description
from fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id =
ffvv.flex_value_set_id
and ffvs.flex_value_set_name =
'XXON_GL_JE_CLASSIFICATIONS') a
where flvv.attribute3 = a.flex_value(+)) flvv,
(select flvv.attribute1 user_je_source_name,
flvv.attribute2 user_je_category_name,
flvv.attribute3 je_class_rpt_group,
a.description je_class_desc
from (select flvv.attribute1,
flvv.attribute2,
flvv.attribute3
from fnd_lookup_values_vl flvv
where flvv.lookup_type =
'XXON_GL_JE_CLASSIFICATION_GRP'
and nvl(enabled_flag, 'N') = 'Y'
and nvl(flvv.start_date_active,
trunc(sysdate)) <= trunc(sysdate)
and nvl(flvv.end_date_active, trunc(sysdate)) >=
trunc(sysdate)
and (flvv.attribute1 = '*' and
flvv.attribute2 <> '*')) flvv,
(select ffvv.flex_value, ffvv.description
from fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id =
ffvv.flex_value_set_id
and ffvs.flex_value_set_name =
'XXON_GL_JE_CLASSIFICATIONS') a
where flvv.attribute3 = a.flex_value(+)) flvv2,
(select flvv.attribute1 user_je_source_name,
flvv.attribute2 user_je_category_name,
flvv.attribute3 je_class_rpt_group,
a.description je_class_desc
from (select flvv.attribute1,
flvv.attribute2,
flvv.attribute3
from fnd_lookup_values_vl flvv
where flvv.lookup_type =
'XXON_GL_JE_CLASSIFICATION_GRP'
and nvl(enabled_flag, 'N') = 'Y'
and nvl(flvv.start_date_active,
trunc(sysdate)) <= trunc(sysdate)
and nvl(flvv.end_date_active, trunc(sysdate)) >=
trunc(sysdate)
and (flvv.attribute1 <> '*' and
flvv.attribute2 = '*')
order by flvv.lookup_code) flvv,
(select ffvv.flex_value, ffvv.description
from fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id =
ffvv.flex_value_set_id
and ffvs.flex_value_set_name =
'XXON_GL_JE_CLASSIFICATIONS') a
where flvv.attribute3 = a.flex_value(+)) flvv3,
gl_je_sources gjs,
gl_je_categories gjc
where gjs.user_je_source_name = flvv.user_je_source_name(+)
and gjc.user_je_category_name =
flvv.user_je_category_name(+)
and gjc.user_je_category_name =
flvv2.user_je_category_name(+)
and gjs.user_je_source_name = flvv3.user_je_source_name(+)
) jscg,
(select ffvv.flex_value je_classification,
ffvv.description je_class_desc
from fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id = ffvv.flex_value_set_id
and ffvs.flex_value_set_name =
'XXON_GL_JE_CLASSIFICATIONS') y,
Per_users fu,
-- (select gjh.work_day wd, gjh.fiscal_date, gjh.period_name
-- from apps.xxon_gl_closing_calendar gjh
-- where 1=1
-- ) wd,
gl_lookups gll
-- (
-- SELECT
-- l.notified_to,
-- l.notified_to_desc,
-- l.approved_by,
-- l.approved_by_desc,
-- l.batch_id,
-- l.org_batch_id,
-- l.notification_id
-- FROM
-- (
-- SELECT
-- wn.recipient_role notified_to,
-- wn.description notified_to_desc,
-- wia.assigned_user approved_by,
-- fu.description approved_by_desc,
-- wia.item_key batch_id,
-- wia.notification_id,
-- regexp_substr(wia.item_key,'[^*]+',1,1) org_batch_id,
-- wia.end_date
-- FROM
-- apps.wf_item_activity_statuses wia,
-- apps.fnd_user fu,
-- (
-- SELECT
-- wn.recipient_role,
-- fu1.description,
-- wn.notification_id
-- FROM
-- apps.wf_notifications wn,
-- apps.fnd_user fu1
-- WHERE
-- wn.recipient_role = fu1.user_name (+)
-- ) wn
-- WHERE
-- wia.item_type = 'GLBATCH'
-- AND wia.activity_result_code = 'APPROVED'
-- AND wia.assigned_user IS NOT NULL
-- AND wia.activity_status = 'COMPLETE'
-- AND wia.notification_id = wn.notification_id (+)
-- AND wia.assigned_user = fu.user_name (+)
-- ) l
-- WHERE
-- l.end_date = (
-- SELECT
-- MAX(wia2.end_date)
-- FROM
-- apps.wf_item_activity_statuses wia2
-- WHERE
-- wia2.item_type = 'GLBATCH'
-- AND wia2.activity_result_code = 'APPROVED'
-- AND wia2.assigned_user IS NOT NULL
-- AND wia2.activity_status = 'COMPLETE'
-- AND wia2.item_key LIKE l.Org_Batch_Id || '*' || '%'
-- )
-- ) wf,
-- (
-- SELECT
-- aph.item_type,
-- aph.on_item_key batch_id,
-- aph.assigned_user approved_by,
-- usr.description approved_by_desc,
-- aph.notification_id,
-- NULL notified_to,
-- NULL notified_to_desc
-- FROM
-- apps.xxon_wf_approval_hist aph,
-- apps.fnd_user usr
-- WHERE
-- aph.item_type = 'GLBATCH'
-- AND usr.user_name = aph.assigned_user
-- AND aph.end_date = (
-- SELECT
-- MAX(apr.end_date)
-- FROM
-- apps.xxon_wf_approval_hist apr
-- WHERE
-- aph.on_item_key = apr.on_item_key
-- )
-- ) onwf
WHERE gdc.conversion_type = gjh.currency_conversion_type
AND gl.ledger_id = gjh.ledger_id
and gjb.je_batch_id = gjh.je_batch_id
and gjh.je_header_id = gjl.je_header_id
and xglcc.code_combination_id = gjl.code_combination_id
AND gjh.je_header_id IN (SELECT je_header_id FROM allhdr)
AND trunc(SYSDATE) BETWEEN xev.effective_start_date (+) AND xev.effective_end_date (+)
AND gjb.approver_employee_id = xev.person_id (+)
--AND Xglcc.Code_Combination_Id = Pxglcc.Code_Combination_Id(+)
and jscg.je_source_name = gjh.je_source
and jscg.je_category_name = gjh.je_category
and gl.name like '%USD PL'
and nvl(gjh.attribute9, jscg.je_class_rpt_group) =
y.je_classification(+)
and fu.username = gjb.created_by
and gll.lookup_type = 'JE_BATCH_APPROVAL_STATUS'
and gll.lookup_code = gjb.approval_status_code
AND ( case when xglcc.segment1 in (:P_Co) then 1
when (COALESCE(NULL,:P_Co) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment2 in (:P_Loc) then 1
when (COALESCE(NULL,:P_Loc) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment3 in (:P_Gac) then 1
when (COALESCE(NULL,:P_Gac) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment4 in (:P_Lac) then 1
when (COALESCE(NULL,:P_Lac) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment5 in (:P_Dept) then 1
when (COALESCE(NULL,:P_Dept) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment6 in (:P_BU) then 1
when (COALESCE(NULL,:P_BU) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment7 in (:P_Ico) then 1
when (COALESCE(NULL,:P_Ico) is NULL ) then 1
end = 1 )
AND ( case when xglcc.segment8 in (:P_Program) then 1
when (COALESCE(NULL,:P_Program) is NULL ) then 1
end = 1 )
AND ( case when xglcc.concatenated_segments in (:P_ACCT_CD_COMB) then 1
when (COALESCE(NULL,:P_ACCT_CD_COMB) is NULL ) then 1
end = 1 )
)
where 1=1
AND ( case when je_class_rpt_group in (:P_JE_CLASS_GRP) then 1
when (COALESCE(NULL,:P_JE_CLASS_GRP) is NULL ) then 1
end = 1 )
Order by je_header_id,
je_line
No comments:
Post a Comment