General Ledger - Oracle EBS R12: XLA to GL Transffered Category, Source & Account Wise Reconcilliation

https://generalledger-gl.blogspot.com/2017/10/xla-to-gl-transffered-category-wise.html

PLSQL Query to Get XLA to GL Transferred Category, Source & Account Wise Reconciliation

CATEGORY WISE

select /*

pp.Acv Category,

NVL(pp.IN_XLA, 0) XLA_AE_LINES_A,

NVL(mm.IN_GL_IMPORT, 0) GL_IMPOERT_REFERENCES_B,

nvl(pp.IN_XLA, 0) - nvl(mm.IN_GL_IMPORT, 0) Difference_AB,

NVL(cc. IN_GL_INTERFACE, 0) GL_INTERFACE_C,

nvl(qq.XLA_TO_GL, 0) XLA_TO_GL_Transferred_D,

nvl(mm.IN_GL_IMPORT - qq.XLA_TO_GL, 0) Difeerence_BD,

NVL(bb.POSTED_IN_GL, 0) POSTED_IN_GL,

NVL(ss.Reversed_in_GL, 0) Reversed_In_GL,

NVL(qq.XLA_TO_GL, 0) - NVL(bb.POSTED_IN_GL, 0) UNPOSTED_IN_GL

*/

pp.Acv Category,

pp.IN_XLA_DEBIT,

pp.IN_XLA_CREDIT,

mm.IN_GL_IMPORT_DEBIT,

mm.IN_GL_IMPORT_CREDIT,

nvl(pp.IN_XLA_DEBIT, 0) - nvl(mm.IN_GL_IMPORT_DEBIT, 0) XLA_IMPORT_DEBIT_DIFF,

nvl(pp.IN_XLA_CREDIT, 0) - nvl(mm.IN_GL_IMPORT_CREDIT, 0) XLA_IMPORT_CREDIT_DIFF,

cc.IN_GL_INTERFACE_DEBIT,

cc.IN_GL_INTERFACE_CREDIT,

qq.XLA_TO_GL_DEBIT,

qq.XLA_TO_GL_CREDIT,

bb.POSTED_IN_GL_DEBIT,

bb.POSTED_IN_GL_CREDIT,

ss.Reversed_in_GL_Debit,

ss.Reversed_in_GL_Credit,

NVL(qq.XLA_TO_GL, 0) - NVL(bb.POSTED_IN_GL, 0) UNPOSTED_IN_GL

from (select glc.user_je_category_name Acv, --xe.JE_CATEGORY_NAME Acv,

NVL(sum(xa.ACCOUNTED_DR), 0) IN_XLA_DEBIT,

NVL(sum(xa.ACCOUNTED_CR), 0) IN_XLA_CREDIT,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_XLA

from xla_ae_lines xa,
xla_ae_headers xe,
gl_code_combinations gcc,
gl_je_categories glc

-- gl_import_references gi

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

-- and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

--- and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and glc.je_category_name = xe.JE_CATEGORY_NAME

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

--and NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by --xe.JE_CATEGORY_NAME

glc.user_je_category_name) pp,

(select glc.user_je_category_name Account, --xe.JE_CATEGORY_NAME Account,

NVL(sum(xa.ACCOUNTED_DR), 0) IN_GL_IMPORT_DEBIT,

NVL(sum(xa.ACCOUNTED_CR), 0) IN_Gl_IMPORT_CREDIT,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_GL_IMPORT

from xla_ae_lines xa,

xla_ae_headers xe,

gl_code_combinations gcc,

gl_import_references gi,

gl_je_categories glc

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and glc.je_category_name = xe.JE_CATEGORY_NAME

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

--and NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by --xe.JE_CATEGORY_NAME

glc.user_je_category_name) mm,

(SELECT tt.USER_JE_CATEGORY_NAME Acx,

NVL(sum(tt.ACCOUNTED_DR), 0) IN_GL_INTERFACE_DEBIT,

NVL(sum(tt.ACCOUNTED_CR), 0) IN_GL_INTERFACE_CREDIT,

NVL(sum(tt.ACCOUNTED_DR), 0) - NVL(sum(tt.ACCOUNTED_CR), 0) IN_GL_INTERFACE

FROM gl_interface tt, gl_code_combinations gc

WHERE tt.CODE_COMBINATION_ID = gc.CODE_COMBINATION_ID

and tt.LEDGER_ID = NVL('&Ledger_id', tt.LEDGER_ID)

AND (USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME, LEDGER_ID,

ACTUAL_FLAG, PERIOD_NAME, GL_SL_LINK_ID) NOT IN -- 413

(SELECT

H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

A.GL_SL_LINK_ID

FROM (SELECT *

FROM gl_interface

WHERE

LEDGER_ID = NVL('&Ledger_id', LEDGER_ID)

AND ACTUAL_FLAG = 'A'

) A,

(SELECT H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

gll.CODE_COMBINATION_ID,

ael.ENTERED_DR,

ael.ENTERED_CR,

ael.gl_sl_link_id,

H.NAME,

H.DEFAULT_EFFECTIVE_DATE,

H.JE_HEADER_ID,

H.DESCRIPTION

FROM GL_JE_HEADERS H,

GL_JE_LINES gll,

XLA_AE_HEADERS aeh,

XLA_AE_LINES ael,

XLA_EVENTS aea,

GL_IMPORT_REFERENCES gir

WHERE 1 = 1

AND H.JE_HEADER_ID = gll.JE_HEADER_ID

AND ael.gl_sl_link_id = gir.gl_sl_link_id

AND aea.event_id = aeh.event_id

AND aeh.ae_header_id = ael.ae_header_id

AND gll.je_header_id = gir.je_header_id

AND gll.je_line_num = gir.je_line_num

AND H.LEDGER_ID = NVL('&Ledger_id', H.LEDGER_ID)

AND H.ACTUAL_FLAG = 'A'

) H

WHERE 1 = 1

AND H.JE_SOURCE = A.USER_JE_SOURCE_NAME

AND H.JE_CATEGORY = A.USER_JE_CATEGORY_NAME

AND H.LEDGER_ID = A.LEDGER_ID

AND H.ACTUAL_FLAG = A.ACTUAL_FLAG

AND H.PERIOD_NAME = A.PERIOD_NAME

AND H.GL_SL_LINK_ID = A.GL_SL_LINK_ID

)

group by tt.USER_JE_CATEGORY_NAME) cc

,

(select glc.user_je_category_name Acc, --hdr.JE_CATEGORY Acc,

NVL(sum(ael.ACCOUNTED_DR), 0) XLA_To_GL_DEBIT,

NVL(sum(ael.ACCOUNTED_CR), 0) XLA_To_GL_CREDIT,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) XLA_TO_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

--and NVL('&To_Account', gcc.SEGMENT4)

group by glc.user_je_category_name) qq,

(select glc.user_je_category_name Acco, --hdr.JE_CATEGORY Acco,

NVL(sum(ael.ACCOUNTED_DR), 0) POSTED_IN_GL_DEBIT,

NVL(sum(ael.ACCOUNTED_CR), 0) POSTED_IN_GL_CREDIT,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) POSTED_IN_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

and hdr.status = 'P'

and ln.status = 'P'

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

-- and NVL('&To_Account', gcc.SEGMENT4)

group by glc.user_je_category_name) bb,

(select glc.user_je_category_name Ack, --nn.JE_CATEGORY Ack,

NVL(sum(jj.ACCOUNTED_DR), 0) Reversed_in_GL_Debit,

NVL(sum(jj.ACCOUNTED_CR), 0) Reversed_in_GL_Credit,

NVL(sum(jj.ACCOUNTED_DR), 0) - NVL(sum(jj.ACCOUNTED_CR), 0) Reversed_in_GL

from gl_je_lines jj,
gl_code_combinations ll,
gl_je_headers nn,
gl_je_categories glc

where jj.CODE_COMBINATION_ID = ll.CODE_COMBINATION_ID

and jj.JE_HEADER_ID = nn.JE_HEADER_ID

and glc.je_category_name = nn.JE_CATEGORY

and jj.LEDGER_ID = NVL('&Ledger_id', jj.LEDGER_ID)

and nn.ACTUAL_FLAG = 'A'

--and jj.STATUS='P'and nn.STATUS='P'

-- and ll.SEGMENT4 between NVL('&From_Account', ll.SEGMENT4)

--and NVL('&To_Account', ll.SEGMENT4)

and nn.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and nn.REVERSED_JE_HEADER_ID is not null

group by glc.user_je_category_name) ss

where pp.Acv = mm.Account(+)

and mm.Account = qq.Acc(+)

and pp.Acv = cc.Acx(+)

and bb.Acco(+) = mm.Account

and mm.Account = ss.Ack(+)

order by Acv

#############################################################################

SOURCE WISE

select

/* pp.Acv Category,

NVL(pp.IN_XLA, 0) XLA_AE_LINES_A,

NVL(mm.IN_GL_IMPORT, 0) GL_IMPOERT_REFERENCES_B,

nvl(pp.IN_XLA, 0) - nvl(mm.IN_GL_IMPORT, 0) Difference_AB,

NVL(cc. IN_GL_INTERFACE, 0) GL_INTERFACE_C,

nvl(qq.XLA_TO_GL, 0) XLA_TO_GL_Transferred_D,

nvl(mm.IN_GL_IMPORT - qq.XLA_TO_GL, 0) Difeerence_BD,

NVL(bb.POSTED_IN_GL, 0) POSTED_IN_GL,

NVL(ss.Reversed_in_GL, 0) Reversed_In_GL,

NVL(qq.XLA_TO_GL, 0) - NVL(bb.POSTED_IN_GL, 0) UNPOSTED_IN_GL*/

pp.Acv Source,

pp.IN_XLA_DEBIT,

pp.IN_XLA_CREDIT,

mm.IN_GL_IMPORT_DEBIT,

mm.IN_GL_IMPORT_CREDIT,

nvl(pp.IN_XLA_DEBIT, 0) - nvl(mm.IN_GL_IMPORT_DEBIT, 0) XLA_IMPORT_DEBIT_DIFF,

nvl(pp.IN_XLA_CREDIT, 0) - nvl(mm.IN_GL_IMPORT_CREDIT, 0) XLA_IMPORT_CREDIT_DIFF,

cc.IN_GL_INTERFACE_DEBIT,

cc.IN_GL_INTERFACE_CREDIT,

qq.XLA_TO_GL_DEBIT,

qq.XLA_TO_GL_CREDIT,

bb.POSTED_IN_GL_DEBIT,

bb.POSTED_IN_GL_CREDIT,

ss.Reversed_in_GL_Debit,

ss.Reversed_in_GL_Credit,

NVL(qq.XLA_TO_GL, 0) - NVL(bb.POSTED_IN_GL, 0) UNPOSTED_IN_GL

from (select v.APPLICATION_NAME Acv, --xe.JE_CATEGORY_NAME Acv,

NVL(sum(xa.ACCOUNTED_DR), 0) IN_XLA_DEBIT,

NVL(sum(xa.ACCOUNTED_CR), 0) IN_XLA_CREDIT,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_XLA

from xla_ae_lines xa,
xla_ae_headers xe,
gl_code_combinations gcc,
gl_je_categories glc,
fnd_application_vl v

-- gl_import_references gi

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

-- and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

--- and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and glc.je_category_name = xe.JE_CATEGORY_NAME

and v.APPLICATION_ID = xe.APPLICATION_ID

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

--and NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by --xe.JE_CATEGORY_NAME

v.APPLICATION_NAME) pp,

(select v.APPLICATION_NAME Account, --xe.JE_CATEGORY_NAME Account,

NVL(sum(xa.ACCOUNTED_DR), 0) IN_GL_IMPORT_DEBIT,

NVL(sum(xa.ACCOUNTED_CR), 0) IN_Gl_IMPORT_CREDIT,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_GL_IMPORT

from xla_ae_lines xa,

xla_ae_headers xe,

gl_code_combinations gcc,

gl_import_references gi,

gl_je_categories glc,
fnd_application_vl v

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and glc.je_category_name = xe.JE_CATEGORY_NAME

and v.APPLICATION_ID = xe.APPLICATION_ID

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

--and NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by --xe.JE_CATEGORY_NAME

v.APPLICATION_NAME) mm,

(SELECT tt.USER_JE_SOURCE_NAME Acx,

NVL(sum(tt.ACCOUNTED_DR), 0) IN_GL_INTERFACE_DEBIT,

NVL(sum(tt.ACCOUNTED_CR), 0) IN_GL_INTERFACE_CREDIT,

NVL(sum(tt.ACCOUNTED_DR), 0) - NVL(sum(tt.ACCOUNTED_CR), 0) IN_GL_INTERFACE

FROM gl_interface tt, gl_code_combinations gc

WHERE tt.CODE_COMBINATION_ID = gc.CODE_COMBINATION_ID

and tt.LEDGER_ID = NVL('&Ledger_id', tt.LEDGER_ID)

AND (USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME, LEDGER_ID,

ACTUAL_FLAG, PERIOD_NAME, GL_SL_LINK_ID) NOT IN -- 413

(SELECT

H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

A.GL_SL_LINK_ID

FROM (SELECT *

FROM gl_interface

WHERE

LEDGER_ID = NVL('&Ledger_id', LEDGER_ID)

AND ACTUAL_FLAG = 'A'

) A,

(SELECT H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

gll.CODE_COMBINATION_ID,

ael.ENTERED_DR,

ael.ENTERED_CR,

ael.gl_sl_link_id,

H.NAME,

H.DEFAULT_EFFECTIVE_DATE,

H.JE_HEADER_ID,

H.DESCRIPTION

FROM GL_JE_HEADERS H,

GL_JE_LINES gll,

XLA_AE_HEADERS aeh,

XLA_AE_LINES ael,

XLA_EVENTS aea,

GL_IMPORT_REFERENCES gir

WHERE 1 = 1

AND H.JE_HEADER_ID = gll.JE_HEADER_ID

AND ael.gl_sl_link_id = gir.gl_sl_link_id

AND aea.event_id = aeh.event_id

AND aeh.ae_header_id = ael.ae_header_id

AND gll.je_header_id = gir.je_header_id

AND gll.je_line_num = gir.je_line_num

AND H.LEDGER_ID = NVL('&Ledger_id', H.LEDGER_ID)

AND H.ACTUAL_FLAG = 'A'

) H

WHERE 1 = 1

AND H.JE_SOURCE = A.USER_JE_SOURCE_NAME

AND H.JE_CATEGORY = A.USER_JE_CATEGORY_NAME

AND H.LEDGER_ID = A.LEDGER_ID

AND H.ACTUAL_FLAG = A.ACTUAL_FLAG

AND H.PERIOD_NAME = A.PERIOD_NAME

AND H.GL_SL_LINK_ID = A.GL_SL_LINK_ID

)

group by tt.USER_JE_SOURCE_NAME) cc

,

(select hdr.JE_SOURCE Acc, --hdr.JE_CATEGORY Acc,

NVL(sum(ael.ACCOUNTED_DR), 0) XLA_To_GL_DEBIT,

NVL(sum(ael.ACCOUNTED_CR), 0) XLA_To_GL_CREDIT,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) XLA_TO_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

--and NVL('&To_Account', gcc.SEGMENT4)

group by hdr.JE_SOURCE) qq,

(select hdr.JE_SOURCE Acco, --hdr.JE_CATEGORY Acco,

NVL(sum(ael.ACCOUNTED_DR), 0) POSTED_IN_GL_DEBIT,

NVL(sum(ael.ACCOUNTED_CR), 0) POSTED_IN_GL_CREDIT,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) POSTED_IN_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

and hdr.status = 'P'

and ln.status = 'P'

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

-- and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4)

-- and NVL('&To_Account', gcc.SEGMENT4)

group by hdr.JE_SOURCE) bb,

(select nn.JE_SOURCE Ack, --nn.JE_CATEGORY Ack,

NVL(sum(jj.ACCOUNTED_DR), 0) Reversed_in_GL_Debit,

NVL(sum(jj.ACCOUNTED_CR), 0) Reversed_in_GL_Credit,

NVL(sum(jj.ACCOUNTED_DR), 0) - NVL(sum(jj.ACCOUNTED_CR), 0) Reversed_in_GL

from gl_je_lines jj,
gl_code_combinations ll,
gl_je_headers nn,
gl_je_categories glc

where jj.CODE_COMBINATION_ID = ll.CODE_COMBINATION_ID

and jj.JE_HEADER_ID = nn.JE_HEADER_ID

and glc.je_category_name = nn.JE_CATEGORY

and jj.LEDGER_ID = NVL('&Ledger_id', jj.LEDGER_ID)

and nn.ACTUAL_FLAG = 'A'

--and jj.STATUS='P'and nn.STATUS='P'

-- and ll.SEGMENT4 between NVL('&From_Account', ll.SEGMENT4)

--and NVL('&To_Account', ll.SEGMENT4)

and nn.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and nn.REVERSED_JE_HEADER_ID is not null

group by nn.JE_SOURCE) ss

where pp.Acv = mm.Account(+)

and mm.Account = qq.Acc(+)

and pp.Acv = cc.Acx(+)

and bb.Acco(+) = mm.Account

and mm.Account = ss.Ack(+)

order by Acv

#############################################################################

ACCOUNT WISE

select pp.Acv Account,

NVL(pp.IN_XLA, 0) XLA_AE_LINES_A,

NVL(mm.IN_GL_IMPORT, 0) GL_IMPOERT_REFERENCES_B,

nvl(pp.IN_XLA, 0) - nvl(mm.IN_GL_IMPORT, 0) Difference_AB,

NVL(cc. IN_GL_INTERFACE, 0) GL_INTERFACE_C,

nvl(qq.XLA_TO_GL, 0) XLA_TO_GL_Transferred_D,

nvl(mm.IN_GL_IMPORT - qq.XLA_TO_GL, 0) Difeerence_BD,

NVL(bb.POSTED_IN_GL, 0) POSTED_IN_GL,

NVL(ss.Reversed_in_GL, 0) Reversed_In_GL,

NVL(qq.XLA_TO_GL, 0) - NVL(bb.POSTED_IN_GL, 0) UNPOSTED_IN_GL

-- ww.ala + qq.XLA_TO_GL - ww.ala IN_GL

/*,ww.ala+qq.XLA_TO_GL-ww.ala IN_GL,qq.XLA_TO_GL-ww.ala Difference,bb.fad Reversal, zz.nn Manual_ENT_in_GL , ww.ala+zz.nn Ending_balance

*/

from (select gcc.segment4 Acv,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_XLA

from xla_ae_lines xa, xla_ae_headers xe, gl_code_combinations gcc

-- gl_import_references gi

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

-- and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

--- and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by gcc.segment4) pp,

(select gcc.segment4 Account,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_GL_IMPORT

from xla_ae_lines xa,

xla_ae_headers xe,

gl_code_combinations gcc,

gl_import_references gi

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by gcc.segment4) mm,

(SELECT gc.SEGMENT4 Acx,

NVL(sum(tt.ACCOUNTED_DR), 0) - NVL(sum(tt.ACCOUNTED_CR), 0) IN_GL_INTERFACE

FROM gl_interface tt, gl_code_combinations gc

WHERE tt.CODE_COMBINATION_ID = gc.CODE_COMBINATION_ID

and tt.LEDGER_ID = nvl('&Ledger_id', tt.LEDGER_ID)

--and USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices'

AND (USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME, LEDGER_ID,

ACTUAL_FLAG, PERIOD_NAME, GL_SL_LINK_ID) NOT IN -- 413

(SELECT

--H.NAME,

--H.DEFAULT_EFFECTIVE_DATE,

--H.DESCRIPTION,

--H.JE_HEADER_ID,

--A.JE_HEADER_ID,

H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

A.GL_SL_LINK_ID

--H.CODE_COMBINATION_ID,

--H.ENTERED_DR,

--H.ENTERED_CR

FROM (SELECT *

FROM gl_interface

WHERE --USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices'

LEDGER_ID = NVL('&Ledger_id', LEDGER_ID)

AND ACTUAL_FLAG = 'A' -- 783

) A,

(SELECT H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

gll.CODE_COMBINATION_ID,

ael.ENTERED_DR,

ael.ENTERED_CR,

ael.gl_sl_link_id,

H.NAME,

H.DEFAULT_EFFECTIVE_DATE,

H.JE_HEADER_ID,

H.DESCRIPTION

FROM GL_JE_HEADERS H,

GL_JE_LINES gll,

XLA_AE_HEADERS aeh,

XLA_AE_LINES ael,

XLA_EVENTS aea,

GL_IMPORT_REFERENCES gir

WHERE 1 = 1

AND H.JE_HEADER_ID = gll.JE_HEADER_ID

AND ael.gl_sl_link_id = gir.gl_sl_link_id

-- AND gir.gl_sl_link_table in ('APECL', 'XLAJEL','XLAJEL')

-- AND aea.application_id = 200

AND aea.event_id = aeh.event_id

AND aeh.ae_header_id = ael.ae_header_id

AND gll.je_header_id = gir.je_header_id

AND gll.je_line_num = gir.je_line_num

-- AND H.JE_SOURCE='Payables'

-- AND H.JE_CATEGORY='Purchase Invoices'

AND H.LEDGER_ID = NVL('&Ledger_id', H.LEDGER_ID)

AND H.ACTUAL_FLAG = 'A'

--AND H.DEFAULT_EFFECTIVE_DATE='20-JUL-2009'

--AND H.PERIOD_NAME='JUL-09'

--AND H.DESCRIPTION LIKE '%200900160%'

) H

WHERE 1 = 1

AND H.JE_SOURCE = A.USER_JE_SOURCE_NAME

AND H.JE_CATEGORY = A.USER_JE_CATEGORY_NAME

AND H.LEDGER_ID = A.LEDGER_ID

AND H.ACTUAL_FLAG = A.ACTUAL_FLAG

AND H.PERIOD_NAME = A.PERIOD_NAME

--AND H.CODE_COMBINATION_ID=A.CODE_COMBINATION_ID

--AND (H.ENTERED_DR=A.ENTERED_DR or H.ENTERED_CR=A.ENTERED_CR)

AND H.GL_SL_LINK_ID = A.GL_SL_LINK_ID

)

group by gc.SEGMENT4) cc

,

(select gcc.segment4 Acc,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) XLA_TO_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

-- and hdr.status = 'P'

--and ln.status = 'P'

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

group by gcc.segment4) qq,

(select gcc.segment4 Acco,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) POSTED_IN_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

and hdr.status = 'P'

and ln.status = 'P'

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

group by gcc.segment4) bb,

/*

(select gg.segment4 Aco,

NVL(sum(al.ACCOUNTED_DR)-sum(al.ACCOUNTED_CR), 0) ala

from gl_je_lines al, gl_code_combinations gg, gl_je_headers vv

where al.JE_HEADER_ID = vv.JE_HEADER_ID

and gg.CODE_COMBINATION_ID = al.CODE_COMBINATION_ID

-- and al.STATUS = 'P'

--and vv.STATUS = 'P'

and vv.ACTUAL_FLAG = 'A'

and gg.SEGMENT4 between NVL('&From_Account', gg.SEGMENT4) and

NVL('&To_Account', gg.SEGMENT4)

and vv.LEDGER_ID = al.LEDGER_ID

and vv.LEDGER_ID = '&Ledger_id'



and vv.JE_SOURCE not in

('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',

'Revaluation', 'Closing Journal')

group by gg.SEGMENT4



) ww,*/

(select ll.segment4 Ack,

NVL(sum(jj.ACCOUNTED_DR), 0) - NVL(sum(jj.ACCOUNTED_CR), 0) Reversed_in_GL

from gl_je_lines jj, gl_code_combinations ll, gl_je_headers nn

where jj.CODE_COMBINATION_ID = ll.CODE_COMBINATION_ID

and jj.JE_HEADER_ID = nn.JE_HEADER_ID

and jj.LEDGER_ID = NVL('&Ledger_id', jj.LEDGER_ID)

and nn.ACTUAL_FLAG = 'A'

--and jj.STATUS='P'and nn.STATUS='P'

and ll.SEGMENT4 between NVL('&From_Account', ll.SEGMENT4) and

NVL('&To_Account', ll.SEGMENT4)

and nn.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and nn.REVERSED_JE_HEADER_ID is not null

group by ll.SEGMENT4) ss

where pp.Acv = mm.Account(+)

-- and qq.Acc=pp.Acv

--and ww.Aco=pp.Acv

and mm.Account = qq.Acc(+)

and pp.Acv = cc.Acx(+)

--and mm.Account = ww.Aco

-- and qq.Acc = ww.Aco

and bb.Acco(+) = mm.Account

and mm.Account = ss.Ack(+)

General Ledger - Oracle EBS R12

Monday, 2 October 2017

XLA to GL Transffered Category, Source & Account Wise Reconcilliation - Oracle EBS R12

No comments:

Post a Comment