Oracle EBS 常用查询 － 价格表查询

--根据价格表名称查询价格表头信息Select Qp_List_Headers_Tl.Name As Price_List_Name, Qp_List_Headers_Tl.Description As Price_List_Description, Qp_List_Headers_Tl.Version_No As Price_List_Version,  Qp_List_Headers_B.List_Header_Id, Qp_List_Headers_B.Start_Date_Active, Qp_List_Headers_B.End_Date_Active, Qp_List_Headers_B.Currency_Code,  Qp_List_Headers_B.global_flag, Qp_List_Headers_B.creation_date, Qp_List_Headers_B.last_update_dateFrom Qp_List_Headers_BInner Join Qp_List_Headers_Tl On Qp_List_Headers_Tl.List_Header_Id = Qp_List_Headers_B.List_Header_IdWHERE Qp_List_Headers_Tl.name='PRICE_LIST_NAME';

--根据价格表名称查询价格表明细Select Qp_List_Lines.*From Qp_List_Linesleft join qp_list_headers_tl on qp_list_headers_tl.list_header_id = Qp_List_Lines.list_header_idWHERE name='PRICE_LIST_NAME'ORDER BY LIST_LINE_NO;

--根据价格表名称和限定词QUALIFIER查询到使用该价格表的客户信息SELECT HZ_CUST_ACCOUNTS_ALL.PRICE_LIST_ID, QP_LIST_HEADERS_TL.NAME AS PRICE_LIST_NAME,  qp_list_headers_tl.DESCRIPTION as price_list_desc, qp_list_headers_tl.VERSION_NO as price_list_version,  HZ_CUST_ACCOUNTS_ALL.PARTY_ID, HZ_CUST_ACCOUNTS_ALL.CUST_ACCOUNT_ID, hz_cust_accounts_all.account_numberFrom Hz_Cust_Accounts_AllInner Join Qp_Qualifiers On Qp_Qualifiers.Qualifier_Attr_Value = Hz_Cust_Accounts_All.Cust_Account_IdInner Join Qp_List_Headers_Tl On Qp_List_Headers_Tl.List_Header_Id = Qp_Qualifiers.List_Header_IdWHERE Qp_List_Headers_Tl.name='PRICE_LIST_NAME';

--根据客户代码及组织查询所使用的价格多语言表头信息SELECT qp_list_headers_tl.LIST_HEADER_ID, qp_list_headers_tl.LANGUAGE, qp_list_headers_tl.SOURCE_LANG, qp_list_headers_tl.NAME, qp_list_headers_tl.DESCRIPTION, qp_list_headers_tl.VERSION_NO, hz_cust_site_uses_all.*FROM hz_cust_accounts_allINNER JOIN hz_cust_acct_sites_all ON hz_cust_accounts_all.cust_account_id = hz_cust_acct_sites_all.cust_account_idINNER JOIN hz_cust_site_uses_all ON hz_cust_acct_sites_all.CUST_ACCT_SITE_ID = hz_cust_site_uses_all.CUST_ACCT_SITE_ID AND hz_cust_site_uses_all.site_use_code='BILL_TO' AND hz_cust_site_uses_all.STATUS='A'INNER JOIN qp_list_headers_tl ON qp_list_headers_tl.list_header_id = hz_cust_site_uses_all.price_list_idWHERE hz_cust_accounts_all.account_number='CUSTOMER_ACCOUNT_ID' AND hz_cust_acct_sites_all.org_id = ORG_ID;

--根据客户代码及组织查询所使用的价格表头信息SELECT qp_list_headers_b.*FROM hz_cust_accounts_allINNER JOIN hz_cust_acct_sites_all ON hz_cust_accounts_all.cust_account_id = hz_cust_acct_sites_all.cust_account_idINNER JOIN hz_cust_site_uses_all ON hz_cust_acct_sites_all.CUST_ACCT_SITE_ID = hz_cust_site_uses_all.CUST_ACCT_SITE_ID AND hz_cust_site_uses_all.site_use_code='BILL_TO' AND hz_cust_site_uses_all.STATUS='A'INNER JOIN qp_list_headers_b ON qp_list_headers_b.list_header_id = hz_cust_site_uses_all.price_list_idWHERE hz_cust_accounts_all.account_number='CUSTOMER_ACCOUNT_ID' AND hz_cust_acct_sites_all.org_id = ORG_ID;

--根据客户代码及组织查询所使用的价格表行信息SELECT hz_cust_acct_sites_all.org_id AS ou_id, mtl_system_items_b.organization_id as io_id,  PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTR_VALUE as item_id, mtl_system_items_b.SEGMENT1 as Item_Name,  qp_pricing_attributes.PRODUCT_UOM_CODE, qp_list_lines.*FROM hz_cust_accounts_allINNER JOIN hz_cust_acct_sites_all ON hz_cust_accounts_all.cust_account_id = hz_cust_acct_sites_all.cust_account_idINNER JOIN hz_cust_site_uses_all ON hz_cust_acct_sites_all.CUST_ACCT_SITE_ID = hz_cust_site_uses_all.CUST_ACCT_SITE_ID AND hz_cust_site_uses_all.site_use_code='BILL_TO' AND hz_cust_site_uses_all.STATUS='A'INNER JOIN qp_list_lines ON qp_list_lines.list_header_id = hz_cust_site_uses_all.price_list_idINNER JOIN qp_pricing_attributes ON qp_pricing_attributes.LIST_LINE_ID = qp_list_lines.LIST_LINE_IDINNER JOIN mtl_system_items_b ON mtl_system_items_b.inventory_item_id = qp_pricing_attributes.PRODUCT_ATTR_VALUEINNER JOIN mtl_parameters ON mtl_parameters.organization_id = mtl_system_items_b.organization_idINNER JOIN org_organization_definitions ON OPERATING_UNIT = hz_cust_acct_sites_all.org_idWHERE hz_cust_accounts_all.account_number='CUSTOMER_ACCOUNT_ID' AND hz_cust_acct_sites_all.org_id = ORG_ID AND mtl_system_items_b.ORGANIZATION_ID=ORG_IDORDER BY LIST_LINE_NO;

--根据价格行号查询Price Break信息SELECT PRODUCT_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTR_VALUE_FROM, PRICING_ATTR_VALUE_TO, ql_pb.OPERANDFROM qp_list_lines ql_pbINNER JOIN qp_pricing_attributes qpa ON ql_pb.LIST_LINE_ID = qpa.LIST_LINE_IDINNER JOIN qp_rltd_modifiers qrm ON qrm.to_rltd_modifier_id = qpa.LIST_LINE_IDWHERE  ql_pb.list_line_type_code IN ('SUR', 'DIS', 'PLL')       AND qrm.rltd_modifier_grp_type = 'PRICE BREAK'       AND qrm.from_rltd_modifier_id = MODIFIER_ID