学步园

来源于采购凭证类型为采购接收（视图：XLA_PO_AEL_GL_V）

select gjh.je_source,
       
       gjh.name,
       
       gjh.doc_sequence_value,
       
       xag.vendor_id,
       
       xag.vendor_name vendor_name, --供应商名称
       
       xag.vendor_site_id,
       
       xag.vendor_site_code vendor_address, --供应商地点
       
       null gl_desc,
       
       '采购接收\向供应商退货' invoice_desc, --发票说明（摘要）
       
       gjh.default_effective_date, --GL日期
       
       -- xag.accounting_date,
       
       gcc.segment4, --科目
       
       nvl(xag.entered_dr, 0) dr_quantity, --借方
       
       nvl(xag.entered_cr, 0) cr_quantity, --贷方

  from gl.gl_je_headers gjh,
       
       gl.gl_je_lines gjl,
       
       (select 201,
               
               poh.org_id,
               
               rrs.code_combination_id,
               
               rrs.entered_dr,
               
               rrs.entered_cr,
               
               rrs.accounted_dr,
               
               rrs.accounted_cr,
               
               rrs.accounting_date,
               
               -- RRS.CURRENCY_CONVERSION_DATE,
               
               pov.vendor_id,
               
               pov.vendor_name,
               
               poh.vendor_site_id,
               
               pvs.vendor_site_code,
               
               rrs.accounting_date,
               
               --NVL(RRS.SOURCE_DOC_QUANTITY, NVL(RCT.SOURCE_DOC_QUANTITY, 0)),
               
               --NVL(PLL.PRICE_OVERRIDE, POL.UNIT_PRICE),
               
               r.je_header_id,
               
               r.je_line_num
        
          from gl_import_references r,
               
               rcv_receiving_sub_ledger rrs,
               
               po_distributions_all pod,
               
               po_line_locations_all pll,
               
               po_lines_all pol,
               
               po_headers_all poh,
               
               po_vendors pov,
               
               po_vendor_sites_all pvs
        
         where pov.vendor_id = poh.vendor_id
              
           and pvs.vendor_site_id = poh.vendor_site_id
              
           and nvl(pvs.org_id, -99) = nvl(poh.org_id, -99)
              
           and poh.po_header_id = pol.po_header_id
              
           and pol.po_line_id = pll.po_line_id
              
           and pll.line_location_id = pod.line_location_id
              
           and pod.po_distribution_id = rrs.reference3
              
           and r.gl_sl_link_table = 'RSL'
              
           and rrs.gl_sl_link_id = r.gl_sl_link_id
              
           and rrs.rcv_transaction_id = r.reference_5) xag,
       
       -- XLA_PO_AEL_GL_V xag, --采购来源追溯视图
       
       rcv_vrc_txs_vendint_v rvh, --追溯到采购订单表
       
       gl_code_combinations_kfv gcc
 where gjh.je_source = 'Purchasing'
      
   and gjl.je_header_id = gjh.je_header_id
      
   and gjh.status = 'P'
      
   and gjl.code_combination_id = gcc.code_combination_id
      
   and gcc.segment4 = '220202' --应付无票
      
   and gcc.segment2 = nvl(p_syb_name, gcc.segment2) --事业部
      
   and xag.vendor_id = nvl(p_vendor_name, xag.vendor_id)
      
   and xag.vendor_site_id = nvl(p_vendor_site, xag.vendor_site_id)
      
   and gjh.period_name between '2009-01' and '2009-01'
      
   and xag.je_header_id = gjl.je_header_id
      
   and xag.je_line_num = gjl.je_line_num
      
   and xag.rcv_receipt_num = rvh.order_num --采购订单号