POR_LINE_LOCATIONS_SUM

Details

Schema: FUSION
Object owner: POR
Object type: VIEW

Columns

Name
REQUISITION_LINE_ID MIN_PO_SCH_NEED_BY_DATE MIN_PO_SCH_PROMISED_DATE MAX_PO_SCH_LAST_UPDATE_DATE SUM_PO_SCH_QUANTITY_RECEIVED SUM_PO_SCH_AMOUNT_RECEIVED MAX_PO_SCH_FULFILLED_DATE ALL_PO_SCH_FULFILLED_FLAG ANY_PO_SCH_FF_PAST_DUE_FLAG ALL_PO_SCH_CANCELED_FLAG

Name

REQUISITION_LINE_ID

MIN_PO_SCH_NEED_BY_DATE

MIN_PO_SCH_PROMISED_DATE

MAX_PO_SCH_LAST_UPDATE_DATE

SUM_PO_SCH_QUANTITY_RECEIVED

SUM_PO_SCH_AMOUNT_RECEIVED

MAX_PO_SCH_FULFILLED_DATE

ALL_PO_SCH_FULFILLED_FLAG

ANY_PO_SCH_FF_PAST_DUE_FLAG

ALL_PO_SCH_CANCELED_FLAG

Query

SQL_Statement
SELECT REQUISITION_LINE_ID, MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN NULL ELSE NEED_BY_DATE END) AS MIN_PO_SCH_NEED_BY_DATE, MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN NULL ELSE PROMISED_DATE END) AS MIN_PO_SCH_PROMISED_DATE, MAX(LAST_UPDATE_DATE) AS MAX_PO_SCH_LAST_UPDATE_DATE, SUM(NVL(QUANTITY_RECEIVED,0)) AS SUM_PO_SCH_QUANTITY_RECEIVED, SUM(NVL(AMOUNT_RECEIVED,0)) AS SUM_PO_SCH_AMOUNT_RECEIVED, MAX(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN NULL ELSE PO_SCH_FULFILLED_DATE END) AS MAX_PO_SCH_FULFILLED_DATE, MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN 'Z' WHEN PO_SCH_FULFILLED_DATE IS NULL THEN 'N' ELSE 'Y' END ) AS ALL_PO_SCH_FULFILLED_FLAG, MAX(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN 'A' WHEN (NVL(PO_SCH_FULFILLED_DATE,SYSDATE)-NVL(PROMISED_DATE, NEED_BY_DATE)) >0 THEN 'Y' ELSE 'N' END) AS ANY_PO_SCH_FF_PAST_DUE_FLAG, MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN 'Y' ELSE 'N' END) AS ALL_PO_SCH_CANCELED_FLAG FROM (SELECT POR_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID, PO_LINE_LOCATIONS_ALL.NEED_BY_DATE, PO_LINE_LOCATIONS_ALL.PROMISED_DATE, PO_LINE_LOCATIONS_ALL.LAST_UPDATE_DATE, PO_LINE_LOCATIONS_ALL.QUANTITY_RECEIVED, PO_LINE_LOCATIONS_ALL.AMOUNT_RECEIVED, CASE WHEN NVL(PO_LINE_LOCATIONS_ALL.RECEIPT_REQUIRED_FLAG,'N') = 'N' AND NVL(PO_LINE_LOCATIONS_ALL.INSPECTION_REQUIRED_FLAG,'N') = 'N' THEN LEAST (NVL(PO_LINE_LOCATIONS_ALL.SHIPMENT_CLOSED_DATE,PO_LINE_LOCATIONS_ALL.CLOSED_FOR_INVOICE_DATE), PO_LINE_LOCATIONS_ALL.CLOSED_FOR_INVOICE_DATE) ELSE LEAST(NVL(PO_LINE_LOCATIONS_ALL.SHIPMENT_CLOSED_DATE,PO_LINE_LOCATIONS_ALL.CLOSED_FOR_RECEIVING_DATE), PO_LINE_LOCATIONS_ALL.CLOSED_FOR_RECEIVING_DATE) END AS PO_SCH_FULFILLED_DATE, PO_LINE_LOCATIONS_ALL.SCHEDULE_STATUS FROM POR_REQ_DISTRIBUTIONS_ALL, PO_DISTRIBUTIONS_ALL , PO_LINE_LOCATIONS_ALL WHERE POR_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID AND PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID = PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID ) GROUP BY REQUISITION_LINE_ID

SQL_Statement

SELECT

REQUISITION_LINE_ID,

MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN NULL ELSE NEED_BY_DATE END)

AS MIN_PO_SCH_NEED_BY_DATE,

MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN NULL ELSE PROMISED_DATE

END) AS MIN_PO_SCH_PROMISED_DATE,

MAX(LAST_UPDATE_DATE) AS MAX_PO_SCH_LAST_UPDATE_DATE,

SUM(NVL(QUANTITY_RECEIVED,0)) AS SUM_PO_SCH_QUANTITY_RECEIVED,

SUM(NVL(AMOUNT_RECEIVED,0)) AS SUM_PO_SCH_AMOUNT_RECEIVED,

MAX(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN NULL ELSE

PO_SCH_FULFILLED_DATE END) AS MAX_PO_SCH_FULFILLED_DATE,

MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN 'Z' WHEN

PO_SCH_FULFILLED_DATE IS NULL THEN 'N' ELSE 'Y' END ) AS

ALL_PO_SCH_FULFILLED_FLAG,

MAX(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN 'A' WHEN

(NVL(PO_SCH_FULFILLED_DATE,SYSDATE)-NVL(PROMISED_DATE, NEED_BY_DATE)) >0 THEN

'Y' ELSE 'N' END) AS ANY_PO_SCH_FF_PAST_DUE_FLAG,

MIN(CASE WHEN SCHEDULE_STATUS = 'CANCELED' THEN 'Y' ELSE 'N' END) AS

ALL_PO_SCH_CANCELED_FLAG

FROM

(SELECT

POR_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID,

PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,

PO_LINE_LOCATIONS_ALL.PROMISED_DATE,

PO_LINE_LOCATIONS_ALL.LAST_UPDATE_DATE,

PO_LINE_LOCATIONS_ALL.QUANTITY_RECEIVED,

PO_LINE_LOCATIONS_ALL.AMOUNT_RECEIVED,

CASE

WHEN NVL(PO_LINE_LOCATIONS_ALL.RECEIPT_REQUIRED_FLAG,'N') = 'N' AND

NVL(PO_LINE_LOCATIONS_ALL.INSPECTION_REQUIRED_FLAG,'N') = 'N'

THEN LEAST

(NVL(PO_LINE_LOCATIONS_ALL.SHIPMENT_CLOSED_DATE,PO_LINE_LOCATIONS_ALL.CLOSED_FOR_INVOICE_DATE), PO_LINE_LOCATIONS_ALL.CLOSED_FOR_INVOICE_DATE)

ELSE

LEAST(NVL(PO_LINE_LOCATIONS_ALL.SHIPMENT_CLOSED_DATE,PO_LINE_LOCATIONS_ALL.CLOSED_FOR_RECEIVING_DATE), PO_LINE_LOCATIONS_ALL.CLOSED_FOR_RECEIVING_DATE)

END AS PO_SCH_FULFILLED_DATE,

PO_LINE_LOCATIONS_ALL.SCHEDULE_STATUS

FROM POR_REQ_DISTRIBUTIONS_ALL,

PO_DISTRIBUTIONS_ALL ,

PO_LINE_LOCATIONS_ALL

WHERE

POR_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID =

PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID

AND PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID =

PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

)

GROUP BY REQUISITION_LINE_ID