[Home] [Help]
| Product: | ISC - Supply Chain Intelligence (Obsolete) |
|---|---|
| Description: | This view is used by the One-Day Book to Ship reports |
| Implementation/DBA Data: | Not implemented in this database |
SELECT GEO.ARE1_AREA1_PK_KEY AREA_FK
, ORG.BGRP_BUSINESS_GROUP_PK_KEY BUS_GRP_FK
, ITEM.CO11_CATEGORY_PK_KEY CATG_FK
, GEO.COUN_COUNTRY_PK_KEY COUNTRY_FK
, ORG.ORGA_ORGANIZATION_PK_KEY INT_ORGA_FK
, ORG.LGET_LEGAL_ENTITY_PK_KEY LEGAL_ENT_FK
, ORG.OPER_OPERATING_UNIT_PK_KEY OPER_UNIT_FK
, BOOKINGS.ORDER_SOURCE_FK_KEY ORDER_SOURCE_FK
, BOOKINGS.ORDER_TYPE_FK_KEY ORDER_TYPE_FK
, TIME.ECPR_CAL_PERIOD_PK_KEY PERIOD_FK
, TIME.ECQR_CAL_QTR_PK_KEY QTR_FK
, GEO.REGN_REGION_PK_KEY REGION_FK
, BOOKINGS.SALES_CHANNEL_FK_KEY SALES_CHANNEL_FK
, TIME.ECYR_CAL_YEAR_PK_KEY YEAR_FK
, DECODE(SIGN(MAX(BOOKINGS.DATE_LATEST_SHIP-BOOKINGS.DATE_LATEST_PICK)-1)
, 1
, 0
, NULL
, 0
, 1) ONEDAYPICK
, DECODE(SIGN(MAX(BOOKINGS.DATE_LATEST_SHIP-BOOKINGS.BOOKED_DATE)-1)
, 1
, 0
, NULL
, 0
, 1) ONEDAYSHIP
, DECODE( FLOOR(MAX(NEXT_DAY(BOOKINGS.DATE_LATEST_SHIP
, 'SUNDAY') - BOOKINGS.DATE_LATEST_PICK)/7)
, 0
, 1
, 1
, DECODE( SIGN(MAX(NEXT_DAY(BOOKINGS.DATE_LATEST_SHIP
, 'SUNDAY') - BOOKINGS.DATE_LATEST_PICK) -9)
, -1
, 1
, 0)
, 0) NOWKENDPICK
, DECODE( FLOOR(MAX(NEXT_DAY(BOOKINGS.DATE_LATEST_SHIP
, 'SUNDAY') - BOOKINGS.BOOKED_DATE)/7)
, 0
, 1
, 1
, DECODE( SIGN(MAX(NEXT_DAY(BOOKINGS.DATE_LATEST_SHIP
, 'SUNDAY') - BOOKINGS.BOOKED_DATE) -9)
, -1
, 1
, 0)
, 0) NOWKENDSHIP
, 1 FULFILLED
, BOOKINGS.HEADER_ID
FROM ISC_EDW_BOOKINGS_F BOOKINGS
, EDW_GEOGRAPHY_M GEO
, EDW_ITEMS_M ITEM
, EDW_LOOKUP_M LKUP
, EDW_ORGANIZATION_M ORG
, EDW_TIME_M TIME
WHERE ITEM.IREV_ITEM_REVISION_PK_KEY = BOOKINGS.TOP_MODEL_FK_KEY
AND ORG.ORGA_ORGANIZATION_PK_KEY = BOOKINGS.INV_ORG_FK_KEY
AND GEO.GLOC_LOCATION_PK_KEY = BOOKINGS.SHIP_TO_LOC_FK_KEY
AND TIME.CDAY_CAL_DAY_PK_KEY = BOOKINGS.DATE_BOOKED_FK_KEY
AND SHIPPABLE_FLAG='Y'
AND LKUP.LUCD_LOOKUP_CODE_PK_KEY = BOOKINGS.ORDER_CATEGORY_FK_KEY
AND LKUP.LUCD_LOOKUP_CODE NOT IN ('RMA'
, 'RETURN')
AND BOOKINGS.HEADER_ID NOT IN (SELECT DISTINCT BOOKINGS.HEADER_ID
FROM ISC_EDW_BOOKINGS_F BOOKINGS
, EDW_LOOKUP_M LKUP
WHERE BOOKINGS.ORDER_CATEGORY_FK_KEY = LKUP.LUCD_LOOKUP_CODE_PK_KEY
AND LKUP.LUCD_LOOKUP_CODE NOT IN ('RMA'
, 'RETURN')
AND BOOKINGS.FULFILLMENT_FLAG IN (NULL
, 'N') ) GROUP BY BOOKINGS.HEADER_ID
, ITEM.CO11_CATEGORY_PK_KEY
, ORG.BGRP_BUSINESS_GROUP_PK_KEY
, ORG.ORGA_ORGANIZATION_PK_KEY
, ORG.LGET_LEGAL_ENTITY_PK_KEY
, ORG.OPER_OPERATING_UNIT_PK_KEY
, BOOKINGS.SALES_CHANNEL_FK_KEY
, GEO.ARE1_AREA1_PK_KEY
, GEO.COUN_COUNTRY_PK_KEY
, GEO.REGN_REGION_PK_KEY
, TIME.ECYR_CAL_YEAR_PK_KEY
, TIME.ECQR_CAL_QTR_PK_KEY
, TIME.ECPR_CAL_PERIOD_PK_KEY
, BOOKINGS.ORDER_SOURCE_FK_KEY
, BOOKINGS.ORDER_TYPE_FK_KEY