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。