要写一个采购报价costdown报表,

是用reports builder 开发,
就是原先的单价的查询PL/SQL 写不出来

如果原先有这个厂商该料号的报价,那么就查出来 ,如果没有,用新报价代替

感觉能写出这样复杂的语句,真不容易,呵呵

/* Formatted on 2006/04/18 16:49 (Formatter Plus v4.8.6) */
SELECT DISTINCT msi.segment1,
poh.segment1
|| '-'
|| pol.line_num
|| '-'
|| poll.shipment_num shipment_num,
poll.quantity - poll.quantity_cancelled quantity,
poh.currency_code, ROUND (pol.unit_price, 4),
ROUND (NVL (poh.rate, 1), 4) rate, pv.vendor_name,
ROUND (NVL (polold.unit_price, pol.unit_price), 4) old_price
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
mtl_system_items_b msi,
po_vendors pv,
po_lines_all polold,
po_vendors pvold,
po_headers_all pohold,
-- po_line_locations_all pollold,
mtl_system_items_b msiold
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND pol.item_id = msi.inventory_item_id
AND pol.org_id = msi.organization_id
AND pol.line_type_id = 1
AND pol.org_id = poh.org_id
AND pol.quantity > 0
AND pol.po_line_id = poll.po_line_id
AND poll.quantity - poll.quantity_cancelled > 0
AND pol.creation_date >= NVL (:p_fromdate, pol.creation_date)
AND pol.creation_date <= NVL (:p_todate, pol.creation_date)
AND poh.segment1 >= NVL (:p_fromsegment1, poh.segment1)
AND poh.segment1 <= NVL (:p_tosegment1, poh.segment1)
AND poh.org_id = NVL (:p_orgid, 141)
AND poh.vendor_id = pv.vendor_id
AND pvold.vendor_id = pv.vendor_id
AND msi.inventory_item_id = msiold.inventory_item_id
AND pohold.po_header_id = polold.po_header_id
AND polold.item_id = msiold.inventory_item_id
AND polold.line_type_id = 1
AND polold.org_id = pohold.org_id
AND pohold.currency_code = poh.currency_code
AND pohold.segment1 >= NVL (:p_fromsegment1, pohold.segment1)
AND pohold.segment1 <= NVL (:p_tosegment1, pohold.segment1)
AND polold.creation_date =
(SELECT MAX (poln.creation_date)
FROM po_lines_all poln,
po_headers_all pohn,
mtl_system_items_b msin,
po_vendors pvn
WHERE pohn.po_header_id = poln.po_header_id
AND poln.item_id = msin.inventory_item_id
AND pvn.vendor_id = pvold.vendor_id --58
AND msin.inventory_item_id = msiold.inventory_item_id
--62996
-- AND poln.item_id = msin.organization_id
AND pohn.currency_code = pohold.currency_code
AND pohn.segment1 >=
NVL (:p_fromsegment1, pohn.segment1)
AND pohn.segment1 <= NVL (:p_tosegment1, pohn.segment1)
AND poln.line_type_id = 1
AND poln.org_id = pohn.org_id
AND poln.creation_date >=
TRUNC (ADD_MONTHS (:p_fromdate, -6),
'month')
AND poln.creation_date <=
LAST_DAY (ADD_MONTHS (:p_fromdate, -1)))
ORDER BY pv.vendor_name, msi.segment1, poh.currency_code

最终上线的:

/* Formatted on 2006/04/19 13:48 (Formatter Plus v4.8.6) */
SELECT DISTINCT msi.segment1,
poh.segment1
|| '-'
|| pol.line_num
|| '-'
|| poll.shipment_num shipment_num,
poll.quantity - poll.quantity_cancelled quantity,
poh.currency_code, ROUND (pol.unit_price, 4) new_price,
ROUND (NVL (poh.rate, 1), 4) rate, pv.vendor_name,
ROUND (NVL (polold.unit_price, pol.unit_price), 4) old_price,
ROUND (NVL (polold.unit_price, pol.unit_price)
- pol.unit_price,
4
) costdowny,
ROUND ( NVL (polold.unit_price, pol.unit_price)
- pol.unit_price,
4
)
* ROUND (NVL (poh.rate, 1), 4) costdownb,
ROUND ( (poll.quantity - poll.quantity_cancelled)
* pol.unit_price,
4
)
* ROUND (NVL (poh.rate, 1), 4) purchase_total,
( ROUND ( (poll.quantity - poll.quantity_cancelled)
* NVL (polold.unit_price, pol.unit_price),
4
)
- ROUND ( (poll.quantity - poll.quantity_cancelled)
* pol.unit_price,
4
)
)
* ROUND (NVL (poh.rate, 1), 4) costdown_totalb,
papf.full_name, DECODE (pol.org_id,
0, 'MST',
141, 'NGB'
) org
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
mtl_system_items_b msi,
po_vendors pv,
po_lines_all polold,
po_vendors pvold,
po_headers_all pohold,
-- po_line_locations_all pollold,
mtl_system_items_b msiold,
per_all_people_f papf
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND pol.item_id = msi.inventory_item_id
AND pol.org_id = msi.organization_id
AND pol.line_type_id = 1
AND pol.org_id = poh.org_id
AND pol.quantity > 0
AND pol.po_line_id = poll.po_line_id
AND poll.quantity - poll.quantity_cancelled > 0
AND pol.creation_date >= NVL (:p_fromdate, pol.creation_date)
AND pol.creation_date <= NVL (:p_todate, pol.creation_date)
AND poh.segment1 >= NVL (:p_fromsegment1, poh.segment1)
AND poh.segment1 <= NVL (:p_tosegment1, poh.segment1)
AND poh.org_id = NVL (:p_orgid, 141)
AND poh.vendor_id = pv.vendor_id
AND pvold.vendor_id = pv.vendor_id
AND msi.inventory_item_id = msiold.inventory_item_id
AND pohold.po_header_id = polold.po_header_id
AND polold.item_id = msiold.inventory_item_id
AND polold.line_type_id = 1
AND polold.org_id = pohold.org_id
AND pohold.currency_code = poh.currency_code
AND pohold.segment1 >= NVL (:p_fromsegment1, pohold.segment1)
AND pohold.segment1 <= NVL (:p_tosegment1, pohold.segment1)
AND poh.agent_id = papf.person_id
AND papf.business_group_id = NVL (:p_orgid, 141)
AND papf.full_name like NVL(:p_full_name, papf.full_name )
AND polold.creation_date =
(SELECT MAX (poln.creation_date)
FROM po_lines_all poln,
po_headers_all pohn,
mtl_system_items_b msin,
po_vendors pvn
WHERE pohn.po_header_id = poln.po_header_id
AND poln.item_id = msin.inventory_item_id
AND pvn.vendor_id = pvold.vendor_id --58
AND msin.inventory_item_id = msiold.inventory_item_id
--62996
-- AND poln.item_id = msin.organization_id
AND pohn.currency_code = pohold.currency_code
AND pohn.segment1 >=
NVL (:p_fromsegment1, pohn.segment1)
AND pohn.segment1 <= NVL (:p_tosegment1, pohn.segment1)
AND poln.line_type_id = 1
AND poln.org_id = pohn.org_id
AND poln.creation_date >=
TRUNC (ADD_MONTHS (:p_fromdate, -6),
'month')
AND poln.creation_date <=
LAST_DAY (ADD_MONTHS (:p_fromdate, -1)))
ORDER BY pv.vendor_name, msi.segment1, poh.currency_code