VIEW: APPS.DDR_R_MFG_PRMTN_DTL_V

Source

View Text - Preformatted

SELECT MFG_SKU_ITEM_ID,ORG_CD,CLNDR_DT,LOWEST_DIST_ID from (SELECT p.MFG_SKU_ITEM_ID, p.ORG_CD, p.clndr_dt, first_value (p.PRMTN_ID ) over ( partition by p.ORG_CD ,p.MFG_SKU_ITEM_ID,p.CLNDR_DT order by p.distance ASC) lowest_dist_id from (select A.ORG_CD, A.mfg_sku_item_id, A.PRMTN_ID, B.CLNDR_DT, A.PRMTN_FROM_DT, A.PRMTN_TO_DT, (A.PRMTN_TO_DT -B.CLNDR_DT +(B.CLNDR_DT -A.PRMTN_FROM_DT) / 1000) distance FROM DDR_R_MFG_PRMTN_DTL A, DDR_R_DAY B WHERE(B.CLNDR_DT BETWEEN A.PRMTN_FROM_DT AND A.PRMTN_TO_DT) )p ) GROUP BY MFG_SKU_ITEM_ID, ORG_CD, clndr_dt, lowest_dist_id

View Text - HTML Formatted

SELECT MFG_SKU_ITEM_ID
, ORG_CD
, CLNDR_DT
, LOWEST_DIST_ID
FROM (SELECT P.MFG_SKU_ITEM_ID
, P.ORG_CD
, P.CLNDR_DT
, FIRST_VALUE (P.PRMTN_ID ) OVER ( PARTITION BY P.ORG_CD
, P.MFG_SKU_ITEM_ID
, P.CLNDR_DT ORDER BY P.DISTANCE ASC) LOWEST_DIST_ID
FROM (SELECT A.ORG_CD
, A.MFG_SKU_ITEM_ID
, A.PRMTN_ID
, B.CLNDR_DT
, A.PRMTN_FROM_DT
, A.PRMTN_TO_DT
, (A.PRMTN_TO_DT -B.CLNDR_DT +(B.CLNDR_DT -A.PRMTN_FROM_DT) / 1000) DISTANCE
FROM DDR_R_MFG_PRMTN_DTL A
, DDR_R_DAY B WHERE(B.CLNDR_DT BETWEEN A.PRMTN_FROM_DT
AND A.PRMTN_TO_DT) )P ) GROUP BY MFG_SKU_ITEM_ID
, ORG_CD
, CLNDR_DT
, LOWEST_DIST_ID