VIEW: APPS.PO_ALERT_BLANKET_VIEW

Source

View Text - Preformatted

SELECT ph.segment1 , ph.end_date , NVL(DECODE(c.minimum_accountable_unit, NULL, ROUND(ph.amount_limit, c.precision), ROUND(ph.amount_limit/c.minimum_accountable_unit)* c.minimum_accountable_unit),0), ph.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), NVL(DECODE(c.minimum_accountable_unit, NULL, ROUND(ph.blanket_total_amount, c.precision), ROUND(ph.blanket_total_amount/c.minimum_accountable_unit)* c.minimum_accountable_unit),0), 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), pnc.notification_id, PH.ORG_ID FROM PO_LINE_LOCATIONS_ALL PLL, FND_CURRENCIES C, PO_NOTIFICATION_CONTROLS PNC, HR_EMPLOYEES_CURRENT_V HRE, FINANCIALS_SYSTEM_PARAMS_ALL FSP, PO_HEADERS PH WHERE ph.currency_code = c.currency_code AND NVL(ph.cancel_flag, 'N') = 'N' AND NVL(ph.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND ph.approved_date is not null AND ph.type_lookup_code = 'BLANKET' AND nvl(ph.global_agreement_flag,'N') = 'N' AND pll.po_header_id(+) = ph.po_header_id AND pll.shipment_type(+) != 'PRICE BREAK' AND ph.po_header_id = pnc.po_header_id AND ph.agent_id = hre.employee_id AND FSP.ORG_ID = PH.ORG_ID AND hre.business_group_id = fsp.business_group_id GROUP BY ph.segment1, ph.end_date, ph.amount_limit, ph.currency_code, hre.email_address, pnc.start_date_active, pnc.end_date_active, pnc.notification_condition_code, pnc.notification_amount, pnc.notification_qty_percentage, ph.blanket_total_amount, c.minimum_accountable_unit, c.precision, pnc.notification_id,PH.ORG_ID UNION ALL Select ph.segment1 , ph.end_date , NVL(DECODE(c.minimum_accountable_unit, NULL, ROUND(ph.amount_limit, c.precision), ROUND(ph.amount_limit/c.minimum_accountable_unit)* c.minimum_accountable_unit),0), ph.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), NVL(DECODE(c.minimum_accountable_unit, NULL, ROUND(ph.blanket_total_amount, c.precision), ROUND(ph.blanket_total_amount/c.minimum_accountable_unit)* c.minimum_accountable_unit),0), 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), pnc.notification_id, PH.ORG_ID From po_line_locations_all pll, fnd_currencies c, po_notification_controls pnc, per_workforce_current_x hre, po_headers_all ph WHERE ph.currency_code = c.currency_code AND NVL(ph.cancel_flag, 'N') = 'N' AND NVL(ph.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND ph.approved_date is not null AND ph.type_lookup_code = 'BLANKET' AND nvl(ph.global_agreement_flag,'N') = 'Y' AND pll.from_header_id(+) = ph.po_header_id AND pll.shipment_type(+) != 'PRICE BREAK' AND ph.po_header_id = pnc.po_header_id AND ph.agent_id = hre.person_id GROUP BY ph.segment1, ph.end_date, ph.amount_limit, ph.currency_code, hre.email_address, pnc.start_date_active, pnc.end_date_active, pnc.notification_condition_code, pnc.notification_amount, pnc.notification_qty_percentage, ph.blanket_total_amount, c.minimum_accountable_unit, c.precision, pnc.notification_id,PH.ORG_ID

View Text - HTML Formatted

SELECT PH.SEGMENT1
, PH.END_DATE
, NVL(DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(PH.AMOUNT_LIMIT
, C.PRECISION)
, ROUND(PH.AMOUNT_LIMIT/C.MINIMUM_ACCOUNTABLE_UNIT)* C.MINIMUM_ACCOUNTABLE_UNIT)
, 0)
, PH.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)
, NVL(DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(PH.BLANKET_TOTAL_AMOUNT
, C.PRECISION)
, ROUND(PH.BLANKET_TOTAL_AMOUNT/C.MINIMUM_ACCOUNTABLE_UNIT)* C.MINIMUM_ACCOUNTABLE_UNIT)
, 0)
, 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)
, PNC.NOTIFICATION_ID
, PH.ORG_ID
FROM PO_LINE_LOCATIONS_ALL PLL
, FND_CURRENCIES C
, PO_NOTIFICATION_CONTROLS PNC
, HR_EMPLOYEES_CURRENT_V HRE
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_HEADERS PH
WHERE PH.CURRENCY_CODE = C.CURRENCY_CODE
AND NVL(PH.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PH.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, 'FINALLY CLOSED')
AND PH.APPROVED_DATE IS NOT NULL
AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND NVL(PH.GLOBAL_AGREEMENT_FLAG
, 'N') = 'N'
AND PLL.PO_HEADER_ID(+) = PH.PO_HEADER_ID
AND PLL.SHIPMENT_TYPE(+) != 'PRICE BREAK'
AND PH.PO_HEADER_ID = PNC.PO_HEADER_ID
AND PH.AGENT_ID = HRE.EMPLOYEE_ID
AND FSP.ORG_ID = PH.ORG_ID
AND HRE.BUSINESS_GROUP_ID = FSP.BUSINESS_GROUP_ID GROUP BY PH.SEGMENT1
, PH.END_DATE
, PH.AMOUNT_LIMIT
, PH.CURRENCY_CODE
, HRE.EMAIL_ADDRESS
, PNC.START_DATE_ACTIVE
, PNC.END_DATE_ACTIVE
, PNC.NOTIFICATION_CONDITION_CODE
, PNC.NOTIFICATION_AMOUNT
, PNC.NOTIFICATION_QTY_PERCENTAGE
, PH.BLANKET_TOTAL_AMOUNT
, C.MINIMUM_ACCOUNTABLE_UNIT
, C.PRECISION
, PNC.NOTIFICATION_ID
, PH.ORG_ID UNION ALL SELECT PH.SEGMENT1
, PH.END_DATE
, NVL(DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(PH.AMOUNT_LIMIT
, C.PRECISION)
, ROUND(PH.AMOUNT_LIMIT/C.MINIMUM_ACCOUNTABLE_UNIT)* C.MINIMUM_ACCOUNTABLE_UNIT)
, 0)
, PH.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)
, NVL(DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(PH.BLANKET_TOTAL_AMOUNT
, C.PRECISION)
, ROUND(PH.BLANKET_TOTAL_AMOUNT/C.MINIMUM_ACCOUNTABLE_UNIT)* C.MINIMUM_ACCOUNTABLE_UNIT)
, 0)
, 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)
, PNC.NOTIFICATION_ID
, PH.ORG_ID
FROM PO_LINE_LOCATIONS_ALL PLL
, FND_CURRENCIES C
, PO_NOTIFICATION_CONTROLS PNC
, PER_WORKFORCE_CURRENT_X HRE
, PO_HEADERS_ALL PH
WHERE PH.CURRENCY_CODE = C.CURRENCY_CODE
AND NVL(PH.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PH.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, 'FINALLY CLOSED')
AND PH.APPROVED_DATE IS NOT NULL
AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND NVL(PH.GLOBAL_AGREEMENT_FLAG
, 'N') = 'Y'
AND PLL.FROM_HEADER_ID(+) = PH.PO_HEADER_ID
AND PLL.SHIPMENT_TYPE(+) != 'PRICE BREAK'
AND PH.PO_HEADER_ID = PNC.PO_HEADER_ID
AND PH.AGENT_ID = HRE.PERSON_ID GROUP BY PH.SEGMENT1
, PH.END_DATE
, PH.AMOUNT_LIMIT
, PH.CURRENCY_CODE
, HRE.EMAIL_ADDRESS
, PNC.START_DATE_ACTIVE
, PNC.END_DATE_ACTIVE
, PNC.NOTIFICATION_CONDITION_CODE
, PNC.NOTIFICATION_AMOUNT
, PNC.NOTIFICATION_QTY_PERCENTAGE
, PH.BLANKET_TOTAL_AMOUNT
, C.MINIMUM_ACCOUNTABLE_UNIT
, C.PRECISION
, PNC.NOTIFICATION_ID
, PH.ORG_ID