ORACLE EBS常用表查询语句

1.  OU、库存组织

SELECT hou.organization_id ou_org_id,           --org_id  

       hou.name ou_name,                        --ou 名称   

       ood.organization_id org_org_id,          -- 库存组织 id  

       ood.organization_code org_org_code,      -- 库存组织代码    

       msi.secondary_inventory_name,            -- 子库存名称   

       msi.description                          -- 子库存描述   

  FROM hr_organization_information   hoi,        -- 组织分类表   

        hr_operating_units           hou,        --ou 视图     

        org_organization_definitions ood,        -- 库存组织定义视图   

        mtl_secondary_inventories    msi         -- 子库存信息表   

  WHERE hoi.org_information1 = 'OPERATING_UNIT'  

   AND hoi.organization_id  = hou.organization_id  

   AND ood.operating_unit   = hoi.organization_id  

   AND ood.organization_id  = msi.organization_id

-- 获取系统 ID

call fnd_global.APPS_INITIALIZE( 1318 , 50583 , 401 )

select fnd_profile.VALUE( 'ORG_ID' ) FROM DUAL

select * from hr_operating_units hou where hou.organization_id= 204

2.  用户、责任及 HR

-- 系统责任定义 VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)

SELECT APPLICATION_ID,

       RESPONSIBILITY_ID,

       RESPONSIBILITY_KEY,

       END_DATE,

       RESPONSIBILITY_NAME,

       DESCRIPTION

  FROM FND_RESPONSIBILITY_VL;

-- 用户责任关系

SELECT USER_ID, RESPONSIBILITY_ID FROM FND_USER_RESP_GROUPS;

-- 用户表

SELECT USER_ID, USER_NAME, EMPLOYEE_ID, PERSON_PARTY_ID, END_DATE

  FROM FND_USER;

-- 人员表 VIEW

SELECT PERSON_ID,

       START_DATE,

       DATE_OF_BIRTH,

       EMPLOYEE_NUMBER,

       NATIONAL_IDENTIFIER,

       SEX,

       FULL_NAME

  FROM per_people_f;

-- 综合查询

SELECT USER_NAME, FULL_NAME, RESPONSIBILITY_NAME, CC.DESCRIPTION

  FROM FND_USER              AA,

       FND_USER_RESP_GROUPS  BB,

       FND_RESPONSIBILITY_VL CC,

       per_people_f          DD

  WHERE AA.USER_ID = BB.USER_ID

   AND BB.RESPONSIBILITY_ID = CC.RESPONSIBILITY_ID

   AND AA.EMPLOYEE_ID = DD.PERSON_ID

   AND RESPONSIBILITY_NAME like '% 供应处 %'

  ORDER BY USER_NAME;

-- 综合查询

-- 人员状况基本信息表

SELECT PAF.PERSON_ID 系统 ID,

       PAF.FULL_NAME 姓名 ,

       PAF.DATE_OF_BIRTH 出生日期 ,

       PAF.REGION_OF_BIRTH 出生地区 ,

       PAF.NATIONAL_IDENTIFIER 身份证号 ,

       PAF.ATTRIBUTE1 招工来源 ,

       PAF.ATTRIBUTE3 员工类型 ,

       PAF.ATTRIBUTE11 集团合同号 ,

       PAF.original_date_of_hire 参加工作日期 ,

       PAF.PER_INFORMATION17 省份 ,

       DECODE (PAF.SEX, 'M' , ' 男 ' , 'F' , ' 女 ' , 'NULL' ) 性别 , --decode 适合和同一值做比较有多种结果，不适合和多种值比较有多种结果

       CASE PAF.SEX

            WHEN 'M' THEN ' 男 '

            WHEN 'F' THEN ' 女 '

            ELSE 'NULL'

            END   性别 1,     --case 用法一

       CASE WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1960' THEN '50 年代 '

            WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1970' THEN '60 年代 '

            WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1980' THEN '70 年代 '

            WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1990' THEN '80 年代 '

            WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '2000' THEN '90 年代 '

            ELSE '21 世纪 ' --case 用法二

       END 出生年代

FROM PER_ALL_PEOPLE_F PAF

 

 

 

3.  供应商 VENDOR

-- 供应商主表数据：

SELECT ass.vendor_id                  vendor_id,

       ass.party_id                   party_id,

       ass.segment1                   vendor_code,

       ass.vendor_name                vendor_name,

       ass.vendor_name                vendor_short_name,

       ass.vendor_type_lookup_code    vendor_type,

       flv.meaning                    vendor_type_meaning,

       hp.tax_reference               tax_registered_name,

       ass.payment_method_lookup_code payment_method,

       att.name                       term_name,

       att.enabled_flag               enabled_flag,

       att.end_date_active            end_date_active,

       ass.creation_date              creation_date,

       ass.created_by                 created_by,

       ass.last_update_date           last_update_date,

       ass.last_updated_by            last_updated_by,

       ass.last_update_login          last_update_login

  FROM ap_suppliers      ass,

       fnd_lookup_values flv,

       hz_parties        hp,

       ap_terms_tl       att

  WHERE ass.vendor_type_lookup_code = flv.lookup_code(+)

   AND flv.lookup_type(+) = 'VENDOR TYPE'

   AND flv.language(+) = userenv ( 'LANG' )

   AND ass.party_id = hp.party_id

   AND att.language = userenv ( 'LANG' )

   AND ass.terms_id = att.term_id(+)

-- 供应商银行信息

SELECT ass.vendor_id         vendor_id,

       ass.party_id          party_id,

       bank.party_id         bank_id,

        bank.party_name       bank_name,

       branch.party_id       branch_id,

       branch.party_name     bank_branch_name,

       ieba.bank_account_num bank_account_num

  FROM ap_suppliers          ass,

       hz_parties            hp,

       iby_account_owners    iao,

       iby_ext_bank_accounts ieba,

       hz_parties            bank,

       hz_parties            branch

  WHERE ass.party_id = hp.party_id

   AND hp.party_id = iao.account_owner_party_id(+)

   AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)

   AND ieba.bank_id = bank.party_id(+)

   AND ieba.branch_id = branch.party_id(+)

  ORDER BY ieba.creation_date;

-- 供应商开户行地址信息

SELECT hps.party_id      party_id,

       hps.party_site_id party_site_id,

        hl.location_id    location_id,

       hl.country        country,

       hl.province       province,

       hl.city           city,

       hl.address1       address1,

       hl.address2       address2,

       hl.address3       address3,

       hl.address4       address4

  FROM hz_party_sites hps, hz_locations hl

  WHERE hps.location_id = hl.location_id

  ORDER BY hps.creation_date

-- 供应商联系人信息

SELECT hr.subject_id subject_id,

       hr.object_id object_id,

       hr.party_id party_id,

       hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||

       hp.person_first_name contact_person,

       hcpp.phone_area_code phone_area_code,

       hcpp.phone_number phone_number,

       hcpp.phone_extension phone_extension,

       hcpf.phone_area_code fax_phone_area_code,

       hcpf.phone_number fax_phone_number,

       hcpe.email_address email_address

  FROM hz_relationships  hr,

       hz_contact_points hcpp,

       hz_contact_points hcpf,

       hz_contact_points hcpe,

       hz_parties        hp

  WHERE hr.object_id = hp.party_id

   AND hcpp.owner_table_id(+) = hr.party_id

   AND hcpf.owner_table_id(+) = hr.party_id

   AND hcpe.owner_table_id(+) = hr.party_id

   AND hr.object_type = 'PERSON'

   AND hr.relationship_code(+) = 'CONTACT'

   AND hcpp.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpf.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpe.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpp.contact_point_type(+) = 'PHONE'

   AND hcpp.phone_line_type(+) = 'GEN'

   AND hcpf.contact_point_type(+) = 'PHONE'

   AND hcpf.phone_line_type(+) = 'FAX'

   AND hcpe.contact_point_type(+) = 'EMAIL'

   AND hcpe.phone_line_type IS NULL

  ORDER BY hr.creation_date;

-- 供应商地址主信息

SELECT assa.vendor_site_id       vendor_site_id,

       assa.vendor_id            vendor_id,

       assa.vendor_site_code     vendor_code,

       assa.vendor_site_code     address_short_name,

       assa.address_line1        address_line1,

       assa.address_line2        address_line2,

       assa.address_line3        address_line3,

       assa.address_line4        address_line4,

       assa.org_id               org_id,

       assa.country              country,

       assa.province             province,

       assa.city                 city,

       assa.county               county,

       assa.zip                  zip,

       assa.pay_site_flag        pay_site_flag,

       assa.purchasing_site_flag purchasing_site_flag,

       assa.inactive_date        inactive_date,

       assa.creation_date        creation_date,

       assa.created_by           created_by,

       assa.last_update_date     last_update_date,

       assa.last_updated_by      last_updated_by,

       assa.last_update_login    last_update_login

  FROM ap_suppliers ass, ap_supplier_sites_all assa

  WHERE assa.vendor_id = ass.vendor_id;

-- 供应商地址联系人信息： phone 、 fax 和 Email

SELECT hcpp.phone_area_code phone_area_code,

       hcpp.phone_number    phone_number,

       hcpp.phone_extension phone_extension,

       hcpf.phone_area_code fax_phone_area_code,

       hcpf.phone_number    fax_phone_number,

       hcpe.email_address   email_address

  FROM ap_supplier_sites_all assa,

       hz_contact_points     hcpp,

       hz_contact_points     hcpf,

       hz_contact_points     hcpe,

       hz_party_sites        hps

  WHERE assa.party_site_id = hps.party_site_id

   AND hcpp.owner_table_id(+) = assa.party_site_id

   AND hcpf.owner_table_id(+) = assa.party_site_id

   AND hcpe.owner_table_id(+) = assa.party_site_id

   AND hcpp.owner_table_name(+) = 'HZ_PARTY_SITES'

   AND hcpf.owner_table_name(+) = 'HZ_PARTY_SITES'

   AND hcpe.owner_table_name(+) = 'HZ_PARTY_SITES'

   AND hcpp.contact_point_type(+) = 'PHONE'

   AND hcpp.phone_line_type(+) = 'GEN'

   AND hcpf.contact_point_type(+) = 'PHONE'

   AND hcpf.phone_line_type(+) = 'FAX'

   AND hcpe.contact_point_type(+) = 'EMAIL'

   AND hcpe.phone_line_type IS NULL ;

-- 供应商地址收件人信息

SELECT assa.party_site_id

FROM ap_supplier_sites_all assa

-- 根据 party_site_id 得到供应商地址的收件人名称   

SELECT hps.addressee FROM hz_party_sites hps;

-- 供应商银行帐户分配层次关系

SELECT * FROM iby_pmt_instr_uses_all;

-- 供应商银行帐户分配层次关系明细 ( 不包括供应商层的分配信息 ):

SELECT * FROM iby_external_payees_all;

 

4.  客户 CUSTOMER

--SQL 查询

-- 客户账户表    以许继 1063 电网客户为例 -->>PARTY_ID = 21302

SELECT * FROM hz_cust_accounts AA WHERE AA.CUST_ACCOUNT_ID = 1063 ;

 

-- 客户名称及地址全局信息表 -->> PARTY_NUMBER = 19316

SELECT * FROM hz_parties AA WHERE AA.PARTY_ID = 21302 ;

 

-- 客户地点账户主文件

SELECT * FROM hz_cust_acct_sites_all WHERE CUST_ACCOUNT_ID = 1063 ;

 

-- 客户地点 ( 关联 hz_cust_acct_sites_all)

SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID = 21302 ;

 

-- 地点地址名称 ( 关联 hz_cust_acct_sites_all)

SELECT AA.ADDRESS1, AA.ADDRESS_KEY

  FROM HZ_LOCATIONS AA, HZ_PARTY_SITES BB

  WHERE AA.LOCATION_ID = BB.LOCATION_ID

   AND BB.PARTY_ID = 21302 ;

 

-- 客户地点业务目的 ( 关联 hz_cust_acct_sites_all 用 CUST_ACCT_SITE_ID)

SELECT * FROM HZ_CUST_SITE_USES_ALL;

 

-- 客户地点详细信息表，以供应处 OU 的身份 ORG_ID = 119

SELECT AA.PARTY_SITE_ID            客户组织地点 ID,

       AA.PARTY_ID                 客户组织 ID,

       AA.LOCATION_ID              地点 ID,

       AA.PARTY_SITE_NUMBER        地点编号 ,

       AA.IDENTIFYING_ADDRESS_FLAG 地址标示 ,

       AA.STATUS                   有效否 ,

       AA.PARTY_SITE_NAME,

       BB.ORG_ID                   业务实体 ,

       BB.bill_to_flag             收单标示 ,

       BB.ship_to_flag              收货标示 ,

       CC.ADDRESS1                 地点名称 ,

       DD.SITE_USE_ID,

       DD.SITE_USE_CODE,

       DD.PRIMARY_FLAG,

       DD.STATUS,

       DD.LOCATION                 业务目的 ,

DD.BILL_TO_SITE_USE_ID      收单地 ID,

       DD.TAX_CODE

  FROM hz_party_sites         AA,

       hz_cust_acct_sites_all BB,

       hz_locations           CC,

       HZ_CUST_SITE_USES_ALL  DD

  WHERE AA.PARTY_SITE_ID = BB.PARTY_SITE_ID

   AND BB.CUST_ACCOUNT_ID = 1063

   AND BB.ORG_ID = 119

   AND AA.STATUS = 'A'

   AND AA.LOCATION_ID = CC.LOCATION_ID

   AND BB.CUST_ACCT_SITE_ID(+) = DD.CUST_ACCT_SITE_ID

   AND DD.STATUS <> 'I' ;

 

--************* 综合查询 ************--

-- 客户主数据

SELECT hca.cust_account_id     customer_id,

       hp.party_number         customer_number,

       hp.party_name           customer_name,

       hp.party_name           customer_short_name,

       hca.customer_type       customer_type,

       alt.meaning             customer_type_meaning,

       hca.customer_class_code customer_class,

       alc.meaning             customer_class_meaning,

       hp.tax_reference        tax_registered_name,

       rt.name                 term_name,

       hca.creation_date       creation_date,

       hca.created_by          created_by,

       hca.last_update_date     last_update_date,

       hca.last_updated_by     last_updated_by,

       hca.last_update_login   last_update_login

  FROM hz_parties           hp,

       hz_cust_accounts     hca,

       ar_lookups           alt,

       ar_lookups           alc,

       hz_customer_profiles hcp,

       ra_terms             rt

  WHERE hp.party_id = hca.party_id

   AND hca.customer_type = alt.lookup_code(+)

   AND alt.lookup_type = 'CUSTOMER_TYPE'

   AND hca.customer_class_code = alc.lookup_code(+)

   AND alc.lookup_type(+) = 'CUSTOMER CLASS'

   AND hca.cust_account_id = hcp.cust_account_id(+)

   AND hcp.standard_terms = rt.term_id(+)

 

-- 客户收款方法 SQL

SELECT arm.name receipt_method_name

FROM hz_cust_accounts        hca,

     ra_cust_receipt_methods rcrm,

     ar_receipt_methods      arm

WHERE hca.cust_account_id = rcrm.customer_id

      AND rcrm.receipt_method_id = arm.receipt_method_id

ORDER BY rcrm.creation_date;

-- 客户账户层银行账户信息 SQL

SELECT hca.cust_account_id   cust_account_id,

       hp.party_id           party_id,

       bank.party_id         bank_id,

       bank.party_name       bank_name,

       branch.party_id       branch_id,

       branch.party_name     bank_branch_name,

       ieba.bank_account_num bank_account_num

FROM hz_cust_accounts      hca,

       hz_parties            hp,

       iby_account_owners    iao,

       iby_ext_bank_accounts ieba,

       hz_parties            bank,

       hz_parties            branch

WHERE hca.party_id = hp.party_id

   AND hp.party_id = iao.account_owner_party_id(+)

   AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)

   AND ieba.bank_id = bank.party_id(+)

   AND ieba.branch_id = branch.party_id(+)

  ORDER BY ieba.creation_date;

-- 客户开户行地址信息 SQL

SELECT hl.country || '-' || hl.province || '-' || hl.city || '-' ||

       hl.address1 || '-' || hl.address2 || '-' || hl.address3 || '-' ||

       hl.address4 bank_address

FROM hz_party_sites hps, hz_locations hl

WHERE hps.location_id = hl.location_id

ORDER BY hps.creation_date;

-- 客户账户层联系人信息：联系人、电话、手机和 Email SQL

SELECT hr.party_id party_id,

       hcar.cust_account_id cust_account_id,

       hcar.cust_acct_site_id cust_acct_site_id,

       hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||

       hp.person_first_name contact_person,

       hcpp.phone_area_code phone_area_code,

       hcpp.phone_number phone_number,

       hcpp.phone_extension phone_extension,

       hcpm.phone_area_code mobile_phone_area_code,

       hcpm.phone_number mobile_phone_number,

       hcpe.email_address email_address

  FROM hz_relationships      hr,

       hz_cust_account_roles hcar,

       hz_org_contacts       hoc,

       hz_contact_points     hcpp,

       hz_contact_points     hcpm,

       hz_contact_points     hcpe,

       hz_parties            hp,

       hz_cust_accounts      hca

  WHERE hr.object_id = hp.party_id

   AND hr.party_id = hcar.party_id

   AND hr.relationship_id = hoc.party_relationship_id(+)

   AND hcpp.owner_table_id(+) = hr.party_id

   AND hcpm.owner_table_id(+) = hr.party_id

   AND hcpe.owner_table_id(+) = hr.party_id

   AND hr.object_type = 'PERSON'

   AND hr.relationship_code(+) = 'CONTACT'

   AND hcpp.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpm.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpe.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpp.contact_point_type(+) = 'PHONE'

   AND hcpp.phone_line_type(+) = 'GEN'

   AND hcpm.contact_point_type(+) = 'PHONE'

   AND hcpm.phone_line_type(+) = 'MOBILE'

   AND hcpe.contact_point_type(+) = 'EMAIL'

   AND hcpe.phone_line_type IS NULL

   AND hr.subject_id = hca.party_id

   AND hcar.cust_acct_site_id IS NULL

  ORDER BY hr.creation_date;

-- 客户地址

SELECT hcasa.cust_acct_site_id customer_site_id,

       hcasa.cust_account_id   customer_id,

       hps.party_site_number   customer_site_code,

       hps.party_site_name     customer_site_name,

       hl.address1             address_line1,

       hl.address2             address_line2,

       hl.address3             address_line3,

       hl.address4             address_line4,

       hcasa.org_id             org_id,

       hl.country              country,

       hl.province             province,

       hl.city                 city,

       hl.county               county,

       hl.postal_code          zip,

       hcasa.bill_to_flag      bill_to_flag,

       hcasa.ship_to_flag      ship_to_flag,

       hca.creation_date       creation_date,

       hca.created_by          created_by,

       hca.last_update_date    last_update_date,

       hca.last_updated_by     last_updated_by,

       hca.last_update_login   last_update_login

  FROM hz_cust_accounts       hca,

       hz_cust_acct_sites_all hcasa,

       hz_party_sites         hps,

       hz_locations           hl

  WHERE hca.cust_account_id = hcasa.cust_account_id

   AND hcasa.party_site_id = hps.party_site_id

   AND hps.location_id = hl.location_id;

 

-- 客户账户层地址 contact person 信息 :phone,mobile,email

SELECT hr.party_id party_id,

       hcar.cust_account_id cust_account_id,

       hcar.cust_acct_site_id cust_acct_site_id,

       hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||

       hp.person_first_name contact_person,

       hcpp.phone_area_code phone_area_code,

       hcpp.phone_number phone_number,

       hcpp.phone_extension phone_extension,

       hcpm.phone_area_code mobile_phone_area_code,

       hcpm.phone_number mobile_phone_number,

       hcpe.email_address email_address

  FROM hz_relationships      hr,

       hz_cust_account_roles hcar,

       hz_org_contacts       hoc,

       hz_contact_points     hcpp,

       hz_contact_points     hcpm,

       hz_contact_points     hcpe,

       hz_parties            hp,

       hz_cust_accounts      hca

  WHERE hr.object_id = hp.party_id

   AND hr.party_id = hcar.party_id

   AND hr.relationship_id = hoc.party_relationship_id(+)

   AND hcpp.owner_table_id(+) = hr.party_id

   AND hcpm.owner_table_id(+) = hr.party_id

   AND hcpe.owner_table_id(+) = hr.party_id

   AND hr.object_type = 'PERSON'

   AND hr.relationship_code(+) = 'CONTACT'

   AND hcpp.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpm.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpe.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpp.contact_point_type(+) = 'PHONE'

   AND hcpp.phone_line_type(+) = 'GEN'

   AND hcpm.contact_point_type(+) = 'PHONE'

   AND hcpm.phone_line_type(+) = 'MOBILE'

   AND hcpe.contact_point_type(+) = 'EMAIL'

   AND hcpe.phone_line_type IS NULL

   AND hr.subject_id = hca.party_id

   AND hca.cust_account_id = hcar.cust_account_id

  ORDER BY hr.creation_date;

 

-- 客户账户地点地址

SELECT hp.party_id,

       hca.cust_account_id,

        hcasa.cust_acct_site_id,

       hcasa.bill_to_flag,

       hcasa.ship_to_flag,

       hcsua.site_use_id,

       hcasa.party_site_id,

       hcsua.site_use_code,

       hcsua.primary_flag,

       hcsua.location,

       hcsua.org_id

  FROM hz_parties             hp,

       hz_cust_accounts       hca,

       hz_party_sites         hps,

       hz_cust_acct_sites_all hcasa,

       hz_cust_site_uses_all  hcsua

  WHERE hp.party_id = hca.party_id

   AND hca.cust_account_id = hcasa.cust_account_id

   AND hcasa.party_site_id = hps.party_site_id

   AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

-- 客户主配置文件

SELECT * FROM hz_cust_profile_classes;

SELECT * FROM hz_customer_profiles;

SELECT * FROM hz_cust_prof_class_amts;

SELECT * FROM hz_cust_profile_amts;

 

5.  订单 OE

--

select * from oe_order_headers_all 销售头

select * from oe_order_lines_all 销售行

select * from wsh_new_deliveries 发送

select * from wsh_delivery_details

select * from wsh_delivery_assignments

-- 综合查询 1- 未结销售订单

SELECT H.ORDER_NUMBER      销售订单 ,

       h.cust_po_number    客户 PO,

       cust.account_number 客户编码 ,

       hp.party_name       客户名称 ,

       ship_use.location   收货地 ,

       bill_use.location   收单地 ,

       h.ordered_date       订单日期 ,

       H.ATTRIBUTE1         合同号 ,

       h.attribute2         屏号 ,

       h.attribute3         来源编码 ,

       l.line_number        行号 ,

       l.ordered_item       物料 ,

       msi.description      物料说明 ,

       l.order_quantity_uom 订购单位 ,

       l.ordered_quantity   订购数量 ,

       l.cancelled_quantity 取消数量 ,

       l.shipped_quantity   发运数量 ,

       l.schedule_ship_date 计划发运日期 ,

       l.booked_flag        登记标记 ,

       ol.MEANING           工作流状态 ,

       l.cancelled_flag     取消标记

  FROM OE_ORDER_HEADERS_ALL  H,

       OE_ORDER_LINES_ALL    L,

       HZ_CUST_ACCOUNTS      CUST,

       hz_parties            hp,

       hz_cust_site_uses_all ship_use,

       hz_cust_site_uses_all bill_use,

       mtl_system_items_b    msi,

       oe_lookups            ol

  WHERE 1 = 1

   AND H.HEADER_ID = L.HEADER_ID

   AND H.SOLD_TO_ORG_ID = CUST.CUST_ACCOUNT_ID

   and cust.party_id = hp.party_id

   and h.ship_to_org_id = ship_use.site_use_id

   and h.invoice_to_org_id = bill_use.site_use_id

   and l.flow_status_code not in ( 'CLOSED' , 'CANCELLED' )

   and l.inventory_item_id = msi.inventory_item_id

   and msi.organization_id = 141

   and l.flow_status_code = ol.LOOKUP_CODE

   and ol.LOOKUP_TYPE = 'LINE_FLOW_STATUS'

   AND CUST.ACCOUNT_NUMBER IN ( '91010072' , '91010067' , '91010036' )

  order by party_name ，收货地，销售订单 ;

6.  采购申请 PR

-- 申请单头 （以电网组织 ORG_ID=112 内部申请 =14140002781 为例

SELECT PRH.REQUISITION_HEADER_ID  申请单头 ID,

       PRH.PREPARER_ID,

       PRH.Org_Id                 OU_ID,

       PRH.SEGMENT1               申请单编号 ,

       PRH.Creation_Date          创建日期 ,

       PRH.Created_By             编制人 ID,

       FU.USER_NAME               用户名称 ,

       PP.FULL_NAME               用户姓名 ,

       PRH.Approved_Date          批准日期 ,

       PRH.Description            说明 ,

       PRH.Authorization_Status   状态 ,

       PRH.Type_Lookup_Code       类型 ,

       PRH.Transferred_To_Oe_Flag 传递标示

  FROM PO_REQUISITION_HEADERS_ALL PRH, FND_USER FU, per_people_f PP

  WHERE PRH.CREATED_BY = FU.USER_ID

   AND FU.EMPLOYEE_ID = PP.PERSON_ID

   AND PRH.ORG_ID = 112

   AND PRH.SEGMENT1 = '14140002781' ;

-->> 内部申请 =14140002781  申请单头 ID = 3379   

-- 申请单行明细

SELECT PRL.REQUISITION_HEADER_ID       申请单 ID,

       PRL.REQUISITION_LINE_ID         行 ID,

       PRL.LINE_NUM                    行号 ,

       PRL.CATEGORY_ID                 分类 ID,

       PRL.ITEM_ID                      物料 ID,

       ITEM.SEGMENT1                   物料编码 ,

       PRL.ITEM_DESCRIPTION            物料说明 ,

       PRL.Quantity                    需求数 ,

       PRL.Quantity_Delivered          送货数 ,

       PRL.Quantity_Cancelled          取消数 ,

       PRL.Unit_Meas_Lookup_Code       单位 ,

       PRL.Unit_Price                  参考价 ,

       PRL.Need_By_Date                需求日期 ,

       PRL.Source_Type_Code            来源类型 ,

       PRL.Org_Id                      OU_ID,

       PRL.Source_Organization_Id      对方组织 ID,

       PRL.Destination_Organization_Id 本方组织 ID

  from PO_REQUISITION_LINES_ALL PRL,MTL_SYSTEM_ITEMS ITEM

  WHERE PRL.ORG_ID = 112

   AND PRL.ITEM_ID = ITEM.INVENTORY_ITEM_ID

   AND PRL.Destination_Organization_Id = ITEM.ORGANIZATION_ID

   AND PRL.REQUISITION_HEADER_ID = 3379 ;

-- 申请单头 ( 加对方订单编号 )

SELECT PRH.REQUISITION_HEADER_ID  申请单头 ID,

       PRH.PREPARER_ID,

       PRH.Org_Id                 OU_ID,

       PRH.SEGMENT1               申请单编号 ,

       PRH.Creation_Date          创建日期 ,

       PRH.Created_By              编制人 ID,

       FU.USER_NAME               用户名称 ,

       PP.FULL_NAME               用户姓名 ,

       PRH.Approved_Date          批准日期 ,

       PRH.Description            说明 ,

       PRH.Authorization_Status   状态 ,

       PRH.Type_Lookup_Code       类型 ,

       PRH.Transferred_To_Oe_Flag 传递标示 ,

       OEH.ORDER_NUMBER 对方 CO 编号

  FROM PO_REQUISITION_HEADERS_ALL PRH, FND_USER FU, per_people_f PP,OE_ORDER_HEADERS_ALL OEH

  WHERE PRH.CREATED_BY = FU.USER_ID

   AND FU.EMPLOYEE_ID = PP.PERSON_ID

   AND PRH.REQUISITION_HEADER_ID = OEH.SOURCE_DOCUMENT_ID(+)

   AND PRH.ORG_ID = 112

   AND PRH.SEGMENT1 = '14140002781' ;

--( 销售订单记录有对方 OU_ID, 申请单关键字 SOURCE_DOCUMENT_ID 申请单号 SOURCE_DOCEMENT_REF)

 

******************* 综合查询类 *******************

-- 申请单头综合查询 （进限制只能查询 -- 电网组织 ORG_ID=112)

SELECT PRH.REQUISITION_HEADER_ID       申请单头 ID,

       PRH.Org_Id                      组织 ID,

       PRH.SEGMENT1                    申请单编号 ,

       PRH.Creation_Date               创建日期 ,

       PRH.Created_By                  编制人 ID,

       FU.USER_NAME                    用户名称 ,

       PP.FULL_NAME                    用户姓名 ,

       PRH.Approved_Date               批准日期 ,

       PRH.Description                 说明 ,

       PRH.Authorization_Status        状态 ,

       PRH.Type_Lookup_Code            类型 ,

       PRH.Transferred_To_Oe_Flag      传递标示 ,

       PRL.REQUISITION_LINE_ID         行 ID,

       PRL.LINE_NUM                    行号 ,

       PRL.CATEGORY_ID                 分类 ID,

       PRL.ITEM_ID                     物料 ID,

       ITEM.SEGMENT1                   物料编码 ,

       PRL.ITEM_DESCRIPTION            物料说明 ,

       PRL.Quantity                    需求数 ,

       PRL.Quantity_Delivered          送货数 ,

       PRL.Quantity_Cancelled          取消数 ,

       PRL.Unit_Meas_Lookup_Code       单位 ,

       PRL.Unit_Price                   参考价 ,

       PRL.Need_By_Date                需求日期 ,

       PRL.Source_Type_Code            来源类型 ,

       PRL.Source_Organization_Id      对方组织 ID,

       PRL.Destination_Organization_Id 本方组织 ID

  FROM PO_REQUISITION_HEADERS_ALL PRH,

       FND_USER                    FU,

       per_people_f               PP,

       PO_REQUISITION_LINES_ALL   PRL,

       MTL_SYSTEM_ITEMS           ITEM

  WHERE PRH.CREATED_BY = FU.USER_ID

   AND FU.EMPLOYEE_ID = PP.PERSON_ID

   AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

   AND PRH.Org_Id = PRL.ORG_ID

   AND PRL.ITEM_ID = ITEM.INVENTORY_ITEM_ID

   AND PRL.Destination_Organization_Id = ITEM.ORGANIZATION_ID

   AND PRH.ORG_ID = 112 ;

 

-- 若需创建视图只需在 SELECT 语句前加上

  CREATE OR REPLACE VIEW CUX_INV_PR112 AS

7.  采购订单 PO

-- 采购单头信息   TYPE_LOOKUP_CODE='STANDARD' （以供应处 OU  ORG_ID=119  采购单 ='' 为例）

-- 类型说明 TYPE_LOOKUP_CODE='STANDARD' 为采购单    TYPE_LOOKUP_CODE='BLANKET' 为采购协议

SELECT POH.ORG_ID               OU_ID,

       POH.PO_HEADER_ID         采购单头 ID,

       POH.TYPE_LOOKUP_CODE     类型 ,

        POH.AUTHORIZATION_STATUS 状态 ,

       POH.VENDOR_ID            供应商 ID,

       VENDOR.VENDOR_NAME       供应商名 ,

       POH.VENDOR_SITE_ID       供应商地址 ID,

       POH.VENDOR_CONTACT_ID    供应商联系人 ID,

       POH.SHIP_TO_LOCATION_ID  本方收货地 ID,

       POH.BILL_TO_LOCATION_ID  本方收单地 ID,

       POH.CREATION_DATE        创建日期 ,

       POH.APPROVED_FLAG        审批 YN,

       POH.APPROVED_DATE        审批日期 ,

       POH.COMMENTS             采购单说明 ,

       POH.TERMS_ID             条款 ID,

       POH.AGENT_ID             采购员 ID,

       AGT_PP.LAST_NAME         采购员 ,                 

       POH.CREATED_BY           创建者 ID,

       FU.USER_NAME             创建用户 ,

       PP.FULL_NAME             用户姓名

  FROM PO_HEADERS_ALL POH, FND_USER FU, per_people_f PP,PER_ALL_PEOPLE_F AGT_PP,ap_suppliers VENDOR

  WHERE POH.CREATED_BY = FU.USER_ID

   AND FU.EMPLOYEE_ID = PP.PERSON_ID

   AND POH.AGENT_ID = AGT_PP.PERSON_ID

   AND POH.VENDOR_ID=VENDOR.VENDOR_ID

   AND POH.ORG_ID = 119

   AND POH.TYPE_LOOKUP_CODE = 'STANDARD'

   AND POH.SEGMENT1 = '14730005436' ;

  /*

  FND_USER FU, per_people_f PP  用户相关表

  po_agents_name_v 采购员视图 ---->   PO_AGENTS.AGENT_ID = PER_ALL_PEOPLE_F.PERSON_ID 采购员相关表

  ap_suppliers 供应商主表

  */

 

-->> POH.SEGMENT1 = '14730005436'  PO_HEADER_ID = 10068

-- 采购单行信息

SELECT POL.ORG_ID                OU_ID,

       POL.PO_HEADER_ID          采购单头 ID,

       POL.PO_LINE_ID            行 ID,

       POL.LINE_NUM              行号 ,

       POL.ITEM_ID               物料 ID,

       ITEM.SEGMENT1             物料编码 ,

       POL.ITEM_DESCRIPTION      物料说明 ,

       POL.UNIT_MEAS_LOOKUP_CODE 单位 ,

       POL.UNIT_PRICE            单价 ,

       PO_LCT.QUANTITY           订购数 ,

       PO_LCT.QUANTITY_RECEIVED  验收数 ,

       PO_LCT.QUANTITY_ACCEPTED  接收数 ,

       PO_LCT.QUANTITY_REJECTED  拒绝数 ,

       PO_LCT.QUANTITY_CANCELLED 取消数 ,

       PO_LCT.QUANTITY_BILLED    到票数 ,

       PO_LCT.PROMISED_DATE      承诺日期 ,

       PO_LCT.NEED_BY_DATE       需求日期

  FROM PO_LINES_ALL          POL,

       Po_Line_Locations_all PO_LCT,

       MTL_SYSTEM_ITEMS      ITEM

  WHERE POL.ORG_ID = PO_LCT.ORG_ID

   AND POL.PO_LINE_ID = PO_LCT.PO_LINE_ID

   AND POL.ITEM_ID = ITEM.INVENTORY_ITEM_ID

   AND ITEM.ORGANIZATION_ID = 142

   AND POL.Org_Id = 119

   AND POL.PO_HEADER_ID = 10068 ;

-- 说明： Po_Line_Locations_all 系 “ 发运表 ”

 

-- 综合查询 1 ，所分配给供应处组织的物料，存在采购协议，但缺失采购员或缺失仓库；

select MSIF.Segment1         物料编码 ,

          MSIF.Description      物料描述 ,

          MSIF.LONG_DESCRIPTION 物料详细描述 ,

       --MSIF.primary_unit_of_measure 计量单位 ,

       PRF.LAST_NAME          采购员 ,

       MISD.subinventory_code 默认接收库存 ,

       PLA.unit_price         未税价 ,

        round (PLA.unit_price * ( 1 + ZRB.percentage_rate / 100 ), 2 )        含税价 ,

       PV.VENDOR_NAME         供应商名称

  from apps.PO_HEADERS_ALL        PHA,

       apps.PO_LINES_ALL          PLA,

       apps.MTL_SYSTEM_ITEMS_FVL  MSIF,

       apps.MTL_ITEM_SUB_DEFAULTS MISD,

       apps.PER_PEOPLE_F          PRF,

       apps.PO_VENDORS            PV,

       apps.PO_VENDOR_SITES_ALL   PVSA,

       apps.ZX_RATES_B            ZRB

  where PHA.Type_Lookup_Code = 'BLANKET' and PHA.Org_Id = 119 and

       PHA.PO_HEADER_ID = PLA.Po_Header_Id and

       PHA.Global_Agreement_Flag = 'Y' and PHA.Approved_Flag in ( 'Y' , 'R' ) and

       NVL (PHA.end_Date, sysdate ) >= sysdate and

       NVL (PLA.Expiration_Date, sysdate ) >= sysdate and

       PLA.Cancel_Flag = 'N' and PLA.Item_Id = MSIF.INVENTORY_ITEM_ID and

       MSIF.ORGANIZATION_ID = 142 and

       MSIF.INVENTORY_ITEM_ID = MISD.INVENTORY_ITEM_ID(+) and

       MISD.ORGANIZATION_ID(+) = 142 and MISD.default_type(+) = 2 and

       MSIF.BUYER_ID = PRF.PERSON_ID(+) and

       PRF.EFFECTIVE_END_DATE(+) = to_date( '4712-12-31' , 'YYYY-MM-DD' ) and

       PHA.VENDOR_ID = PV.VENDOR_ID and

       PHA.Vendor_Site_Id = PVSA.VENDOR_SITE_ID and

       PVSA.VAT_CODE = ZRB.tax_rate_code and

       (MISD.subinventory_code is null or PRF.LAST_NAME is null )

 

-- 采购其他相关表

select * from po_distributions_all  分配

select * from po_releases_all

select * from rcv_shipment_headers 采购接收头

select * from rcv_shipment_lines 采购接收行

select * from rcv_transactions 接收事务处理

select * from po_agents

select * from po_vendors

select * from po_vendor_sites_all

 

8.  库存 INV

-- 物料主表

select MSI.ORGANIZATION_ID            组织 ID,

       MSI.INVENTORY_ITEM_ID          物料 ID,

       MSI.SEGMENT1                   物料编码 ,

       MSI.DESCRIPTION                物料说明 ,

       MSI.ITEM_TYPE                  项目类型 ,

       MSI.PLANNING_MAKE_BUY_CODE     制造或购买 ,

       MSI.PRIMARY_UNIT_OF_MEASURE    基本度量单位 ,

       MSI.BOM_ENABLED_FLAG           BOM 标志 ,

       MSI.INVENTORY_ASSET_FLAG       库存资产否 ,

       MSI.BUYER_ID                    采购员 ID,

       MSI.PURCHASING_ENABLED_FLAG    可采购否 ,

       MSI.PURCHASING_ITEM_FLAG       采购项目 ,

       MSI.UNIT_OF_ISSUE              单位 ,

       MSI.INVENTORY_ITEM_FLAG        是否为库存 ,

       MSI.LOT_CONTROL_CODE           是否批量 ,

       MSI.RESERVABLE_TYPE            是否要预留 ,

       MSI.STOCK_ENABLED_FLAG         能否库存 ,

       MSI.FIXED_DAYS_SUPPLY          固定提前期 ,

       MSI.FIXED_LOT_MULTIPLIER       固定批量大小 ,

       MSI.INVENTORY_PLANNING_CODE    库存计划方法 ,

       MSI.MAXIMUM_ORDER_QUANTITY     最大定单数 ,

       MSI.MINIMUM_ORDER_QUANTITY     最小定单数 ,

       MSI.FULL_LEAD_TIME             固定提前期 ,

       MSI.PLANNER_CODE               计划员码 ,

       MISD.SUBINVENTORY_CODE         接收子仓库 ,

       MSI.SOURCE_SUBINVENTORY        来源子仓库 ,

       MSI.WIP_SUPPLY_SUBINVENTORY    供应子仓库 ,

       MSI.ATTRIBUTE12                老编码 ,

       MSI.INVENTORY_ITEM_STATUS_CODE 物料状态 ,

       MSS.SAFETY_STOCK_QUANTITY      安全库存量   

  from mtl_system_items MSI, MTL_ITEM_SUB_DEFAULTS MISD,mtl_safety_stocks MSS

  where MSI.ORGANIZATION_ID = MISD.ORGANIZATION_ID(+)

   and MSI.INVENTORY_ITEM_ID = MISD.INVENTORY_ITEM_ID(+)

   and MSI.ORGANIZATION_ID = MSS.ORGANIZATION_ID(+)

   and MSI.INVENTORY_ITEM_ID = MSS.INVENTORY_ITEM_ID(+)

   and MSI.ORGANIZATION_ID = 1155

   and MSI.SEGMENT1 = '18020200012'

-- 物料库存数量

SELECT MOQ.ORGANIZATION_ID,

       MOQ.INVENTORY_ITEM_ID,

       MOQ.SUBINVENTORY_CODE,

       SUM (MOQ.TRANSACTION_QUANTITY) QTY

  FROM mtl_onhand_quantities MOQ

  WHERE MOQ.INVENTORY_ITEM_ID = 12781

   AND MOQ.ORGANIZATION_ID = 1155

  GROUP BY MOQ.ORGANIZATION_ID, MOQ.INVENTORY_ITEM_ID, MOQ.SUBINVENTORY_CODE;

-- 移动平均成本

SELECT CST.INVENTORY_ITEM_ID       ITEM_ID,

       CST.ORGANIZATION_ID         ORG_ID,

       CST.COST_TYPE_ID            成本类型 ,

       CST.ITEM_COST               单位成本 ,

       CST.MATERIAL_COST           材料成本 ,

       CST.MATERIAL_OVERHEAD_COST  间接费 ,

       CST.Resource_Cost           人工费 ,

       CST.OUTSIDE_PROCESSING_COST 外协费 ,

       CST.OVERHEAD_COST           制造费

  FROM CST_ITEM_COSTS CST

  WHERE CST.COST_TYPE_ID = 2

    AND CST.INVENTORY_ITEM_ID = 12781

   AND CST.ORGANIZATION_ID = 1155 ;

  

-- 综合查询 - 库存数量及成本

SELECT MSI.ORGANIZATION_ID        组织 ID,

       MSI.INVENTORY_ITEM_ID      物料 ID,

       MSI.SEGMENT1               物料编码 ,

       MSI.DESCRIPTION            物料说明 ,

       MSI.PLANNING_MAKE_BUY_CODE M1P2,

       MOQV.SUBINVENTORY_CODE     子库存 ,

       MOQV.QTY                   当前库存量 ,

       CST.ITEM_COST               单位成本 ,

       CST.MATERIAL_COST           材料成本 ,

       CST.MATERIAL_OVERHEAD_COST  间接费 ,

       CST.Resource_Cost           人工费 ,

       CST.OUTSIDE_PROCESSING_COST 外协费 ,

       CST.OVERHEAD_COST           制造费

  FROM MTL_SYSTEM_ITEMS MSI,

       CST_ITEM_COSTS CST,

       ( SELECT MOQ.ORGANIZATION_ID,

               MOQ.INVENTORY_ITEM_ID,

               MOQ.SUBINVENTORY_CODE,

               SUM (MOQ.TRANSACTION_QUANTITY) QTY

          FROM mtl_onhand_quantities MOQ

         WHERE MOQ.ORGANIZATION_ID = 1155

         GROUP BY MOQ.ORGANIZATION_ID,

                  MOQ.INVENTORY_ITEM_ID,

                   MOQ.SUBINVENTORY_CODE) MOQV

  WHERE MSI.ORGANIZATION_ID = CST.ORGANIZATION_ID(+)

   AND MSI.INVENTORY_ITEM_ID = CST.INVENTORY_ITEM_ID(+)

   AND MSI.ORGANIZATION_ID = MOQV.ORGANIZATION_ID(+)

   AND MSI.INVENTORY_ITEM_ID = MOQV.INVENTORY_ITEM_ID(+)

   AND CST.COST_TYPE_ID = 2

   AND MSI.ORGANIZATION_ID = 1155

   AND MSI.SEGMENT1 = '18020200012'

-- 子库存列表

SELECT * FROM mtl_secondary_inventories;

-- 货位列表  

SELECT ORGANIZATION_ID       组织代码 ,

       INVENTORY_LOCATION_ID 货位内码 ,

       SUBINVENTORY_CODE      子库名称 ,

       SEGMENT1              货位编码

  FROM mtl_item_locations;

-- 计划员表   

SELECT PLANNER_CODE    计划员代码 ,

       ORGANIZATION_ID 组织代码 ,

       DESCRIPTION      计划员描述 ,

       MP.EMPLOYEE_ID  员工 ID,

       DISABLE_DATE    失效日期

  FROM mtl_planners MP;

-- 科目设置等参数

select * from MTL_PARAMETERS MP

 

9.  物料清单 BOM

--BOM 主表 bom_bill_of_materials

select AA.BILL_SEQUENCE_ID 清单序号 ,

          AA.ASSEMBLY_ITEM_ID 装配件内码 ,

          AA.ORGANIZATION_ID  组织代码 ,

         BB.SEGMENT1         物料编码 ,

          BB.DESCRIPTION      物料说明 ,

          AA.ASSEMBLY_TYPE    装配类别

  from bom_bill_of_materials AA ， mtl_system_items BB

  where AA.ASSEMBLY_ITEM_ID = BB.INVENTORY_ITEM_ID

   and AA.ORGANIZATION_ID = BB.ORGANIZATION_ID;

--BOM 明细表 bom_inventory_components

select BILL_SEQUENCE_ID      清单序号 ,

       COMPONENT_SEQUENCE_ID 构件序号 ,

       ITEM_NUM              项目序列 ,

       OPERATION_SEQ_NUM     操作序列号 ,

       COMPONENT_ITEM_ID     子物料内码 ,

       COMPONENT_QUANTITY    构件数量 ,

       DISABLE_DATE          失效日期 ,

       supply_subinventory   供应子库存 ,

       BOM_ITEM_TYPE

  from bom_inventory_components;

 

--BOM 明细综合查询 ( 组织 限定供应处 142 装配件 = '5XJ061988')

SELECT VBOM.BID                   清单序号 ,

       VBOM.F_ITEMID              装配件内码 ,

       BB.SEGMENT1                物料编码 ,

       BB.DESCRIPTION             物料说明 ,

       VBOM.OGT_ID                组织内码 ,

       VBOM.CID                   操作 ID,

       VBOM.ITEM_NUM              物料序号 ,

       VBOM.OPID                  工序 ,

       VBOM.C_ITEMID              子物料内码 ,

       CC.SEGMENT1                物料编码 ,

       CC.DESCRIPTION              物料说明 ,

       VBOM.QTY                   构件数量 ,

       CC.PRIMARY_UOM_CODE        子计量单位码 ,

       CC.PRIMARY_UNIT_OF_MEASURE 子计量单位名 ,

       VBOM.WHSE                  供应子仓库

  FROM ( select AA.BILL_SEQUENCE_ID      BID,

               BB.ASSEMBLY_ITEM_ID       F_ITEMID,

               BB.ORGANIZATION_ID       OGT_ID,

               AA.COMPONENT_SEQUENCE_ID CID,

               AA.ITEM_NUM              ITEM_NUM,

               AA.OPERATION_SEQ_NUM     OPID,

               AA.COMPONENT_ITEM_ID     C_ITEMID,

               AA.COMPONENT_QUANTITY    QTY,

               AA.supply_subinventory   WHSE

          from bom_inventory_components AA, bom_bill_of_materials BB

         where AA.BILL_SEQUENCE_ID = BB.BILL_SEQUENCE_ID) VBOM,

       mtl_system_items BB,

        mtl_system_items CC

  WHERE VBOM.F_ITEMID = BB.INVENTORY_ITEM_ID

   and VBOM.OGT_ID = BB.ORGANIZATION_ID

   and VBOM.C_ITEMID = CC.INVENTORY_ITEM_ID

   and VBOM.OGT_ID = CC.ORGANIZATION_ID

   and VBOM.OGT_ID = 142

   and BB.SEGMENT1 = '5XJ061988'

  ORDER BY VBOM.ITEM_NUM;

 

-- 单层 BOM 成本查询 ( 需系统提交请求计算后 )

select inventory_item_id, organization_id, item_cost, program_update_date

  from bom.cst_item_costs

  where inventory_item_id = 23760

   and organization_id = 142 ;

  

select inventory_item_id, organization_id, item_cost, program_update_date

  from cst_item_cost_details

  where inventory_item_id = 23760

   and organization_id = 142 ;

 

10. 作业任务 WIP

  说明： 查询作业任务头以及作业任务工序和 BOM 情况

-- 作业任务头信息表

-- （以直流 OU_ID=117 ； ORGANIZATION_ID=1155; 及任务 WIP_ENTITY_NAME='XJ39562'; 装配件编码 SEGMENT1 = '07D9202.92742' 为例）

SELECT AA.WIP_ENTITY_ID   任务令 ID,

       AA.ORGANIZATION_ID 组织 ID,

       AA.WIP_ENTITY_NAME 任务名称 ,

       AA.ENTITY_TYPE     任务类型 ,

       AA.CREATION_DATE   创建日期 ,

       AA.CREATED_BY      创建者 ID,

       AA.DESCRIPTION     说明 ,

       AA.PRIMARY_ITEM_ID 装配件 ID,

       BB.SEGMENT1        物料编码 ,

       BB.DESCRIPTION     物料说明

  FROM wip_entities AA, mtl_system_items BB

  WHERE AA.PRIMARY_ITEM_ID = BB.INVENTORY_ITEM_ID

   and AA.ORGANIZATION_ID = BB.ORGANIZATION_ID

   and AA.ORGANIZATION_ID = 1155

   and AA.WIP_ENTITY_NAME = 'XJ39562' ;

--=> WIP_ENTITY_ID = 48825

 

-- 离散作业任务详细主信息表

-- 用途 1 ）作业任务下达及完成情况查询

-- 说明 1 ）此表包括 wip_entities 表大部分信息 2) 重复作业任务表为 wip_repetitive_items, wip_repetitive_schedules

select AA.WIP_ENTITY_ID                 任务令 ID,

       BB.WIP_ENTITY_NAME               任务名称 ,

       AA.ORGANIZATION_ID               组织 ID,

       AA.SOURCE_LINE_ID                行 ID,

       AA.STATUS_TYPE                   状态 TYPE,

       AA.PRIMARY_ITEM_ID               装配件 ID,

       CC.SEGMENT1                      物料编码 ,

       CC.DESCRIPTION                   物料说明 ,

       AA.FIRM_PLANNED_FLAG,

       AA.JOB_TYPE                      作业类型 ,

       AA.WIP_SUPPLY_TYPE               供应 TYPE,

       AA.CLASS_CODE                    任务类别 ,

       AA.SCHEDULED_START_DATE           起始时间 ,

       AA.DATE_RELEASED                 下达时间 ,

       AA.SCHEDULED_COMPLETION_DATE     完工时间 ,

       AA.DATE_COMPLETED                完工时间 ,

       AA.DATE_CLOSED                   关门时间 ,

       AA.START_QUANTITY                计划数 ,

       AA.QUANTITY_COMPLETED            完工数 ,

       AA.QUANTITY_SCRAPPED             报废数 ,

       AA.NET_QUANTITY                  MRP 净值 ,

       AA.COMPLETION_SUBINVENTORY       接收子库 ,

       AA.COMPLETION_LOCATOR_ID         货位

  from wip_discrete_jobs AA, wip.wip_entities BB, mtl_system_items CC

  where AA.WIP_ENTITY_ID = BB.WIP_ENTITY_ID

   and AA.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID

   and AA.ORGANIZATION_ID = CC.ORGANIZATION_ID

   and AA.organization_id = 1155

   and BB.WIP_ENTITY_NAME = 'XJ39562' ;

/*

1 ）任务状态 TYPE 值说明：

   STATUS_TYPE =1 未发放的 - 收费不允许

   STATUS_TYPE =3 发入 - 收费允许

   STATUS_TYPE =4 完成 - 允许收费

   STATUS_TYPE =5 完成 - 不允许收费

   STATUS_TYPE =6 暂挂 - 不允许收费

   STATUS_TYPE =7 已取消 - 不允许收费

   STATUS_TYPE =8 等待物料单加载

   STATUS_TYPE =9 失败的物料单加载

   STATUS_TYPE =10 等待路线加载

   STATUS_TYPE =11 失败的路线加载

   STATUS_TYPE =12 关闭 - 不可收费

   STATUS_TYPE =13 等待 - 成批加载

   STATUS_TYPE =14 等待关闭

   STATUS_TYPE =15 关闭失败

2 ）供应类型 TYPE 值说明：            

   WIP_SUPPLY_TYPE =1 推式

   WIP_SUPPLY_TYPE =2 装配拉式

   WIP_SUPPLY_TYPE =3 操作拉式

   WIP_SUPPLY_TYPE =4 大量

   WIP_SUPPLY_TYPE =5 供应商

   WIP_SUPPLY_TYPE =6 虚拟

   WIP_SUPPLY_TYPE =7 以帐单为基础

*/

 

-- 离散作业任务工序状况表

select AA.ORGANIZATION_ID            组织 ID,

       AA.WIP_ENTITY_ID              任务令 ID,

       BB.WIP_ENTITY_NAME            任务名称 ,

       AA.OPERATION_SEQ_NUM          工序号 ,

       AA.DESCRIPTION                工序描述 ,

       AA.DEPARTMENT_ID              部门 ID,

       AA.SCHEDULED_QUANTITY         计划数量 ,

       AA.QUANTITY_IN_QUEUE          排队数量 ,

       AA.QUANTITY_RUNNING           运行数量 ,

       AA.QUANTITY_WAITING_TO_MOVE    待移动数量 ,

       AA.QUANTITY_REJECTED          故障品数量 ,

       AA.QUANTITY_SCRAPPED          报废品数量 ,

       AA.QUANTITY_COMPLETED         完工数量 ,

       AA.FIRST_UNIT_START_DATE      最早一个单位上线时间 ,

       AA.FIRST_UNIT_COMPLETION_DATE 最早一个单位完成时间 ,

       AA.LAST_UNIT_START_DATE       最后一个单位上线时间 ,

       AA.LAST_UNIT_COMPLETION_DATE  最后一个单位完工时间 ,

       AA.PREVIOUS_OPERATION_SEQ_NUM 前一工序序号 ,

       AA.NEXT_OPERATION_SEQ_NUM     下一工序序号 ,

       AA.COUNT_POINT_TYPE           是否自动计费 ,

       AA.BACKFLUSH_FLAG             倒冲否 ,

       AA.MINIMUM_TRANSFER_QUANTITY  最小传送数量 ,

       AA.DATE_LAST_MOVED            最后移动时间

  from wip_operations AA,wip_entities BB

  where AA.WIP_ENTITY_ID = BB.WIP_ENTITY_ID

  and   BB.WIP_ENTITY_NAME = 'XJ39562' ;

-- 离散作业任务子查询 ——— 工单工序状况查询（不单独使用）

select WDJ.ORGANIZATION_ID,

       WDJ.WIP_ENTITY_ID,

       COUNT ( 1 ) COUNT_OPER,

       max ( decode (wo.quantity_completed, 1 , wo.operation_seq_num, 10 )) OPER

  from WIP_DISCRETE_JOBS WDJ, WIP_OPERATIONS WO

  where 1 = 1

   AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID

   AND WDJ.wip_entity_id = '48825'

   group by WDJ.ORGANIZATION_ID,WDJ.WIP_ENTITY_ID;

 

 

-- 离散作业任务 BOM ( 无材料费 )

SELECT WOP.ORGANIZATION_ID       组织 ID,

       WOP.WIP_ENTITY_ID         任务令 ID,

       BB.WIP_ENTITY_NAME       装配件名称 ,

       BB.PRIMARY_ITEM_ID       装配件 ID,

       CC.SEGMENT1              装配件物料编码 ,

       CC.DESCRIPTION           装配件说明 ,

       WOP.OPERATION_SEQ_NUM     工序号 ,

       WOP.DEPARTMENT_ID         部门 ID,

       WOP.WIP_SUPPLY_TYPE       供应类型 ,

       WOP.DATE_REQUIRED         要求日期 ,

       WOP.INVENTORY_ITEM_ID     子物料 ID,

       DD.SEGMENT1              子物料编码 ,

       DD.DESCRIPTION           子物料说明 ,

       WOP.QUANTITY_PER_ASSEMBLY 单位需量 ,

       WOP.REQUIRED_QUANTITY     总需求量 ,

       WOP.QUANTITY_ISSUED       已发放量 ,

       WOP.COMMENTS              注释 ,

        WOP.SUPPLY_SUBINVENTORY   供应子库

  FROM wip_requirement_operations WOP,

      wip_entities BB,

      mtl_system_items CC,

      mtl_system_items DD

  WHERE WOP.WIP_ENTITY_ID = BB.WIP_ENTITY_ID

   and BB.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID

   and BB.ORGANIZATION_ID = CC.ORGANIZATION_ID

   and WOP.INVENTORY_ITEM_ID = DD.INVENTORY_ITEM_ID

   and WOP.ORGANIZATION_ID = DD.ORGANIZATION_ID

   and WOP.ORGANIZATION_ID = 1155

   and BB.WIP_ENTITY_NAME = 'XJ39562' ;

 

-- 作业任务已发放材料处理记录清单 0101 （最详细） （内码为 48825 为例）

-- 用途 1 ）查询工单发料详细明细，包括发料类型、时间、用户等

select MTL.TRANSACTION_ID 交易 ID,

       MTL.INVENTORY_ITEM_ID 项目 ID,

       CC.SEGMENT1 物料编码 ,

       CC.DESCRIPTION 物料说明 ,

       MTL.ORGANIZATION_ID 组织 ID,

       MTL.SUBINVENTORY_CODE 子库名称 ,

       MTL.TRANSACTION_TYPE_ID 交易类型 ID,

       BB.TRANSACTION_TYPE_NAME 交易类型名称 ,

       MTL.TRANSACTION_QUANTITY 交易数量 ,

       MTL.TRANSACTION_UOM 单位 ,

       MTL.TRANSACTION_DATE 交易日期 ,

       MTL.TRANSACTION_REFERENCE 交易参考 ,

       MTL.TRANSACTION_SOURCE_ID 参考源 ID,

       FF.WIP_ENTITY_NAME 任务名称 ,

       MTL.DEPARTMENT_ID 部门 ID,

       MTL.OPERATION_SEQ_NUM 工序号 ,

       ROUND (MTL.PRIOR_COST, 2 ) 原来成本 ,

       ROUND (MTL.NEW_COST, 2 ) 新成本 ,

       MTL.TRANSACTION_QUANTITY * ROUND (MTL.PRIOR_COST, 2 ) 交易金额 ,

       DD.USER_NAME 用户名称 ,

       EE.FULL_NAME 用户姓名

  from mtl_material_transactions MTL,

       mtl_transaction_types     BB,

       mtl_system_items          CC,

       FND_USER                  DD,

       per_people_f              EE,

       wip_entities              FF

  where MTL.TRANSACTION_TYPE_ID = BB.TRANSACTION_TYPE_ID

   and MTL.CREATED_BY = DD.USER_ID

   and MTL.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID

   and MTL.ORGANIZATION_ID = CC.ORGANIZATION_ID

   and DD.EMPLOYEE_ID = EE.PERSON_ID

   and MTL.TRANSACTION_SOURCE_ID = FF.WIP_ENTITY_ID

   and MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )

   and MTL.ORGANIZATION_ID = 1155

   and MTL.TRANSACTION_SOURCE_ID = 48825 ;

-- 按工单的材料费汇总（不单独使用）

select MTL.ORGANIZATION_ID,

       MTL.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,

       ABS ( round ( SUM (MTL.TRANSACTION_QUANTITY * MTL.PRIOR_COST), 2 )) AMT

  from mtl_material_transactions MTL

  where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )

   and MTL.ORGANIZATION_ID = 1155

   and MTL.TRANSACTION_SOURCE_ID = 48825

  group by MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID;

  

-- 离散作业任务子查询 01——— 材料消耗状况及材料费综合查询  

-- 用途   1 ）查询发料状况   2 ）查询材料费物料小计

SELECT WOP.ORGANIZATION_ID       组织 ID,

       WOP.WIP_ENTITY_ID         任务令 ID,

       BB.WIP_ENTITY_NAME        装配件名称 ,

       BB.PRIMARY_ITEM_ID        装配件 ID,

       CC.SEGMENT1               装配件物料编码 ,

       CC.DESCRIPTION            装配件说明 ,

       WOP.OPERATION_SEQ_NUM     工序号 ,

       WOP.DEPARTMENT_ID         部门 ID,

       WOP.WIP_SUPPLY_TYPE       供应类型 ,

       WOP.DATE_REQUIRED         要求日期 ,

        WOP.INVENTORY_ITEM_ID     子物料 ID,

       DD.SEGMENT1               子物料编码 ,

       DD.DESCRIPTION            子物料说明 ,

       WOP.QUANTITY_PER_ASSEMBLY 单位需量 ,

       WOP.REQUIRED_QUANTITY     总需求量 ,

       WOP.QUANTITY_ISSUED       已发放量 ,

       CST.AMT                    已发生材料费 ,

       WOP.COMMENTS              注释 ,

       WOP.SUPPLY_SUBINVENTORY   供应子库

  FROM wip_requirement_operations WOP,

       wip_entities BB,

       mtl_system_items CC,

       mtl_system_items DD,

       ( select MTL.ORGANIZATION_ID orgID,

               MTL.TRANSACTION_SOURCE_ID wipID,

               MTL.OPERATION_SEQ_NUM oprID,

               MTL.INVENTORY_ITEM_ID itemID,

               sum (MTL.TRANSACTION_QUANTITY * ROUND (MTL.actual_cost, 2 )) amt

          from mtl_material_transactions MTL

         where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )

           and MTL.ORGANIZATION_ID = 1155

           and MTL.TRANSACTION_SOURCE_ID = 48825

         group by MTL.ORGANIZATION_ID,

                  MTL.TRANSACTION_SOURCE_ID,

                  MTL.OPERATION_SEQ_NUM,

                  MTL.INVENTORY_ITEM_ID) CST

  WHERE WOP.WIP_ENTITY_ID = BB.WIP_ENTITY_ID

   and BB.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID

   and BB.ORGANIZATION_ID = CC.ORGANIZATION_ID

   and WOP.INVENTORY_ITEM_ID = DD.INVENTORY_ITEM_ID

   and WOP.ORGANIZATION_ID = DD.ORGANIZATION_ID

   and WOP.ORGANIZATION_ID = CST.orgID

   and WOP.WIP_ENTITY_ID = CST.wipID

   and WOP.OPERATION_SEQ_NUM = CST.oprID

   and WOP.INVENTORY_ITEM_ID = CST.itemID

   and WOP.ORGANIZATION_ID = 1155

   and BB.WIP_ENTITY_NAME = 'XJ39562' ;

                     

-- 离散作业任务子查询 0201——— 作业资源报工明细表

SELECT WTA.ORGANIZATION_ID   组织代码 ,

       WTA.TRANSACTION_ID    交易代码 ,

       WTA.REFERENCE_ACCOUNT 参考科目 ,

       WTA.TRANSACTION_DATE  报工日期 ,

       WTA.WIP_ENTITY_ID     任务令内码 ,

       WTA.ACCOUNTING_LINE_TYPE   会计栏类型 ,

       WTA.BASE_TRANSACTION_VALUE 费用额 ,

       WTA.CONTRA_SET_ID          反方集代码 ,

       WTA.PRIMARY_QUANTITY       基本数量 ,

       WTA.RATE_OR_AMOUNT         率或金额 ,

       WTA.BASIS_TYPE             基本类型 ,

       WTA.RESOURCE_ID            资源代码 ,

       WTA.COST_ELEMENT_ID        成本要素 ID,

       WTA.ACCOUNTING_LINE_TYPE   成本类型 ID,

       WTA.OVERHEAD_BASIS_FACTOR  费用因子 ,

       WTA.BASIS_RESOURCE_ID      基本资源 ID,

       WTA.CREATED_BY              录入人 ID,

       DD.USER_NAME               用户名称 ,

       EE.FULL_NAME               用户姓名

  FROM wip_transaction_accounts WTA, FND_USER DD, per_people_f EE

  WHERE WTA.CREATED_BY = DD.USER_ID

   and DD.EMPLOYEE_ID = EE.PERSON_ID

   and WTA.BASE_TRANSACTION_VALUE <> 0

   and WTA.ORGANIZATION_ID = 1155

   and WTA.WIP_ENTITY_ID = 48839 ;

-- 成本类型 ID ACCOUNTING_LINE_TYPE

SELECT * FROM MFG_LOOKUPS ML

WHERE ML.LOOKUP_TYPE LIKE 'CST_ACCOUNTING_LINE_TYPE'

ORDER BY ML.LOOKUP_CODE;

-- 成本要素 ID COST_ELEMENT_ID

--( 待补充 --------------------------------------------------------------------------?)

 

-- 统计人工费与制造费 ( 不单独应用 )

SELECT ORGANIZATION_ID, WIP_ENTITY_ID, SUM (HR_FEE) HR_FEE, SUM (MD_FEE) MD_FEE

  FROM ( Select WTA.ORGANIZATION_ID,

               WTA.WIP_ENTITY_ID,

                decode (COST_ELEMENT_ID, 3 , WTA.BASE_TRANSACTION_VALUE, 0 ) HR_FEE,

               decode (COST_ELEMENT_ID, 5 , WTA.BASE_TRANSACTION_VALUE, 0 ) MD_FEE

          from WIP_TRANSACTION_ACCOUNTS WTA

         where WTA.ACCOUNTING_LINE_TYPE = 7

           and WTA.BASE_TRANSACTION_VALUE <> 0 ) WTA_COST

  WHERE WTA_COST.ORGANIZATION_ID = 1155

   AND WTA_COST.WIP_ENTITY_ID = '48839'

  GROUP BY WTA_COST.ORGANIZATION_ID,WTA_COST.WIP_ENTITY_ID;

 

-- 工单进度及费用信息综合查询 ( 未下达及下达零发料和报工的看不到 )

select WE.WIP_ENTITY_NAME            任务名称 ,

       MSI.SEGMENT1                  物料 ,

       MSI.DESCRIPTION               物料描述 ,

       MSI.PRIMARY_UNIT_OF_MEASURE   单位 ,

       WDJ.SCHEDULED_START_DATE      计划开始时间 ,

       WDJ.SCHEDULED_COMPLETION_DATE 计划完成时间 ,

       WDJ.START_QUANTITY            工单数量 ,

       WDJ.QUANTITY_COMPLETED        完成数量 ,

       WDJ.DATE_RELEASED             实际开始时间 ,

       WDJ.DATE_COMPLETED            时间完成时间 ,

       WDJ.DESCRIPTION               工单备注 ,

       PP.SEGMENT1                   项目号 ,

       PP.DESCRIPTION                项目描述 ,

       PT.TASK_NUMBER                任务号 ,

       PT.DESCRIPTION                任务描述 ,

       WO.COUNT_OPER                 工序数 ,

       WO1.OPERATION_SEQ_NUM         当前工序 ,

       WO1.DESCRIPTION               当前工序描述 ,

       MTA.MT_FEE                    材料费 ,

       WCT.HR_FEE                    人工费 ,

       WCT.MD_FEE                    制造费 ,

       WE.WIP_ENTITY_ID,

       WE.ORGANIZATION_ID,

       WDJ.PRIMARY_ITEM_ID,

       WDJ.PROJECT_ID,

       WDJ.TASK_ID

  from WIP_ENTITIES WE,

       WIP_OPERATIONS WO1,

       WIP_DISCRETE_JOBS WDJ,

       MTL_SYSTEM_ITEMS_B MSI,

       PA_PROJECTS_ALL PP,

       PA_TASKS PT,

       ( select WDJ.ORGANIZATION_ID,

               WDJ.WIP_ENTITY_ID,

               COUNT ( 1 ) COUNT_OPER,

               max ( decode (wo.quantity_completed, 1 , wo.operation_seq_num, 10 )) OPER

          from WIP_DISCRETE_JOBS WDJ, WIP_OPERATIONS WO

         where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID

         group by WDJ.ORGANIZATION_ID, WDJ.WIP_ENTITY_ID) WO, -- 工序进度

       ( select MTL.ORGANIZATION_ID,

               MTL.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,

               ABS ( SUM (MTL.TRANSACTION_QUANTITY * MTL.actual_cost)) MT_FEE

          from mtl_material_transactions MTL

         where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )

         group by MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID) MTA, -- 材料费    

       ( select WTA_COST.ORGANIZATION_ID,

               WTA_COST.WIP_ENTITY_ID,

               SUM (WTA_COST.HR_FEE1) HR_FEE,

                SUM (WTA_COST.MD_FEE1) MD_FEE

          from ( select WTA.ORGANIZATION_ID,

                       WTA.WIP_ENTITY_ID,

                       decode (COST_ELEMENT_ID,

                              3 ,

                              WTA.BASE_TRANSACTION_VALUE,

                              0 ) HR_FEE1,

                       decode (COST_ELEMENT_ID,

                              5 ,

                              WTA.BASE_TRANSACTION_VALUE,

                              0 ) MD_FEE1

                  from WIP_TRANSACTION_ACCOUNTS WTA

                 where WTA.ACCOUNTING_LINE_TYPE = 7

                   and WTA.BASE_TRANSACTION_VALUE <> 0 ) WTA_COST

         group by WTA_COST.ORGANIZATION_ID, WTA_COST.WIP_ENTITY_ID) WCT -- 人工与制造

  WHERE 1 = 1

   AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID

   AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID

   AND WDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID

   AND WDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID

   AND WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID

   AND WO1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID

   AND WO.OPER = WO1.OPERATION_SEQ_NUM

   AND WE.ORGANIZATION_ID = MTA.ORGANIZATION_ID

   AND WE.WIP_ENTITY_ID = MTA.WIP_ENTITY_ID(+)

   AND WE.ORGANIZATION_ID = WCT.ORGANIZATION_ID

   AND WE.WIP_ENTITY_ID = WCT.WIP_ENTITY_ID(+)

   AND WDJ.PROJECT_ID = PP.PROJECT_ID(+)

   AND WDJ.TASK_ID = PT.TASK_ID(+)

   and we.organization_id = 1155

   and we.wip_entity_id = '48825' ;

  

  -- 工单进度及费用信息综合查询 ( 不论是否下达和发料都能看到 )

SELECT WDJ.WIP_ENTITY_ID           任务令 ID,

       WE.WIP_ENTITY_NAME          任务名称 ,

       WDJ.ORGANIZATION_ID         组织 ID,

       WDJ.STATUS_TYPE             状态 ,

       WDJ.PRIMARY_ITEM_ID         装配件 ID,

       MSI.SEGMENT1                物料编码 ,

       MSI.DESCRIPTION             物料说明 ,

       WDJ.FIRM_PLANNED_FLAG       任务类型 ,

       WDJ.JOB_TYPE                作业类型 ,

       WDJ.WIP_SUPPLY_TYPE         供应类型 ,

       WDJ.CLASS_CODE              任务类别 ,

       WDJ.SCHEDULED_START_DATE    起始时间 ,

       WDJ.DATE_RELEASED           下达时间 ,

       WDJ.DATE_COMPLETED          完工时间 ,

       WDJ.DATE_CLOSED             关闭时间 ,

       WDJ.START_QUANTITY          计划数 ,

       WDJ.QUANTITY_COMPLETED      完工数 ,

       WDJ.QUANTITY_SCRAPPED       报废数 ,

       WDJ.NET_QUANTITY            MRP 净值 ,

       WDJ.DESCRIPTION             工单备注 ,

       WDJ.COMPLETION_SUBINVENTORY 接收子库 ,

       WDJ.COMPLETION_LOCATOR_ID   货位 ID,

       WDJ.PROJECT_ID              项目 ID,

       WDJ.TASK_ID                 项目任务 ID,

       PP.SEGMENT1                 项目号 ,

       PP.DESCRIPTION              项目描述 ,

       PT.TASK_NUMBER              任务号 ,

       PT.DESCRIPTION              任务描述 ,

       WPF.COUNT_OPER              工序数 ,

       WPF.CUR_OPER                当前工序 ,

       WPF.CUR_OPERNAME            工序名 ,

       WPF.MT_FEE                   材料费 ,

       WPF.HR_FEE                  人工费 ,

       WPF.MD_FEE                  制造费

  FROM wip_discrete_jobs WDJ,

       wip.wip_entities WE,

       mtl_system_items MSI,

       PA_PROJECTS_ALL PP,

       PA_TASKS PT,

       ( select WDJ1.WIP_ENTITY_ID,

               WDJ1.ORGANIZATION_ID,

               WO.COUNT_OPER,

               WO1.OPERATION_SEQ_NUM CUR_OPER,

               WO1.DESCRIPTION       CUR_OPERNAME,

               MTA.MT_FEE,

               WCT.HR_FEE,

               WCT.MD_FEE

          from WIP_OPERATIONS WO1,

               WIP_DISCRETE_JOBS WDJ1,

               ( select WDJ.ORGANIZATION_ID,

                       WDJ.WIP_ENTITY_ID,

                       COUNT ( 1 ) COUNT_OPER,

                       max ( decode (wo.quantity_completed,

                                  1 ,

                                  wo.operation_seq_num,

                                  10 )) OPER

                  from WIP_DISCRETE_JOBS WDJ, WIP_OPERATIONS WO

                 where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID

                 group by WDJ.ORGANIZATION_ID, WDJ.WIP_ENTITY_ID) WO, -- 工序进度

               ( select MTL.ORGANIZATION_ID,

                       MTL.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,

                       ABS ( SUM (MTL.TRANSACTION_QUANTITY * MTL.actual_cost)) MT_FEE

                  from mtl_material_transactions MTL

                 where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )

                 group by MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID) MTA, -- 材料费    

               ( select WTA_COST.ORGANIZATION_ID,

                       WTA_COST.WIP_ENTITY_ID,

                       SUM (WTA_COST.HR_FEE1) HR_FEE,

                       SUM (WTA_COST.MD_FEE1) MD_FEE

                  from ( select WTA.ORGANIZATION_ID,

                               WTA.WIP_ENTITY_ID,

                               decode (COST_ELEMENT_ID,

                                      3 ,

                                      WTA.BASE_TRANSACTION_VALUE,

                                      0 ) HR_FEE1,

                               decode (COST_ELEMENT_ID,

                                      5 ,

                                      WTA.BASE_TRANSACTION_VALUE,

                                      0 ) MD_FEE1

                          from WIP_TRANSACTION_ACCOUNTS WTA

                         where WTA.ACCOUNTING_LINE_TYPE = 7

                           and WTA.BASE_TRANSACTION_VALUE <> 0 ) WTA_COST

                 group by WTA_COST.ORGANIZATION_ID, WTA_COST.WIP_ENTITY_ID) WCT -- 人工与制造

         WHERE 1 = 1

           AND WDJ1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID(+)

           AND WO1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID

           AND WO.OPER = WO1.OPERATION_SEQ_NUM

           AND WDJ1.ORGANIZATION_ID = MTA.ORGANIZATION_ID

           AND WDJ1.WIP_ENTITY_ID = MTA.WIP_ENTITY_ID(+)

           AND WDJ1.ORGANIZATION_ID = WCT.ORGANIZATION_ID

           AND WDJ1.WIP_ENTITY_ID = WCT.WIP_ENTITY_ID(+)) WPF

  WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID

   AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID

   AND WDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID

   AND WDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID

   AND WDJ.PROJECT_ID = PP.PROJECT_ID(+)

   AND WDJ.TASK_ID = PT.TASK_ID(+)

   AND WDJ.ORGANIZATION_ID = WPF.ORGANIZATION_ID(+)

   AND WDJ.WIP_ENTITY_ID = WPF.WIP_ENTITY_ID(+)

   AND WDJ.organization_id = 1155

   and PP.SEGMENT1 = '07D9202' ;

 

11. MRP相关查询

 

 

12. 总账 GL

select * from gl_sets_of_books 总帐

select * from gl_code_combinations gcc where gcc.summary_flag='Y' 科目组合

select * from gl_balances 科目余额

select * from gl_je_batches 凭证批

select * from gl_je_headers 凭证头

select * from gl_je_lines 凭证行

select * from gl_je_categories 凭证分类

select * from gl_je_sources 凭证来源

select * from gl_summary_templates 科目汇总模板

select * from gl_account_hierarchies 科目汇总模板层次

13. 应收 AR

select * from ar_batches_all 事务处理批

select * from ra_customer_trx_all 发票头

select * from ra_customer_trx_lines_all 发票行

select * from ra_cust_trx_line_gl_dist_all 发票分配

select * from ar_cash_receipts_all 收款

select * from ar_receivable_applications_all 核销

select * from ar_payment_schedules_all 发票调整

select * from ar_adjustments_all 会计分录

select * from ar_distributions_all 付款计划

14. 应付 AP

select * from ap_invoices_all 发票头

select * from ap_invoice_distributions_all 发票行

select * from ap_payment_schedules_all 付款计划

select * from ap_check_stocks_all 单据

select * from ap_checks_all 付款

select * from ap_bank_branches 银行

select * from ap_bank_accounts_all 银行帐号

select * from ap_invoice_payments_all 核销

 

15. 应用、值集、弹性域

--fnd

select * from fnd_application

select * from fnd_application_tl where application_id= 101

select * from fnd_application_vl where application_id = 101

-- 值集

select * from fnd_flex_value_sets

select * from fnd_flex_values

select * from fnd_flex_values_vl

-- 弹性域

select * from fnd_id_flexs

select * from fnd_id_flex_structures where id_flex_code= 'GL#'

select * from fnd_id_flex_segments where id_flex_code= 'GL#' and id_flex_num= 50671

 

select * from fnd_profile_options_vl

select * from fnd_concurrent_programs 程序表

select * from fnd_concurrent_requests 请求表

select * from fnd_concurrent_processes 进程表

16. 许继配送管理相关表

--- 为了实现系统内方便的配送发货模式而开发

-- 配送关系 （以供应处角度 ORGANIZATION_ID = 142 ）

SELECT AA.CUSTOMER_RELATION_ID     配送关系 ID,

       AA.ORGANIZATION_ID          组织 ID,

       AA.CUST_ACCOUNT_ID          客户 ID,

       CC.PARTY_NAME               客户名称 ,

       AA.CUST_ACCT_SITE_ID        配送地 ID,

       DD.LOCATION                 客户地点 ,

       DD.STATUS                   A 有效 ,

       AA.DELIVERY_BY_SO_FLAG      源于 CO,

       AA.OUTBOUND_TRX_TYPE_ID     出库类型 ,

       AA.OUTBOUND_RET_TRX_TYPE_ID 出库 R 类型 ,

       AA.OUTBOUND_COST_CCID       出库账户 ID,

       EE.concatenated_segments    出库账户 ,

       AA.CUST_ORG_ID              客户库存组织 ID,

       AA.INBOUND_TRX_TYPE_ID      入库类型 ,

       AA.INBOUND_RET_TRX_TYPE_ID  入库 R 类型 ,

       AA.INBOUND_CONFIRM_FLAG     入库确认 ,

       AA.INBOUND_COST_CCID        入库账户 ID,

       FF.concatenated_segments    入库账户 ,

       AA.MANAGE_CHARGE            加价率 ,

       AA.SETTLE_MODE              结算模式 ,

       AA.INBOUND_SUBIN_CODE       接收子仓库 ,

       AA.OUTBOUND_SUBIN_CODE      配送子库存 ,

       AA.ATTRIBUTE1               直接生产发料 ,

       AA.Creation_Date            创建日期 ,

       AA.CREATED_BY               创建者 ,

       AA.LAST_UPDATED_BY          更新者 ,

       AA.LAST_UPDATE_DATE         更新日期

  FROM CUX_INV_CUSTOMER_RELATION_ALL AA,

       hz_cust_accounts              BB,

       hz_parties                    CC,

       HZ_CUST_SITE_USES_ALL         DD,

       gl_code_combinations_kfv      EE,

       gl_code_combinations_kfv      FF

  WHERE AA.ORGANIZATION_ID = 142

   AND AA.CUST_ACCOUNT_ID = BB.CUST_ACCOUNT_ID

   AND BB.PARTY_ID = CC.PARTY_ID

   AND AA.Cust_Acct_Site_Id = DD.Site_Use_Id

   AND DD.STATUS = 'A'

   AND AA.OUTBOUND_COST_CCID = EE.code_combination_id

   AND AA.INBOUND_COST_CCID  = FF.code_combination_id

   and CC.PARTY_NAME = ' 许继电气电网保护自动化公司 ' ;

-- 配送单头

SELECT AA.DN_HEADER_ID         配送单 ID,

       AA.DN_NUMBER            配送单编号 ,

       AA.DN_STATUS_CODE       状态 ,

       AA.CUST_ACCOUNT_ID      客户 ID,

       CC.PARTY_NAME           客户名称 ,

       AA.CUST_ACCT_SITE_ID    配送地址 ID,

       DD.LOCATION             客户地点 ,

       AA.DELIVERY_ORG_ID      配送方组织 ID,

       AA.CUST_ORG_ID          客户组织 ID,

       AA.MANAGE_CHARGE        费率 ,

       AA.INBOUND_CONFIRM_FLAG 入库确认否 ,

       AA.SO_HEADER_ID          销售订单 ID,

       EE.ORDER_NUMBER         销售订单 ,

       EE.CUST_PO_NUMBER       客户 PO,

       ee.attribute1,

       ee.attribute2,

       AA.PROCESS_FLAG,

       AA.COMMENTS             配送单说明

  FROM CUX_INV_DN_HEADERS_ALL AA, hz_cust_accounts BB, hz_parties CC, HZ_CUST_SITE_USES_ALL DD,OE_ORDER_HEADERS_ALL EE

  WHERE   AA.DELIVERY_ORG_ID = 142

   AND AA.CUST_ACCOUNT_ID = BB.CUST_ACCOUNT_ID

   AND BB.PARTY_ID = CC.PARTY_ID

   AND AA.Cust_Acct_Site_Id = DD.Site_Use_Id

   AND DD.STATUS = 'A'

   AND AA.SO_HEADER_ID = EE.HEADER_ID

   AND AA.DN_NUMBER = '14780016022' ;

-- 配送单明细

SELECT AA.DN_HEADER_ID,

       AA.DN_LINE_ID,

       AA.SO_LINE_ID,

       LL.LINE_NUMBER         SO 行号 ,

       AA.INVENTORY_ITEM_ID   物料 ID,

       CC.SEGMENT1            物料编码 ,

        CC.DESCRIPTION         物料说明 ,

       AA.OUTBOUND_SUBIN_CODE 发出仓 ,

       AA.OUTBOUND_LOCATOR_ID 发出货位 ,

       AA.REQUIRE_DATE        需求日期 ,

       AA.REQUIRE_QTY         需求数 ,

       AA.OUTBOUND_QTY        已出库 ,

       AA.INBOUND_QTY           已接收 ,

       AA.ATTRIBUTE1            最近确认接收数 ,

       AA.INBOUND_SUBIN_CODE    入库仓 ,

       AA.INBOUND_LOCATOR_ID    入库货位 ,

       AA.RETURN_NO_RECEIVE_QTY 退回数 ,

       AA.OUTING_QTY,

       AA.INING_QTY,

       AA.REQUEST_ID 最近打印请求 ID

  FROM CUX_INV_DN_LINES_ALL AA, CUX_INV_DN_HEADERS_ALL BB,mtl_system_items CC,OE_ORDER_LINES_ALL LL

  WHERE AA.DN_HEADER_ID = BB.DN_HEADER_ID

   AND AA.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID

   AND BB.DELIVERY_ORG_ID = CC.ORGANIZATION_ID

   AND AA.SO_LINE_ID = LL.LINE_ID

   AND BB.DN_NUMBER = '14780016022' ;

 

99-其他相关表

1)         会计科目组合表

-- 查询会计科目分段信息

select * from gl_code_combinations;

-- 查询会计科目组合信息

select * from gl_code_combinations_kfv;

2)         查询自定义的客户化相关表和视图

-- 静态数据字典中的视图分为三类，它们分别由三个前缀够成： user_* 、 all_* 、 dba_*

-- user_* 该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）

-- all_* 该视图存储了当前用户能够访问的对象的信息。（与 user_* 相比， all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）

-- dba_* 该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限

-- 查询该用户拥有哪些表

select * from user_tables where table_name like 'CUX%' ;

-- 查询该用户拥有哪些视图

select * from user_views where view_name like 'CUX%' ;

-- 查询该用户拥有哪些索引

select * from user_indexes;

3)         查询物料处理记录

说明： mtl_material_transactions 这个表记录了所有涉及仓库收发的物料交易记录，包括：采购、 WIP 、订单、杂项等多种处理模式的内容。

-- 举例： 查询某用户在电网的账户别名发放清单

select AA.TRANSACTION_ID             交易代码 ,

       AA.INVENTORY_ITEM_ID          项目内码 ,

       CC.SEGMENT1                   物料编码 ,

       CC.DESCRIPTION                物料说明 ,

       AA.ORGANIZATION_ID            组织代码 ,

       AA.SUBINVENTORY_CODE          子库名称 ,

       AA.TRANSACTION_TYPE_ID        类型 ID,

       BB.TRANSACTION_TYPE_NAME      类型名称 ,

       AA.TRANSACTION_QUANTITY       数量 ,

       AA.TRANSACTION_UOM            单位 ,

       AA.TRANSACTION_DATE           交易日期 ,

       AA.TRANSACTION_REFERENCE      交易参考 ,

       AA.TRANSACTION_SOURCE_ID      参考源 ID,

       AA.DEPARTMENT_ID              部门 ID,

       AA.OPERATION_SEQ_NUM          工序号 ,

       ROUND (AA.Actual_Cost, 2 )       实际成本 ,

       ROUND (AA.Transaction_Cost, 2 )  处理成本 ,

       ROUND (AA.PRIOR_COST, 2 )        旧成本 ,

       ROUND (AA.NEW_COST, 2 )          新成本 ,

       ROUND (AA.VARIANCE_AMOUNT, 2 )   差异金额 ,

       AA.TRANSACTION_QUANTITY * ROUND (AA.PRIOR_COST, 2 )  交易金额 ,

       DD.USER_NAME                  用户名称 ,

       EE.FULL_NAME                  用户姓名 ,

       AA.ATTRIBUTE1                 弹性域人名 ,

       AA.ATTRIBUTE15                弹性域备注

  from mtl_material_transactions AA ,

       mtl_transaction_types BB,

       mtl_system_items CC,

       FND_USER DD,

       per_people_f EE

  where AA.TRANSACTION_TYPE_ID = BB.TRANSACTION_TYPE_ID and

        AA.CREATED_BY = DD.USER_ID and

        AA.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID and

        AA.ORGANIZATION_ID = CC.ORGANIZATION_ID and

        DD.EMPLOYEE_ID = EE.PERSON_ID and

        AA.ORGANIZATION_ID = 1155 AND

        CC.SEGMENT1= '07D9202.92742' and

        AA.TRANSACTION_DATE >= TO_DATE( '2011-01-29 00:00:00' , 'YYYY-MM-DD HH24:MI:SS' )

  order by aa.TRANSACTION_ID;

/* 物料处理记录 mtl_material_transactions 表，类型与成本说明：

-- 杂项收   TRANSACTION_TYPE_ID = 41  录入价格优先， =Actual_Cost ，移动平均      TRANSACTION_QUANTITY > 0 , 调整类似

          注： 1 ）如果再接收界面录入了价格，以录入价格计入 Actual_Cost ，进行移动平均

              2 ）如果没有录入价格，字段 NULL ，则系统会以当前成本接收，计入 Actual_Cost

-- 杂项发   TRANSACTION_TYPE_ID = 31  以出 =Actual_Cost ，               TRANSACTION_QUANTITY < 0 , 调整类似

-- 采购收   TRANSACTION_TYPE_ID = 18  以 入 =Actual_Cost ，移动平均      TRANSACTION_QUANTITY > 0

-- 采购退   TRANSACTION_TYPE_ID = 36  以 出 =Actual_Cost ，              TRANSACTION_QUANTITY < 0

          注： 1) 系统按采购成本退货和扣除库存金额，不考虑已消耗状况；

              2) 如果库存金额够扣除，则扣除后重新计算出一个新成本；

              3 ）如果库存金额不够扣除，则扣除全部金额，就会出现有库存量而单位成本 =0 的物资，不够扣的部分计入字段 VARIANCE_AMOUNT 。

-- 作业发   TRANSACTION_TYPE_ID = 35   以当前成本出， =Actual_Cost ，              TRANSACTION_QUANTITY < 0 ，特定组件发料类似

-- 作业退   TRANSACTION_TYPE_ID = 43   以当前成本入， =Actual_Cost ，不移动平均    TRANSACTION_QUANTITY > 0

-- 配送出   TRANSACTION_TYPE_ID = 100  以当前成本出， =Actual_Cost ，              TRANSACTION_QUANTITY < 0

-- 配送退   TRANSACTION_TYPE_ID = 101  以配送价入   ， =Actual_Cost ，移动平均      TRANSACTION_QUANTITY > 0

-- 销售发   TRANSACTION_TYPE_ID = 33   以当前成本出， =Actual_Cost ，              TRANSACTION_QUANTITY < 0

-- 销售退   TRANSACTION_TYPE_ID = 15   以当前成本入， =Actual_Cost ，              TRANSACTION_QUANTITY > 0

*/

-- 物料处理记录类型列表

select bb.transaction_type_id   类型 ID,

       bb.transaction_type_name 别名 ,

       bb.description           说明

  from mtl_transaction_types BB

  order by bb.transaction_type_id

-- 交易来源类型列表

SELECT *

FROM mtl_txn_source_types

-- 交易原因代码表  

SELECT REASON_ID 原因代码 , REASON_NAME 名称 , DESCRIPTION 描述

  FROM inv.mtl_transaction_reasons;

 

 

评论

1 楼 SanFrans 2013-08-14  

很不错的资料，谢谢分享