AR应收帐款凭证

select user_name,

       currency_code,

       exchange_rate,

       account_number,

       descrption,

       code_accounts,

       sourcedescription,

       sum(entered_dr) as entered_dr,

       sum(entered_cr) as entered_cr,

       sum(accounted_dr) as accounted_dr,

       sum(accounted_cr) as accounted_cr,

       to_char(:startdate, 'yyyy-mm-dd') as startdate,

       to_char(:enddate, 'yyyy-mm-dd') as enddate

  from (select rac_bill_party.party_name as user_name,

               ct.invoice_currency_code as currency_code,

               ct.exchange_rate,

               rac_bill.account_number,

               ct.comments as descrption,

               substr(gcc_ori.concatenated_segments,

                      instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,

                      instr(gcc_ori.concatenated_segments, '-', 1, 2)) as code_accounts,

               substr(fa_rx_flex_pkg.get_description(101,

                                                     'GL#',

                                                     gcc_ori.chart_of_accounts_id,

                                                     'ALL',

                                                     gcc_ori.concatenated_segments),

                      instr(fa_rx_flex_pkg.get_description(101,

                                                           'GL#',

                                                           gcc_ori.chart_of_accounts_id,

                                                           'ALL',

                                                           gcc_ori.concatenated_segments),

                            '-',

                            1,

                            1) + 1,

                      instr(fa_rx_flex_pkg.get_description(101,

                                                           'GL#',

                                                           gcc_ori.chart_of_accounts_id,

                                                           'ALL',

                                                           gcc_ori.concatenated_segments),

                            '-',

                            1,

                            2) - 1) sourcedescription,

               -sum(xal.entered_dr) as entered_dr,

               -sum(xal.entered_cr) as entered_cr,

               -sum(xal.accounted_dr) as accounted_dr,

               -sum(xal.accounted_cr) as accounted_cr,

               to_char(:startdate, 'yyyy-mm-dd') as startdate,

               to_char(:enddate, 'yyyy-mm-dd') as enddate

          from xla_ae_lines                 xal,

               xla_ae_headers               xah,

               xla.xla_transaction_entities xte,

               gl_code_combinations_kfv     gcc_ori,

               ra_cust_trx_line_gl_dist_all gd,

               hz_cust_accounts             rac_bill,

               hz_parties                   rac_bill_party,

               ra_customer_trx              ct,

               fnd_user                     fu

         where xal.application_id = xah.application_id

           and xal.ae_header_id = xah.ae_header_id

           and xte.entity_id = xah.entity_id

           and xal.code_combination_id = gcc_ori.code_combination_id

           and xah.application_id = 222

           and xte.source_id_int_1 = ct.customer_trx_id

           and ct.customer_trx_id = gd.customer_trx_id

           and ct.bill_to_customer_id = rac_bill.cust_account_id

           and rac_bill.party_id = rac_bill_party.party_id

           and xte.entity_code = 'TRANSACTIONS'

           and 'REC' = gd.account_class

           and 'Y' = gd.latest_rec_flag

           and ct.created_by = fu.user_id

           and xah.event_type_code = 'CM_CREATE'

           and xah.description not like '%SH-保证金退回 %'

           and nvl(ct.doc_sequence_value, 1) =

               nvl(:sequence_value, nvl(ct.doc_sequence_value, 1))

         &p1

           and to_char(gd.gl_date, 'yyyy-mm-dd') between

               to_char(:startdate, 'yyyy-mm-dd') and

               to_char(:enddate, 'yyyy-mm-dd')

         group by rac_bill_party.party_name,

                  ct.invoice_currency_code,

                  ct.exchange_rate,

                  rac_bill.account_number,

                  ct.comments,

                  substr(gcc_ori.concatenated_segments,

                         instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,

                         instr(gcc_ori.concatenated_segments, '-', 1, 2)),

                  substr(fa_rx_flex_pkg.get_description(101,

                                                        'GL#',

                                                        gcc_ori.chart_of_accounts_id,

                                                        'ALL',

                                                        gcc_ori.concatenated_segments),

                         instr(fa_rx_flex_pkg.get_description(101,

                                                              'GL#',

                                                              gcc_ori.chart_of_accounts_id,

                                                              'ALL',

                                                              gcc_ori.concatenated_segments),

                               '-',

                               1,

                               1) + 1,

                         instr(fa_rx_flex_pkg.get_description(101,

                                                              'GL#',

                                                              gcc_ori.chart_of_accounts_id,

                                                              'ALL',

                                                              gcc_ori.concatenated_segments),

                               '-',

                               1,

                               2) - 1)

        union all

        

        select rac_bill_party.party_name as user_name,

               ct.invoice_currency_code as currency_code,

               ct.exchange_rate,

               rac_bill.account_number,

               ct.comments as descrption,

               substr(gcc_ori.concatenated_segments,

                      instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,

                      instr(gcc_ori.concatenated_segments, '-', 1, 2)) as code_accounts,

               substr(fa_rx_flex_pkg.get_description(101,

                                                     'GL#',

                                                     gcc_ori.chart_of_accounts_id,

                                                     'ALL',

                                                     gcc_ori.concatenated_segments),

                      instr(fa_rx_flex_pkg.get_description(101,

                                                           'GL#',

                                                           gcc_ori.chart_of_accounts_id,

                                                           'ALL',

                                                           gcc_ori.concatenated_segments),

                            '-',

                            1,

                            1) + 1,

                      instr(fa_rx_flex_pkg.get_description(101,

                                                           'GL#',

                                                           gcc_ori.chart_of_accounts_id,

                                                           'ALL',

                                                           gcc_ori.concatenated_segments),

                            '-',

                            1,

                            2) - 1) sourcedescription,

               sum(xal.entered_dr) as entered_dr,

               sum(xal.entered_cr) as entered_cr,

               sum(xal.accounted_dr) as accounted_dr,

               sum(xal.accounted_cr) as accounted_cr,

               to_char(:startdate, 'yyyy-mm-dd') as startdate,

               to_char(:enddate, 'yyyy-mm-dd') as enddate

          from xla_ae_lines                 xal,

               xla_ae_headers               xah,

               xla.xla_transaction_entities xte,

               gl_code_combinations_kfv     gcc_ori,

               ra_cust_trx_line_gl_dist_all gd,

               hz_cust_accounts             rac_bill,

               hz_parties                   rac_bill_party,

               ra_customer_trx              ct,

               fnd_user                     fu

         where xal.application_id = xah.application_id

           and xal.ae_header_id = xah.ae_header_id

           and xte.entity_id = xah.entity_id

           and xal.code_combination_id = gcc_ori.code_combination_id

           and xah.application_id = 222

           and xte.source_id_int_1 = ct.customer_trx_id

           and ct.customer_trx_id = gd.customer_trx_id

           and ct.bill_to_customer_id = rac_bill.cust_account_id

           and rac_bill.party_id = rac_bill_party.party_id

           and xte.entity_code = 'TRANSACTIONS'

           and 'REC' = gd.account_class

           and 'Y' = gd.latest_rec_flag

           and ct.created_by = fu.user_id

           and xah.event_type_code = 'INV_CREATE'

           and xah.description not like '%SH-保证金退回 %'

           and nvl(ct.doc_sequence_value, 1) =

               nvl(:sequence_value, nvl(ct.doc_sequence_value, 1))

         &p1

           and to_char(gd.gl_date, 'yyyy-mm-dd') between

               to_char(:startdate, 'yyyy-mm-dd') and

               to_char(:enddate, 'yyyy-mm-dd')

         group by rac_bill_party.party_name,

                  ct.invoice_currency_code,

                  ct.exchange_rate,

                  rac_bill.account_number,

                  ct.comments,

                  substr(gcc_ori.concatenated_segments,

                         instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,

                         instr(gcc_ori.concatenated_segments, '-', 1, 2)),

                  substr(fa_rx_flex_pkg.get_description(101,

                                                        'GL#',

                                                        gcc_ori.chart_of_accounts_id,

                                                        'ALL',

                                                        gcc_ori.concatenated_segments),

                         instr(fa_rx_flex_pkg.get_description(101,

                                                              'GL#',

                                                              gcc_ori.chart_of_accounts_id,

                                                              'ALL',

                                                              gcc_ori.concatenated_segments),

                               '-',

                               1,

                               1) + 1,

                         instr(fa_rx_flex_pkg.get_description(101,

                                                              'GL#',

                                                              gcc_ori.chart_of_accounts_id,

                                                              'ALL',

                                                              gcc_ori.concatenated_segments),

                               '-',

                               1,

                               2) - 1))



 group by user_name,

          currency_code,

          exchange_rate,

          account_number,

          descrption,

          code_accounts,

          sourcedescription



--application_id  的值 .从ap来的数据为200， 从ar来的数据就是222 

select * from fnd_application

--EVENT_TYPE_CODE

猜想：xla_ae_headers，xla_ae_lines是类似总账汇总的表，分配行“分类”为“未获收入”的借和贷行，在line表中只显示一行，金额为0。