General Ledger - Oracle EBS R12: Data Transferred from XLA to GL (Query)

http://generalledger-gl.blogspot.com/2016/07/gl-tables-and-xla-tables-match-on.html

PLSQL to Get Data Transferred from XLA to GL

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

Tuesday, 12 July 2016

Data Transferred from XLA to GL (Query) - Oracle EBS R12

No comments:

Post a Comment