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