VIEW: APPS.PO_ALERT_CONTRACT_VIEW2

Source

View Text - Preformatted

SELECT ph.po_header_id PO_HEADER_ID, NVL(SUM(DECODE(c.minimum_accountable_unit, NULL, ROUND ((pll.quantity - NVL(pll.quantity_cancelled, 0)) * NVL(pll.price_override, 0), c.precision ), ROUND ((pll.quantity - NVL(pll.quantity_cancelled, 0)) * NVL(pll.price_override, 0) / c.minimum_accountable_unit ) * c.minimum_accountable_unit ) ), 0) total_amount, PH.ORG_ID ORG_ID FROM po_line_locations_all pll, po_lines_all pl, fnd_currencies c, po_headers ph WHERE ph.currency_code = c.currency_code AND pl.po_header_id = pll.po_header_id AND pl.po_line_id = pll.po_line_id AND pl.contract_id = ph.po_header_id AND ph.type_lookup_code = 'CONTRACT' AND ph.approved_date is not null GROUP BY ph.po_header_id, PH.ORG_ID

View Text - HTML Formatted

SELECT PH.PO_HEADER_ID PO_HEADER_ID
, NVL(SUM(DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND ((PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)) * NVL(PLL.PRICE_OVERRIDE
, 0)
, C.PRECISION )
, ROUND ((PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)) * NVL(PLL.PRICE_OVERRIDE
, 0) / C.MINIMUM_ACCOUNTABLE_UNIT ) * C.MINIMUM_ACCOUNTABLE_UNIT ) )
, 0) TOTAL_AMOUNT
, PH.ORG_ID ORG_ID
FROM PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL PL
, FND_CURRENCIES C
, PO_HEADERS PH
WHERE PH.CURRENCY_CODE = C.CURRENCY_CODE
AND PL.PO_HEADER_ID = PLL.PO_HEADER_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PL.CONTRACT_ID = PH.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE = 'CONTRACT'
AND PH.APPROVED_DATE IS NOT NULL GROUP BY PH.PO_HEADER_ID
, PH.ORG_ID