CREATE OR REPLACE VIEW PO_MCHNT_BYR AS
SELECT P.SHCODE,P.LOCNO,POCODE,P.ACTIVE,SHIP_FLAG,P.POSTRING,P.B1CODE,S.SCODE,STYLENO,STYLEBUY,S.DESCRIPTION,U1.UCODE MANAGER,U2.UCODE,U0.LNAME||'/'||U1.LNAME||'/'||U2.LNAME MNAME,B3.B3FULL
FROM USERMST U0,USERMST U1,USERMST U2,POMST P,STYLE S,BUYER1 B1,BUYER2 B2,BUYER3 B3
WHERE U0.UCODE=U1.MANAGER AND U1.UCODE=P.MANAGER AND U1.UCODE=U2.MANAGER AND U2.UCODE=P.UCODE AND
P.POCODE=S.PO AND S.B1CODE=B1.B1CODE AND S.B2CODE=B2.B2CODE AND S.B3CODE=B3.B3CODE AND
B3.B2CODE=B2.B2CODE AND B3.B1CODE=B1.B1CODE AND B2.B1CODE=B1.B1CODE AND P.ACTIVE<>'C' ORDER BY 1;

---- Create table

create table USERMST
(
  UCODE           NUMBER(4) not null,
  UGROUP          VARCHAR2(15),
  MANAGER         NUMBER(4),
  NAME            VARCHAR2(20) not null,
  PASSWORD        VARCHAR2(15) not null,
  DEPTNO          NUMBER(2),
  LNAME           VARCHAR2(3) not null,
  LOCNO           NUMBER(2) not null,
  DES_FLAG        VARCHAR2(1),
  ACTIVE          VARCHAR2(1),
  EMAIL           VARCHAR2(100),
  D_HEAD          VARCHAR2(1),
  CCODE           NUMBER(2),
  CREATION_DATE   DATE,
  LAST_LOGIN_DATE DATE
)
tablespace SAKET
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 1104K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table USERMST
  add constraint USERMST1 unique (UCODE)
  using index 
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 80K
    next 40K
    minextents 1
    maxextents 505
    pctincrease 50
  );
-- Create/Recreate indexes 
create index UMST_INDX2 on USERMST (UCODE, MANAGER, LNAME)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 80K
    next 64K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create index UMST_INDX3 on USERMST (UCODE, LOCNO)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 40K
    minextents 1
    maxextents 505
    pctincrease 50
  );
create index UMST_INDX4 on USERMST (LOCNO, ACTIVE)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 40K
    minextents 1
    maxextents 505
    pctincrease 50
  );
create index USERMNGR on USERMST (MANAGER)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 80K
    next 64K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );

-------------

create table POMST
(
  POCODE     VARCHAR2(8) not null,
  POSCODE    NUMBER(3) not null,
  UCODE      NUMBER(4) not null,
  MANAGER    NUMBER(4),
  POSTRING   VARCHAR2(2) not null,
  ACTIVE     VARCHAR2(1) not null,
  B1CODE     NUMBER(3),
  SHCODE     NUMBER(2),
  GROUP_NAME VARCHAR2(25),
  SHIP_FLAG  VARCHAR2(1),
  LOCNO      NUMBER(2),
  PODATE     DATE,
  S_P        VARCHAR2(1),
  NO_OF_PCS  NUMBER,
  CURCODE    NUMBER,
  EX_RATE    NUMBER(12,2)
)
tablespace SAKET
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 8320K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Add comments to the columns 
comment on column POMST.POCODE
  is 'PO_NO Internal Refernce no.';
comment on column POMST.POSCODE
  is 'Season Code';
comment on column POMST.UCODE
  is 'User Code';
comment on column POMST.MANAGER
  is 'User_code as Manager';
comment on column POMST.POSTRING
  is '2 Chars to Identify the Order Type';
comment on column POMST.ACTIVE
  is 'Store Y/C { If PO IS Active - Y, If PO is cancel - ''C'' ) ';
comment on column POMST.B1CODE
  is 'Buyer1 code';
comment on column POMST.SHCODE
  is 'Shipper code';
comment on column POMST.GROUP_NAME
  is 'Text Input for ex. story_name';
comment on column POMST.SHIP_FLAG
  is 'Flag {S- Shipped, U-Unshipped}';
comment on column POMST.LOCNO
  is 'Location no.';
comment on column POMST.PODATE
  is 'PO_System Entry Date';
-- Create/Recreate primary, unique and foreign key constraints 
alter table POMST
  add constraint POMST1 primary key (POCODE)
  disable;
-- Create/Recreate indexes 
create bitmap index POMST_IND on POMST (SHIP_FLAG)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 38648K
    next 19176K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create bitmap index POMST_IND2 on POMST (LOCNO)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 7688K
    next 3784K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create index POMST_IND3 on POMST (POCODE, ACTIVE, SHCODE, SHIP_FLAG, LOCNO, GROUP_NAME)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10840K
    next 5680K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create index POMST_IND4 on POMST (POSCODE, POCODE)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 6640K
    next 3784K
    minextents 1
    maxextents 505
    pctincrease 50
  );
create index POMST_IND5 on POMST (POCODE, SHIP_FLAG, ACTIVE)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 7640K
    next 3784K
    minextents 1
    maxextents 505
    pctincrease 50
  );
create index POMST_IND6 on POMST (SHCODE, LOCNO, POCODE, POSTRING, B1CODE, ACTIVE, UCODE, MANAGER, SHIP_FLAG)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 14840K
    next 8520K
    minextents 1
    maxextents 505
    pctincrease 50
  );
create index POMST_IND7 on POMST (SHCODE)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 2520K
    minextents 1
    maxextents 505
    pctincrease 50
  );
create index POMST_IND8 on POMST (POSCODE, POCODE, B1CODE, SHCODE, ACTIVE, GROUP_NAME)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10440K
    next 5680K
    minextents 1
    maxextents 505
    pctincrease 50
  );

-----
create table BUYER1
(
  B1CODE     NUMBER(3) not null,
  B1NAME     VARCHAR2(25) not null,
  B1NAMES    VARCHAR2(6),
  B1NORMAL   NUMBER(3),
  B1QUICK    NUMBER(3),
  B1SHIPNAME VARCHAR2(50),
  B1ADD1     VARCHAR2(35),
  B1ADD2     VARCHAR2(35),
  B1ADD3     VARCHAR2(35),
  B1AGCOM    NUMBER(6,2),
  B1FRIEGHT  NUMBER(6,2),
  B1MISC     NUMBER(6,2),
  ACTIVE     CHAR(1) default 'Y',
  B1ADD4     VARCHAR2(30)
)
tablespace SAKET
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table BUYER1
  add constraint BUYER1_PKEY primary key (B1CODE)
  using index 
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes 
create index BYR1_NAMES on BUYER1 (B1NAMES)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 80K
    next 64K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );

---------

create table BUYER2
(
  B1CODE NUMBER(3) not null,
  B2CODE NUMBER(2) not null,
  B2NAME VARCHAR2(10)
)
tablespace SAKET
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table BUYER2
  add constraint B2_PK primary key (B1CODE, B2CODE)
  using index 
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 50M
    next 50M
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
----
create table BUYER3
(
  B1CODE  NUMBER(3) not null,
  B2CODE  NUMBER(2) not null,
  B3CODE  NUMBER(3) not null,
  B3NAME  VARCHAR2(10),
  B3FULL  VARCHAR2(11) not null,
  MANAGER NUMBER(4)
)
tablespace SAKET
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table BUYER3
  add constraint BUYER32 primary key (B1CODE, B2CODE, B3CODE)
  using index 
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
---
create table STYLE
(
  B1CODE      NUMBER(3) not null,
  B2CODE      NUMBER(2) not null,
  B3CODE      NUMBER(3) not null,
  SCODE       NUMBER(3) not null,
  AGCODE      NUMBER(3),
  STYLENO     NUMBER(6) not null,
  PO          VARCHAR2(8),
  DEVNO       VARCHAR2(6),
  STYLEBUY    VARCHAR2(100) not null,
  DESCRIPTION VARCHAR2(100),
  GFCODE      NUMBER(2),
  COLCUFF     VARCHAR2(1) default 'N' not null,
  LASFREF     VARCHAR2(15),
  GRADING     NUMBER(4),
  UCODE       NUMBER(4),
  LOGF        NUMBER(4),
  DATEF       DATE,
  LOGM        NUMBER(4),
  DATEM       DATE,
  ECM         NUMBER(8,2),
  STITCHING   VARCHAR2(2000),
  TRIM        VARCHAR2(2000),
  PACKING     VARCHAR2(2000),
  BASIC       VARCHAR2(2000),
  REMARKS     VARCHAR2(2000),
  SFLAG       VARCHAR2(1),
  RFLAG       VARCHAR2(1),
  SAM         NUMBER(3),
  PROD_TRIMS  NUMBER(2),
  MERCH_TRIMS NUMBER(2),
  BUYER_TRIMS NUMBER(2),
  BUYER_FAB   NUMBER(2),
  MERCH_FAB   NUMBER(2),
  PAFLAG      CHAR(1) not null,
  ITEM_CODE   NUMBER(3)
)
tablespace SAKET
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 5216K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Add comments to the columns 
comment on column STYLE.B1CODE
  is 'Buyer1 code ';
comment on column STYLE.B2CODE
  is 'Buyer2 code';
comment on column STYLE.B3CODE
  is 'Buyer3 code';
comment on column STYLE.SCODE
  is 'Season code';
comment on column STYLE.AGCODE
  is 'Agent code';
comment on column STYLE.STYLENO
  is 'Internal Style no.';
comment on column STYLE.PO
  is 'Po number.';
comment on column STYLE.DEVNO
  is 'Development Number';
comment on column STYLE.STYLEBUY
  is 'Style buyer name';
comment on column STYLE.DESCRIPTION
  is 'Style Description';
comment on column STYLE.GFCODE
  is 'Garment Finish code linked with Gfinish';
comment on column STYLE.COLCUFF
  is 'not used now';
comment on column STYLE.LASFREF
  is 'Input Text field';
comment on column STYLE.GRADING
  is 'Grading code - now not used';
comment on column STYLE.UCODE
  is 'Usercode from usermst';
comment on column STYLE.LOGF
  is 'Ucode {login id }';
comment on column STYLE.DATEF
  is 'Date of Entry';
comment on column STYLE.LOGM
  is 'Logs Usercode of the user who Modifies the record';
comment on column STYLE.DATEM
  is 'Stores date of  modification';
comment on column STYLE.ECM
  is 'Stores ECM - now not used';
comment on column STYLE.STITCHING
  is 'Stores Free Text for Storing Stitching Remarks';
comment on column STYLE.TRIM
  is 'Stores Free Text for Storing Trim Remarks';
comment on column STYLE.PACKING
  is 'Stores Free Text for Packing Remarks';
comment on column STYLE.BASIC
  is 'Stores Free Text for storing Basic Remarks';
comment on column STYLE.REMARKS
  is 'Stores Free Text for Remarks';
comment on column STYLE.SFLAG
  is 'not used';
comment on column STYLE.RFLAG
  is 'not used';
comment on column STYLE.SAM
  is 'not used - stores sam';
comment on column STYLE.PROD_TRIMS
  is 'Stores no. of Production Trims';
comment on column STYLE.MERCH_TRIMS
  is 'Stores no. merchant Trims';
comment on column STYLE.BUYER_TRIMS
  is 'Stores no. of Buyer Trims';
comment on column STYLE.BUYER_FAB
  is 'Stores no. of Buyer Fabric';
comment on column STYLE.MERCH_FAB
  is 'Stores no. of Merchant Fabric';
comment on column STYLE.PAFLAG
  is 'not used default value ''A''';
comment on column STYLE.ITEM_CODE
  is 'Stores Garment Style Item code linked with Item_master table.';
-- Create/Recreate primary, unique and foreign key constraints 
alter table STYLE
  add constraint STYLE1 primary key (STYLENO)
  using index 
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1120K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
alter table STYLE
  add constraint STYLE5 foreign key (PO)
  references POMST (POCODE)
  disable;
-- Create/Recreate indexes 
create index STYLE3 on STYLE (PO)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1120K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index STYLE4 on STYLE (PO, UCODE, B1CODE, B2CODE, B3CODE)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1560K
    next 744K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create index STYLE6 on STYLE (PO, STYLENO)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1080K
    next 496K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create index STYLE7 on STYLE (PO, B1CODE, STYLENO)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1600K
    next 744K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create index STYLE8 on STYLE (PO, STYLENO, STYLEBUY)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1720K
    next 1120K
    minextents 1
    maxextents unlimited
    pctincrease 50
  );
create index STYL_INDX1 on STYLE (PO, B1CODE, B2CODE, B3CODE, SCODE, STYLENO, STYLEBUY, DESCRIPTION)
  tablespace SAKET
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 2520K
    minextents 1
    maxextents 505
    pctincrease 50
  );