Oracle ERP供应链常用信息查询

1、.Cajan.Z ：.； ORACLE EBS常用表查询语句OU、库存组织SELECT anization_id ou_org_id, -org_id ou_name, -ou称号 anization_id org_org_id, -库存组织id anization_code org_org_code, -库存组织代码 msi.secondary_inventory_name, -子库存称号 msi.description -子库存描画 FROM hr_organization_information hoi, -组织分类表 hr_o

2、perating_units hou, -ou视图 org_organization_definitions ood, -库存组织定义视图 mtl_secondary_inventories msi -子库存信息表 WHERE _information1 = OPERATING_UNIT AND anization_id = anization_id AND ood.operating_unit = anization_id AND anization_id = anization_id-获取系统IDcall

3、fnd_global.APPS_INITIALIZE(1318,50583,401)select fnd_profile.VALUE(ORG_ID) FROM DUALselect * from hr_operating_units hou where anization_id=204用户、责任及HR-系统责任定义VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)SELECT APPLICATION_ID, RESPONSIBILITY_ID, RESPONSIBILITY_KEY, END_DATE, RESPONSIBIL

4、ITY_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;-人员表VIEWSELECT PERSON_ID, START_DATE, DATE_OF_BIRTH, EMPLOYEE_NUMBER, NATIONAL_IDENTIFIER, SEX, FULL

5、_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 RESPO

6、NSIBILITY_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_INFORMAT

7、ION17 省份, 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)

8、1980 THEN 70年代 WHEN TO_CHAR(PAF.DATE_OF_BIRTH, YYYY) 1990 THEN 80年代 WHEN TO_CHAR(PAF.DATE_OF_BIRTH, YYYY) PARTY_ID = 21302SELECT * FROM hz_cust_accounts AA WHERE AA.CUST_ACCOUNT_ID = 1063;-客户称号及地址全局信息表 - PARTY_NUMBER = 19316SELECT * FROM hz_parties AA WHERE AA.PARTY_ID = 21302;-客户地点账户主文件SELECT * FRO

9、M 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

10、 = 21302;-客户地点业务目的 (关联hz_cust_acct_sites_all 用CUST_ACCT_SITE_ID)SELECT * FROM HZ_CUST_SITE_USES_ALL;-客户地点详细信息表，以供应处OU的身份 ORG_ID = 119SELECT 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_NA

11、ME, 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 WHER

12、E 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.p

13、arty_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, term_name, hca.creation_date creation_date, hca.cr

14、eated_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_t

15、ype = 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(+)-客户收款方法SQLSELECT receipt_method_nameFROM hz_cust_accou

16、nts hca, ra_cust_receipt_methods rcrm, ar_receipt_methods armWHERE hca.cust_account_id = rcrm.customer_id AND rcrm.receipt_method_id = arm.receipt_method_idORDER BY rcrm.creation_date;-客户账户层银行账户信息SQLSELECT hca.cust_account_id cust_account_id, hp.party_id party_id, bank.party_id bank_id, bank.party_n

17、ame bank_name, branch.party_id branch_id, branch.party_name bank_branch_name, ieba.bank_account_num bank_account_numFROM hz_cust_accounts hca, hz_parties hp, iby_account_owners iao, iby_ext_bank_accounts ieba, hz_parties bank, hz_parties branchWHERE hca.party_id = hp.party_id AND hp.party_id = iao.a

18、ccount_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;-客户开户行地址信息SQLSELECT hl.country | - | vince | - | hl.city | - | hl.address1 | - | hl.address2 | - | hl.addre

19、ss3 | - | hl.address4 bank_addressFROM hz_party_sites hps, hz_locations hlWHERE hps.location_id = hl.location_idORDER BY hps.creation_date;-客户账户层联络人信息：联络人、手机和Email SQLSELECT 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 | |

20、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_rela

21、tionships 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.o

22、wner_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_PAR

23、TIES 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(+) = 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.

24、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.o

25、rg_id org_id, hl.country country, vince 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_up

26、dated_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信息:p

27、hone,mobile, 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

28、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_c

29、ust_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.relati

30、onship_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

31、AND hcpe.contact_point_type(+) = 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_us

32、e_id, hcasa.party_site_id, hcsua.site_use_code, hcsua.primary_flag, hcsua.location, _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 AN

33、D 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;订单OE-select * from oe_order_headers_all 销售头select *

34、 from oe_order_lines_all 销售行select * from wsh_new_deliveries 发送select * from wsh_delivery_detailsselect * 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

35、_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.ca

36、ncelled_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 c

37、ust.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 anization_id = 141 and l.flow_status_code = ol.LOOKUP_CODE and ol.LOOKUP_

38、TYPE = LINE_FLOW_STATUS AND CUST.ACCOUNT_NUMBER IN (91010072, 91010067, 91010036) order by party_name，收货地，销售订单;采购恳求PR-恳求单头 以电网组织ORG_ID=112 内部恳求 为例SELECT PRH.REQUISITION_HEADER_ID 恳求单头ID, PRH.PREPARER_ID, PRH.Org_Id OU_ID, PRH.SEGMENT1 恳求单编号, PRH.Creation_Date 创建日期, PRH.Created_By 编制人ID,

39、 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_

40、ID AND PRH.ORG_ID = 112 AND PRH.SEGMENT1 =- 内部恳求 恳求单头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

41、_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 PR

42、L.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_B

43、y 编制人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 P

44、RH.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 =-(销售订单记录有对方 OU_ID,恳求单关键字SOURCE_DOCUMENT_ID 恳求单号SOURCE_DOCEMENT_REF)*综合查询类*-恳求单头综合查询 进限制只能查询 -电网组织ORG_ID=112)SELECT PRH.REQUISITIO

45、N_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,

46、 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_Organizati

47、on_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 AN

48、D 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采购订单PO-采购单头信息 TYPE_LOOKUP_CODE=STANDARD 以供应处OU ORG_ID=119 采购单= 为例-类型阐明 TYPE_LOOKUP_CODE=S

49、TANDARD为采购单 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_LOCA

50、TION_ID 本方收单地ID, POH.CREATION_DATE 创建日期, POH.APPROVED_FLAG 审批YN, POH.APPROVED_DATE 审批日期, POHMENTS 采购单阐明, 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_

51、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 = /* FND_USER FU, per_people_f PP 用户相关表 po_agents_n

52、ame_v 采购员视图 PO_AGENTS.AGENT_ID = PER_ALL_PEOPLE_F.PERSON_ID 采购员相关表 ap_suppliers 供应商主表 */ - POH.SEGMENT1 =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

53、.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_L

54、ocations_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，所分配给供应处组织的物料，存在采购协议，但缺失采购

55、员或缺失仓库；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_A

56、LL 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.Glob

57、al_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 MI

58、SD.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_cod

59、e 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_agentsselect * from po_vendors select * from po_vendor_sit

60、es_all库存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.PURCHASI