SELECT supplier_id,
(SELECT area_id
FROM crm_area_country
WHERE area_type = 'office' START
WITH area_id = c.supplier_country_id CONNECT BY prior area_parent_id = area_id
(SELECT area_id
FROM crm_area_country
WHERE area_type = 'office' START
WITH area_id = c.supplier_country_id CONNECT BY prior area_parent_id = area_id
SELECT user_id, psa.vendor_id supplier_id,popedom_type --DISTINCT
FROM po_vendor_sites_all psa,
(SELECT area_id,
area_code,
area_correspond_code,
area_chinese_name,
area_type,
user_id
,popedom_type
FROM (SELECT c.area_id,--地区部的权限范围
area_code,
area_correspond_code,
area_chinese_name,
area_type,
c.popedom_user user_id
,c.popedom_type
FROM crm_popedom c,
(SELECT area_id,
area_code,
area_correspond_code,
area_chinese_name,
area_type,
area_parent_id,
(rtrim(substr(ltrim(sys_connect_by_path(area_parent_id, ','), ','),
0,
instr(ltrim(sys_connect_by_path(area_parent_id, ','), ','),
',',
1)),
','))
AS root_area_id
FROM crm_area_country ca
START WITH ca.area_parent_id IN (SELECT area_id FROM crm_popedom)
CONNECT BY PRIOR ca.area_id = ca.area_parent_id) a
WHERE a.root_area_id = c.area_id or a.area_parent_id=c.area_id )
WHERE area_type = 'Country') ca --,po_vendors_all pa
WHERE psa.country = ca.area_correspond_code
union all
select cac.popedom_user user_id, supplier_id, popedom_type
from (select supplier_id,
(select area_id
from crm_area_country
WHERE area_type = 'office'
start with area_id = c.supplier_country_id
connect by prior area_parent_id = area_id) a,
(select area_id
from crm_area_country
WHERE area_type = 'Regional'
start with area_id = c.supplier_country_id
connect by prior area_parent_id = area_id) b
from crm_supplier_baseinfo c
where supplier_cooperate_id = 'LATENCY') tmp,
crm_popedom cac
where (tmp.a = cac.area_id or tmp.b = cac.area_id);
-- Create table
create table PO_VENDOR_SITES_ALL
(
VENDOR_SITE_ID NUMBER not null,
VENDOR_ID NUMBER not null,
ORG_ID NUMBER not null,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
VENDOR_SITE_CODE VARCHAR2(50) not null,
CREATION_DATE DATE,
CREATED_BY NUMBER,
PURCHASING_SITE_FLAG VARCHAR2(1),
RFQ_ONLY_SITE_FLAG VARCHAR2(1),
PAY_SITE_FLAG VARCHAR2(1),
ATTENTION_AR_FLAG VARCHAR2(1),
ADDRESS_LINE1 VARCHAR2(240),
ADDRESS_LINE2 VARCHAR2(240),
ADDRESS_LINE3 VARCHAR2(240),
CITY VARCHAR2(25),
STATE VARCHAR2(150),
ZIP VARCHAR2(20),
PROVINCE VARCHAR2(150),
AREA_CODE VARCHAR2(10),
PHONE VARCHAR2(15),
SHIP_TO_LOCATION_ID NUMBER,
BILL_TO_LOCATION_ID NUMBER,
BANK_ACCOUNT_NUM VARCHAR2(60),
BANK_NUM VARCHAR2(60),
TERMS_ID NUMBER,
INVOICE_CURRENCY_CODE VARCHAR2(30),
PAYMENT_CURRENCY_CODE VARCHAR2(30),
VAT_CODE VARCHAR2(30),
FOB_LOOKUP_CODE VARCHAR2(80),
TAX_RATE NUMBER,
PAYMENT_TERMS VARCHAR2(50),
FAX_AREA_CODE VARCHAR2(10),
FAX VARCHAR2(15),
BRANCH_NUM VARCHAR2(60),
EMAIL VARCHAR2(60),
IS_ENGINEERING VARCHAR2(1),
COUNTRY VARCHAR2(25)
)
tablespace SCS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column PO_VENDOR_SITES_ALL.TERMS_ID
is '付款条件';
comment on column PO_VENDOR_SITES_ALL.INVOICE_CURRENCY_CODE
is '发票币种';
comment on column PO_VENDOR_SITES_ALL.PAYMENT_CURRENCY_CODE
is '付款币种';
comment on column PO_VENDOR_SITES_ALL.VAT_CODE
is '税率代码';
comment on column PO_VENDOR_SITES_ALL.FOB_LOOKUP_CODE
is 'FOB类型';
comment on column PO_VENDOR_SITES_ALL.IS_ENGINEERING
is '是否是工程服务供应商';
-- Create/Recreate indexes
create unique index PO_VENDOR_SITES_U1 on PO_VENDOR_SITES_ALL (VENDOR_SITE_ID, ORG_ID)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create unique index PO_VENDOR_SITES_U2 on PO_VENDOR_SITES_ALL (VENDOR_ID, VENDOR_SITE_CODE, ORG_ID)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index PO_VENDOR_SITES_VN1 on PO_VENDOR_SITES_ALL (VENDOR_SITE_ID)
tablespace SCS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index PO_VENDOR_SITES_VN2 on PO_VENDOR_SITES_ALL (VENDOR_ID)
tablespace SCS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index PO_VENDOR_SITES_VU4 on PO_VENDOR_SITES_ALL (COUNTRY)
tablespace SCS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table
create table CRM_POPEDOM
(
POPEDOM_ID NUMBER not null,
AREA_ID NUMBER,
POPEDOM_SPECIFY VARCHAR2(1) not null,
POPEDOM_TYPE VARCHAR2(1) not null,
POPEDOM_DESC VARCHAR2(100),
POPEDOM_USER NUMBER,
POPEDOM_UPDATE_DATE DATE,
POPEDOM_CREATE_DATE DATE,
POPEDOM_CREATE_BY NUMBER,
POPEDOM_UPDATE_BY NUMBER,
POPEDOM_ESPECIAL_ALL VARCHAR2(5),
POPEDOM_ESPECIAL_AREA VARCHAR2(1)
)
tablespace SCS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table CRM_POPEDOM
is '合作资源权限表';
-- Add comments to the columns
comment on column CRM_POPEDOM.POPEDOM_ID
is 'id';
comment on column CRM_POPEDOM.AREA_ID
is '区域id';
comment on column CRM_POPEDOM.POPEDOM_SPECIFY
is '是否可以指派权限';
comment on column CRM_POPEDOM.POPEDOM_TYPE
is '权限类型修改Y，查看N';
comment on column CRM_POPEDOM.POPEDOM_DESC
is '权限描述';
comment on column CRM_POPEDOM.POPEDOM_USER
is '权限人';
comment on column CRM_POPEDOM.POPEDOM_UPDATE_DATE
is '权限更新时间';
comment on column CRM_POPEDOM.POPEDOM_CREATE_DATE
is '权限创建时间';
comment on column CRM_POPEDOM.POPEDOM_CREATE_BY
is '权限创建人';
comment on column CRM_POPEDOM.POPEDOM_UPDATE_BY
is '权限更新人';
comment on column CRM_POPEDOM.POPEDOM_ESPECIAL_ALL
is '权限区分all';
comment on column CRM_POPEDOM.POPEDOM_ESPECIAL_AREA
is '权限区分是否时超级管理员添加，是为Y';
-- Create/Recreate primary, unique and foreign key constraints
alter table CRM_POPEDOM
add constraint PK_CRM_POPEDOM primary key (POPEDOM_ID)
using index
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index IN_CRM_POPEDOM_N on CRM_POPEDOM (AREA_ID)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IN_CRM_POPEDOM_N1 on CRM_POPEDOM (POPEDOM_TYPE)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IN_CRM_POPEDOM_N2 on CRM_POPEDOM (POPEDOM_USER)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table
create table CRM_AREA_COUNTRY
(
AREA_ID NUMBER not null,
AREA_CODE VARCHAR2(10),
AREA_TYPE VARCHAR2(20),
AREA_CHINESE_NAME VARCHAR2(100),
AREA_ENGLISH_NAME VARCHAR2(100),
AREA_DESCRIPTION VARCHAR2(100),
AREA_ENABLED_FLAG VARCHAR2(5),
AREA_PARENT_ID NUMBER,
AREA_LAST_UPDATE_DATE DATE,
AREA_NUMBER VARCHAR2(10),
AREA_POSTALCODE NUMBER,
AREA_CORRESPOND_CODE VARCHAR2(10)
)
tablespace SCS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table CRM_AREA_COUNTRY
is '片区和国家关系表';
-- Add comments to the columns
comment on column CRM_AREA_COUNTRY.AREA_ID
is '区域id';
comment on column CRM_AREA_COUNTRY.AREA_CODE
is '区域编码';
comment on column CRM_AREA_COUNTRY.AREA_TYPE
is '区域类型（片区,地区部,国家,省标记）';
comment on column CRM_AREA_COUNTRY.AREA_CHINESE_NAME
is '区域国家中文名';
comment on column CRM_AREA_COUNTRY.AREA_ENGLISH_NAME
is '区域国家英文名';
comment on column CRM_AREA_COUNTRY.AREA_DESCRIPTION
is '区域国家描叙';
comment on column CRM_AREA_COUNTRY.AREA_ENABLED_FLAG
is '启用标记';
comment on column CRM_AREA_COUNTRY.AREA_PARENT_ID
is '父节点';
comment on column CRM_AREA_COUNTRY.AREA_LAST_UPDATE_DATE
is '更新时间';
comment on column CRM_AREA_COUNTRY.AREA_NUMBER
is '区号';
comment on column CRM_AREA_COUNTRY.AREA_POSTALCODE
is '邮政编码';
comment on column CRM_AREA_COUNTRY.AREA_CORRESPOND_CODE
is '与erp中对应的country code';
-- Create/Recreate primary, unique and foreign key constraints
alter table CRM_AREA_COUNTRY
add constraint PK_CRM_AREA_COUNTRY primary key (AREA_ID)
using index
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index CRM_AREA_COUNTRY_U1 on CRM_AREA_COUNTRY (AREA_CORRESPOND_CODE)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IN_CRM_AREA_COUNTRY_PANTERN on CRM_AREA_COUNTRY (AREA_PARENT_ID)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create bitmap index IN_CRM_AREA_COUNTRY_TYPERN on CRM_AREA_COUNTRY (AREA_TYPE)
tablespace SCS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);