eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.ISC_EDW_ORDER_FULFILL_SHIP_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, 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,to_char(to_date('01/01/1995','MM/DD/YYYY'),'DY')) - bookings.date_latest_pick)/7), 0,1, 1, DECODE( SIGN(max(NEXT_DAY(bookings.date_latest_ship,to_char(to_date('01/01/1995','MM/DD/YYYY'),'DY')) - bookings.date_latest_pick) -9),-1,1,0), 0) nowkendpick, DECODE( FLOOR(max(NEXT_DAY(bookings.date_latest_ship,to_char(to_date('01/01/1995','MM/DD/YYYY'),'DY')) - bookings.booked_date)/7), 0,1, 1, DECODE( SIGN(max(NEXT_DAY(bookings.date_latest_ship,to_char(to_date('01/01/1995','MM/DD/YYYY'),'DY')) - bookings.booked_date) -9),-1,1,0), 0) nowkendship, 1 fulfilled, bookings.header_id||'-'||bookings.instance_fk_key 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 bookings.date_latest_ship is not null AND lkup.lucd_lookup_code_pk_key = bookings.order_category_fk_key AND lkup.lucd_lookup_code <> 'RETURN' AND bookings.qty_ordered <> 0 AND NOT EXISTS (SELECT 1 FROM isc_edw_bookings_f b, edw_lookup_m lkup WHERE b.order_category_fk_key = lkup.lucd_lookup_code_pk_key AND lkup.lucd_lookup_code <> 'RETURN' AND b.header_id = bookings.header_id AND (b.fulfillment_flag = 'N' or b.fulfillment_flag is NULL) AND b.qty_ordered <> 0 ) GROUP BY bookings.header_id, bookings.instance_fk_key, 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
, 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
, TO_CHAR(TO_DATE('01/01/1995'
, 'MM/DD/YYYY')
, 'DY')) - BOOKINGS.DATE_LATEST_PICK)/7)
, 0
, 1
, 1
, DECODE( SIGN(MAX(NEXT_DAY(BOOKINGS.DATE_LATEST_SHIP
, TO_CHAR(TO_DATE('01/01/1995'
, 'MM/DD/YYYY')
, 'DY')) - BOOKINGS.DATE_LATEST_PICK) -9)
, -1
, 1
, 0)
, 0) NOWKENDPICK
, DECODE( FLOOR(MAX(NEXT_DAY(BOOKINGS.DATE_LATEST_SHIP
, TO_CHAR(TO_DATE('01/01/1995'
, 'MM/DD/YYYY')
, 'DY')) - BOOKINGS.BOOKED_DATE)/7)
, 0
, 1
, 1
, DECODE( SIGN(MAX(NEXT_DAY(BOOKINGS.DATE_LATEST_SHIP
, TO_CHAR(TO_DATE('01/01/1995'
, 'MM/DD/YYYY')
, 'DY')) - BOOKINGS.BOOKED_DATE) -9)
, -1
, 1
, 0)
, 0) NOWKENDSHIP
, 1 FULFILLED
, BOOKINGS.HEADER_ID||'-'||BOOKINGS.INSTANCE_FK_KEY 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 BOOKINGS.DATE_LATEST_SHIP IS NOT NULL
AND LKUP.LUCD_LOOKUP_CODE_PK_KEY = BOOKINGS.ORDER_CATEGORY_FK_KEY
AND LKUP.LUCD_LOOKUP_CODE <> 'RETURN'
AND BOOKINGS.QTY_ORDERED <> 0
AND NOT EXISTS (SELECT 1
FROM ISC_EDW_BOOKINGS_F B
, EDW_LOOKUP_M LKUP
WHERE B.ORDER_CATEGORY_FK_KEY = LKUP.LUCD_LOOKUP_CODE_PK_KEY
AND LKUP.LUCD_LOOKUP_CODE <> 'RETURN'
AND B.HEADER_ID = BOOKINGS.HEADER_ID
AND (B.FULFILLMENT_FLAG = 'N' OR B.FULFILLMENT_FLAG IS NULL)
AND B.QTY_ORDERED <> 0 ) GROUP BY BOOKINGS.HEADER_ID
, BOOKINGS.INSTANCE_FK_KEY
, 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