Demo tables for practice:
create user samik identified by samik;
grant connect, resource to samik;
create tablespace sam_ts datafile '/data1/samik/sam1.dbf' size 200M;
alter user samik quota unlimited on sam_ts;
CREATE TABLE LOCATIONS(LOCATION_ID VARCHAR2(15) NOT NULL ENABLE,WAREHOUSE_ID VARCHAR2(15) NOT NULL ENABLE,DESCRIPTION VARCHAR2(80),LOCATION_TYPE CHAR(1) NOT NULL ENABLE,CONSTRAINT "CHK_LOCATIONS" CHECK ((LOCATION_TYPE = 'T' Or LOCATION_TYPE = 'R' OrLOCATION_TYPE = 'F')) ENABLE,PRIMARY KEY (WAREHOUSE_ID, LOCATION_ID));CREATE INDEX IND_LOCATIONS_1 ON LOCATIONS (LOCATION_ID);SELECT 'CREATING UMS' FROM DUAL;CREATE TABLE UMS (UNIT_OF_MEASURE VARCHAR2(15) NOT NULL ENABLE,DESCRIPTION VARCHAR2(40),UOM_SCALE NUMBER NOT NULL ENABLE,CONSTRAINT "CHK_UOM_SCALE" CHECK ((UOM_SCALE >= 0 And UOM_SCALE <= 4)) ENABLE,PRIMARY KEY ("UNIT_OF_MEASURE"));SELECT 'CREATING VENDORS' FROM DUAL;CREATE TABLE VENDORS (VENDOR_ID VARCHAR2(15) NOT NULL ENABLE,VENDOR_NAME VARCHAR2(50),ADDR_1 VARCHAR2(50),ADDR_2 VARCHAR2(50),ADDR_3 VARCHAR2(50),CITY VARCHAR2(30),STATE VARCHAR2(10),ZIPCODE VARCHAR2(10),COUNTRY VARCHAR2(50),CONTACT_FIRST_NAME VARCHAR2(30),CONTACT_LAST_NAME VARCHAR2(30),CONTACT_INITIAL VARCHAR2(2),CONTACT_POSITION VARCHAR2(20),CONTACT_HONORIFIC VARCHAR2(4),CONTACT_SALUTATION VARCHAR2(60),CONTACT_PHONE VARCHAR2(20),CONTACT_FAX VARCHAR2(20),REMIT_TO_NAME VARCHAR2(50),REMIT_TO_ADDR_1 VARCHAR2(50),REMIT_TO_ADDR_2 VARCHAR2(50),REMIT_TO_ADDR_3 VARCHAR2(50),REMIT_TO_CITY VARCHAR2(30),REMIT_TO_STATE VARCHAR2(10),REMIT_TO_ZIPCODE VARCHAR2(10),REMIT_TO_COUNTRY VARCHAR2(50),FREE_ON_BOARD VARCHAR2(25),SHIP_VIA VARCHAR2(40),BUYER VARCHAR2(15),REPORT_1099_MISC CHAR(1) DEFAULT 'N' NOT NULL ENABLE,TERMS_NET_TYPE CHAR(1) DEFAULT ' ' NOT NULL ENABLE,TERMS_NET_DAYS NUMBER,TERMS_NET_DATE DATE,TERMS_DISC_TYPE CHAR(1) DEFAULT ' ' NOT NULL ENABLE,TERMS_DISC_DAYS NUMBER,TERMS_DISC_DATE DATE,TERMS_DISC_PERCENT NUMBER(5,3),TERMS_DESCRIPTION VARCHAR2(50),USER_1 VARCHAR2(80),USER_2 VARCHAR2(80),USER_3 VARCHAR2(80),USER_4 VARCHAR2(80),USER_5 VARCHAR2(80),USER_6 VARCHAR2(80),USER_7 VARCHAR2(80),USER_8 VARCHAR2(80),USER_9 VARCHAR2(80),USER_10 VARCHAR2(80),CONSTRAINT "CHK_VENDORS" CHECK ((REPORT_1099_MISC = 'Y' Or REPORT_1099_MISC = 'N')AND (TERMS_NET_TYPE = 'A'Or TERMS_NET_TYPE = 'M'Or TERMS_NET_TYPE = 'D'Or TERMS_NET_TYPE = 'N'Or TERMS_NET_TYPE = 'E')AND (TERMS_DISC_TYPE = 'A'Or TERMS_DISC_TYPE = 'M'Or TERMS_DISC_TYPE = 'D'Or TERMS_DISC_TYPE = 'N'Or TERMS_DISC_TYPE = 'E')) ENABLE,PRIMARY KEY (VENDOR_ID));CREATE TABLE VENDORS_TEMP ASSELECT*FROMVENDORS;https://hoopercharles.wordpress.com/2010/08/25/oracle-logging-trigger-creator/SELECT 'CREATING PARTS' FROM DUAL;CREATE TABLE PARTS (PART_ID VARCHAR2(30) NOT NULL ENABLE,DESCRIPTION VARCHAR2(40),STOCK_UM VARCHAR2(15) NOT NULL ENABLE,PLANNING_LEADTIME NUMBER DEFAULT 0 NOT NULL ENABLE,ORDER_POLICY CHAR(1) DEFAULT 'M' NOT NULL ENABLE,ORDER_POINT NUMBER(14,4),SAFETY_STOCK_QTY NUMBER(14,4),FIXED_ORDER_QTY NUMBER(14,4),DAYS_OF_SUPPLY NUMBER,MINIMUM_ORDER_QTY NUMBER(14,4),MAXIMUM_ORDER_QTY NUMBER(14,4),ENGINEERING_MSTR VARCHAR2(3),PRODUCT_CODE VARCHAR2(15),COMMODITY_CODE VARCHAR2(15),MFG_NAME VARCHAR2(30),MFG_PART_ID VARCHAR2(30),FABRICATED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,PURCHASED CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,STOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,DETAIL_ONLY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,DEMAND_HISTORY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,TOOL_OR_FIXTURE CHAR(1) DEFAULT 'N' NOT NULL ENABLE,INSPECTION_REQD CHAR(1) DEFAULT 'N' NOT NULL ENABLE,WEIGHT NUMBER(14,4),WEIGHT_UM VARCHAR2(15),DRAWING_ID VARCHAR2(15),DRAWING_REV_NO VARCHAR2(8),PREF_VENDOR_ID VARCHAR2(15),PRIMARY_WHS_ID VARCHAR2(15),PRIMARY_LOC_ID VARCHAR2(15),BACKFLUSH_WHS_ID VARCHAR2(15),BACKFLUSH_LOC_ID VARCHAR2(15),INSPECT_WHS_ID VARCHAR2(15),INSPECT_LOC_ID VARCHAR2(15),MRP_REQUIRED CHAR(1) DEFAULT 'N',MRP_EXCEPTIONS CHAR(1) DEFAULT 'N',PRIVATE_UM_CONV CHAR(1) DEFAULT 'N',AUTO_BACKFLUSH CHAR(1) DEFAULT 'Y',PLANNER_USER_ID VARCHAR2(20),BUYER_USER_ID VARCHAR2(20),ABC_CODE CHAR(1),ANNUAL_USAGE_QTY NUMBER(15,4),INVENTORY_LOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,UNIT_MATERIAL_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,UNIT_LABOR_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,UNIT_BURDEN_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,UNIT_SERVICE_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,BURDEN_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,PURC_BUR_PERCENT NUMBER(6,3) DEFAULT 0 NOT NULL ENABLE,PURC_BUR_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,UNIT_PRICE NUMBER(20,6),NEW_MATERIAL_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,NEW_LABOR_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,NEW_BURDEN_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,NEW_SERVICE_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,NEW_BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,NEW_BURDEN_PERUNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,NEW_FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,MAT_GL_ACCT_ID VARCHAR2(30),LAB_GL_ACCT_ID VARCHAR2(30),BUR_GL_ACCT_ID VARCHAR2(30),SER_GL_ACCT_ID VARCHAR2(30),QTY_ON_HAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,QTY_AVAILABLE_ISS NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,QTY_AVAILABLE_MRP NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,QTY_ON_ORDER NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,QTY_IN_DEMAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,USER_1 VARCHAR2(80),USER_2 VARCHAR2(80),USER_3 VARCHAR2(80),USER_4 VARCHAR2(80),USER_5 VARCHAR2(80),USER_6 VARCHAR2(80),USER_7 VARCHAR2(80),USER_8 VARCHAR2(80),USER_9 VARCHAR2(80),USER_10 VARCHAR2(80),LT_PLUS_DAYS NUMBER,LT_MINUS_DAYS NUMBER,STATUS CHAR(1),USE_SUPPLY_BEF_LT CHAR(1),QTY_COMMITTED NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,PRT_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,PRT_CREATE_DATE DATE DEFAULT SYSDATE,CONSTRAINT "CHK_PART1" CHECK ((PLANNING_LEADTIME >= 0)AND (ORDER_POLICY = 'N'Or ORDER_POLICY = 'M'Or ORDER_POLICY = 'F'Or ORDER_POLICY = 'E'Or ORDER_POLICY = 'D'Or ORDER_POLICY = 'P')AND (ORDER_POINT >= 0)AND (SAFETY_STOCK_QTY >= 0)AND (FIXED_ORDER_QTY >= 0)AND (DAYS_OF_SUPPLY >= 0)AND (MINIMUM_ORDER_QTY >= 0)AND (MAXIMUM_ORDER_QTY >= 0)AND (FABRICATED = 'Y' Or FABRICATED = 'N')AND (PURCHASED = 'Y' Or PURCHASED = 'N')AND (STOCKED = 'Y' Or STOCKED = 'N')AND (DETAIL_ONLY = 'Y' Or DETAIL_ONLY = 'N')AND (DEMAND_HISTORY = 'Y' Or DEMAND_HISTORY = 'N')AND (TOOL_OR_FIXTURE = 'Y' Or TOOL_OR_FIXTURE = 'N')AND (MRP_REQUIRED = 'Y' Or MRP_REQUIRED = 'N')AND (MRP_EXCEPTIONS = 'Y' Or MRP_EXCEPTIONS = 'N')AND (PRIVATE_UM_CONV = 'Y' Or PRIVATE_UM_CONV = 'N')AND (INVENTORY_LOCKED = 'Y' Or INVENTORY_LOCKED = 'N')AND (INSPECTION_REQD = 'Y' Or INSPECTION_REQD = 'N')) ENABLE,PRIMARY KEY (PART_ID),CONSTRAINT "FKEY_INSP" FOREIGN KEY (INSPECT_WHS_ID, INSPECT_LOC_ID)REFERENCES LOCATIONS (WAREHOUSE_ID, LOCATION_ID) ENABLE,CONSTRAINT "FKEY_PREF_VENDOR" FOREIGN KEY (PREF_VENDOR_ID)REFERENCES VENDORS (VENDOR_ID) ENABLE,CONSTRAINT "FKEY_UM" FOREIGN KEY (WEIGHT_UM)REFERENCES UMS (UNIT_OF_MEASURE) ENABLE,CONSTRAINT "FKEY_STOCK_UM" FOREIGN KEY (STOCK_UM)REFERENCES UMS (UNIT_OF_MEASURE) ENABLE);CREATE INDEX IND_PARTS_1 ON PARTS (MRP_EXCEPTIONS);CREATE INDEX IND_PARTS_2 ON PARTS (MFG_NAME, MFG_PART_ID);CREATE INDEX IND_PARTS_3 ON PARTS (WEIGHT_UM);CREATE INDEX IND_PARTS_4 ON PARTS (MRP_REQUIRED);CREATE INDEX IND_PARTS_5 ON PARTS (PREF_VENDOR_ID);CREATE INDEX IND_PARTS_6 ON PARTS (STOCK_UM);CREATE INDEX IND_PARTS_7 ON PARTS (ORDER_POINT);CREATE TABLE PARTS_TEMP ASSELECT*FROMPARTS;SELECT 'CREATING PO_HEADER' FROM DUAL;CREATE TABLE PO_HEADER (PURC_ORDER_ID VARCHAR2(15) NOT NULL ENABLE,VENDOR_ID VARCHAR2(15) NOT NULL ENABLE,CONTACT_FIRST_NAME VARCHAR2(30),CONTACT_LAST_NAME VARCHAR2(30),CONTACT_INITIAL VARCHAR2(2),CONTACT_POSITION VARCHAR2(20),CONTACT_HONORIFIC VARCHAR2(4),CONTACT_SALUTATION VARCHAR2(60),CONTACT_PHONE VARCHAR2(20),CONTACT_FAX VARCHAR2(20),PURC_ORD_ADDR_NO NUMBER,SHIPTO_ADDR_NO NUMBER,ORDER_DATE DATE DEFAULT SYSDATE NOT NULL ENABLE,DESIRED_RECV_DATE DATE DEFAULT TRUNC(SYSDATE),BUYER VARCHAR2(15),FREE_ON_BOARD VARCHAR2(25),SHIP_VIA VARCHAR2(40),SALES_TAX_GROUP_ID VARCHAR2(15),PO_STATUS CHAR(1) DEFAULT ' ' NOT NULL ENABLE,BACK_ORDER CHAR(1) DEFAULT ' ' NOT NULL ENABLE,SELL_RATE NUMBER(15,8) NOT NULL ENABLE,BUY_RATE NUMBER(15,8) NOT NULL ENABLE,ENTITY_ID VARCHAR2(5) NOT NULL ENABLE,POSTING_CANDIDATE CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,LAST_RECEIVED_DATE DATE,TOTAL_AMT_ORDERED NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,TOTAL_AMT_RECVD NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,MARKED_FOR_PURGE CHAR(1) DEFAULT 'N' NOT NULL ENABLE,EXCH_RATE_FIXED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,PROMISE_DATE DATE,PRINTED_DATE DATE,TERMS_DISC_TYPE CHAR(1),EDI_BLANKET_FLAG CHAR(1),EDI_BLANKET_PO_NO VARCHAR2(30),CONTRACT_ID VARCHAR2(30),SHIPTO_ID VARCHAR2(20),TERMS_NET_TYPE CHAR(1) DEFAULT ' ' NOT NULL ENABLE,TERMS_NET_DAYS NUMBER,TERMS_NET_DATE DATE,TERMS_DISC_DAYS NUMBER,TERMS_DISC_DATE DATE,TERMS_DISC_PERCENT NUMBER(5,3),TERMS_DESCRIPTION VARCHAR2(50),CURRENCY_ID VARCHAR2(15),WAREHOUSE_ID VARCHAR2(15),CREATE_DATE DATE DEFAULT SYSDATE NOT NULL ENABLE,CONTACT_MOBILE VARCHAR2(20),CONTACT_EMAIL VARCHAR2(50),USER_1 VARCHAR2(80),USER_2 VARCHAR2(80),USER_3 VARCHAR2(80),USER_4 VARCHAR2(80),USER_5 VARCHAR2(80),USER_6 VARCHAR2(80),USER_7 VARCHAR2(80),USER_8 VARCHAR2(80),USER_9 VARCHAR2(80),USER_10 VARCHAR2(80),UDF_LAYOUT_ID VARCHAR2(15),PO_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,CONSTRAINT "CHK_PO" CHECK ((PO_STATUS = 'F' Or PO_STATUS = 'R' Or PO_STATUS = 'C' Or PO_STATUS = 'X')AND (BACK_ORDER = 'Y' Or BACK_ORDER = 'N')AND (POSTING_CANDIDATE = 'Y' Or POSTING_CANDIDATE = 'N')AND (MARKED_FOR_PURGE = 'Y' Or MARKED_FOR_PURGE = 'N')AND (TERMS_DISC_TYPE = 'A' Or TERMS_DISC_TYPE = 'M' Or TERMS_DISC_TYPE = 'D' Or TERMS_DISC_TYPE = 'N' Or TERMS_DISC_TYPE = 'E')AND (TERMS_NET_TYPE = 'A' Or TERMS_NET_TYPE = 'M' Or TERMS_NET_TYPE = 'D' Or TERMS_NET_TYPE = 'N' Or TERMS_NET_TYPE = 'E')) ENABLE,PRIMARY KEY (PURC_ORDER_ID));CREATE INDEX IND_PO_HEADER_1 ON PO_HEADER (VENDOR_ID, PURC_ORD_ADDR_NO);CREATE INDEX IND_PO_HEADER_2 ON PO_HEADER (VENDOR_ID);CREATE INDEX IND_PO_HEADER_3 ON PO_HEADER (SHIPTO_ADDR_NO);CREATE INDEX IND_PO_HEADER_4 ON PO_HEADER (POSTING_CANDIDATE);CREATE TABLE PO_HEADER_TEMP ASSELECT*FROMPO_HEADER;SELECT 'CREATING PO_LINE' FROM DUAL;CREATE TABLE PO_LINE (PURC_ORDER_ID VARCHAR2(15) NOT NULL ENABLE,LINE_NO NUMBER NOT NULL ENABLE,PART_ID VARCHAR2(30),VENDOR_PART_ID VARCHAR2(30),SERVICE_ID VARCHAR2(15),USER_ORDER_QTY NUMBER(14,4) NOT NULL ENABLE,ORDER_QTY NUMBER(14,4) NOT NULL ENABLE,PURCHASE_UM VARCHAR2(15),UNIT_PRICE NUMBER(20,6) NOT NULL ENABLE,TRADE_DISC_PERCENT NUMBER(6,3) DEFAULT 0 NOT NULL ENABLE,FIXED_CHARGE NUMBER(15,2),EST_FREIGHT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,GL_EXPENSE_ACCT_ID VARCHAR2(30),SALES_TAX_GROUP_ID VARCHAR2(15),PRODUCT_CODE VARCHAR2(15),COMMODITY_CODE VARCHAR2(15),DESIRED_RECV_DATE DATE,LINE_STATUS CHAR(1) DEFAULT ' ' NOT NULL ENABLE,LAST_RECEIVED_DATE DATE,TOTAL_ACT_FREIGHT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,TOTAL_USR_RECD_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,TOTAL_RECEIVED_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,TOTAL_AMT_RECVD NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,TOTAL_AMT_ORDERED NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,MFG_NAME VARCHAR2(30),MFG_PART_ID VARCHAR2(30),PROMISE_DATE DATE,PIECE_COUNT NUMBER(14,4),LENGTH NUMBER(14,4),WIDTH NUMBER(14,4),HEIGHT NUMBER(14,4),DIMENSIONS_UM VARCHAR2(15),VAT_CODE VARCHAR2(15),TOTAL_DISPATCH_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,TOTAL_USR_DISP_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,MINIMUM_CHARGE NUMBER(15,2),LAST_DISPATCH_DATE DATE,EDI_BLANKET_QTY NUMBER(14,4),EDI_BLANKET_USRQTY NUMBER(14,4),EDI_ACCUM_QTY_REL NUMBER(14,4),EDI_ACCUM_USR_REL NUMBER(14,4),EDI_ACCUM_QTY_REC NUMBER(14,4),EDI_ACCUM_USR_REC NUMBER(14,4),EDI_LAST_REC_DATE DATE,EDI_RELEASE_NO VARCHAR2(3),EDI_RELEASE_DATE DATE,EDI_QTY_RELEASED NUMBER(14,4),EDI_USR_QTY_REL NUMBER(14,4),EDI_REQ_REL_DATE DATE,SHIPTO_ID VARCHAR2(20),WAREHOUSE_ID VARCHAR2(15),WIP_VAS_REQUIRED CHAR(1),ALLOCATED_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,FULFILLED_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,HTS_CODE VARCHAR2(20),ORIG_COUNTRY_ID VARCHAR2(15),USER_1 VARCHAR2(80),USER_2 VARCHAR2(80),USER_3 VARCHAR2(80),USER_4 VARCHAR2(80),USER_5 VARCHAR2(80),USER_6 VARCHAR2(80),USER_7 VARCHAR2(80),USER_8 VARCHAR2(80),USER_9 VARCHAR2(80),USER_10 VARCHAR2(80),UDF_LAYOUT_ID VARCHAR2(15),POL_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,POL_CREATE_DATE DATE DEFAULT SYSDATE,CONSTRAINT "CHK_PO_LINE" CHECK ((LINE_STATUS = 'A' Or LINE_STATUS = 'C')) ENABLE,PRIMARY KEY (PURC_ORDER_ID, LINE_NO),CONSTRAINT "FKEY_PO_HEADER" FOREIGN KEY (PURC_ORDER_ID) REFERENCES PO_HEADER (PURC_ORDER_ID) ON DELETE CASCADE ENABLE,CONSTRAINT "FKEY_PART_ID" FOREIGN KEY (PART_ID) REFERENCES PARTS (PART_ID) ENABLE,CONSTRAINT "FKEY_PURC_UM" FOREIGN KEY (PURCHASE_UM)REFERENCES UMS (UNIT_OF_MEASURE) ENABLE);CREATE INDEX IND_PO_LINE_1 ON PO_LINE (WAREHOUSE_ID);CREATE INDEX IND_PO_LINE_2 ON PO_LINE (SERVICE_ID);CREATE INDEX IND_PO_LINE_3 ON PO_LINE (PART_ID);CREATE INDEX IND_PO_LINE_4 ON PO_LINE (VENDOR_PART_ID);CREATE TABLE PO_LINE_TEMP ASSELECT*FROMPO_LINE;CREATE TABLE NARROW (C1 NUMBER,C2 NUMBER);SELECT 'INSERTING INTO LOCATIONS' FROM DUAL;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_LOCATIONS_UMS';INSERT INTOLOCATIONSSELECT /*+ ORDERED */LOC.LOCATION_ID,WH.WAREHOUSE_ID,RPAD(WH.WAREHOUSE_ID||'-'||LOC.LOCATION_ID,60),DECODE(MOD(ROWNUM,5),0,'T',1,'R','F')FROM(SELECTTRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.1415/180*1.2)*1000000,0))))||'LOC' LOCATION_ID,ROWNUM RNFROMDUALCONNECT BYLEVEL<=200) LOC,(SELECTTRIM(TO_CHAR(ABS(ROUND(SIN(ROWNUM*3.1415/180*10.1)*1000000,0))))||'WH' WAREHOUSE_ID,ROWNUM RNFROMDUALCONNECT BYLEVEL<=20) WHWHERE(MOD(WH.RN,10)*20+1) <= LOC.RN;COMMIT;EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'LOCATIONS',CASCADE=>TRUE);INSERT INTOUMSSELECTDECODE(ROWNUM,1,'EA',2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN'),NULL, 4FROMDUALCONNECT BYLEVEL<=8;COMMIT;EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'UMS',CASCADE=>TRUE);SELECT 'INSERTING INTO VENDORS' FROM DUAL;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_VENDORS';INSERT INTOVENDORS_TEMPSELECTTRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.14159265/180*51.491976)*10000000,0))))||'VEN' VENDOR_ID,TRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.1415/180*.49)*1000000,0))))||'VENDOR NAME' VENDOR_NAME,RPAD('ADDR_1',40) ADDR_1,RPAD('ADDR_2',35) ADDR_2,NULL ADDR_3,RPAD('CITY',20) CITY,'CA' STATE,LPAD(TO_CHAR(ROWNUM),6) ZIPCODE,'NONE' COUNTRY,NULL CONTACT_FIRST_NAME,NULL CONTACT_LAST_NAME,NULL CONTACT_INITIAL,NULL CONTACT_POSITION,NULL CONTACT_HONORIFIC,NULL CONTACT_SALUTATION,NULL CONTACT_PHONE,NULL CONTACT_FAX,TRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.1415/180*4.491976)*1000000,0))))||'VENDOR NAME' REMIT_TO_NAME,RPAD('ADDR_1',40) REMIT_TO_ADDR_1,RPAD('ADDR_2',35) REMIT_TO_ADDR_2,NULL REMIT_TO_ADDR_3,RPAD('CITY',20) REMIT_TO_CITY,'CA' REMIT_TO_STATE,LPAD(TO_CHAR(ROWNUM),6) REMIT_TO_ZIPCODE,'NONE' REMIT_TO_COUNTRY,'NONE' FREE_ON_BOARD,'SPECIAL DEL' SHIP_VIA,'UNKNOWN' BUYER,'N' REPORT_1099_MISC,DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E') TERMS_NET_TYPE,ROWNUM TERMS_NET_DAYS,NULL TERMS_NET_DATE,DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E') TERMS_DISC_TYPE,MOD(ROWNUM,100)+10 TERMS_DISC_DAYS,NULL TERMS_DISC_DATE,3.5 TERMS_DISC_PERCENT,'STANDARD' TERMS_DESCRIPTION,'X' USER_1,TO_CHAR(TRUNC(SYSDATE,'YYYY'),'MON DD, YYYY') USER_2,NULL USER_3,NULL USER_4,NULL USER_5,NULL USER_6,NULL USER_7,NULL USER_8,NULL USER_9,NULL USER_10FROMDUALCONNECT BYLEVEL<=50000;SELECT 'ELIMINATING DUP V' FROM DUAL;DELETE FROMVENDORS_TEMPWHERE(VENDOR_ID,TERMS_NET_DAYS) IN(SELECTV.VENDOR_ID,V.TERMS_NET_DAYSFROMVENDORS_TEMP V,(SELECTVENDOR_ID,MIN(TERMS_NET_DAYS) TERMS_NET_DAYSFROMVENDORS_TEMPGROUP BYVENDOR_IDHAVINGCOUNT(*)>1) MWHEREV.VENDOR_ID=M.VENDOR_IDAND V.TERMS_NET_DAYS>M.TERMS_NET_DAYS);INSERT INTOVENDORSSELECT*FROMVENDORS_TEMP;COMMIT;EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'VENDORS',CASCADE=>TRUE);SELECT 'INSERTING INTO PARTS' FROM DUAL;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_PARTS';INSERT INTOPARTS_TEMPSELECTTRIM(TO_CHAR(ABS(ROUND(SIN(ROWNUM*3.14159265/180*10.191976)*10000000,0))))||'PART' PART_ID,TRIM(TO_CHAR(ABS(ROUND(SIN(ROWNUM*3.14159265/180*10.191976)*10000000,0))))||'DESCRIPTION' DESCRIPTION,DECODE(MOD(ROWNUM,20),2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN','EA') STOCK_UM, 1 PLANNING_LEADTIME,'M' ORDER_POLICY,ROWNUM ORDER_POINT,1 SAFETY_STOCK_QTY,1 FIXED_ORDER_QTY,1 DAYS_OF_SUPPLY,1 MINIMUM_ORDER_QTY,9999 MAXIMUM_ORDER_QTY,'0' ENGINEERING_MSTR,DECODE(MOD(ROWNUM,20),1,'SHOP',2,'OFFICE',3,'JANITOR',4,'INVENTORY',5,'INVENTORY','FG') PRODUCT_CODE,DECODE(MOD(ROWNUM,7),1,'SHOP',2,'OFFICE',3,'JANITOR',4,'INVENTORY',5,'INVENTORY','FG') COMMODITY_CODE,'UNKNOWN' MFG_NAME,'UNKNOWN' MFG_PART_ID,DECODE(MOD(ROWNUM,3),1,'Y','N') FABRICATED,DECODE(MOD(ROWNUM,3),1,'N','Y') PURCHASED,'N' STOCKED,'N' DETAIL_ONLY,'N' DEMAND_HISTORY,'N' TOOL_OR_FIXTURE,'N' INSPECTION_REQD,0 WEIGHT,DECODE(MOD(ROWNUM,20),2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN','EA') WEIGHT_UM,NULL DRAWING_ID,NULL DRAWING_REV_NO,NULL PREF_VENDOR_ID,NULL PRIMARY_WHS_ID,NULL PRIMARY_LOC_ID,NULL BACKFLUSH_WHS_ID,NULL BACKFLUSH_LOC_ID,NULL INSPECT_WHS_ID,NULL INSPECT_LOC_ID,'Y' MRP_REQUIRED,'N' MRP_EXCEPTIONS,'N' PRIVATE_UM_CONV,'Y' AUTO_BACKFLUSH,NULL PLANNER_USER_ID,NULL BUYER_USER_ID,DECODE(MOD(ROWNUM,7),1,'A',2,'B',3,'B','C') ABC_CODE,ROWNUM-100000 ANNUAL_USAGE_QTY,'N' INVENTORY_LOCKED,0 UNIT_MATERIAL_COST,0 UNIT_LABOR_COST,0 UNIT_BURDEN_COST,0 UNIT_SERVICE_COST,0 BURDEN_PERCENT,0 BURDEN_PER_UNIT,0 PURC_BUR_PERCENT,0 PURC_BUR_PER_UNIT,0 FIXED_COST,0 UNIT_PRICE,0 NEW_MATERIAL_COST,0 NEW_LABOR_COST,0 NEW_BURDEN_COST,0 NEW_SERVICE_COST,0 NEW_BURDEN_PERCENT,0 NEW_BURDEN_PERUNIT,0 NEW_FIXED_COST,'1111111' MAT_GL_ACCT_ID,'2222222' LAB_GL_ACCT_ID,'3333333' BUR_GL_ACCT_ID,'4444444' SER_GL_ACCT_ID,ABS(ROUND(SIN(ROWNUM*3.14159265/180*2)*100000,3)) QTY_ON_HAND,ABS(ROUND(SIN(ROWNUM*3.14159265/180*2)*100000,3)) QTY_AVAILABLE_ISS,ABS(ROUND(SIN(ROWNUM*3.14159265/180*2)*100000,3)) QTY_AVAILABLE_MRP,0 QTY_ON_ORDER,0 QTY_IN_DEMAND,RPAD('USER_1',30) USER_1,RPAD('USER_2',30) USER_2,RPAD('USER_3',30) USER_3,NULL USER_4,NULL USER_5,NULL USER_6,NULL USER_7,NULL USER_8,NULL USER_9,NULL USER_10,0 LT_PLUS_DAYS,0 LT_MINUS_DAYS,'A' STATUS,'Y' USE_SUPPLY_BEF_LT,0 QTY_COMMITTED,'TESTING' PRT_CREATE_USER_ID,SYSDATE PRT_CREATE_DATEFROMDUALCONNECT BYLEVEL<=100000;SELECT 'REMOVING DUPLICATE PARTS' FROM DUAL;DELETE FROMPARTS_TEMPWHERE(PART_ID,ORDER_POINT) IN(SELECTV.PART_ID,V.ORDER_POINTFROMPARTS_TEMP V,(SELECTPART_ID,MIN(ORDER_POINT) ORDER_POINTFROMPARTS_TEMPGROUP BYPART_IDHAVINGCOUNT(*)>1) MWHEREV.PART_ID=M.PART_IDAND V.ORDER_POINT>M.ORDER_POINT);INSERT INTOPARTSSELECT*FROMPARTS_TEMP;UPDATEPARTS PSET(PRIMARY_WHS_ID,PRIMARY_LOC_ID)=(SELECTWAREHOUSE_ID,LOCATION_IDFROM(SELECTWAREHOUSE_ID,LOCATION_ID,ROWNUM RNFROMLOCATIONS)WHEREMOD(P.ORDER_POINT,2000)=RN);UPDATEPARTSSETPREF_VENDOR_ID=TRIM(TO_CHAR(ABS(ROUND(COS((MOD(ROWNUM,9000)*2+1)*3.14159265/180*51.491976)*10000000,0))))||'VEN'WHEREPURCHASED='Y';COMMIT;EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'PARTS',CASCADE=>TRUE);SELECT 'INSERTING INTO PO_HEADER' FROM DUAL;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_PO_HEADER';INSERT INTOPO_HEADER_TEMPSELECT'PO'||TO_CHAR(ROWNUM) PURC_ORDER_ID,TRIM(TO_CHAR(ABS(ROUND(COS((MOD(ROWNUM,9000)*2+1)*3.14159265/180*51.491976)*10000000,0))))||'VEN' VENDOR_ID,NULL CONTACT_FIRST_NAME,NULL CONTACT_LAST_NAME,NULL CONTACT_INITIAL,NULL CONTACT_POSITION,NULL CONTACT_HONORIFIC,NULL CONTACT_SALUTATION,NULL CONTACT_PHONE,NULL CONTACT_FAX,1 PURC_ORD_ADDR_NO,1 SHIPTO_ADDR_NO,TRUNC(SYSDATE-(COS(ROWNUM*3.14159265/180)*1000)) ORDER_DATE,TRUNC(SYSDATE-(COS(ROWNUM*3.14159265/180)*1000))+10 DESIRED_RECV_DATE,'MY_BUYER' BUYER,NULL FREE_ON_BOARD,'BEST WAY' SHIP_VIA,'REGULAR' SALES_TAX_GROUP_ID,DECODE(MOD(ROWNUM,6),1,'F',2,'R',3,'X','C') PO_STATUS,'N' BACK_ORDER,1 SELL_RATE,1 BUY_RATE,'1' ENTITY_ID,DECODE(MOD(ROWNUM,3),1,'Y','N') POSTING_CANDIDATE,NULL LAST_RECEIVED_DATE,0 TOTAL_AMT_ORDERED,0 TOTAL_AMT_RECVD,'N' MARKED_FOR_PURGE,'Y' EXCH_RATE_FIXED,TRUNC(SYSDATE-(COS(ROWNUM*3.14159265/180)*1000))+10 PROMISE_DATE,SYSDATE PRINTED_DATE,DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E') TERMS_DISC_TYPE,NULL EDI_BLANKET_FLAG,NULL EDI_BLANKET_PO_NO,1 CONTRACT_ID,1 SHIPTO_ID,DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E')TERMS_NET_TYPE,1 TERMS_NET_DAYS,NULL TERMS_NET_DATE,1 TERMS_DISC_DAYS,NULL TERMS_DISC_DATE,3 TERMS_DISC_PERCENT,'ON TIME' TERMS_DESCRIPTION,'USD' CURRENCY_ID,NULL WAREHOUSE_ID,SYSDATE CREATE_DATE,NULL CONTACT_MOBILE,NULL CONTACT_EMAIL,NULL USER_1,NULL USER_2,NULL USER_3,NULL USER_4,NULL USER_5,NULL USER_6,NULL USER_7,NULL USER_8,NULL USER_9,NULL USER_10,'DEFAULT' UDF_LAYOUT_ID,'TESTING' PO_CREATE_USER_IDFROMDUALCONNECT BYLEVEL<=500000;INSERT INTOPO_HEADERSELECT*FROMPO_HEADER_TEMP;COMMIT;EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'PO_HEADER',CASCADE=>TRUE);SELECT 'INSERTING INTO PO_LINES' FROM DUAL;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_PO_LINES';INSERT INTO PO_LINE_TEMP (PURC_ORDER_ID,LINE_NO,PART_ID,VENDOR_PART_ID,SERVICE_ID,USER_ORDER_QTY,ORDER_QTY,PURCHASE_UM,UNIT_PRICE,FIXED_CHARGE,GL_EXPENSE_ACCT_ID,SALES_TAX_GROUP_ID,PRODUCT_CODE,COMMODITY_CODE,DESIRED_RECV_DATE,TRADE_DISC_PERCENT,EST_FREIGHT,LINE_STATUS,TOTAL_ACT_FREIGHT,TOTAL_USR_RECD_QTY,TOTAL_RECEIVED_QTY,TOTAL_AMT_RECVD,TOTAL_AMT_ORDERED,TOTAL_DISPATCH_QTY,TOTAL_USR_DISP_QTY,ALLOCATED_QTY,FULFILLED_QTY)SELECT /*+ ORDERED */PURC_ORDER_ID,ORDER_POINT-START_LINE+1,PART_ID,PART_ID,NULL,10,10,DECODE(MOD(ROWNUM,20),2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN','EA'),1099.99,0,NULL,NULL,PRODUCT_CODE,COMMODITY_CODE,TRUNC(SYSDATE-1000+ROWNUM/1000),0,0,'A',0,0,0,0,0,0,0,0,0FROM(SELECT'PO'||TO_CHAR(ROWNUM) PURC_ORDER_ID,ROWNUM RN,ABS(SIN(ROWNUM*3.14159265/180))*90000 START_LINE,MOD(ROWNUM,50)+1 LINESFROMDUALCONNECT BYLEVEL<=500000) POL,PARTS PWHEREP.ORDER_POINT BETWEEN START_LINE AND (START_LINE+LINES-1);INSERT INTOPO_LINESELECT*FROMPO_LINE_TEMP;COMMIT;EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'PO_LINE',CASCADE=>TRUE);SELECT 'UPDATE-ROLLBACK TEST' FROM DUAL;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'ROLLBACK_TEST';UPDATEPO_LINESETPART_ID='8729425PART'WHEREPART_ID BETWEEN '3000000PART' AND '6576035PART';ROLLBACK;SELECT 'INSERT-NARROW-TABLE' FROM DUAL;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'NARROW_TABLE';INSERT INTONARROWSELECTROWNUM,NULLFROMDUALCONNECT BYLEVEL<=900000;COMMIT;SELECTSUBSTR(SN.NAME,1,25) STAT_NAME,MS.VALUEFROMV$STATNAME SN,V$MYSTAT MSWHERESN.NAME IN ('table fetch by rowid','table scan rows gotten','table fetch continued row','table scan blocks gotten','consistent gets') AND SN.STATISTIC#=MS.STATISTIC#ORDER BYSN.NAME;UPDATENARROWSETC1=ROUND(SIN(C1*3.14159265/180),2),C2=C1;SELECTSUBSTR(SN.NAME,1,25) STAT_NAME,MS.VALUEFROMV$STATNAME SN,V$MYSTAT MSWHERESN.NAME IN ('table fetch by rowid','table scan rows gotten','table fetch continued row','table scan blocks gotten','consistent gets')AND SN.STATISTIC#=MS.STATISTIC#ORDER BYSN.NAME;UPDATENARROWSETC1=ROUND(SIN(C2*3.14159265/180),10);UPDATENARROWSETC2=C1;SELECTSUBSTR(SN.NAME,1,25) STAT_NAME,MS.VALUEFROMV$STATNAME SN,V$MYSTAT MSWHERESN.NAME IN ('table fetch by rowid','table scan rows gotten','table fetch continued row','table scan blocks gotten','consistent gets')AND SN.STATISTIC#=MS.STATISTIC#ORDER BYSN.NAME;SELECT*FROMNARROW;SELECTSUBSTR(SN.NAME,1,25) STAT_NAME,MS.VALUEFROMV$STATNAME SN,V$MYSTAT MSWHERESN.NAME IN ('table fetch by rowid','table scan rows gotten','table fetch continued row','table scan blocks gotten','consistent gets')AND SN.STATISTIC#=MS.STATISTIC#ORDER BYSN.NAME;DELETE FROMNARROWWHEREC1<0;COMMIT;SELECT 'TABLE AND INDEX STATS' FROM DUAL;ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXTOFF'EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'NARROW',CASCADE=>TRUE);
SELECTSUBSTR(SN.NAME,1,25) STAT_NAME,MS.VALUEFROMV$STATNAME SN,V$MYSTAT MSWHERESN.NAME IN ('table fetch by rowid','table scan rows gotten','table fetch continued row','table scan blocks gotten','consistent gets') AND SN.STATISTIC#=MS.STATISTIC#ORDER BYSN.NAME;SELECTSUBSTR(TABLE_NAME,1,10) TABLE_NAME,SUBSTR(INDEX_NAME,1,15) INDEX_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTORFROMUSER_INDEXESWHERETABLE_NAME IN ('PO_HEADER','PO_LINE','PARTS','VENDORS','LOCATIONS','UMS','NARROW')ORDER BYTABLE_NAME,INDEX_NAME;ALTER SYSTEM FLUSH BUFFER_CACHE;ALTER SYSTEM FLUSH BUFFER_CACHE;ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_TEST';ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
Select statements
SELECTPO.VENDOR_ID,P.PRODUCT_CODE,P.STOCK_UM,SUM(POL.ORDER_QTY) ORDER_QTYFROMPO_HEADER PO,PO_LINE POL,PARTS PWHEREPO.ORDER_DATE BETWEEN TRUNC(SYSDATE-90) AND TRUNC(SYSDATE)AND PO.PURC_ORDER_ID=POL.PURC_ORDER_IDAND POL.PART_ID=P.IDGROUP BYPO.VENDOR_ID,P.PRODUCT_CODE,P.STOCK_UM;SELECTPOL.PART_ID,P.DESCRIPTION,MAX(DESIRED_RECV_DATE) LAST_RECEIVE_DATEFROMPO_LINE POL,PARTS PWHEREP.PRODUCT_CODE='FG'AND P.ABC_CODE='C'AND P.PART_ID=POL.PART_IDGROUP BYPOL.PART_ID,P.DESCIPTION;SELECTCOUNT(*) LOCATIONSFROMLOCATIONS;SELECTPRODUCT_CODE,COUNT(*) PARTS_LARGE_WHFROM(SELECTWAREHOUSE_IDFROMLOCATIONSGROUP BYWAREHOUSE_IDHAVINGCOUNT(*)>160) W,PARTS PWHEREW.WAREHOUSE_ID=P.PRIMARY_WHS_IDGROUP BYPRODUCT_CODEORDER BYPRODUCT_CODE;SELECTCOUNT(*)FROMPARTSWHEREQTY_ON_HAND>1000;SELECTCOUNT(*)FROMVENDORSWHEREZIPCODE>' 44444';SELECTCOUNT(*)FROMPO_LINE POL,PARTS PWHEREPOL.PURC_ORDER_ID BETWEEN '10000' AND '20000'AND POL.PART_ID=P.PART_ID;SELECTPART_ID,ABC_CODE,PRODUCT_CODE,MAX(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE,ABC_CODE) MAX_QTY_PRD_ABC,MIN(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE,ABC_CODE) MIN_QTY_PRD_ABC,DENSE_RANK() OVER (PARTITION BY PRODUCT_CODE,ABC_CODE ORDER BY QTY_ON_HAND) DR_QTY_PRD_ABC,DENSE_RANK() OVER (PARTITION BY PREF_VENDOR_ID ORDER BY ORDER_POINT) DR_OP_VENDFROMPARTSORDER BYPART_ID;SELECTV.VENDOR_ID,V.VENDOR_NAMEFROMVENDORS V,(SELECT DISTINCTPO.VENDOR_IDFROMPO_HEADER PO,PO_LINE POL,PARTS PWHEREPO.PURC_ORDER_ID=POL.PURC_ORDER_IDAND POL.PART_ID=P.PART_IDAND P.PRODUCT_CODE='FG') PVWHEREV.VENDOR_ID=PV.VENDOR_ID(+)AND PV.VENDOR_ID IS NULLORDER BYV.VENDOR_ID;SELECTPART_ID,DESCRIPTION,QTY_ON_HAND,RANK() OVER (PARTITION BY PRODUCT_CODE ORDER BY QTY_ON_HAND DESC NULLS LAST) RANK_PC_QTY,AVG(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE ORDER BY QTY_ON_HAND) AVG_PC_QTY,MIN(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE ORDER BY QTY_ON_HAND) MIN_PC_QTY,MAX(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE ORDER BY QTY_ON_HAND) MAX_PC_QTY,COUNT(UNIT_MATERIAL_COST) OVER (PARTITION BY PRODUCT_CODE ORDER BY UNIT_MATERIAL_COST) COUNT_PC,RANK() OVER (PARTITION BY COMMODITY_CODE ORDER BY QTY_ON_HAND DESC NULLS LAST) RANK_CC_QTY,AVG(QTY_ON_HAND) OVER (PARTITION BY COMMODITY_CODE ORDER BY QTY_ON_HAND) AVG_CC_QTY,MIN(QTY_ON_HAND) OVER (PARTITION BY COMMODITY_CODE ORDER BY QTY_ON_HAND) MIN_CC_QTY,MAX(QTY_ON_HAND) OVER (PARTITION BY COMMODITY_CODE ORDER BY QTY_ON_HAND) MAX_CC_QTY,COUNT(QTY_ON_HAND) OVER (PARTITION BY COMMODITY_CODE ORDER BY QTY_ON_HAND) COUNT_CC,RANK() OVER (PARTITION BY NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY QTY_ON_HAND DESC NULLS LAST) RANK_VENDOR_QTY,AVG(QTY_ON_HAND) OVER (PARTITION BY NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY QTY_ON_HAND) AVG_VENDOR_QTY,MIN(QTY_ON_HAND) OVER (PARTITION BY NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY QTY_ON_HAND) MIN_VENDOR_QTY,MAX(QTY_ON_HAND) OVER (PARTITION BY NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY QTY_ON_HAND) MAX_VENDOR_QTY,COUNT(QTY_ON_HAND) OVER (PARTITION BY PREF_VENDOR_ID ORDER BY QTY_ON_HAND) COUNT_VENDORFROMPARTSORDER BYPART_ID;SELECTPRODUCT_CODE,RANK(1) WITHIN GROUP (ORDER BY QTY_ON_HAND DESC NULLS LAST) UNIT_PRICE,RANK(2) WITHIN GROUP (ORDER BY QTY_ON_HAND DESC NULLS LAST) UNIT_PRICE,RANK(3) WITHIN GROUP (ORDER BY QTY_ON_HAND DESC NULLS LAST) UNIT_PRICE,RANK(4) WITHIN GROUP (ORDER BY QTY_ON_HAND DESC NULLS LAST) UNIT_PRICE,RANK(5) WITHIN GROUP (ORDER BY QTY_ON_HAND DESC NULLS LAST) UNIT_PRICEFROMPARTSGROUP BYPRODUCT_CODEORDER BYPRODUCT_CODE;SELECTPO.PART_ID,P.DESCRIPTION,PO.VENDOR_ID,PO.CREATE_DATE,PO.UNIT_PRICE,PO.LAST_VENDOR_ID,PO.LAST_CREATE_DATE,PO.LAST_UNIT_PRICE,P.PRODUCT_CODE,P.COMMODITY_CODEFROM(SELECTPOL.PART_ID,PO.VENDOR_ID,TRUNC(NVL(POL.POL_CREATE_DATE,PO.CREATE_DATE))CREATE_DATE,POL.UNIT_PRICE,LEAD(PO.VENDOR_ID,1,NULL) OVER (PARTITION BY PART_ID ORDER BY NVL(POL.POL_CREATE_DATE,PO.CREATE_DATE) DESC) LAST_VENDOR_ID,TRUNC(LEAD(NVL(POL.POL_CREATE_DATE,PO.CREATE_DATE),1,NULL) OVER (PARTITION BY PART_ID ORDER BYNVL(POL.POL_CREATE_DATE,PO.CREATE_DATE) DESC)) LAST_CREATE_DATE,LEAD(POL.UNIT_PRICE,1,NULL) OVER (PARTITION BYPART_ID ORDER BY NVL(POL.POL_CREATE_DATE,PO.CREATE_DATE) DESC) LAST_UNIT_PRICEFROMPO_HEADER PO,PO_LINE POLWHEREPO.ID=POL.PURC_ORDER_IDAND PO.CREATE_DATE>TRUNC(SYSDATE-720)ORDER BYPOL.PART_ID,NVL(POL.POL_CREATE_DATE,PO.CREATE_DATE) DESC) PO,PARTS PWHEREPO.PART_ID=P.PART_IDAND PO.CREATE_DATE>TRUNC(SYSDATE-90)AND (PO.VENDOR_ID<>NVL(PO.LAST_VENDOR_ID,'-')OR PO.CREATE_DATE>(NVL(PO.LAST_CREATE_DATE,SYSDATE-1024)+180)OR PO.UNIT_PRICE<>NVL(PO.LAST_UNIT_PRICE,-1));SELECT 'FINISHED' FROM DUAL;ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'SPOOL OFF
No comments:
Post a Comment