ap_invoice_distributions_all与PO表关联问题

在ap_invoice_distributions_all中有时rcv_transaciton_id为空,有时却又是有值的, 这是为什么呢?(请参考po_line_locations_all.match_option)

 

 

或许你还记得我们在发票工作台上选择匹配时,有时是Match PO,有时却是Match Receipt,其实这就是原因所在, 当然影响这个匹配项的根本还是PO的shipment上的一个开关:是Receipt,还是Purchase Order,如果这里是Receipt,在匹配PO时,只能选择Receipt(匹配成功保存后rcv_transaciton_id是有值的),反之在匹配值只能选择Purchase Order(匹配成功保存后rcv_transaciton_id是没有值的)

 

其实在这个ap_invoice_distributions_all还有个po_distribution_id字段,不管rcv_transaciton_id是否为空,只要是和PO建立关系的,po_distribution_id字段总归是有值的.

 

 

现有这样的需求:查询---- 发票号码,接收号码,采购订单号码

(我总是会使用po_distribution_id作为条件,而不要使用rcv_transaciton_id)

----------------------------------------

 

select  ai.invoice_num,
       rsh.receipt_num,
       ph.segment1,
       aid.distribution_line_number,
       aid.rcv_transaction_id,
       aid.po_distribution_id
   from  ap_invoices_all              ai,
       ap_invoice_distributions_all aid,
       po_distributions_all         pd,
       rcv_transactions             rt,
       rcv_shipment_lines           rsl,
       rcv_shipment_headers         rsh,
       po_headers_all               ph
  where  ai.invoice_id  =  aid.invoice_id
    and  aid.set_of_books_id  =   ' &sob '
    and  aid.period_name  =   ' &period_name '
    and  aid.po_distribution_id  =  pd.po_distribution_id
    and  pd.po_distribution_id  =  rt.po_distribution_id
    and  rt.po_distribution_id  =  rsl.po_distribution_id
    and  rsl.shipment_line_id  =  rt.shipment_line_id
    and  rsh.shipment_header_id  =  rsl.shipment_header_id
    and  rt.destination_type_code  =   ' RECEIVING '
    and  rt.po_header_id  =  ph.po_header_id
  order   by   1 ,
           2

 

 

 

For more information, pls refer to Metalink ID 181429.1