Split the journal tracing view-source receivables (sales invoice)

-- The source is the sales invoice for the journals receivable (Standard view: xla_ar_inv_ael_gl_v)
-- Obtain the business data that is traced back to the credential line

Select Gjh. je_header_id,

Gjl. je_line_num,

Gjh. description, -- Summary

Gjh.doc _ sequence_value, -- Credential No.

Gjh. period_name, -- Period

Gcc. segment6 item_number, -- Product

Gcc. segment4 sub_accounts, -- Subject

To_char (XAG. trx_date, ' YYYY-MM-DD ' ) Trx_date, nvl (CTL. quantity_credited, CTL. quantity_invoiced) quantity, -- Quantity

Nvl (CTL. revenue_amount, 0 ) Rev_amount -- Amount

From Gl_je_headers_v gjh,

Gl_je_lines_v gjl,

( Select Jeh. je_header_id,

R. je_line_num,

Ct. trx_number trx_number_displayed,

CTL. line_number trx_line_number,

Ct. trx_date

From Gl_je_headers Jeh,

Gl_import_references R,

Ra_cust_trx_line_gl_dist ctlgd,

Ra_customer_trx CT,

Ra_customer_trx_lines CTL

Where Ctlgd. customer_trx_line_id = CTL. customer_trx_line_id ( + )

And Nvl (ctlgd.org _ id, - 99 ) = Nvl (ctl.org _ ID ( + ), - 99 )

And Ctlgd. account_set_flag =   ' N '

And Ct. customer_trx_id = Ctlgd. customer_trx_id

And Nvl (ct.org _ id, - 99 ) = Nvl (ctlgd.org _ id, - 99 )

And Ctlgd. cust_trx_line_gl_dist_id = To_number (R. reference_3)

And R. reference_10 =   ' Ra_cust_trx_line_gl_dist '

And R. je_header_id = Jeh. je_header_id

And Jeh. je_category In ( ' Sales invoices ' , ' Credit memos ' ,

' Debit memos ' , ' Chargebacks ' ) XAG,

Ra_customer_trx CT,

Ra_customer_trx_lines CTL,

Gl_code_combinations_kfv gcc

Where Gjh. je_source =   ' Receivables '   -- Receivables Management System

And Gjl. je_header_id = Gjh. je_header_id

And Gjh. Status =   ' P '   -- Posted

And Gjl. code_combination_id = Gcc. code_combination_id

And Gcc. segment1 =   ' 330007 '   -- Company Section

And Gcc. segment4 =   ' 600101 '   -- Subject section: main business income-internal main business income

And XAG. application_id =   222

And XAG. je_header_id = Gjl. je_header_id

And XAG. je_line_num = Gjl. je_line_num

And XAG. trx_number_displayed = Ct. trx_number

And XAG. trx_line_number = CTL. line_number

And Ct. customer_trx_id = CTL. customer_trx_id ( + )

And CTL. line_type ( + ) =   ' Line '

And Gjh. period_name =   ' 2008-05 '