拆分日记账追溯视图-来源应收类型为调整

select gjl.je_header_id,
       
       gjl.je_line_num,
       
       gjh.je_source,
       
       gjh.name,
       
       gjh.doc_sequence_value,
       
       xag.third_party_id vendor_id,
       
       xag.third_party_name vendor_name,
       
       xag.third_party_sub_id vendor_site_id,
       
       xag.third_party_sub_name vendor_address,
       
       null gl_desc,
       
       xag.trx_type_name invoice_desc, --发票说明（摘要）
       
       gjh.default_effective_date, --GL日期
       
       gcc.segment4, --科目
       
       xag.entered_dr,
       
       xag.entered_cr

  from gl_je_headers_v gjh,
       
       gl_je_lines_v gjl,
       
       (select jeh.je_header_id je_header_id,
               
               r.je_line_num je_line_num,
               
               222 application_id,
               
               adj.org_id org_id,
               
               trxact.name trx_type_name,
               
               ard.amount_dr entered_dr,
               
               ard.amount_cr entered_cr,
               
               ard.third_party_id third_party_id,
               
               cust_acct.account_number third_party_number,
               
               substrb(party.party_name, 1, 50) third_party_name,
               
               ard.third_party_sub_id third_party_sub_id,
               
               su.location third_party_sub_name
        
          from gl_je_headers jeh,
               
               gl_import_references r,
               
               ar_distributions_all ard,
               
               ar_adjustments_all adj,
               
               ra_customer_trx_all ct,
               
               ra_customer_trx_all ct_chg,
               
               ar_receivables_trx_all trxact,
               
               hz_cust_site_uses_all su,
               
               hz_cust_accounts cust_acct,
               
               hz_parties party,
               
               ra_cust_trx_types_all ctt,
               
               fnd_document_sequences seq,
               
               gl_daily_conversion_types glct
        
         where ard.source_table = 'ADJ'
              
           and ard.source_id = adj.adjustment_id
              
           and nvl(ard.org_id, -99) = nvl(adj.org_id, -99)
              
           and glct.conversion_type(+) = ard.currency_conversion_type
              
           and seq.doc_sequence_id(+) = adj.doc_sequence_id
              
           and ct.customer_trx_id = adj.customer_trx_id
              
           and nvl(ct.org_id, -99) = nvl(adj.org_id, -99)
              
           and nvl(ctt.org_id, -99) = nvl(ct.org_id, -99)
              
           and ctt.cust_trx_type_id = ct.cust_trx_type_id
              
           and su.site_use_id = ard.third_party_sub_id
              
           and nvl(su.org_id, -99) = nvl(ard.org_id, -99)
              
           and cust_acct.cust_account_id = ard.third_party_id
              
           and cust_acct.party_id = party.party_id
              
           and ct_chg.customer_trx_id(+) = adj.chargeback_customer_trx_id
              
           and nvl(ct_chg.org_id(+), -99) = nvl(adj.org_id, -99)
              
           and trxact.receivables_trx_id(+) = adj.receivables_trx_id
              
           and nvl(trxact.org_id(+), -99) = nvl(adj.org_id, -99)
              
           and ard.line_id = to_number(r.reference_3)
              
           and r.je_header_id = jeh.je_header_id
              
           and jeh.je_category = 'Adjustment') xag,
       
       gl_code_combinations_kfv gcc

 where xag.application_id = 222
      
   and gjh.je_source = 'Receivables' --应收款管理系统
      
   and gjl.je_header_id = gjh.je_header_id
      
   and gjh.status = 'P'
      
   and gjl.code_combination_id = gcc.code_combination_id
      
   and gcc.segment2 = nvl('02', gcc.segment2) --事业部段，02表示液态奶事业部
      
   and gcc.segment4 = '22410302' --客护保证金 Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4
      
   and xag.je_header_id = gjl.je_header_id
      
   and xag.je_line_num = gjl.je_line_num
      
   and xag.third_party_id = nvl(p_vendor_name, xag.third_party_id) --供应商
      
   and xag.third_party_sub_id = nvl(p_vendor_site, xag.third_party_sub_id) --地点
      
   and gjh.period_name = '2009-01'