SELECT
gle.NAME ledger_name,
gle.currency_code
ledger_currency_code,
gcc.concatenated_segments
sla_gl_account,
gcc1.concatenated_segments
gl_account,
gps.period_name period_name,
party.party_name party_name,
party.party_number party_number,
cust.account_name account_name,
cust.account_number
customer_number,
rct.trx_number,
rct.trx_date,
ctt.NAME trx_type,
DECODE (ctt.TYPE,
'INV', 'Invoices',
'CM', 'Credit Memos',
'DM', 'Debit Memos',
'CB', 'Charge Backs',
NULL)
trx_class,
rct.complete_flag,
rct.invoice_currency_code,
rctgd.amount dist_AMT,
rctgd.acctd_amount
dist_ACCTD_AMOUNT,
rctgd.gl_date ar_gl_date,
rctgd.gl_posted_date
ar_gl_posted_date,
rctgd.account_class
ar_line_account_class,
ael.entered_dr
sla_line_entered_dr,
ael.entered_cr
sla_line_entered_cr,
NVL (ael.accounted_dr, 0) - NVL (ael.accounted_cr, 0)
sla_line_accounted_net,
ael.accounted_dr
sla_line_accounted_dr,
ael.accounted_cr sla_line_accounted_cr,
xdl.unrounded_entered_dr
sla_dist_entered_dr,
xdl.unrounded_entered_cr
sla_dist_entered_cr,
xdl.unrounded_accounted_dr
sla_dist_accounted_dr,
xdl.unrounded_accounted_cr
sla_dist_accounted_cr,
NVL (xdl.unrounded_accounted_dr, 0)
- NVL (xdl.unrounded_accounted_cr, 0)
sla_dist_accounted_net,
DECODE (rctgd.posting_control_id, -3, 'N', 'Y')
gl_transfer_status
,
jl.entered_dr gl_entered_dr,
jl.entered_cr gl_entered_cr,
jl.accounted_dr gl_accounted_dr,
jl.accounted_cr gl_accounted_cr,
NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0)
gl_net_entered_amt,
NVL (jl.accounted_dr, 0) - NVL (jl.accounted_cr, 0)
gl_net_accounted_amt
,
jb.NAME je_batch_name,
jb.description
je_batch_description,
jh.NAME je_name,
jh.description je_description,
jh.posted_date je_posted_date,
DECODE (
jh.status,
'P',
'Posted',
'U',
'Unposted',
'F',
'Error7 - Showing
invalid journal entry lines or no journal entry lines',
'K',
'Error10 - Showing unbalanced intercompany journal entry',
'Z',
'Error7 - Showing
invalid journal entry lines or no journal entry lines',
'Unknown'
)
je_status,
DECODE (jh.actual_flag,
'A',
'Actual',
'B',
'Budget',
'E',
'Encumbrance')
je_type
,
DECODE (
rctgd.posting_control_id,
-3,
'Not-Transferred-To-SLA',
DECODE (aeh.gl_transfer_status_code,
'Y', 'Transferred-to-GL',
'Not Transferred-to-GL')
)
reconcile_status,
xdl.accounting_line_code,
xdl.event_class_code,
xdl.event_type_code,
aeh.accounting_date,
aeh.gl_transfer_date,
hou.NAME operating_unit,
--primary Keys
rct.customer_trx_id,
rctgd.cust_trx_line_gl_dist_id,
ctt.cust_trx_type_id,
gle.ledger_id,
gcc.code_combination_id
sla_code_combination_id,
gcc1.code_combination_id
gl_code_combination_id,
party.party_id,
cust.cust_account_id,
aeh.ae_header_id,
ael.ae_line_num,
jl.je_line_num,
jh.je_header_id,
jb.je_batch_id,
jes.je_source_name,
xdl.event_id,
jec.je_category_name,
gps.start_date,
gps.end_date,
hou.organization_id org_id,
--Primary Keys
gcc.code_combination_id,
aeh.application_id,
xdl.temp_line_num,
xdl.ref_ae_header_id,
gps.set_of_books_id,
hou.organization_id,
GCC.SEGMENT2 GCC#50353#ACCOUNT,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT2,
'MAS_GL_COA_ACCOUNT')
GCC#50353#ACCOUNT#DESCR,
GCC.SEGMENT1 GCC#50353#COMPANY,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT1,
'MAS_GL_COA_COMPANY')
GCC#50353#COMPANY#DESCR,
GCC.SEGMENT6 GCC#50353#DEPARTMENT,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT6,
'MAS_GL_COA_DEPARTMENT')
GCC#50353#DEPARTMENT#DESCR,
GCC.SEGMENT8 GCC#50353#FUTURE,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT8,
'MAS_GL_COA_FUTURE')
GCC#50353#FUTURE#DESCR,
GCC.SEGMENT7 GCC#50353#IC,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT7,
'MAS_GL_COA_COMPANY')
GCC#50353#IC#DESCR,
GCC.SEGMENT4 GCC#50353#LOB,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT4,
'MAS_GL_COA_LOB')
GCC#50353#LOB#DESCR,
GCC.SEGMENT5 GCC#50353#LOCATIONS,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT5,
'MAS_GL_COA_LOCATION')
GCC#50353#LOCATIONS#DESCR,
GCC.SEGMENT3 GCC#50353#QUALIFIER,
xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT3,
'MAS_GL_COA_ACCT_QUAL')
GCC#50353#QUALIFIER#DESCR,
GCC1.SEGMENT2 GCC1#50353#ACCOUNT,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT2,
'MAS_GL_COA_ACCOUNT')
GCC1#50353#ACCOUNT#DESCR,
GCC1.SEGMENT1 GCC1#50353#COMPANY,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT1,
'MAS_GL_COA_COMPANY')
GCC1#50353#COMPANY#DESCR,
GCC1.SEGMENT6
GCC1#50353#DEPARTMENT,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT6,
'MAS_GL_COA_DEPARTMENT')
GCC1#50353#DEPARTMENT#DESCR,
GCC1.SEGMENT8 GCC1#50353#FUTURE,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT8,
'MAS_GL_COA_FUTURE')
GCC1#50353#FUTURE#DESCR,
GCC1.SEGMENT7 GCC1#50353#IC,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT7,
'MAS_GL_COA_COMPANY')
GCC1#50353#IC#DESCR,
GCC1.SEGMENT4 GCC1#50353#LOB,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT4,
'MAS_GL_COA_LOB')
GCC1#50353#LOB#DESCR,
GCC1.SEGMENT5 GCC1#50353#LOCATIONS,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT5,
'MAS_GL_COA_LOCATION')
GCC1#50353#LOCATIONS#DESCR,
GCC1.SEGMENT3 GCC1#50353#QUALIFIER,
xxeis.eis_rs_fin_utility.decode_vset (GCC1.SEGMENT3,
'MAS_GL_COA_ACCT_QUAL')
GCC1#50353#QUALIFIER#DESCR
FROM ra_customer_trx rct,
ra_cust_trx_line_gl_dist rctgd,
ra_cust_trx_types ctt,
gl_ledgers gle,
gl_code_combinations_kfv gcc,
gl_code_combinations_kfv gcc1,
hz_parties party,
hz_cust_accounts cust,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_distribution_links xdl,
gl_import_references jir,
gl_je_batches jb,
gl_je_headers jh,
gl_je_lines jl,
gl_je_sources jes,
gl_je_categories jec,
gl_period_statuses gps,
hr_operating_units hou
WHERE rct.customer_trx_id
= rctgd.customer_trx_id
AND rct.cust_trx_type_id = ctt.cust_trx_type_id
AND rct.org_id = ctt.org_id
AND rct.set_of_books_id = gle.ledger_id
AND rctgd.code_combination_id = gcc.code_combination_id
AND gcc.code_combination_id(+) = ael.code_combination_id
AND gcc1.code_combination_id(+) = jl.code_combination_id
AND rct.bill_to_customer_id = cust.cust_account_id
AND cust.party_id = party.party_id
AND rctgd.account_set_flag = 'N'
--xla tables
AND rctgd.cust_trx_line_gl_dist_id =
xdl.source_distribution_id_num_1(+)
AND xdl.source_distribution_type(+) =
'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND xdl.ae_header_id = aeh.ae_header_id(+)
AND xdl.ae_header_id = ael.ae_header_id(+)
AND xdl.ae_line_num = ael.ae_line_num(+)
AND jir.gl_sl_link_id(+) = ael.gl_sl_link_id
AND jir.gl_sl_link_table(+) = ael.gl_sl_link_table
-- Gl Joins
AND jb.je_batch_id(+) = jir.je_batch_id
AND jh.je_header_id(+) = jir.je_header_id
AND jl.je_line_num(+) = jir.je_line_num
AND jl.je_header_id(+) = jir.je_header_id
AND jes.je_source_name(+) = jh.je_source
AND jec.je_category_name(+) = jh.je_category
AND jh.je_source(+) = 'Receivables'
AND jh.je_category(+) = 'Credit Memos'
AND ctt.TYPE = 'CM'
AND rct.complete_flag = 'Y'
AND ctt.post_to_gl = 'Y'
AND gl_security_pkg.validate_access (gle.ledger_id) = 'TRUE'
AND gle.object_type_code = 'L'
AND gps.ledger_id = gle.ledger_id
AND gps.application_id = 222
AND gps.adjustment_period_flag = 'N'
AND rctgd.gl_date BETWEEN gps.start_date AND gps.end_date
AND hou.organization_id = rct.org_id;
No comments:
Post a Comment