GL Journal Entry Drilldown Report
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,
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,
-- TO_CHAR(gjb.default_effective_date, 'MM/DD/YYYY HH:MI:SS PM') acctg_dt,
TO_CHAR((FROM_TZ(CAST(gjb.default_effective_date AS TIMESTAMP),'GMT') AT TIME ZONE 'MST'),'MM/DD/YYYY HH:MI:SS PM') AS acctg_dt,
-- TO_CHAR(gjh.creation_Date, 'MM/DD/YYYY HH:MI:SS PM') date_created,
TO_CHAR((FROM_TZ(CAST(gjh.creation_Date AS TIMESTAMP),'GMT') AT TIME ZONE 'MST'),'MM/DD/YYYY HH:MI:SS PM') AS date_created,
-- (SELECT TO_CHAR(batch.CREATION_DATE,'MM/DD/YYYY HH:MI:SS PM') AS created_date
-- --CreatedByPersonName.DISPLAY_NAME AS Created_by
-- FROM GL_JE_BATCHES batch,
-- PER_USERS CreatedByUser,
-- PER_PERSON_NAMES_F_V CreatedByPersonName
-- WHERE batch.CREATED_BY = CreatedByUser.USERNAME(+)
-- AND ('Y') = CreatedByUser.ACTIVE_FLAG(+)
-- AND CreatedByUser.PERSON_ID = CreatedByPersonName.PERSON_ID(+)
-- AND ( ( (batch.je_batch_id = gjb.je_batch_id) ) )
-- AND ( sysdate BETWEEN CreatedByPersonName.EFFECTIVE_START_DATE(+)
-- AND CreatedByPersonName.EFFECTIVE_END_DATE(+))) date_created,
TO_CHAR((FROM_TZ(CAST(gjb.posted_date AS TIMESTAMP),'GMT') AT TIME ZONE 'MST'),'MM/DD/YYYY HH:MI:SS PM') AS date_posted,
--TO_CHAR(gjb.posted_date, 'MM/DD/YYYY HH:MI:SS PM') date_posted,
-- (SELECT --batch.CREATION_DATE AS created_date,
-- CreatedByUser.USERNAME AS Created_by
-- FROM GL_JE_BATCHES batch,
-- PER_USERS CreatedByUser,
-- PER_PERSON_NAMES_F_V CreatedByPersonName
-- WHERE batch.CREATED_BY = CreatedByUser.USERNAME(+)
-- AND ('Y') = CreatedByUser.ACTIVE_FLAG(+)
-- AND CreatedByUser.PERSON_ID = CreatedByPersonName.PERSON_ID(+)
-- AND ( ( (batch.je_batch_id = gjb.je_batch_id ) ) )
-- AND ( sysdate BETWEEN CreatedByPersonName.EFFECTIVE_START_DATE(+)
-- AND CreatedByPersonName.EFFECTIVE_END_DATE(+))) created_by,
nvl((select nvl(pu.username,gjb.created_by)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
-- gl_je_batches gjb
where 1=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('CREATED')
--and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
and gjal.action_date=((select max(gjal.action_date)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
--gl_je_batches gjb
where 1=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('CREATED')
-- and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
-- GROUP by gjb.je_batch_id ,gjb.name,gjal.action_date
))
), (select
pu.username
from
per_users pu,
per_person_names_f_v papf
where 1=1
and pu.person_id=papf.person_id(+)
and pu.USERNAME=gjh.Created_by
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+)))) created_by,
nvl((select nvl(papf.display_name,gjb.created_by)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
-- gl_je_batches gjb
where 1=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('CREATED')
--and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
and gjal.action_date=((select max(gjal.action_date)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
--gl_je_batches gjb
where 1=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('CREATED')
-- and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
-- GROUP by gjb.je_batch_id ,gjb.name,gjal.action_date
))
),(select
papf.display_name
from
per_users pu,
per_person_names_f_v papf
where 1=1
and pu.person_id=papf.person_id(+)
and pu.USERNAME=gjh.Created_by
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+)))) created_by_desc,
-- (select
-- papf.display_name
-- from
-- per_users pu,
-- per_person_names_f_v papf
-- where 1=1
-- and pu.person_id=papf.person_id(+)
-- and pu.USERNAME=gjh.Created_by
-- AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
-- AND papf.EFFECTIVE_END_DATE(+))) created_by_desc,
-- (select
-- pu.username
-- from
-- per_users pu,
-- per_person_names_f_v papf
-- where 1=1
-- and pu.person_id=papf.person_id(+)
-- and pu.USERNAME=gjh.Created_by
-- AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
-- AND papf.EFFECTIVE_END_DATE(+))) created_by,
-- 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
-- gl_je_batches gjb
where 1=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('ORA_ASSIGNED TO')
--and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
and gjal.action_date=((select max(gjal.action_date)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
--gl_je_batches gjb
where 1=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('ORA_ASSIGNED TO')
-- and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
-- GROUP by gjb.je_batch_id ,gjb.name,gjal.action_date
))
) notify_to,
-- (select
-- pu.username from
-- GL_je_action_log gjal,
-- per_users pu,
-- per_person_names_f_v papf
-- where 1=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('ORA_ASSIGNED TO')
-- AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
-- AND papf.EFFECTIVE_END_DATE(+))) notify_to,
(select papf.display_name
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
-- gl_je_batches gjb
where 1=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('ORA_ASSIGNED TO')
--and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
and gjal.action_date=((select max(gjal.action_date)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
--gl_je_batches gjb
where 1=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('ORA_ASSIGNED TO')
-- and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
-- GROUP by gjb.je_batch_id ,gjb.name,gjal.action_date
))
) notify_to_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)
-- (select
-- papf.DISPLAY_NAME from
-- GL_je_action_log gjal,
-- per_users pu,
-- per_person_names_f_v papf
-- where 1=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('ORA_ASSIGNED TO')
-- AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
-- AND papf.EFFECTIVE_END_DATE(+))) notify_to_desc,
-- )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
-- CreatedByUser.username AS DISPLAY_NAME_B
-- FROM
-- GL_JE_BATCHES JrnlBatch,
-- PER_PERSON_NAMES_F_V BatchApprovedBy ,
-- PER_USERS CreatedByUser
-- WHERE 1=1
-- AND JrnlBatch.APPROVER_EMPLOYEE_ID = BatchApprovedBy.PERSON_ID(+)
-- AND ( sysdate BETWEEN BatchApprovedBy.EFFECTIVE_START_DATE(+) AND BatchApprovedBy.EFFECTIVE_END_DATE(+))
-- AND CreatedByUser.PERSON_ID = BatchApprovedBy.PERSON_ID
-- AND ('Y') = CreatedByUser.ACTIVE_FLAG(+)
-- and JrnlBatch.je_batch_id =gjb.je_batch_id) Approved_by,
-- (select
-- pu.username from
-- GL_je_action_log gjal,
-- per_users pu,
-- per_person_names_f_v papf
-- where rownum=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,
decode(gjb.APPROVAL_STATUS_CODE,'A',(select pu.USERNAME
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
-- gl_je_batches gjb
where 1=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('APPROVED')
--and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
and gjal.action_date=((select max(gjal.action_date)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
-- gl_je_batches gjb
where 1=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('APPROVED')
-- and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
-- GROUP by gjb.je_batch_id ,gjb.name,gjal.action_date
))
),'') Approved_by,
decode(gjb.APPROVAL_STATUS_CODE,'A',(select papf.display_name
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
-- gl_je_batches gjb
where 1=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('APPROVED')
--and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
and gjal.action_date=((select max(gjal.action_date)
from
GL_je_action_log gjal,
per_users pu,
per_person_names_f_v papf
-- gl_je_batches gjb
where 1=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('APPROVED')
-- and gjb.je_batch_id=242807
AND ( sysdate BETWEEN papf.EFFECTIVE_START_DATE(+)
AND papf.EFFECTIVE_END_DATE(+))
-- GROUP by gjb.je_batch_id ,gjb.name,gjal.action_date
))
),'')Approved_Desc,
-- (SELECT
-- BatchApprovedBy.DISPLAY_NAME AS DISPLAY_NAME_B
-- FROM
-- GL_JE_BATCHES JrnlBatch,
-- PER_PERSON_NAMES_F_V BatchApprovedBy
-- WHERE 1=1
-- AND JrnlBatch.APPROVER_EMPLOYEE_ID = BatchApprovedBy.PERSON_ID(+)
-- AND ( sysdate BETWEEN BatchApprovedBy.EFFECTIVE_START_DATE(+) AND BatchApprovedBy.EFFECTIVE_END_DATE(+))
-- and JrnlBatch.je_batch_id =gjb.je_batch_id) Approved_Desc,
-- (select
-- papf.display_name from
-- GL_je_action_log gjal,
-- per_users pu,
-- per_person_names_f_v papf
-- where rownum=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,
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.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,
nvl(gjh.currency_code,gjl.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.description,
gjh.accrual_rev_status reversal_flag,
gjh.accrual_rev_period_name reversal_period,
gjh.period_name period,
gjh.POSTING_ACCT_SEQ_VALUE doc_sequence_number,
--jscg.user_je_category_name||' '||xglcc.SEGMENT1||' '||'20'||SUBSTR(gjh.period_name,INSTR(gjh.period_name,'-')+1,2) doc_sequence_name,
RTRIM(ltrim(decode(gjh.POSTING_ACCT_SEQ_VALUE,null,'',jscg.user_je_category_name||' '||xglcc.SEGMENT1||' '||'20'||SUBSTR(gjh.period_name,INSTR(gjh.period_name,'-')+1,2)))) doc_sequence_name,
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,
(select to_char(gjb.posted_date,'WW') ww from dual) fiscal_wk,
--(select apps.xxon_date_with_weeknum(gjb.posted_date) from dual) fiscal_wk, --Ann L 01-Aug 24
TO_CHAR(sysdate, 'MM/DD/YYYY HH:MI:SS PM') timestamp,
gjb.APPROVAL_STATUS_CODE Approval_status_code
--sysdate timestamp
from gl_ledgers gl,
gl_daily_conversion_types gdc,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
-- --apps.xxon_employee_view xev,
per_person_names_f_v xev,
(SELECT
xglcc.segment1,
xglcc.segment2,
xglcc.segment3,
xglcc.segment4,
xglcc.segment5,
xglcc.segment6,
xglcc.segment7,
xglcc.segment8,
xglcc.segment9,
--xglcc.account_type,
decode(xglcc.Account_type,'A','Asset','E','Expense','O','Owner Equity','L','Liability','R','Revenue','') account_type,
xglcc.code_Combination_id,
xglcc.concatenated_segments
from gl_code_combinations xglcc
WHERE 1=1
-- and (xglcc.segment1 IN (:Co) OR COALESCE(:Co,null) IS Null)
AND ( case when xglcc.segment1 in (:P_Co) then 1
when (COALESCE(NULL,:P_Co) is NULL ) then 1
end = 1 )
--and (xglcc.segment2 IN (:Loc) OR COALESCE(:Loc,null) IS Null)
AND ( case when xglcc.segment2 in (:P_Loc) then 1
when (COALESCE(NULL,:P_Loc) is NULL ) then 1
end = 1 )
--and (xglcc.segment3 IN (:Gac) OR COALESCE(:Gac,null) IS Null)
AND ( case when xglcc.segment3 in (:P_Gac) then 1
when (COALESCE(NULL,:P_Gac) is NULL ) then 1
end = 1 )
--and (xglcc.segment4 IN (:Lac) OR COALESCE(:Lac,null) IS Null)
AND ( case when xglcc.segment4 in (:P_Lac) then 1
when (COALESCE(NULL,:P_Lac) is NULL ) then 1
end = 1 )
--and (xglcc.segment5 IN (:Dept) OR COALESCE(:Dept,null) IS Null)
AND ( case when xglcc.segment5 in (:P_Dept) then 1
when (COALESCE(NULL,:P_Dept) is NULL ) then 1
end = 1 )
--and (xglcc.segment6 IN (:BU) OR COALESCE(:BU,null) IS Null)
AND ( case when xglcc.segment6 in (:P_BU) then 1
when (COALESCE(NULL,:P_BU) is NULL ) then 1
end = 1 )
--and (xglcc.segment7 IN (:Ico) OR COALESCE(:Ico,null) IS Null)
AND ( case when xglcc.segment7 in (:P_Ico) then 1
when (COALESCE(NULL,:P_Ico) is NULL ) then 1
end = 1 )
--and (xglcc.segment8 IN (:Program) OR COALESCE(:Program,null) IS Null)
AND ( case when xglcc.segment8 in (:P_Program) then 1
when (COALESCE(NULL,:P_Program) is NULL ) then 1
end = 1 )
--and (xglcc.concatenated_segments IN (:ACCT_CD_COMB) OR COALESCE(:ACCT_CD_COMB,null) IS Null)
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,
(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 GL_PERIODS gjh
-- -- -- --apps.xxon_gl_closing_calendar gjh
-- -- -- where 1=1
-- -- -- -- %IF 'amp;PARM01' = '' %THENDO;
-- -- -- -- %ELSE;
-- -- -- -- and (gjh.period_name amp;SPARM01)
-- -- -- -- %ENDIF;
-- -- -- ) wd,
gl_lookups gll
WHERE 1=1
and 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 trunc(SYSDATE) BETWEEN xev.effective_start_date (+) AND xev.effective_end_date (+)
and Fu.person_id=xev.person_id (+)
AND gjb.approver_employee_id = xev.person_id (+)
and jscg.je_source_name = gjh.je_source
and jscg.je_category_name = gjh.je_category
--and gjb.name='Receipt Accounting A 7810618000001 7810617 Y 1'
-- --and gl.name like '%USD PL'
-- -- -- and wd.fiscal_date(+) = trunc(gjb.posted_date)
-- -- -- AND wd.Period_name(+) = gjh.Period_name
-- -- -- AND Wf.Batch_Id(+) LIKE nvl(gjb.parent_je_batch_id,Gjb.Je_Batch_Id) || '*' || '%'
-- -- -- AND onwf.batch_id(+) = nvl(gjb.parent_je_batch_id,Gjb.Je_Batch_Id)
and nvl(gjh.attribute9, jscg.je_class_rpt_group) =
y.je_classification(+)
-- and (gjh.period_name IN (:Period_name) OR COALESCE(:Period_name,null) IS Null)
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 (gl.name IN (:GL_name) OR COALESCE(:GL_name,null) IS Null)
AND ( case when gl.name in (:P_GL_name) then 1
when (COALESCE(NULL,:P_GL_name) is NULL ) then 1
end = 1 )
--and (fu.username IN (:Created_by) OR COALESCE(:Created_by,null) IS Null)
AND ( case when fu.username in (:P_Created_by) then 1
when (COALESCE(NULL,:P_Created_by) is NULL ) then 1
end = 1 )
--and (gjb.name IN (:Batch_name) OR COALESCE(:Batch_name,null) IS Null)
AND ( case when gjb.name in (:P_Batch_name) then 1
when (COALESCE(NULL,:P_Batch_name) is NULL ) then 1
end = 1 )
-- and (gjh.name IN (:Ledger_name) OR COALESCE(:Ledger_name,null) IS Null)
AND ( case when gjh.name in (:P_Ledger_name) then 1
when (COALESCE(NULL,:P_Ledger_name) is NULL ) then 1
end = 1 )
-- and (gjb.status IN (:posted_status) OR COALESCE(:posted_status,null) IS Null)
AND ( case when gjb.status in (:P_posted_status) then 1
when (COALESCE(NULL,:P_posted_status) is NULL ) then 1
end = 1 )
-- and (gjh.currency_code IN (:CY_CD) OR COALESCE(:CY_CD,null) IS Null)
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 (gll.lookup_code IN (:APPROVAL_STATUS) OR COALESCE(:APPROVAL_STATUS,null) IS Null)
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(+) = nvl(gjb.created_by,gjh.created_by)
and fu.Active_flag(+)='Y'
and gll.lookup_type = 'JE_BATCH_APPROVAL_STATUS'
and gll.lookup_code = gjb.approval_status_code
)
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 )
--and (je_class_rpt_group IN (:JE_CLASS_GRP) OR COALESCE(:JE_CLASS_GRP,null) IS Null)
Order by co,
je_header_id,
je_line
-------GL OnSemi Intercompany Debit Credit Memo ReportGL OnSemi Intercompany Debit Credit Memo Report
select sum(dist_local_amt) TotalLocalAmt,sum(dist_entered_amt) TotalUsd,sum(dist_qty) TotalQty ,trx_id from (SELECT fnd.description dist_description,
1 dist_qty,
NVL(ROUND(((NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) * (select gld.conversion_rate from gl_daily_rates gld
where fnb.batch_date = gld.conversion_date
and gll.currency_code = gld.from_currency
and gld.conversion_type = 'Corporate'
AND (SELECT distinct(TARGET_CURRENCY_CODE) FROM GL_LEDGER_RELATIONSHIPS GLR WHERE GLR.TARGET_LEDGER_CATEGORY_CODE='SECONDARY'
AND GLR.SOURCE_LEDGER_ID = fnb.from_ledger_id and rownum = 1) = gld.to_currency)),2),NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) dist_local_amt,
NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0) dist_entered_amt,
fnd.trx_id ,
(select gld.conversion_rate from gl_daily_rates gld
where fnb.batch_date = gld.conversion_date
and gll.currency_code = gld.from_currency
and gld.conversion_type = 'Corporate'
AND (SELECT distinct(TARGET_CURRENCY_CODE) FROM GL_LEDGER_RELATIONSHIPS GLR WHERE GLR.TARGET_LEDGER_CATEGORY_CODE='SECONDARY'
AND GLR.SOURCE_LEDGER_ID = fnb.from_ledger_id and rownum = 1) = gld.to_currency) as converstion_rate
FROM FUN_DIST_LINES fnd,
fun_trx_headers fnh,
fun_trx_batches fnb,
gl_ledgers gll
WHERE fnd.party_type_flag = 'I'
AND fnd.dist_type_flag = 'L'
AND fnd.trx_id = fnh.trx_id
AND fnh.batch_id = fnb.batch_id
AND fnb.from_ledger_id = gll.ledger_id)
GROUP BY trx_id
**SELECT fnd.description dist_description,
1 dist_qty,
NVL(ROUND(((NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) * (select gld.conversion_rate from gl_daily_rates gld
where fnb.batch_date = gld.conversion_date
and gll.currency_code = gld.from_currency
and gld.conversion_type = 'Corporate'
AND (SELECT distinct(TARGET_CURRENCY_CODE) FROM GL_LEDGER_RELATIONSHIPS GLR WHERE GLR.TARGET_LEDGER_CATEGORY_CODE='SECONDARY'
AND GLR.SOURCE_LEDGER_ID = fnb.from_ledger_id and rownum = 1) = gld.to_currency)),2),NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) dist_local_amt,
NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0) dist_entered_amt,
fnd.trx_id ,
(select gld.conversion_rate from gl_daily_rates gld
where fnb.batch_date = gld.conversion_date
and gll.currency_code = gld.from_currency
and gld.conversion_type = 'Corporate'
AND (SELECT distinct(TARGET_CURRENCY_CODE) FROM GL_LEDGER_RELATIONSHIPS GLR WHERE GLR.TARGET_LEDGER_CATEGORY_CODE='SECONDARY'
AND GLR.SOURCE_LEDGER_ID = fnb.from_ledger_id and rownum = 1) = gld.to_currency) as converstion_rate
FROM FUN_DIST_LINES fnd,
fun_trx_headers fnh,
fun_trx_batches fnb,
gl_ledgers gll
WHERE fnd.party_type_flag = 'I'
AND fnd.dist_type_flag = 'L'
AND fnd.trx_id = fnh.trx_id
AND fnh.batch_id = fnb.batch_id
AND fnb.from_ledger_id = gll.ledger_id
***select
fth.trx_id ,
ftb.batch_number||'-'||fth.trx_number transaction_number,
ihzp.INTERCO_ORG_NAME initiator_party_name,
rhzp.INTERCO_ORG_NAME recipient_party_name,
DECODE(SUBSTR(ihzp.INTERCO_ORG_NAME,1,3)
, '572', REPLACE(InitrLERegNum.REGISTRATION_NUMBER,'-')
, '577','GST Reg No: '||InitrLERegNum.REGISTRATION_NUMBER
, '579','GST Reg No: '||InitrLERegNum.REGISTRATION_NUMBER
, '681','ABN: '||InitrLERegNum.REGISTRATION_NUMBER
, (case when UPPER(substr(InitrLERegNum.REGISTRATION_NUMBER,1,3))='VAT' then InitrLERegNum.REGISTRATION_NUMBER else 'VAT:'||InitrLERegNum.REGISTRATION_NUMBER
end)) init_vat_reg_num ,
CASE
WHEN (SUBSTR(rhzp.INTERCO_ORG_NAME,1,3)='572')
THEN REPLACE(RcvrLERegNum.REGISTRATION_NUMBER,'-')
WHEN (SUBSTR(rhzp.INTERCO_ORG_NAME,1,3) IN ('577','579'))
THEN 'GST Reg No: '||RcvrLERegNum.REGISTRATION_NUMBER
WHEN (SUBSTR(rhzp.INTERCO_ORG_NAME,1,3) IN ('681'))
THEN 'ABN: '||RcvrLERegNum.REGISTRATION_NUMBER
WHEN ( (upper(RcvrLERegNum.REGISTRATION_NUMBER) Not like '%VAT%')
AND (upper(RcvrLERegNum.REGISTRATION_NUMBER) Not like '%GST%')
AND (SUBSTR(rhzp.INTERCO_ORG_NAME,1,3) NOT IN ('572', '574', '577', '579', '681'))
)
THEN 'VAT: '||RcvrLERegNum.REGISTRATION_NUMBER
ELSE RcvrLERegNum.REGISTRATION_NUMBER
END recip_vat_reg_num ,
Case WHEN SUBSTR(ihzp.INTERCO_ORG_NAME,1,3) In (Select Fv.Flex_Value
From Fnd_Flex_Value_Sets Fvs, Fnd_Flex_Values Fv
Where Fvs.Flex_Value_Set_Name = 'XXON_LE_OU_LEVEL'
And Fvs.Flex_Value_Set_Id = Fv.Flex_Value_Set_Id)
Then 'N'
Else 'Y'
END display_ou_code,
Case
When SUBSTR(rhzp.INTERCO_ORG_NAME,1,3) In (Select Fv.Flex_Value
From Fnd_Flex_Value_Sets Fvs, Fnd_Flex_Values Fv
Where Fvs.Flex_Value_Set_Name = 'XXON_LE_OU_LEVEL'
And Fvs.Flex_Value_Set_Id = Fv.Flex_Value_Set_Id)
THEN 'N'
Else 'Y'
END display_ou_code1,
Case
When SUBSTR(ihzp.INTERCO_ORG_NAME,1,3) In ('572', '577', '579', '681')
THEN 'TAX INVOICE'
Else 'INVOICE'
END INV_TYPE ,
ftb.note vat_para,
ftb.currency_code ENTERED_CURR,
(SELECT Sum(gl_currency_api.convert_amount (ftb.currency_code,
'USD',
ftb.gl_date,
ftb.exchange_rate_type,
Nvl(ftl.reci_amount_cr,ftl.reci_amount_dr) )) total_amount
FROM fun_trx_lines ftl
WHERE ftl.trx_id = fth.trx_id ) total_amount,
(select distinct sqn.DOC_SEQUENCE_VALUE from gl_je_headers gjh,GL_DOC_SEQUENCE_AUDIT sqn where gjh.ledger_id = ftb.from_ledger_id
and gjh.DOC_SEQUENCE_ID = sqn.DOC_SEQUENCE_ID
and gjh.LEGAL_ENTITY_ID = ftb.from_le_id or gjh.LEGAL_ENTITY_ID = fth.TO_LE_ID and rownum=1) document_number ,
SUBSTR(ihzp.INTERCO_ORG_NAME,1,3) OU_CODE,
SUBSTR(rhzp.INTERCO_ORG_NAME,1,3) OU_CODE1,
ihl.address_line_1 i_address_line_1,
ihl.address_line_2 i_address_line_2,
ihl.address_line_3 i_address_line_3,
ihl.town_or_city i_town_or_city,
ihl.postal_code i_postal_code,
ift.territory_short_name i_country,
PO_BIP_HELPER.get_formatted_address(ihl.address_line_1,ihl.address_line_2,ihl.address_line_3,ihl.town_or_city,ihl.postal_code,ift.territory_short_name,'','','','','','','','','','','','N') i_address_formatted,
rhl.address_line_1 r_address_line_1,
rhl.address_line_2 r_address_line_2,
rhl.address_line_3 r_address_line_3,
rhl.town_or_city r_town_or_city,
rhl.postal_code r_postal_code,
rft.territory_short_name r_country,
PO_BIP_HELPER.get_formatted_address(rhl.address_line_1,rhl.address_line_2,rhl.address_line_3,rhl.town_or_city,rhl.postal_code,rft.territory_short_name,'','','','','','','','','','','','N') r_address_formatted,
ftb.gl_date curr_date,
DECODE(SUBSTR(ihzp.INTERCO_ORG_NAME,1,3),'331','Company Identification No. 45193533, registerd ar Regional Court in Ostrava on 29th April 1992 under Rg B331', NULL) TAX_REG_TEXT,
(select gld.to_currency from gl_daily_rates gld,gl_ledgers gll
where ftb.batch_date = gld.conversion_date
and gll.currency_code = gld.from_currency
and gld.conversion_type = 'Corporate'
and ftb.from_ledger_id = gll.ledger_id
AND (SELECT distinct(TARGET_CURRENCY_CODE) FROM GL_LEDGER_RELATIONSHIPS GLR WHERE GLR.TARGET_LEDGER_CATEGORY_CODE='SECONDARY'
AND GLR.SOURCE_LEDGER_ID = ftb.from_ledger_id and rownum = 1 ) = gld.to_currency ) LOCAL_CURR,
ihzp.PAY_BU_ID ,
iorg.ORGANIZATION_ID,
ftb.from_ledger_id,
ftb.from_le_id,
:P_TERMS AS Payment_terms
FROM
fun_trx_batches ftb,
fun_trx_headers fth ,
FUN_INTERCO_ORGANIZATIONS ihzp,
FUN_INTERCO_ORGANIZATIONS rhzp ,
--xle_entity_profiles ixep ,
XLE_REGISTRATIONS InitrLERegNum,
--xle_entity_profiles rxep ,
XLE_REGISTRATIONS RcvrLERegNum ,
HR_ORGANIZATION_V iorg ,
HR_ORGANIZATION_V rorg,
HR_LOCATIONS_ALL ihl,
fnd_territories_vl ift,
HR_LOCATIONS_ALL rhl,
fnd_territories_vl rft
where
ftb.batch_id = fth.batch_id
and fth.initiator_id = ihzp.INTERCO_ORG_ID
and fth.RECIPIENT_ID = rhzp.INTERCO_ORG_ID
ANd ftb.from_le_id = InitrLERegNum.SOURCE_ID
AnD fth.TO_LE_ID = RcvrLERegNum.SOURCE_ID
--AND SUBSTR(ihzp.INTERCO_ORG_NAME,1,3) in ('572','577','579','681')
--AND ixep.LEGAL_ENTITY_ID=InitrLERegNum.SOURCE_ID
--AND rxep.LEGAL_ENTITY_ID=RcvrLERegNum.SOURCE_ID
AND ihzp.PAY_BU_ID = iorg.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN iorg.EFFECTIVE_START_DATE AND iorg.EFFECTIVE_END_DATE
AND iorg.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND rhzp.REC_BU_ID = rorg.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN rorg.EFFECTIVE_START_DATE AND rorg.EFFECTIVE_END_DATE
AND rorg.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND iorg.LOCATION_ID = ihl.LOCATION_ID
AND rorg.LOCATION_ID = rhl.location_id
AND ihl.country = ift.territory_code (+)
AND rhl.country = rft.territory_code (+)
AND (ihzp.INTERCO_ORG_NAME = :P_I_ORG OR :P_I_ORG IS NULL)
AND (rhzp.INTERCO_ORG_NAME = :P_R_ORG OR :P_R_ORG IS NULL)
--AND (ftb.gl_date >= :P_START_DATE or :P_START_DATE IS NULL)
--AND (ftb.gl_date <= :P_END_DATE or :P_END_DATE IS NULL)
AND (ftb.batch_number = :P_B_NUM OR :P_B_NUM IS NULL)
--AND ftb.batch_number = '2215'
--AND fth.trx_id=300000518660784
--AND fth.trx_id= 300000520204496
AND trunc(ftb.gl_date) BETWEEN (NVL(:P_START_DATE,ftb.gl_date))
AND (NVL(:P_END_DATE,ftb.gl_date))
ORDER BY ftb.gl_date,ftb.batch_number,fth.trx_number
No comments:
Post a Comment