Get EFF details at the Order (header level in this query, but can be fetched from lines EFF with slight modifications), with links to AR Invoices (Headers and Lines tables), Project Billing Events Table, and Contracts tables.

SELECT count(*),  d.customer_trx_id,

h.attribute_char1, h.attribute_char2, h.attribute_char3, h.attribute_char4, h.attribute_char5

FROM PJB_INV_LINE_DISTS a,

--pjb_invoice_lines a,

                                pjb_invoice_headers b, 

                                ra_customer_Trx_lines_all c, 

                                ra_customer_trx_all d,

PJB_BILLING_EVENTS E,

DOO_FULFILL_LINEs_ALL F,

doo_headers_all g,

doo_headers_eff_b h

--

where a.invoice_id = b.invoice_id

and a.invoice_line_id = c.interface_line_attribute5

and a.contract_id = c.interface_line_attribute2

and c.customer_Trx_id = d.customer_trx_id

and d.trx_number = :p_trx_number

--

AND E.EVENT_ID = A.TRANSACTION_ID

AND e.sourceref = f.fulfill_line_id

and f.header_id = g.header_id

--

and h.header_id = g.header_id

group by h.attribute_char1, h.attribute_char2, h.attribute_char3, h.attribute_char4, h.attribute_char5, d.customer_trx_id