SELECT	 
	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