SQL for Customer Account with Price List
select hca.account_number
,hp.party_name
, hps.party_site_number
–,hl.city
–,hl.state
,qplh.name price_list_name
,trim(to_char(qplh.start_date_active,’DD-Month’))||’-‘||trim(to_char(qplh.start_date_active,’YYYY’)) price_list_effective_date
–,qplh.end_date_active
,msib.segment1 bill_code
,msib.description Bill_code_desc
,qpll.operand rate
from
apps.hz_cust_accounts hca
, apps.hz_parties hp
,apps.hz_cust_acct_sites_all hcasa
, apps.hz_party_sites hps
,apps.hz_locations hl
, apps.hz_cust_site_uses_all hcsua
, apps.qp_list_headers qplh
, apps.qp_list_lines qpll
, apps.qp_pricing_attributes qppa
, apps.mtl_system_items_b msib
where 1=1
and account_number in (
‘22153.003865’)—‘22153.003865’–‘22152.002431’– ‘22153.003865’
and hca.party_id = hp.party_id
and hca.cust_account_id = hcasa.cust_account_id
and hcasa.party_site_id = hps.party_site_id
and hps.location_id = hl.location_id
and hps.party_id = hp.party_id
and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
and hcsua.site_use_code = ‘SHIP_TO’
and hcsua.price_list_id = qplh.list_header_id
and qplh.attribute1 = ‘ARCUS’
and hca.attribute3 = ‘ARCUS’
and qpll.list_header_id = qplh.list_header_id
and qpll.end_date_active is null
and qpll.list_line_id = qppa.list_line_id–7788676
and product_attribute_context = ‘ITEM’
and qppa.product_attr_value = msib.inventory_item_id
and msib.organization_id = <<Org_id>>;
