--根据价格表名称查询价格表头信息
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
原文链接:https://www.f2er.com/oracle/213113.html