AR Inter Company Invoices

select
        ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',type.type)   Class,
        trx.trx_number                           Invoice_Number,
        substrb(party.party_name,1,50)              Customer_Name ,
        cust.account_number                       Customer_Number,
        null           Receivable_Accounting,
        line.line_number                         Line_No ,
        null  Revenue_Accounting, 
	AR_RAXICI_XMLP_PKG.c_data_not_foundformula(ARPT_SQL_FUNC_UTIL.get_lookup_meaning ( 'INV/CM' , type.type )) C_DATA_NOT_FOUND, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_receivable_accounting', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') D_Receivable_Accounting, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_revenue_accounting', 'SQLGL', 'GL#', cc_rev.CHART_OF_ACCOUNTS_ID, NULL, cc_rev.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') D_Revenue_Accounting
from
        gl_code_combinations cc,
        ar_xla_ctlgd_lines_v  head_dist,
        gl_code_combinations cc_rev,
        ar_xla_ctlgd_lines_v  dist,
        ra_customer_trx_lines line,
        hz_cust_accounts cust,
        hz_parties party,
        ra_cust_trx_types type,
        ra_customer_trx trx
where
          head_dist.account_class       = 'REC'
and     head_dist.latest_rec_flag     = 'Y'
and     head_dist.gl_date is not null
and     cc.code_combination_id        = head_dist.code_combination_id
and     trx.customer_trx_id              = head_dist.customer_trx_id
and     trx.set_of_books_id              = :SOB_ID
and     trx.complete_flag  = 'Y'
and     cust.cust_account_id                   = trx.bill_to_customer_id
and     cust.party_id = party.party_id
and     type.cust_trx_type_id           = trx.cust_trx_type_id
and     type.type                                in ('INV', 'DEP', 'GUAR', 'CM')
and     dist.account_class                 = 'REV'
and     dist.account_set_flag       = 'N'
and     cc_rev.code_combination_id = dist.code_combination_id
and     line.customer_trx_id             = trx.customer_trx_id
and     line.customer_trx_line_id     = dist.customer_trx_line_id
&lp_where 
and     exists (
        select  1
        from
                gl_code_combinations gl,
                ar_xla_ctlgd_lines_v d,
                ra_customer_trx_lines l
        where
                l.customer_trx_id                     = trx.customer_trx_id
        and     d.customer_trx_line_id         = l.customer_trx_line_id
        and     d.account_class                     = 'REV'
       and     d.account_set_flag = 'N'
        and     gl.code_combination_id        = d.code_combination_id
        and     fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')     != fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg_gl', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
        )  
order by 
         trx.trx_number,
         line.line_number,
         trx.customer_trx_id,
         dist.gl_date,
         fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_all_seg_rev', 'SQLGL', 'GL#', cc_rev.CHART_OF_ACCOUNTS_ID, NULL, cc_rev.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')