Query to get AR Invoice Details from SLA & GL in fusion

Below I have attached a sample query to get the AR Invoice details from SLA & GL

SELECT gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' || gcc.segment7 || '-' || gcc.segment8 cc,
--null Journal_Excel_Row,
GJH.NAME Je_Name,
--GJH.NAME || ' (' || gjl.JE_LINE_NUM || ')' Journal_NAME1,
xect.name event_class,
xett.NAME event_type,
to_char(xe.EVENT_DATE, 'mm/dd/yyyy') event_date,
(
SELECT MEANING
FROM xla_lookups
WHERE LOOKUP_typE = 'XLA_ACCOUNTING_CLASS'
AND lookup_code = xal.accounting_class_code
) accounting_class,
Nvl(xal.entered_dr, 0) Ent_Dr,
Nvl(xal.entered_cr, 0) Ent_Cr,
Nvl(xal.accounted_dr, 0) Acc_Dr,
Nvl(xal.accounted_cr, 0) Acc_Cr,
nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0) ent_net,
nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) acc_net,
'Customer Name' cp1,
(
SELECT DISTINCT hp.party_name
FROM hz_cust_accounts hca,
ra_customer_trx_all rct,
hz_parties hp
WHERE hca.cust_account_id = rct.bill_to_customer_id
AND rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND hp.party_id = hca.party_id
AND ROWNUM = 1
) cv1,
'Customer Number' cp2,
(
SELECT DISTINCT account_number
FROM hz_cust_accounts hca,
ra_customer_trx_all rct
WHERE hca.cust_account_id = rct.bill_to_customer_id
AND rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv2,
'Invoice Number' cp3,
(
SELECT DISTINCT trx_number
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv3,
'Invoice Document Number' cp4,
(
SELECT DISTINCT to_char(DOC_SEQUENCE_VALUE)
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv4,
'Invoice Date' cp5,
(
SELECT DISTINCT to_char(trx_date, 'mm/dd/yyyy')
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv5,
'Transaction Type' cp6,
(
SELECT invt.name
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all invt
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND invt.cust_trx_type_seq_id = rct.cust_trx_type_seq_id
AND ROWNUM = 1
) cv6,
'Batch Source' cp7,
(
SELECT rbs.name
FROM ra_customer_trx_all rct,
ra_batch_sources_all rbs
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND rct.batch_source_seq_id = rbs.batch_source_seq_id
AND ROWNUM = 1
) cv7,
NULL cp8,
NULL cv8,
NULL cp9,
NULL cv9,
NULL cp10,
NULL cv10,
xal.CREATED_BY,
xal.LAST_UPDATED_BY,
xal.CURRENCY_CODE,
nvl(xal.CURRENCY_CONVERSION_RATE, 1) rate,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gjh.DOC_SEQUENCE_VALUE
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
ledgers gl,
periods gp,
xla_events xe,
xla_transaction_entities xte,
XLA_EVENT_TYPES_TL xett,
XLA_EVENT_CLASSES_TL xect
WHERE 1 = 1
AND gjh.period_name = gp.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND gjl.STATUS = 'P'
AND gjh.je_source = 'Receivables'
AND gjh.ledger_id = gl.ledger_id
AND gjh.je_category IN (
'Sales Invoices',
'Contract Invoices',
'Credit Memos'
)
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.application_id = xte.application_id
AND xe.entity_id = xte.entity_id
AND xett.APPLICATION_ID = xe.APPLICATION_ID
AND xett.EVENT_TYPE_CODE = xe.EVENT_TYPE_CODE
AND xett.LANGUAGE = userenv('LANG')
AND xett.APPLICATION_ID = xect.APPLICATION_ID
AND xect.EVENT_CLASS_CODE = xett.EVENT_CLASS_CODE
AND xect.LANGUAGE = userenv('LANG')
AND (
gcc.segment1 IN (:p_company)
OR 'All' IN (:p_company || 'All')
)
AND (
gcc.segment2 IN (:p_department)
OR 'All' IN (:p_department || 'All')
)
AND (
gcc.segment4 IN (:p_product)
OR 'All' IN (:p_product || 'All')
)
AND (
gcc.segment5 IN (:p_cost_center)
OR 'All' IN (:p_cost_center || 'All')
)
AND (
gcc.segment6 IN (:p_location)
OR 'All' IN (:p_location || 'All')
)
AND (
gcc.segment7 IN (:p_intercompany)
OR 'All' IN (:p_intercompany || 'All')
)
AND (
gcc.segment8 IN (:p_future1)
OR 'All' IN (:p_future1 || 'All')
)
AND gcc.segment3 >= nvl(:p_account_from, gcc.segment3)
AND gcc.segment3 <= nvl(:p_account_to, gcc.segment3)
AND (
gcc.segment3 IN (:p_acc)
OR 'All' IN (:p_acc || 'All')
)
AND (
CASE 
WHEN :p_translated_flag = 'ENTERED'
AND gjl.CURRENCY_CODE = :p_currency_code
THEN 1
WHEN :p_translated_flag = 'TOTAL'
AND gl.currency_code = :p_currency_code
THEN 1
WHEN :p_translated_flag = 'TOTAL'
AND gl.ledger_id IN (
SELECT ledger_id
FROM trans_flag
)
THEN 1
END
) = 1

-----------------------------------------------------------------------------------------------------------------------------

Below is the query for the same requirement with added logics

SELECT DISTINCT gjh.PERIOD_NAME,

gjl.EFFECTIVE_DATE GL_Date,

gl.name Ledger_Name,

gjb.name Batch_Name,

gjh.JE_SOURCE Journal_source,

gjh.JE_CATEGORY Journal_Category,

(

SELECT MEANING

FROM xla_lookups

WHERE LOOKUP_typE = 'XLA_ACCOUNTING_CLASS'

AND lookup_code = xal.accounting_class_code

) Accounting_Class,

gjl.DESCRIPTION Line_Description,

(

SELECT DISTINCT HCA.ACCOUNT_NUMBER

FROM ra_customer_trx_all rct,

HZ_CUST_ACCOUNTS HCA

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID

AND ROWNUM = 1

) Account_Number,

GCC.SEGMENT4 GL_Account,

nvl(gjl.ACCOUNTED_DR, 0) ACCOUNTED_DR,

nvl(gjl.ACCOUNTED_CR * - 1, 0) ACCOUNTED_CR,

nvl(gjl.ACCOUNTED_DR, 0) + nvl(gjl.ACCOUNTED_CR * - 1, 0) Dr_Cr,

gjh.CURRENCY_CODE,

(

SELECT DISTINCT HP.party_name

FROM ra_customer_trx_all rct,

HZ_CUST_ACCOUNTS hca,

HZ_PARTIES hp

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID

AND HCA.PARTY_ID = HP.PARTY_ID

AND ROWNUM = 1

) Bill_To_Name,

(

SELECT DISTINCT trx_number

FROM ra_customer_trx_all rct

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND ROWNUM = 1

) Transaction_Number,

(

SELECT DISTINCT to_char(trx_date, 'mm/dd/yyyy')

FROM ra_customer_trx_all rct

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND ROWNUM = 1

) Transaction_Date,

(

SELECT DISTINCT rt.name

FROM ra_customer_trx_all rct,

ra_terms_vl rt

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND rt.TERM_ID = rct.TERM_ID

AND ROWNUM = 1

) Payment_Term,

(

SELECT rbs.name

FROM ra_customer_trx_all rct,

ra_batch_sources_all rbs

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND rct.batch_source_seq_id = rbs.batch_source_seq_id

AND ROWNUM = 1

) SOURCE,

(

SELECT DISTINCT TRX_CLASS

FROM ra_customer_trx_all rct

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND ROWNUM = 1

) Class20,

(

SELECT invt.name

FROM ra_customer_trx_all rct,

ra_cust_trx_types_all invt

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND invt.cust_trx_type_seq_id = rct.cust_trx_type_seq_id

AND ROWNUM = 1

) Type,

(

SELECT DISTINCT HCSUA.LOCATION

FROM RA_CUSTOMER_TRX_ALL RCT,

HZ_CUST_SITE_USES_ALL HCSUA

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND RCT.BILL_TO_SITE_USE_ID = HCSUA.SITE_USE_ID

AND ROWNUM = 1

) Bill_to_Site_ID,

(

SELECT DISTINCT HCSUA.location

FROM ra_customer_trx_all rct,

HZ_CUST_SITE_USES_ALL HCSUA

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND RCT.BILL_TO_SITE_USE_ID = HCSUA.SITE_USE_ID

AND ROWNUM = 1

) Bill_To_Location,

(

SELECT DISTINCT SUM(AMOUNT_LINE_ITEMS_REMAINING)

FROM ra_customer_trx_all rct,

ar_payment_schedules_all APS

WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1

AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID

) Balance_Due_Total,

0 Original_Payment_Amount,

NULL Receipt_Method,

NULL Payment_Method,

NULL Invoicing_Rule,

gjh.name Journal_Name,

gjl.JE_LINE_NUM Journal_Line,

to_char(xal.ACCOUNTING_DATE, 'mm/dd/yyyy') ACCT_DATE,

GCC.segment1 Company,

gjl.je_header_id,

gjl.je_line_num,

xe.application_id,

xe.entity_id

FROM gl_je_headers gjh,

GL_LEDGERS gl,

GL_JE_BATCHES gjb,

gl_je_lines gjl,

gl_code_combinations gcc,

gl_import_references gir,

xla_ae_lines xal,

xla_ae_headers xah,

xla_events xe,

xla_transaction_entities xte

WHERE 1 = 1

AND gjh.je_source = 'Receivables'

AND gjh.JE_CATEGORY = 'Sales Invoices'

AND gjh.name = '31-05-2025 Sales Invoices USSYS11976'

AND gl.LEDGER_ID = gjh.LEDGER_ID

AND gjb.JE_BATCH_ID = gjh.JE_BATCH_ID

AND gjb.JE_BATCH_ID = 31029

AND gjh.je_header_id = gjl.je_header_id

AND gjl.code_combination_id = gcc.code_combination_id

AND gjh.je_header_id = gir.je_header_id

AND gjl.je_line_num = gir.je_line_num

AND gir.gl_sl_link_id = xal.gl_sl_link_id

AND gir.gl_sl_link_table = xal.gl_sl_link_table

AND xal.ae_header_id = xah.ae_header_id

AND xah.application_id = xe.application_id

AND xah.event_id = xe.event_id

AND xe.application_id = xte.application_id

AND xe.entity_id = xte.entity_id

AND xte.Transaction_Number IN (

'31916430',

'15001'

)