oracle关联查询月价格,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_date

From Qp_List_Headers_B

Inner Join Qp_List_Headers_Tl On Qp_List_Headers_Tl.List_Header_Id = Qp_List_Headers_B.List_Header_Id

WHERE Qp_List_Headers_Tl.name='PRICE_LIST_NAME';

--根据价格表名称查询价格表明细

Select Qp_List_Lines.*

From Qp_List_Lines

left join qp_list_headers_tl on qp_list_headers_tl.list_header_id = Qp_List_Lines.list_header_id

WHERE 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_number

From Hz_Cust_Accounts_All

Inner Join Qp_Qualifiers On Qp_Qualifiers.Qualifier_Attr_Value = Hz_Cust_Accounts_All.Cust_Account_Id

Inner Join Qp_List_Headers_Tl On Qp_List_Headers_Tl.List_Header_Id = Qp_Qualifiers.List_Header_Id

WHERE 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_all

INNER JOIN hz_cust_acct_sites_all ON hz_cust_accounts_all.cust_account_id = hz_cust_acct_sites_all.cust_account_id

INNER 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_id

WHERE 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_all

INNER JOIN hz_cust_acct_sites_all ON hz_cust_accounts_all.cust_account_id = hz_cust_acct_sites_all.cust_account_id

INNER 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_id

WHERE 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_all

INNER JOIN hz_cust_acct_sites_all ON hz_cust_accounts_all.cust_account_id = hz_cust_acct_sites_all.cust_account_id

INNER 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_id

INNER JOIN qp_pricing_attributes ON qp_pricing_attributes.LIST_LINE_ID = qp_list_lines.LIST_LINE_ID

INNER JOIN mtl_system_items_b ON mtl_system_items_b.inventory_item_id = qp_pricing_attributes.PRODUCT_ATTR_VALUE

INNER JOIN mtl_parameters ON mtl_parameters.organization_id = mtl_system_items_b.organization_id

INNER JOIN org_organization_definitions ON OPERATING_UNIT = hz_cust_acct_sites_all.org_id

WHERE 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_ID

ORDER BY LIST_LINE_NO;

--根据价格行号查询Price Break信息

SELECT PRODUCT_ATTRIBUTE_CONTEXT,PRICING_ATTRIBUTE_CONTEXT,PRICING_ATTR_VALUE_FROM,PRICING_ATTR_VALUE_TO,ql_pb.OPERAND

FROM qp_list_lines ql_pb

INNER JOIN qp_pricing_attributes qpa ON ql_pb.LIST_LINE_ID = qpa.LIST_LINE_ID

INNER JOIN qp_rltd_modifiers qrm ON qrm.to_rltd_modifier_id = qpa.LIST_LINE_ID

WHERE 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

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。