Product Rent-Primary Price list
Price List can be imagined as a table of Item vs Item Price. Every Item must have a price. Items are integral part of Inventory and Order Management.Query here below is joins between customer accounts,items,price list and there code combination id's in GL tables.
SELECT DISTINCT hcsua.price_list_id,
qlh.NAME pricelist_name,
hcaa.account_number,
hcaa.account_name,
hcsua.site_use_code,
hcsua.LOCATION,
loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.city,
loc.postal_code,
loc.state,
abc.flex_value "PRODUCT",
abc.description "PRODUCT DESCRIPTION",
party_site.party_site_number,
NULL precedence,
qlh.currency_code currency,
qlh.attribute1 pl_type,
qll.product_attr_val_disp item_number,
(SELECT description
FROM apps.mtl_system_items
WHERE segment1 = qll.product_attr_val_disp
AND ROWNUM = 1) item_description,
qll.operand price,
qll.product_uom_code uom,
qll.attribute1 free_days,
qll.attribute2 progressive_days,
qll.attribute3 cylinder_group_code,
qll.attribute4 min_charge,
qll.attribute5 max_charge,
qll.attribute6 EXEMPT
FROM apps.hz_cust_accounts_all hcaa,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_cust_site_uses_all hcsua,
apps.hz_locations loc,
apps.hz_party_sites party_site,
apps.hz_parties hp,
apps.qp_list_headers qlh,
apps.qp_list_lines_v qll,
apps.mtl_system_items_b msib,
apps.gl_code_combinations gcc,
--apps.mtl_item_categories_v micat,
apps.fnd_flex_values_vl abc
WHERE hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND loc.location_id = party_site.location_id
AND hcasa.party_site_id = party_site.party_site_id
AND hp.party_id = party_site.party_id
AND hcsua.price_list_id = qlh.list_header_id
AND qlh.list_header_id = hcsua.price_list_id
AND qlh.list_header_id = qll.list_header_id(+)
AND NVL(qll.end_date_active, SYSDATE + 1) > SYSDATE
AND NVL(qlh.end_date_active, SYSDATE + 1) > SYSDATE
AND gcc.code_combination_id = msib.cost_of_sales_account
AND abc.flex_value_set_id = P_flexfield_id
AND gcc.segment7 = abc.flex_value
AND TO_CHAR(msib.inventory_item_id) = qll.product_id
--AND msib.organization_id = qll.organization_id
AND msib.organization_id = P_organization_id
AND qll.product_id IS NOT NULL
AND hcaa.account_number=P_customer_account_number
