EBS部分表整理
1.客户表1序号
中文名称
字段名称
类型和长度
1.
客户ID
CUST_ACCOUNT_ID
NUMBER
2.
客户编码
ACCOUNT_NUMBER
VARCHAR2(30)
3.
客户类型
CUSTOMER_CLASS_CODE
VARCHAR2(30)
4.
客户名称
PARTY_NAME
VARCHAR2(360)
5.
客户状态
STATUS
VARCHAR2(10)
6.
失效期
EXPIRED_DATE
DATE
7.
创建时间
CREATION_DATE
DATE
8.
创建人
CREATED_BY
NUMBER
9.
修改时间
LAST_UPDATE_DATE
DATE
10.
修改人
LAST_UPDATED_BY
NUMBER
select cust.cust_account_id,
cust.account_number,
cust.customer_class_code,
cust_party.party_name,
cust.status,
sysdate expired_date,
cust.creation_date,
cust.created_by,
cust.last_update_date,
cust.last_updated_by
from hz_parties cust_party,
hz_cust_accounts cust
where cust.party_id = cust_party.party_id
and cust_party.party_type = 'ORGANIZATION';
2.客户表2
序号
中文名称
字段名称
类型和长度
1.
客户ID
CUST_ACCOUNT_ID
NUMBER
2.
客户地址
ADDRESS1
VARCHAR2(240)
3.
ADDRESS2
VARCHAR2(240)
4.
ADDRESS3
VARCHAR2(240)
5.
ADDRESS4
VARCHAR2(240)
6.
地址ID
CUST_ACCT_SITE_ID
NUMBER
7.
地址状态
STATUS
VARCHAR2(10)
8.
失效期
EXPIRED_DATE
DATE
9.
创建时间
CREATION_DATE
DATE
10.
创建人
CREATED_BY
NUMBER
11.
修改时间
LAST_UPDATE_DATE
DATE
12.
修改人
LAST_UPDATED_BY
NUMBER
select addr.cust_account_id,
loc.address1,
loc.address2,
loc.address3,
loc.address4,
addr.cust_acct_site_id,
addr.status,
sysdate EXPIRED_DATE,
addr.creation_date,
addr.created_by,
addr.last_update_date,
addr.last_updated_by
from hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
hz_parties hp
where addr.party_site_id = party_site.party_site_id
and party_site.location_id = loc.location_id
and party_site.party_id = hp.party_id
and hp.party_type = 'ORGANIZATION'
order by addr.cust_account_id
3.客户表3
序号
中文名称
字段名称
类型和长度
1.
地址ID
CUST_ACCT_SITE_ID
NUMBER
2.
业务目的
MEANING
VARCHAR2(80)
3.
部门编码
SEGMENT2
VARCHAR2(25)
4.
状态
STATUS
VARCHAR2(10)
5.
失效期
EXPIRED_DATE
DATE
6.
创建时间
CREATION_DATE
DATE
7.
创建人
CREATED_BY
NUMBER
8.
修改时间
LAST_UPDATE_DATE
DATE
9.
修改人
LAST_UPDATED_BY
NUMBER
select csua.cust_acct_site_id,
flvv.meaning,
gcc.segment2,
csua.status,
sysdate expired_date,
csua.creation_date,
csua.created_by,
csua.last_update_date,
csua.last_updated_by
from hz_cust_site_uses_all csua,
fnd_lookup_values flvv,
gl_code_combinationsgcc,
fnd_flex_values_vl ffvv
where csua.site_use_code = flvv.lookup_code
and flvv.lookup_type = 'SITE_USE_CODE'
and flvv.language = userenv('LANG')
and csua.gl_id_rev = gcc.code_combination_id(+)
and gcc.segment2 = ffvv.flex_value(+)
and ffvv.flex_value_set_id(+) = 1009628
4.客户联系人
序号
中文名称
字段名称
类型和长度
1.
联系人ID
CONTACT_ID
NUMBER
2.
地址ID
CUST_ACCT_SITE_ID
NUMBER
3.
联系人名称
FIRST_NAME
VARCHAR2(150)
4.
LAST_NAME
VARCHAR2(150)
5.
联系人电话
COUNTRY_CODE
VARCHAR2(10)
6.
联系人手机
AREA_CODE
VARCHAR2(10)
7.
联系人传真
PHONE_NUMBER
VARCHAR2(50)
8.
PHONE_TYPE
VARCHAR2(30)
9.
MAIL_STOP
VARCHAR2(60)
10.
失效期
EXPIRED_DATE
DATE
11.
创建时间
CREATION_DATE
DATE
12.
创建人
CREATED_BY
NUMBER
13.
修改时间
LAST_UPDATE_DATE
DATE
14.
修改人
LAST_UPDATED_BY
NUMBER
15.
状态
PHONE_STATUS
VARCHAR2(30)
16.
CONTACT_STATUS
VARCHAR2(10)
select contact.contact_id,
addr.cust_acct_site_id,
contact.first_name,
contact.last_name,
phone.country_code,
phone.area_code,
phone.phone_number,
phone.phone_type,
contact.mail_stop,
sysdate expired_date,
contact.creation_date,
contact.created_by,
contact.last_update_date,
contact.last_updated_by,
phone.status phone_status, --电话
contact.status contact_status --联系人
from hz_cust_acct_sites_all addr,
ar_phones_v phone,
(select acct_role.cust_account_role_id contact_id,
party.person_first_name first_name,
party.person_last_name last_name,
org_cont.mail_stop,
acct_role.status,
acct_role.creation_date,
acct_role.created_by,
acct_role.last_update_date,
acct_role.last_updated_by,
acct_role.cust_account_id,
acct_role.cust_acct_site_id,
rel_party.party_id
from hz_cust_account_roles acct_role,
hz_parties party,
hz_parties rel_party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_cust_accounts role_acct
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel_party.party_id = rel.party_id
and acct_role.cust_account_id = role_acct.cust_account_id
and role_acct.party_id = rel.object_id) contact
where addr.cust_account_id = contact.cust_account_id(+)
and addr.cust_acct_site_id = contact.cust_acct_site_id(+)
and contact.party_id = phone.owner_table_id(+)
4. 物品表
序号
中文名称
字段名称
类型和长度
1、
物品ID
ITEM_ID
NUMBER
2、
物品编码
ITEM_NO
VARCHAR2(32)
3、
物品名称
ITEM_DESC1
VARCHAR2(70)
4、
ITEM_DESC2
VARCHAR2(70)
5、
单位
ITEM_UM
VARCHAR2(4)
6、
ITEM_UM2
VARCHAR2(4)
7、
采购提前期（天）
PURCHASE_DATE
DATE
8、
创建时间
CREATION_DATE
DATE
9、
创建人
CREATED_BY
NUMBER
10、
修改时间
LAST_UPDATE_DATE
DATE
11、
修改人
LAST_UPDATED_BY
NUMBER
select mst.item_id,
mst.item_no,
mstt.item_desc1,
mstt.item_desc2,
mst.item_um,
mst.item_um2,
sysdate purchase_date,
mst.creation_date,
mst.created_by,
mst.last_update_date,
mst.last_updated_by
from ic_item_mst_b mst,
gmi.ic_item_mst_tl mstt
where mst.item_id = mstt.item_id
and mstt.language = userenv('LANG')
5.物品子表
序号
中文名称
字段名称
类型和长度
1、
物品ID
ITEM_ID
NUMBER
2、
日历
CALENDAR_CODE
VARCHAR2(4)
3、
起始日期
START_DATE
DATE
4、
START_DATE1
DATE
5、
结束日期
END_DATE
DATE
6、
成本计算方法
COST_MTHD_CODE
VARCHAR2(4)
7、
仓库编码
WHSE_CODE
VARCHAR2(4)
8、
标准单价
PRICE
NUMBER
9、
创建时间
CREATION_DATE
VARCHAR2(10)
10、
创建人
CREATED_BY
NUMBER
11、
修改时间
LAST_UPDATE_DATE
VARCHAR2(10)
12、
修改人
LAST_UPDATED_BY
NUMBER
select --cm.cmpntcost_id,
cm.item_id,
cm.calendar_code,
--b.start_date,
cldr.start_date start_date,
cldr.end_date,
cm.cost_mthd_code,
cm.whse_code,
--cm.period_code,
--cm.cmpnt_cost,
sum(cm.cmpnt_cost) price,
to_char(cm.creation_date,'yyyy-mm-dd') creation_date,
cm.created_by,
to_char(cm.last_update_date,'yyyy-mm-dd') last_update_date,
cm.last_updated_by
from apps.cm_cmpt_dtl cm,
apps.cm_cldr_hdr_tl t,
apps.cm_cldr_hdr_bb,
apps.cm_cldr_dtl cldr
where b.calendar_code = t.calendar_code
and t.language = userenv('LANG')
and b.calendar_code = cm.calendar_code
and b.cost_mthd_code = cm.cost_mthd_code
and cldr.calendar_code = cm.calendar_code
and cldr.period_code = cm.period_code
6.产品表
序号
中文名称
字段名称
类型和长度
1.
产品ID
ITEM_ID
NUMBER
2.
产品编号
ITEM_NO
VARCHAR2(32)
3.
产品名称
ITEM_DESC1
VARCHAR2(70)
4.
ITEM_DESC2
VARCHAR2(70)
5.
产品品牌
PRD_BRAND
VARCHAR2(50)
6.
预算分类
PRD_ELSE2
VARCHAR2(50)
7.
所属口味类别
PRD_TASTE_TYPE
VARCHAR2(50)
8.
所属中品类
PRD_ELSE5
VARCHAR2(50)
9.
所属大品类
PRD_ELSE4
VARCHAR2(50)
10.
体积
VOLUME
NUMBER
11.
重量
UM_TYPE
VARCHAR2(10)
12.
TYPE_FACTORREV
NUMBER
13.
大包装
PRD_BALE
VARCHAR2(50)
14.
中包装
PRD_PACKET
VARCHAR2(50)
15.
产品损益分类
PRD_ELSE1
VARCHAR2(50)
16.
企划分类1
PRD_SPEC1_TYPE
VARCHAR2(50)
17.
企划分类2
PRD_SPEC2_TYPE
VARCHAR2(50)
18.
单位
ITEM_UM
VARCHAR2(4)
19.
ITEM_UM2
VARCHAR2(4)
20.
入数
PRD_SCORE
VARCHAR2(50)
21.
容量
PRD_CONTENT
VARCHAR2(50)
22.
箱别
PRD_OUTER
VARCHAR2(50)
23.
销售类别
SALE_CATE
VARCHAR2(40)
24.
创建时间
CREATION_DATE
DATE
25.
创建人
CREATED_BY
NUMBER
26.
产品分类修改时间
CATE_UPDATE_DATE
DATE
27.
产品修改时间
LAST_UPDATE_DATE
DATE
28.
修改人
LAST_UPDATED_BY
NUMBER
select mst.item_id,
mst.item_no,
mstt.item_desc1,
mstt.item_desc2,
type.prd_brand,
type.prd_else2,
type.prd_taste_type,
type.prd_else5,
type.prd_else4,
123 volume,--无体积
cnv.um_type,
cnv.type_factorrev,
type.prd_bale,
type.prd_packet,
type.prd_else1,
type.prd_spec1_type,
type.prd_spec2_type,
mst.item_um,
mst.item_um2,
type.prd_score,
type.prd_content,
type.prd_outer,
type.sale_cate,
mst.creation_date,
mst.created_by,
type.last_update_date cate_update_date,
mst.last_update_date,
mst.last_updated_by
from ic_item_mst_b mst,
gmi.ic_item_mst_tl mstt,
hek_item_type_info type,
ic_item_cnv cnv
where mst.item_id = mstt.item_id
and mst.item_no = type.item_no
and mst.item_id = cnv.item_id(+)
and mstt.language = userenv('LANG')
7.订单头表
序号
中文名称
字段名称
类型和长度
1.
订单ID
HEADER_ID
NUMBER
2.
订单号
ORDER_NUMBER
NUMBER
3.
客户编码
CUSTOMER_NUMBER
VARCHAR2(30)
4.
营业所编码
SEGMENT2
VARCHAR2(25)
5.
订单类型
ORDER_TYPE
VARCHAR2(30)
6.
订单创建日期
ORDERED_DATE
DATE
7.
订单状态
FLOW_STATUS_CODE
VARCHAR2(30)
8.
制单人
CREATED_BY
NUMBER
9.
修改时间
LAST_UPDATE_DATE
DATE
10.
修改人
LAST_UPDATED_BY
NUMBER
select h.header_id,
h.order_number,
cust_acct.account_number customer_number,
gcc.segment2,
ot.name order_type,
h.ordered_date,
h.flow_status_code,
h.created_by,
h.last_update_date,
h.last_updated_by
from oe_order_headers_all h,
hz_cust_accounts cust_acct,
oe_transaction_types_tl ot,
hz_cust_site_uses_all bill_su,
gl_code_combinations gcc,
fnd_flex_values_vl ffvv
where h.sold_to_org_id = cust_acct.cust_account_id(+)
and h.order_type_id = ot.transaction_type_id
and ot.language = userenv('LANG')
and h.invoice_to_org_id = bill_su.site_use_id(+)
and bill_su.gl_id_rec = gcc.code_combination_id
and gcc.segment2 = ffvv.flex_value(+)
and ffvv.flex_value_set_id(+) = 1009628
order by 1
8.订单行表
序号
中文名称
字段名称
类型和长度
1.
订单ID
HEADER_ID
NUMBER
2.
订单行ID
LINE_ID
NUMBER
3.
行类型
LINE_TYPE
VARCHAR2(30)
4.
行状态
FLOW_STATUS_CODE
VARCHAR2(30)
5.
产品编码
ORDERED_ITEM
VARCHAR2(2000)
6.
数量
ORDERED_QUANTITY
NUMBER
7.
单价
UNIT_SELLING_PRICE
NUMBER
8.
金额
EXTENDED_PRICE
NUMBER
9.
发货日期
ORDERED_DATE
DATE
10.
受益产品
ATTRIBUTE1
VARCHAR2(240)
select l.header_id,
l.line_id,
lt.name line_type,
l.flow_status_code,
l.ordered_item,
l.ordered_quantity,
l.unit_selling_price,
l.ordered_quantity * l.unit_selling_price extended_price,
h.ordered_date,
l.attribute1
from oe_order_headers_all h,
oe_order_lines_all l,
oe_transaction_types_tl lt
where l.header_id = h.header_id
and l.line_type_id = lt.transaction_type_id
and lt.language = userenv('LANG')
9.POP采购表
序号
中文名称
字段名称
类型和长度
1.
申请ID
LINE_ID
NUMBER
2.
采购单号
SEGMENT1
VARCHAR2(20)
3.
请购日期
CREATION_DATE
DATE
4.
预算月份
ATTRIBUTE2
VARCHAR2(50)
5.
营业所编码
ATTRIBUTE3
VARCHAR2(50)
6.
受益品类
SEGMENT2
VARCHAR2(25)
7.
广促品品名
ATTRIBUTE1
VARCHAR2(50)
8.
采购数量
DESCRIPTION
VARCHAR2(240)
9.
采购单价
QUANTITY_3
NUMBER
10.
状态
FLOW_STATUS
VARCHAR2(100)
11.
金额
PRICE
NUMBER
12.
申请人
CREATED_BY
NUMBER
13.
备注
REMARK
VARCHAR2(150)
--PO的信息
select popline.line_id,
poheader.segment1,
prheader.creation_date,
popheader.attribute2,
popheader.attribute3,
gcc.segment2,
popheader.attribute1,
item.description,
popline.quantity_3,
popheader.flow_status,
(popline.quantity_3 * poline.unit_price) price,
popheader.created_by,
'XXX' remark
from hek_om_pop_headers_all popheader,
hek_om_pop_lines_all popline,
mtl_system_items_b item,
ar.hz_cust_site_uses_all site1,
ar.hz_cust_site_uses_all site2,
gl_code_combinations gcc,
po_requisition_headers_all prheader,
po_requisition_lines_all prline,
po_line_locations_all poll,
po_headers_all poheader,
po_lines_all poline
where popheader.header_id = popline.header_id
and item.inventory_item_id = popline.item_id
and popheader.ship_to_org_id = site1.site_use_id
and site1.bill_to_site_use_id = site2.site_use_id
and site2.gl_id_rec = gcc.code_combination_id
and popheader.request_header_id = prheader.requisition_header_id(+)
and item.organization_id = 155
/*and popheader.request_header_id is not null*/
and prheader.requisition_header_id = prline.requisition_header_id
and popline.item_id = prline.item_id
/*and prline.line_location_id is not null*/ --连接采购单的字段
and prline.line_location_id = poll.line_location_id
and poll.po_header_id = poheader.po_header_id
and poll.po_line_id = poline.po_line_id
UNION ALL
--PR的信息
select popline.line_id,
null,
prheader.creation_date,
popheader.attribute2,
popheader.attribute3,
gcc.segment2,
popheader.attribute1,
item.description,
popline.quantity_3,
popheader.flow_status,
null,
popheader.created_by,
null
from hek_om_pop_headers_all popheader,
hek_om_pop_lines_all popline,
mtl_system_items_b item,
ar.hz_cust_site_uses_all site1,
ar.hz_cust_site_uses_all site2,
gl_code_combinations gcc,
po_requisition_headers_all prheader,
po_requisition_lines_all prline
where popheader.header_id = popline.header_id
and item.inventory_item_id = popline.item_id
and popheader.ship_to_org_id = site1.site_use_id
and site1.bill_to_site_use_id = site2.site_use_id
and site2.gl_id_rec = gcc.code_combination_id
and popheader.request_header_id = prheader.requisition_header_id(+)
and item.organization_id = 155
and popheader.request_header_id is not null
and prheader.requisition_header_id = prline.requisition_header_id
and popline.item_id = prline.item_id
and prline.line_location_id is null
UNION ALL
--只有POP的信息
select popline.line_id,
null,
null,
popheader.attribute2,
popheader.attribute3,
gcc.segment2,
popheader.attribute1,
item.description,
popline.quantity_3,
popheader.flow_status,
null,
popheader.created_by,
null
from hek_om_pop_headers_all popheader,
hek_om_pop_lines_all popline,
mtl_system_items_b item,
ar.hz_cust_site_uses_all site1,
ar.hz_cust_site_uses_all site2,
gl_code_combinations gcc
where popheader.header_id = popline.header_id
and item.inventory_item_id = popline.item_id
and popheader.ship_to_org_id = site1.site_use_id
and site1.bill_to_site_use_id = site2.site_use_id
and site2.gl_id_rec = gcc.code_combination_id
and item.organization_id = 155
and popheader.request_header_id is null
页:
[1]