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_combinations gcc,
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_b b,
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
