VIEW: APPS.ISC_EDW_BOOK_TO_SHIP_CYCLE_S

Source

View Text - Preformatted

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, max(bookings.date_latest_pick) - bookings.booked_date BOOK_TO_LATEST_PICK_CYCLE, max(bookings.date_latest_ship) - bookings.booked_date BOOK_TO_LATEST_SHIP_CYCLE, bookings.line_id FROM ISC_EDW_BOOKINGS_F bookings, EDW_GEOGRAPHY_M geo, EDW_ITEMS_M item, EDW_TIME_M time, EDW_ORGANIZATION_M org, EDW_LOOKUP_M lkup WHERE item.irev_item_revision_pk_key = bookings.top_model_fk_key AND time.cday_cal_day_pk_key = bookings.date_booked_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 lkup.lucd_lookup_code_pk_key = bookings.order_category_fk_key AND lkup.lucd_lookup_code <> 'RETURN' AND bookings.shippable_flag ='Y' GROUP BY bookings.booked_date, bookings.line_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

View Text - HTML Formatted

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
, MAX(BOOKINGS.DATE_LATEST_PICK) - BOOKINGS.BOOKED_DATE BOOK_TO_LATEST_PICK_CYCLE
, MAX(BOOKINGS.DATE_LATEST_SHIP) - BOOKINGS.BOOKED_DATE BOOK_TO_LATEST_SHIP_CYCLE
, BOOKINGS.LINE_ID
FROM ISC_EDW_BOOKINGS_F BOOKINGS
, EDW_GEOGRAPHY_M GEO
, EDW_ITEMS_M ITEM
, EDW_TIME_M TIME
, EDW_ORGANIZATION_M ORG
, EDW_LOOKUP_M LKUP
WHERE ITEM.IREV_ITEM_REVISION_PK_KEY = BOOKINGS.TOP_MODEL_FK_KEY
AND TIME.CDAY_CAL_DAY_PK_KEY = BOOKINGS.DATE_BOOKED_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 LKUP.LUCD_LOOKUP_CODE_PK_KEY = BOOKINGS.ORDER_CATEGORY_FK_KEY
AND LKUP.LUCD_LOOKUP_CODE <> 'RETURN'
AND BOOKINGS.SHIPPABLE_FLAG ='Y' GROUP BY BOOKINGS.BOOKED_DATE
, BOOKINGS.LINE_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