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'
)
