或许你还记得我们在发票工作台上选择匹配时,有时是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
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