View: PO_ALERT_PLANNED_VIEW

Product:	PO - Purchasing
Description:	Planned purchase order
Implementation/DBA Data:	APPS.PO_ALERT_PLANNED_VIEW

View Text

SELECT POH.SEGMENT1
, POH.END_DATE
, NVL(DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(POH.AMOUNT_LIMIT
, C.PRECISION)
, ROUND(POH.AMOUNT_LIMIT/C.MINIMUM_ACCOUNTABLE_UNIT)* C.MINIMUM_ACCOUNTABLE_UNIT)
, 0)
, POH.CURRENCY_CODE
, HRE.EMAIL_ADDRESS
, NVL(PNC.START_DATE_ACTIVE
, SYSDATE-1)
, NVL(PNC.END_DATE_ACTIVE
, SYSDATE+1)
, PNC.NOTIFICATION_CONDITION_CODE
, NVL(DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(PNC.NOTIFICATION_AMOUNT
, C.PRECISION)
, ROUND(PNC.NOTIFICATION_AMOUNT/C.MINIMUM_ACCOUNTABLE_UNIT)* C.MINIMUM_ACCOUNTABLE_UNIT)
, 0)
, ROUND(PNC.NOTIFICATION_QTY_PERCENTAGE
, 2)
, 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 ) )
, DECODE (PV1.PO_HEADER_ID
, ''
, 0
, PV1.TOTAL_AMOUNT)
, PNC.NOTIFICATION_ID
, POH.ORG_ID
FROM PO_LINE_LOCATIONS_ALL PLL
, PO_ALERT_SCHEDULED_VIEW PV1
, FND_CURRENCIES C
, PO_NOTIFICATION_CONTROLS PNC
, HR_EMPLOYEES_CURRENT_V HRE
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_HEADERS POH
WHERE POH.CURRENCY_CODE = C.CURRENCY_CODE
AND NVL(POH.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POH.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, 'FINALLY CLOSED')
AND POH.APPROVED_DATE IS NOT NULL
AND POH.TYPE_LOOKUP_CODE = 'PLANNED'
AND POH.PO_HEADER_ID = PNC.PO_HEADER_ID
AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.SHIPMENT_TYPE = 'PLANNED'
AND PLL.PO_HEADER_ID = PV1.PO_HEADER_ID(+)
AND POH.AGENT_ID = HRE.EMPLOYEE_ID
AND POH.ORG_ID = FSP.ORG_ID
AND HRE.BUSINESS_GROUP_ID = FSP.BUSINESS_GROUP_ID GROUP BY POH.SEGMENT1
, POH.END_DATE
, POH.AMOUNT_LIMIT
, POH.CURRENCY_CODE
, HRE.EMAIL_ADDRESS
, PNC.START_DATE_ACTIVE
, PNC.END_DATE_ACTIVE
, PNC.NOTIFICATION_CONDITION_CODE
, PNC.NOTIFICATION_AMOUNT
, PNC.NOTIFICATION_QTY_PERCENTAGE
, PV1.PO_HEADER_ID
, PV1.TOTAL_AMOUNT
, C.MINIMUM_ACCOUNTABLE_UNIT
, C.PRECISION
, PNC.NOTIFICATION_ID
, POH.ORG_ID

Columns