http://generalledger-gl.blogspot.com/2016/06/gl-and-subledgers-link-information-query.html
GL to Subledgers Link (Information Query)
/*select count(*)
from */--846187 --19339 --5831
select * from
(select
nvl(ael.ENTERED_DR,0) - nvl(ael.ENTERED_CR,0) Entered ,
nvl(ael.ACCOUNTED_DR , 0) - nvl( ael.ACCOUNTED_CR,0) Accounted,
hdr.JE_HEADER_ID,
ael.AE_HEADER_ID,
hdr.PERIOD_NAME Period_Name,
hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
hdr.NAME Journal_Name,
ln.DESCRIPTION GL_Description,
ael.DESCRIPTION Subledger_Description,
ln.JE_LINE_NUM GL_Line_Num,
ael.AE_LINE_NUM Subledger_Line_Number,
hdr.CURRENCY_CODE Currency,
ln.ENTERED_DR GL_Entered_DR,
ln.ENTERED_CR GL_Entered_CR,
ael.ENTERED_DR SUB_Enterred_DR,
ael.ENTERED_CR SUB_Enterred_CR,
ael.ENTERED_DR,
ael.ENTERED_CR,
-- hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
NVL( ael.CURRENCY_CONVERSION_RATE,1) Exch_Rate,
ael.ACCOUNTED_DR Subledger_Accounted_DT,
ael.ACCOUNTED_CR Subledger_Accounted_CR,
null GL_Accounted_DR,
null GL_Accounted_CR,
ael.ACCOUNTED_DR ,
ael.ACCOUNTED_CR,
hdr.JE_Source,
hdr.DOC_SEQUENCE_VALUE GL_Doc_Seq_Value,
glc.user_je_category_name JE_Category,
fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
aeh.DOC_SEQUENCE_VALUE Subledger_Doc_Seq_Value,
(select aia.ATTRIBUTE2
from ap_invoices_all aia
where aia.INVOICE_ID = ent.source_id_int_1
and ent.entity_code = 'AP_INVOICES'
and ent.application_id = 200
and aia.SET_OF_BOOKS_ID = :Ledger_id) PO_NUMBER,
(select pv.VENDOR_NAME from ap_invoices_all aia, po_vendors pv
where aia.VENDOR_ID=pv.VENDOR_ID
and aia.INVOICE_ID=ent.source_id_int_1 and ent.entity_code = 'AP_INVOICES'
and ent.application_id =200 and aia.SET_OF_BOOKS_ID=:Ledger_id-- and pv.SET_OF_BOOKS_ID='&Ledger_id'
and ent.ledger_id=:Ledger_id union
select pv.vendor_name from ap_checks_all aca, po_vendors pv
where aca.CHECK_ID=ent.source_id_int_1
and aca.VENDOR_ID=pv.VENDOR_ID and ent.entity_code = 'AP_PAYMENTS'
and ent.application_id=200 and ent.ledger_id=:Ledger_id) Supplier_Name, --and pv.SET_OF_BOOKS_ID='&Ledger_id'
/* (select fn.user_name
from fnd_user fn, ap_invoices_all ai
where ai.CREATED_BY = fn.USER_ID
and ai.INVOICE_ID = ent.source_id_int_1
and ent.entity_code = 'AP_INVOICES'
and ent.application_id = 200
and ai.SET_OF_BOOKS_ID = :Ledger_id) APN_USERNAME */
(select fn.user_name
from fnd_user fn, ap_invoices_all ai
where ai.CREATED_BY = fn.USER_ID
and ai.INVOICE_ID = ent.source_id_int_1
and ent.entity_code = 'AP_INVOICES'
and ent.application_id = 200
and ai.SET_OF_BOOKS_ID = :Ledger_id union
select fk.user_name
from fnd_user fk, ap_checks_all ac
where ac.CREATED_BY = fk.USER_ID
and ac.check_ID = ent.source_id_int_1
and ent.entity_code = 'AP_PAYMENTS'
and ent.application_id = 200
and ent.ledger_id = :Ledger_id ) AP_Creator
, NVL(ael.party_id,0) Party_id
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 = :Ledger_id
and ln.LEDGER_ID = :Ledger_id
and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
and gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
and gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
and gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
and gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)
union
select
nvl(ln.ENTERED_DR,0) - nvl(ln.ENTERED_CR,0) Entered ,
nvl(ln.ACCOUNTED_DR , 0) - nvl( ln.ACCOUNTED_CR,0) Accounted,
hdr.JE_HEADER_ID,
null,
hdr.PERIOD_NAME Period_Name,
hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
hdr.NAME Journal_Name,
ln.DESCRIPTION,
null,
ln.JE_LINE_NUM,
null,
hdr.CURRENCY_CODE Currency,
ln.ENTERED_DR,
ln.ENTERED_CR,
null,
null,
ln.ENTERED_DR,
ln.ENTERED_CR,
hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
null,
null,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
hdr.JE_Source,
hdr.DOC_SEQUENCE_VALUE,
glc.user_je_category_name JE_Category,
fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
null,
null,
null,
null,
nvl(null,0)
from gl_je_lines ln,
gl_code_combinations gcc,
gl_je_headers hdr,
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 hdr.JE_SOURCE in
('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
and ln.LEDGER_ID = :Ledger_id
and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
and gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
and gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
and gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
and gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)
union
select
nvl(ln.ENTERED_DR,0) - nvl(ln.ENTERED_CR,0) Entered ,
nvl(ln.ACCOUNTED_DR , 0) - nvl( ln.ACCOUNTED_CR,0) Accounted,
hdr.JE_HEADER_ID,
null,
hdr.PERIOD_NAME Period_Name,
hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
hdr.NAME Journal_Name,
ln.DESCRIPTION,
null,
ln.JE_LINE_NUM,
null,
hdr.CURRENCY_CODE Currency,
ln.ENTERED_DR,
ln.ENTERED_CR,
null,
null,
ln.ENTERED_DR,
ln.ENTERED_CR,
hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
null,
null,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
hdr.JE_Source,
hdr.DOC_SEQUENCE_VALUE,
glc.user_je_category_name JE_Category,
fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
null,
null,
null,
null,
nvl(null,0)
from gl_je_lines ln,
gl_code_combinations gcc,
gl_je_headers hdr,
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 hdr.REVERSED_JE_HEADER_ID is not null
and hdr.JE_SOURCE not in
('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
and ln.LEDGER_ID = :Ledger_id
and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
and gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
and gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
and gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
and gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)
)
where 1=1
and SEGMENT2 between NVL(:From_Location,SEGMENT2) and NVL(:To_Location,SEGMENT2)
and SEGMENT3 between NVL(:From_Department,SEGMENT3) and NVL(:To_Department,SEGMENT3)
and SEGMENT4 between NVL(:From_Account,SEGMENT4) and NVL(:To_Account,SEGMENT4)
and SEGMENT5 between NVL(:From_Project,SEGMENT5) and NVL(:To_Project,SEGMENT5)
and SEGMENT7 between NVL(:From_Future1,SEGMENT7) and NVL(:To_Future1,SEGMENT7)
and Effective_date between NVL(:From_Date,Effective_date) AND NVL(:To_Date,Effective_date)
and PARTY_ID=nvl(:P_Vendor_name,party_id)
order by EFFECTIVE_DATE
--------------------------------------------------------------------------------------------------------------------------
FINAL
select
je_category ,
VENDOR_ID,
VENDOR_NAME ,
PERIOD_NAME,
DEFAULT_EFFECTIVE_DATE ,
nvl((ENTERED_DR),0) -nvl((ENTERED_CR),0) Entered,
Exch_Rate,
nvl((ACCOUNTED_DR),0) -nvl((ACCOUNTED_CR),0) Accounted ,
Voucher_num, Currency,
segment2,segment4,LEDGER_ID, Description, PO_NUMBER
from (
select
ent.source_id_int_1 ,
(
select s.VENDOR_ID
from ap_invoices_all aia , ap_suppliers s
where aia.INVOICE_ID = ent.source_id_int_1
and hdr.je_category = 'Purchase Invoices'
and aia.VENDOR_ID = s.VENDOR_ID
UNION
select s.VENDOR_ID
from ap_checks_all aca, ap_suppliers s
where aca.CHECK_ID = ent.source_id_int_1
and hdr.je_category in ( 'Reconciled Payments' , 'Payments')
and aca.VENDOR_ID = s.VENDOR_ID
) VENDOR_ID,
(
select s.VENDOR_NAME
from ap_invoices_all aia , ap_suppliers s
where aia.INVOICE_ID = ent.source_id_int_1
and hdr.je_category = 'Purchase Invoices'
and aia.VENDOR_ID = s.VENDOR_ID
UNION
select s.VENDOR_NAME
from ap_checks_all aca, ap_suppliers s
where aca.CHECK_ID = ent.source_id_int_1
and hdr.je_category in ( 'Reconciled Payments' , 'Payments')
and aca.VENDOR_ID = s.VENDOR_ID
) VENDOR_NAME,
(
select aia.ATTRIBUTE2
from ap_invoices_all aia , ap_suppliers s
where aia.INVOICE_ID = ent.source_id_int_1
and hdr.je_category = 'Purchase Invoices'
and aia.VENDOR_ID = s.VENDOR_ID) PO_NUMBER,
hdr.je_category,
aeh.PERIOD_NAME,
ael.ACCOUNTING_DATE DEFAULT_EFFECTIVE_DATE,
ael.ENTERED_DR,
ael.ENTERED_CR,
NVL( ael.CURRENCY_CONVERSION_RATE,1) Exch_Rate,
ael.ACCOUNTED_DR
, ael.ACCOUNTED_CR
,gir.subledger_doc_sequence_value Voucher_num,
ael.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
ael.LEDGER_ID,
nvl(ael.DESCRIPTION,ln.DESCRIPTION) Description
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
where /*ln.period_name = 'JUN-16' --:P_PERIOD --
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 ln.LEDGER_ID=hdr.LEDGER_ID
and hdr.LEDGER_ID=aeh.LEDGER_ID
and aeh.LEDGER_ID=ael.LEDGER_ID
and ent.ledger_id=ael.LEDGER_ID
--and gcc.segment4 = '248111100'
--and gcc.SEGMENT1 = '01' --:P_SEGMENT1 --
--and gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 = '1.01.000.351010.000000.000'
and hdr.je_source not in ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
--and hdr.je_category = 'Payments'
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
UNION ALL
select
null source_id_int_1 ,
0 VENDOR_ID ,
'MANUAL ENTRIES' VENDOR_NAME ,
null PO_NUMBER,
cat.user_je_category_name je_category,
h.PERIOD_NAME,
h.DEFAULT_EFFECTIVE_DATE ,
ln.ENTERED_DR,
ln.ENTERED_CR,
h.CURRENCY_CONVERSION_RATE Exch_Rate,
LN.ACCOUNTED_DR ,
LN.ACCOUNTED_CR ,
h.DOC_SEQUENCE_VALUE Voucher_num,
h.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
h.LEDGER_ID,
ln.DESCRIPTION Description
from gl_je_headers H , GL_JE_LINES LN , gl_code_combinations gcc , gl_je_categories_v cat
where H.JE_HEADER_ID = LN.JE_HEADER_ID
and LN.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and h.JE_CATEGORY = cat.je_category_name
--and ln.period_name = 'JUN-16' --:P_PERIOD --
and h.status = 'P'
and ln.status = 'P'
and h.actual_flag = 'A'
and ln.period_name = h.period_name
and ln.code_combination_id = gcc.code_combination_id
and h.LEDGER_ID=ln.LEDGER_ID
--and gcc.segment4 = '248111100'
--and gcc.SEGMENT1 = '01' --:P_SEGMENT1 --
--and gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 = '1.01.000.351010.000000.000'
and h.je_source in ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
UNION ALL
select
null source_id_int_1 ,
0 VENDOR_ID ,
'MANUAL ENTRIES' VENDOR_NAME ,
null PO_NUMBER,
cat.user_je_category_name je_category,
h.PERIOD_NAME,
h.DEFAULT_EFFECTIVE_DATE ,
ln.ENTERED_DR,
ln.ENTERED_CR,
h.CURRENCY_CONVERSION_RATE Exch_Rate,
LN.ACCOUNTED_DR ,
LN.ACCOUNTED_CR ,
h.DOC_SEQUENCE_VALUE Voucher_num,
h.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
h.LEDGER_ID,
ln.DESCRIPTION Description
from gl_je_headers H , GL_JE_LINES LN , gl_code_combinations gcc , gl_je_categories_v cat
where H.JE_HEADER_ID = LN.JE_HEADER_ID
and LN.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and h.JE_CATEGORY = cat.je_category_name
--and ln.period_name = 'JUN-16' --:P_PERIOD --
and h.status = 'P'
and ln.status = 'P'
and h.actual_flag = 'A'
and ln.period_name = h.period_name
and ln.code_combination_id = gcc.code_combination_id
and H.REVERSED_JE_HEADER_ID is not null
--and gcc.segment4 = '248111100'
--and gcc.SEGMENT1 = '01' --:P_SEGMENT1 --
--and gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 = '1.01.000.351010.000000.000'
and h.je_source not in ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
and h.LEDGER_ID=ln.LEDGER_ID
) A
where trunc( DEFAULT_EFFECTIVE_DATE) between nvl( '&P_FROM_DATE' , trunc( DEFAULT_EFFECTIVE_DATE) ) and nvl( '&P_TO_DATE ', trunc( DEFAULT_EFFECTIVE_DATE) )
and SEGMENT2 between NVL('&From_Location',SEGMENT2) and NVL('&To_Location',SEGMENT2)
and SEGMENT3 between NVL('&From_Department',SEGMENT3) and NVL('&To_Department',SEGMENT3)
and SEGMENT4 between NVL('&From_Account',SEGMENT4) and NVL('&To_Account',SEGMENT4)
and SEGMENT5 between NVL('&From_Project',SEGMENT5) and NVL('&To_Project',SEGMENT5)
and SEGMENT7 between NVL('&From_Future1',SEGMENT7) and NVL('&To_Future1',SEGMENT7)
and ledger_id = '&LEDGER_ID'
select * from
(select
nvl(ael.ENTERED_DR,0) - nvl(ael.ENTERED_CR,0) Entered ,
nvl(ael.ACCOUNTED_DR , 0) - nvl( ael.ACCOUNTED_CR,0) Accounted,
hdr.JE_HEADER_ID,
ael.AE_HEADER_ID,
hdr.PERIOD_NAME Period_Name,
hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
hdr.NAME Journal_Name,
ln.DESCRIPTION GL_Description,
ael.DESCRIPTION Subledger_Description,
ln.JE_LINE_NUM GL_Line_Num,
ael.AE_LINE_NUM Subledger_Line_Number,
hdr.CURRENCY_CODE Currency,
ln.ENTERED_DR GL_Entered_DR,
ln.ENTERED_CR GL_Entered_CR,
ael.ENTERED_DR SUB_Enterred_DR,
ael.ENTERED_CR SUB_Enterred_CR,
ael.ENTERED_DR,
ael.ENTERED_CR,
-- hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
NVL( ael.CURRENCY_CONVERSION_RATE,1) Exch_Rate,
ael.ACCOUNTED_DR Subledger_Accounted_DT,
ael.ACCOUNTED_CR Subledger_Accounted_CR,
null GL_Accounted_DR,
null GL_Accounted_CR,
ael.ACCOUNTED_DR ,
ael.ACCOUNTED_CR,
hdr.JE_Source,
hdr.DOC_SEQUENCE_VALUE GL_Doc_Seq_Value,
glc.user_je_category_name JE_Category,
fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
aeh.DOC_SEQUENCE_VALUE Subledger_Doc_Seq_Value,
(select aia.ATTRIBUTE2
from ap_invoices_all aia
where aia.INVOICE_ID = ent.source_id_int_1
and ent.entity_code = 'AP_INVOICES'
and ent.application_id = 200
and aia.SET_OF_BOOKS_ID = :Ledger_id) PO_NUMBER,
(select pv.VENDOR_NAME from ap_invoices_all aia, po_vendors pv
where aia.VENDOR_ID=pv.VENDOR_ID
and aia.INVOICE_ID=ent.source_id_int_1 and ent.entity_code = 'AP_INVOICES'
and ent.application_id =200 and aia.SET_OF_BOOKS_ID=:Ledger_id-- and pv.SET_OF_BOOKS_ID='&Ledger_id'
and ent.ledger_id=:Ledger_id union
select pv.vendor_name from ap_checks_all aca, po_vendors pv
where aca.CHECK_ID=ent.source_id_int_1
and aca.VENDOR_ID=pv.VENDOR_ID and ent.entity_code = 'AP_PAYMENTS'
and ent.application_id=200 and ent.ledger_id=:Ledger_id) Supplier_Name, --and pv.SET_OF_BOOKS_ID='&Ledger_id'
/* (select fn.user_name
from fnd_user fn, ap_invoices_all ai
where ai.CREATED_BY = fn.USER_ID
and ai.INVOICE_ID = ent.source_id_int_1
and ent.entity_code = 'AP_INVOICES'
and ent.application_id = 200
and ai.SET_OF_BOOKS_ID = :Ledger_id) APN_USERNAME */
(select fn.user_name
from fnd_user fn, ap_invoices_all ai
where ai.CREATED_BY = fn.USER_ID
and ai.INVOICE_ID = ent.source_id_int_1
and ent.entity_code = 'AP_INVOICES'
and ent.application_id = 200
and ai.SET_OF_BOOKS_ID = :Ledger_id union
select fk.user_name
from fnd_user fk, ap_checks_all ac
where ac.CREATED_BY = fk.USER_ID
and ac.check_ID = ent.source_id_int_1
and ent.entity_code = 'AP_PAYMENTS'
and ent.application_id = 200
and ent.ledger_id = :Ledger_id ) AP_Creator
, NVL(ael.party_id,0) Party_id
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 = :Ledger_id
and ln.LEDGER_ID = :Ledger_id
and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
and gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
and gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
and gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
and gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)
union
select
nvl(ln.ENTERED_DR,0) - nvl(ln.ENTERED_CR,0) Entered ,
nvl(ln.ACCOUNTED_DR , 0) - nvl( ln.ACCOUNTED_CR,0) Accounted,
hdr.JE_HEADER_ID,
null,
hdr.PERIOD_NAME Period_Name,
hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
hdr.NAME Journal_Name,
ln.DESCRIPTION,
null,
ln.JE_LINE_NUM,
null,
hdr.CURRENCY_CODE Currency,
ln.ENTERED_DR,
ln.ENTERED_CR,
null,
null,
ln.ENTERED_DR,
ln.ENTERED_CR,
hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
null,
null,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
hdr.JE_Source,
hdr.DOC_SEQUENCE_VALUE,
glc.user_je_category_name JE_Category,
fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
null,
null,
null,
null,
nvl(null,0)
from gl_je_lines ln,
gl_code_combinations gcc,
gl_je_headers hdr,
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 hdr.JE_SOURCE in
('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
and ln.LEDGER_ID = :Ledger_id
and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
and gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
and gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
and gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
and gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)
union
select
nvl(ln.ENTERED_DR,0) - nvl(ln.ENTERED_CR,0) Entered ,
nvl(ln.ACCOUNTED_DR , 0) - nvl( ln.ACCOUNTED_CR,0) Accounted,
hdr.JE_HEADER_ID,
null,
hdr.PERIOD_NAME Period_Name,
hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
hdr.NAME Journal_Name,
ln.DESCRIPTION,
null,
ln.JE_LINE_NUM,
null,
hdr.CURRENCY_CODE Currency,
ln.ENTERED_DR,
ln.ENTERED_CR,
null,
null,
ln.ENTERED_DR,
ln.ENTERED_CR,
hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
null,
null,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
ln.ACCOUNTED_DR,
ln.ACCOUNTED_CR,
hdr.JE_Source,
hdr.DOC_SEQUENCE_VALUE,
glc.user_je_category_name JE_Category,
fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
null,
null,
null,
null,
nvl(null,0)
from gl_je_lines ln,
gl_code_combinations gcc,
gl_je_headers hdr,
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 hdr.REVERSED_JE_HEADER_ID is not null
and hdr.JE_SOURCE not in
('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
and ln.LEDGER_ID = :Ledger_id
and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
and gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
and gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
and gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
and gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)
)
where 1=1
and SEGMENT2 between NVL(:From_Location,SEGMENT2) and NVL(:To_Location,SEGMENT2)
and SEGMENT3 between NVL(:From_Department,SEGMENT3) and NVL(:To_Department,SEGMENT3)
and SEGMENT4 between NVL(:From_Account,SEGMENT4) and NVL(:To_Account,SEGMENT4)
and SEGMENT5 between NVL(:From_Project,SEGMENT5) and NVL(:To_Project,SEGMENT5)
and SEGMENT7 between NVL(:From_Future1,SEGMENT7) and NVL(:To_Future1,SEGMENT7)
and Effective_date between NVL(:From_Date,Effective_date) AND NVL(:To_Date,Effective_date)
and PARTY_ID=nvl(:P_Vendor_name,party_id)
order by EFFECTIVE_DATE
--------------------------------------------------------------------------------------------------------------------------
FINAL
select
je_category ,
VENDOR_ID,
VENDOR_NAME ,
PERIOD_NAME,
DEFAULT_EFFECTIVE_DATE ,
nvl((ENTERED_DR),0) -nvl((ENTERED_CR),0) Entered,
Exch_Rate,
nvl((ACCOUNTED_DR),0) -nvl((ACCOUNTED_CR),0) Accounted ,
Voucher_num, Currency,
segment2,segment4,LEDGER_ID, Description, PO_NUMBER
from (
select
ent.source_id_int_1 ,
(
select s.VENDOR_ID
from ap_invoices_all aia , ap_suppliers s
where aia.INVOICE_ID = ent.source_id_int_1
and hdr.je_category = 'Purchase Invoices'
and aia.VENDOR_ID = s.VENDOR_ID
UNION
select s.VENDOR_ID
from ap_checks_all aca, ap_suppliers s
where aca.CHECK_ID = ent.source_id_int_1
and hdr.je_category in ( 'Reconciled Payments' , 'Payments')
and aca.VENDOR_ID = s.VENDOR_ID
) VENDOR_ID,
(
select s.VENDOR_NAME
from ap_invoices_all aia , ap_suppliers s
where aia.INVOICE_ID = ent.source_id_int_1
and hdr.je_category = 'Purchase Invoices'
and aia.VENDOR_ID = s.VENDOR_ID
UNION
select s.VENDOR_NAME
from ap_checks_all aca, ap_suppliers s
where aca.CHECK_ID = ent.source_id_int_1
and hdr.je_category in ( 'Reconciled Payments' , 'Payments')
and aca.VENDOR_ID = s.VENDOR_ID
) VENDOR_NAME,
(
select aia.ATTRIBUTE2
from ap_invoices_all aia , ap_suppliers s
where aia.INVOICE_ID = ent.source_id_int_1
and hdr.je_category = 'Purchase Invoices'
and aia.VENDOR_ID = s.VENDOR_ID) PO_NUMBER,
hdr.je_category,
aeh.PERIOD_NAME,
ael.ACCOUNTING_DATE DEFAULT_EFFECTIVE_DATE,
ael.ENTERED_DR,
ael.ENTERED_CR,
NVL( ael.CURRENCY_CONVERSION_RATE,1) Exch_Rate,
ael.ACCOUNTED_DR
, ael.ACCOUNTED_CR
,gir.subledger_doc_sequence_value Voucher_num,
ael.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
ael.LEDGER_ID,
nvl(ael.DESCRIPTION,ln.DESCRIPTION) Description
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
where /*ln.period_name = 'JUN-16' --:P_PERIOD --
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 ln.LEDGER_ID=hdr.LEDGER_ID
and hdr.LEDGER_ID=aeh.LEDGER_ID
and aeh.LEDGER_ID=ael.LEDGER_ID
and ent.ledger_id=ael.LEDGER_ID
--and gcc.segment4 = '248111100'
--and gcc.SEGMENT1 = '01' --:P_SEGMENT1 --
--and gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 = '1.01.000.351010.000000.000'
and hdr.je_source not in ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
--and hdr.je_category = 'Payments'
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
UNION ALL
select
null source_id_int_1 ,
0 VENDOR_ID ,
'MANUAL ENTRIES' VENDOR_NAME ,
null PO_NUMBER,
cat.user_je_category_name je_category,
h.PERIOD_NAME,
h.DEFAULT_EFFECTIVE_DATE ,
ln.ENTERED_DR,
ln.ENTERED_CR,
h.CURRENCY_CONVERSION_RATE Exch_Rate,
LN.ACCOUNTED_DR ,
LN.ACCOUNTED_CR ,
h.DOC_SEQUENCE_VALUE Voucher_num,
h.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
h.LEDGER_ID,
ln.DESCRIPTION Description
from gl_je_headers H , GL_JE_LINES LN , gl_code_combinations gcc , gl_je_categories_v cat
where H.JE_HEADER_ID = LN.JE_HEADER_ID
and LN.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and h.JE_CATEGORY = cat.je_category_name
--and ln.period_name = 'JUN-16' --:P_PERIOD --
and h.status = 'P'
and ln.status = 'P'
and h.actual_flag = 'A'
and ln.period_name = h.period_name
and ln.code_combination_id = gcc.code_combination_id
and h.LEDGER_ID=ln.LEDGER_ID
--and gcc.segment4 = '248111100'
--and gcc.SEGMENT1 = '01' --:P_SEGMENT1 --
--and gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 = '1.01.000.351010.000000.000'
and h.je_source in ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
UNION ALL
select
null source_id_int_1 ,
0 VENDOR_ID ,
'MANUAL ENTRIES' VENDOR_NAME ,
null PO_NUMBER,
cat.user_je_category_name je_category,
h.PERIOD_NAME,
h.DEFAULT_EFFECTIVE_DATE ,
ln.ENTERED_DR,
ln.ENTERED_CR,
h.CURRENCY_CONVERSION_RATE Exch_Rate,
LN.ACCOUNTED_DR ,
LN.ACCOUNTED_CR ,
h.DOC_SEQUENCE_VALUE Voucher_num,
h.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
h.LEDGER_ID,
ln.DESCRIPTION Description
from gl_je_headers H , GL_JE_LINES LN , gl_code_combinations gcc , gl_je_categories_v cat
where H.JE_HEADER_ID = LN.JE_HEADER_ID
and LN.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and h.JE_CATEGORY = cat.je_category_name
--and ln.period_name = 'JUN-16' --:P_PERIOD --
and h.status = 'P'
and ln.status = 'P'
and h.actual_flag = 'A'
and ln.period_name = h.period_name
and ln.code_combination_id = gcc.code_combination_id
and H.REVERSED_JE_HEADER_ID is not null
--and gcc.segment4 = '248111100'
--and gcc.SEGMENT1 = '01' --:P_SEGMENT1 --
--and gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 = '1.01.000.351010.000000.000'
and h.je_source not in ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
'Revaluation', 'Closing Journal')
and h.LEDGER_ID=ln.LEDGER_ID
) A
where trunc( DEFAULT_EFFECTIVE_DATE) between nvl( '&P_FROM_DATE' , trunc( DEFAULT_EFFECTIVE_DATE) ) and nvl( '&P_TO_DATE ', trunc( DEFAULT_EFFECTIVE_DATE) )
and SEGMENT2 between NVL('&From_Location',SEGMENT2) and NVL('&To_Location',SEGMENT2)
and SEGMENT3 between NVL('&From_Department',SEGMENT3) and NVL('&To_Department',SEGMENT3)
and SEGMENT4 between NVL('&From_Account',SEGMENT4) and NVL('&To_Account',SEGMENT4)
and SEGMENT5 between NVL('&From_Project',SEGMENT5) and NVL('&To_Project',SEGMENT5)
and SEGMENT7 between NVL('&From_Future1',SEGMENT7) and NVL('&To_Future1',SEGMENT7)
and ledger_id = '&LEDGER_ID'
No comments:
Post a Comment