*************SETTING SESSION TO USE CHOOSE OPTIMIZER************* alter session set optimizer_mode = choose; *************SETTING SESSION TO USE DEFAULT OPTIMIZER INDEX ADJUSTMENTS ************* alter session set optimizer_index_cost_adj = 100; alter session set optimizer_index_caching = 0; *************SETTING SESSION TO CREATE STORED OUTLINE IN DEFAULT CATEGORY************* alter session set create_stored_outlines = TRUE; *************DEFINE INPUT AND OUTPUT VARIABLES TO ALLOW STATEMENT TO RUN IN SQLPLUS************* DECLARE A1 VARCHAR2(2) := '80'; A2 VARCHAR2(10) := '111435163'; A3 NUMBER(2,0) := 10; A4 NUMBER(2,0) := 90; A5 VARCHAR2(3) := 'STD'; A6 DATE := sysdate; B1 CASE_HDR.CONS_CASE_PRTY%TYPE; B2 CASE_HDR.PUTWY_TYPE%TYPE; B3 CASE_HDR.USER_ID%TYPE; B4 CASE_HDR.RETN_DISP_CODE%TYPE; B5 CASE_HDR.FINAL_DISP_CODE%TYPE; B6 CASE_HDR.INCUB_DATE%TYPE; B7 CASE_HDR.SUPP_FTZ_CASE%TYPE; B8 CASE_HDR.RCPT_IN_PROC_FLAG%TYPE; B9 CASE_HDR.CASE_NBR%TYPE; *************DEFINE ALL 52 OUTPUT VARIABLES TO ALLOW STATEMENT TO RUN IN SQLPLUS************* B52 CASE_HDR.MOD_DATE_TIME%TYPE; BEGIN execute immediate *************ACTUAL SQL STATEMENT TO CREATE STORED OUTLINE FOLLOWS************* 'SELECT CASE_HDR.CONS_CASE_PRTY, CASE_HDR.PUTWY_TYPE, CASE_HDR.USER_ID, CASE_HDR.RETN_DISP_CODE, CASE_HDR.FINAL_DISP_CODE, CASE_HDR.INCUB_DATE, CASE_HDR.SUPP_FTZ_CASE, CASE_HDR.RCPT_IN_PROC_FLAG, CASE_HDR.CASE_NBR, CASE_HDR.WHSE, CASE_HDR.LOCN_ID, CASE_HDR.PREV_LOCN_ID, CASE_HDR.DEST_LOCN_ID, CASE_HDR.RCVD_SHPMT_NBR, CASE_HDR.ORIG_SHPMT_NBR, CASE_HDR.DC_ORD_NBR, CASE_HDR.WORK_ORD_NBR, CASE_HDR.MFG_PLNT, CASE_HDR.CONS_PRTY_DATE, CASE_HDR.CONS_SEQ, CASE_HDR.MFG_DATE, CASE_HDR.RCVD_DATE, CASE_HDR.XPIRE_DATE, CASE_HDR.SHIP_BY_DATE, CASE_HDR.PROC_IMMD_NEEDS, CASE_HDR.VOL, CASE_HDR.EST_WT, CASE_HDR.ACTL_WT, CASE_HDR.CASE_SIZE_TYPE, CASE_HDR.MANIF_NBR, CASE_HDR.MANIF_TYPE, CASE_HDR.PO_NBR, CASE_HDR.SHIP_VIA, CASE_HDR.TRLR_NBR, CASE_HDR.STAT_CODE, CASE_HDR.STAT_DATE_TIME, CASE_HDR.VENDOR_ID, CASE_HDR.VENDOR_CNTR_NBR, CASE_HDR.PHYS_ENTITY_CODE, CASE_HDR.PLT_ID, CASE_HDR.TIER_QTY, CASE_HDR.SNGL_SKU_CASE, CASE_HDR.OUT_OF_ZONE_INDIC, CASE_HDR.LAST_FROZN_DATE_TIME, CASE_HDR.LAST_CNT_DATE_TIME, CASE_HDR.SPL_INSTR_CODE_1, CASE_HDR.SPL_INSTR_CODE_2, CASE_HDR.SPL_INSTR_CODE_3, CASE_HDR.SPL_INSTR_CODE_4, CASE_HDR.SPL_INSTR_CODE_5, CASE_HDR.CREATE_DATE_TIME, CASE_HDR.MOD_DATE_TIME FROM CASE_DTL, LOCN_HDR, CASE_HDR WHERE ( ( ( CASE_HDR.LOCN_ID = LOCN_HDR.LOCN_ID) AND ( CASE_HDR.CASE_NBR = CASE_DTL.CASE_NBR ) ) AND ( ( ( ( ( ( ( CASE_HDR.WHSE = :1 ) AND ( CASE_DTL.SKU_ID = :2 ) ) AND ( CASE_HDR.STAT_CODE >= :3 ) ) AND ( CASE_HDR.STAT_CODE 0 ) ) AND ( LOCN_HDR.PULL_ZONE = :5 ) ) AND ( ( CASE_HDR.XPIRE_DATE IS NULL ) OR ( CASE_HDR.XPIRE_DATE >= :6 ) ) ) ) ORDER BY CASE_HDR.CONS_CASE_PRTY ASC, CASE_HDR.CONS_PRTY_DATE ASC, CASE_HDR.CONS_SEQ ASC, LOCN_HDR.LOCN_PICK_SEQ ASC, CASE_HDR.CASE_NBR ASC' *************INTO STATEMENT BELOW ALLOWS STATEMENT TO RUN IN SQLPLUS************* INTO B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,B18,B19,B20,B21,B22,B23,B24,B25,B26,B27,B28,B29,B30,B3 1,B32,B33,B34,B35,B36,B37,B38,B39,B40,B41,B42,B43,B44,B45,B46,B47,B48,B49,B50,B51,B52 using A1,A2,A3,A4,A5,A6; END; / *************SETTING SESSION TO STOP STORED OUTLINE CREATION IN DEFAULT CATEGORY************* alter session set create_plan_outlines = FALSE;