Anonymous
public text v1 · immutableSELECT
gl_dist.code_combination_id code_combination_id,
gl_dist.account_class gl_acc_type,
&ACCT_FLEX_ALL_SEG segment,
trx.trx_number trx_number,
&ACCT_FLEX_BAL_SEG balancing_seg_rev,
types.name trx_type,
&ACCT_FLEX_ACCT_SEG account_seg_rev,
substrb(party.party_name,1,50) customer_name,
c.account_number customer_number,
trx.trx_date,
gl_dist.gl_date gl_date,
sum(nvl(decode(gl_dist.account_class,
'REC', decode(sign(gl_dist.amount),
1, 0,
-1 * gl_dist.amount
),
decode(sign(gl_dist.amount),
1, gl_dist.amount,
0
)
) ,0
)
) curr_credit_amount,
sum(nvl(decode(gl_dist.account_class,
'REC', decode(sign(gl_dist.amount),
1, gl_dist.amount,
0
),
decode(sign(gl_dist.amount),
1, 0,
-1 * gl_dist.amount
)
), 0
)
) curr_debit_amount ,
trx.invoice_currency_code currency_code_rev ,
&P_LINE_NUMBER line_number,
decode(upper(:p_sort_by),
'CUSTOMER', substrb(party.party_name,1,50),
'INVOICE NUMBER', trx.trx_number,
trx.trx_number) orderby1,
decode(upper(:p_sort_by),
'INVOICE NUMBER', substrb(party.party_name,1,50),
'CUSTOMER', trx.trx_number,
c.account_number) orderby2
FROM
ra_cust_trx_types types,
hz_cust_accounts c,
hz_parties party,
ra_customer_trx trx,
ra_customer_trx_lines lines,
ra_customer_trx_lines link_line,
gl_code_combinations gl,
ra_cust_trx_line_gl_dist gl_dist
-- Begin 28-JAN-2004
-- ,xxst_flex_value_hierarchy_v2 a
-- ,fnd_flex_value_norm_hierarchy b
-- End 28-JAN-2004
WHERE
trx.complete_flag = 'Y'
and
gl_dist.account_set_flag = 'N'
and
link_line.customer_trx_line_id(+) = lines.link_to_cust_trx_line_id
and
gl_dist.customer_trx_line_id = lines.customer_trx_line_id(+)
and
gl_dist.customer_trx_id = trx.customer_trx_id
and
types.cust_trx_type_id = trx.cust_trx_type_id
and
c.cust_account_id = trx.bill_to_customer_id
and
c.party_id = party.party_id
and
gl.code_combination_id = gl_dist.code_combination_id
and
gl_dist.set_of_books_id = :set_of_books_id
and nvl(gl_dist.gl_posted_date,
TO_DATE('01/01/0001',
'MM/DD/YYYY')) =
decode(:p_posting_status,
'POSTED', gl_dist.gl_posted_date,
'UNPOSTED', TO_DATE('01/01/0001',
'MM/DD/YYYY'),
nvl(gl_dist.gl_posted_date,
TO_DATE('01/01/0001',
'MM/DD/YYYY')))
&P_WHERE_1
&LP_START_GL_DATE
&LP_END_GL_DATE
&LP_TRX_DATE_LOW
&LP_TRX_DATE_HIGH
&P_WHERE_GL_TYPE
&LP_COMPANY_START
&LP_COMPANY_END
&LP_TRX_TYPE_LOW
&LP_TRX_TYPE_HIGH
&LP_TRX_NUMBER_LOW
&LP_TRX_NUMBER_HIGH
&LP_CUSTOMER_LOW
&LP_CUSTOMER_HIGH
&LP_CUSTOMER_NUMBER_LOW
&LP_CUSTOMER_NUMBER_HIGH
-- Begin 28-JAN-2004
--and a.flex_value_set_id = b.flex_value_set_id
--and a.parent_flex_value = b.parent_flex_value
--and b.child_flex_value_low = gl.segment1
and EXISTS ( select 'x'
from xxst_flex_value_hierarchy_v2 a
, fnd_flex_value_norm_hierarchy b
where a.flex_value_set_id = b.flex_value_set_id
and a.parent_flex_value = b.parent_flex_value
and b.child_flex_value_low = gl.segment1)
-- End 28-JAN-2004
group by
trx.invoice_currency_code ,
&ACCT_FLEX_BAL_SEG,
trx.trx_number,
party.party_name,
c.account_number,
gl_dist.code_combination_id,
&ACCT_FLEX_ALL_SEG,
&P_LINE_NUMBER,
&P_LINE_NUMBER_ORDER,
types.name,
&ACCT_FLEX_ACCT_SEG,
gl_dist.gl_date,
trx.trx_date,
types.type,
gl_dist.account_class
order by
trx.invoice_currency_code,
&ACCT_FLEX_BAL_SEG,
decode(upper(:p_sort_by),
'CUSTOMER', substrb(party.party_name,1,50),
'INVOICE NUMBER', trx.trx_number,
trx.trx_number),
decode(upper(:p_sort_by),
'INVOICE NUMBER', substrb(party.party_name,1,50),
'CUSTOMER', trx.trx_number,
c.account_number),
&P_LINE_NUMBER_ORDER,
gl_dist.gl_date,
&ACCT_FLEX_ACCT_SEG,
&ACCT_FLEX_ALL_SEG