| ; pl.line_num po_lineno, pll.shipment_num po_shipment,
-- msi.segment1 item_no,
NVL (msi.must_use_approved_vendor_flag,''''N'''') avl_flag,
msi.segment1 || '''' '''' || pl.item_description descrip,
pll.quantity - NVL (pll.quantity_cancelled,0) qty,
NVL (pl.attribute1, pl.unit_meas_lookup_code) i_uom,
ph.currency_code currency, pl.unit_price u_price,
DECODE (ph.vendor_contact_id,
NULL,'''''''',
pvc.last_name || pvc.first_name
) contact_name,
ROUND (( (pll.quantity - NVL (pll.quantity_cancelled,0))
* pl.unit_price
),
4
) line_total,
ph.comments v_desc, pll.line_location_id location_id,
pv.vendor_id v_vendor_id, ph.vendor_site_id v_site_id,
pl.item_id item_id, pl.line_type_id line_type,
prl_line.segment1 req_num
FROM po_vendors pv,
po_vendor_sites_all pvs,
po_vendor_contacts pvc,
po_headers_all ph,
po_lines_all pl,
ap_terms ap,
po_line_locations_all pll,
mtl_system_items msi,
(SELECT prl.*, prh.segment1
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id) prl_line
WHERE pv.vendor_id = ph.vendor_id
AND pvs.vendor_site_id = ph.vendor_site_id
AND( ph.vendor_contact_id = pvc.vendor_contact_id
OR ph.vendor_contact_id ISNULL
) --best
AND ph.terms_id = ap.term_id
AND ph.org_id = NVL (:p_org_id,141)
AND ph.po_header_id = pl.po_header_id
AND pl.org_id = ph.org_id
AND pl.po_line_id = pll.po_line_id
AND pl.po_header_id = pll.po_header_id
AND pll.org_id = pl.org_id
AND pl.item_id = msi.inventory_item_id
AND pl.org_id = msi.organization_id
AND pll.quantity - NVL (pll.quantity_cancelled,0)>0
AND ph.segment1 IN
(NVL (:p_segment1, ph.segment1),
NVL (:p_segment2,''''''''),
NVL (:p_segment3,''''''''),
NVL (:p_segment4,''''''''),
NVL (:p_segment5,''''''''),
NVL (:p_segment6,''''''''),
NVL (:p_segment7,''''''''),
NVL (:p_segment8,''''''''),
NVL (:p_segment9,''''''''),
NVL (:p_segment10,''''''''))
AND pll.line_location_id = prl_line.line_location_id(+)--notice here :outer join
AND pll.po_header_id = ph.po_header_id
ORDERBY pl.line_num , pll.shipment_num, ph.segment1;
上一页 [1] [2] |
