|
|
|
关于PO 和PR 的联系问题 |
热 ★★★★ |
|
关于PO 和PR 的联系问题 |
|
作者:闵涛 文章来源:闵涛的学习笔记 点击数:1443 更新时间:2009/4/22 23:09:08 |
|
; 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] 没有相关教程
|
|
教程录入:mintao 责任编辑:mintao |
|
|
上一篇教程: 通用高效分页存储过程代码 下一篇教程: 前触发器和后触发器简介(downmoon) |
|
|
【字体:小 大】【发表评论】【加入收藏】【告诉好友】【打印此文】【关闭窗口】 |
|
注:本站部分文章源于互联网,版权归原作者所有!如有侵权,请原作者与本站联系,本站将立即删除! 本站文章除特别注明外均可转载,但需注明出处! [MinTao学以致用网] |
网友评论:(只显示最新10条。评论内容只代表网友观点,与本站立场无关!) |
|
|
|
|
|
|
|
同类栏目 |
|
|
赞助链接 |
|
|
500 - 内部服务器错误。
|
|
|
|
|
|