OracleApps QP Queries

--- Price List Query

select
LH.name,
(select Flex_Value  from apps.FND_FLEX_VALUES_VL where flex_value_set_id=1009583 and description=LH.name) ValueSetCode,
LH.currency_code,
msib.segment1  ITEM,
LL.operand PRICE,
LL.start_date_active,
LL.end_date_active,
(Select user_name||'.'||description from applsys.FND_user where user_id = LL.last_updated_by) Last_Updated_by,
(Select user_name||'.'||description from applsys.FND_user where user_id = LL.created_by) created_by,
trunc(LL.creation_date) Created_on
from
apps.qp_secu_list_headers_v LH,
apps.qp_list_lines_v LL,
apps.mtl_system_items_b msib
where
LH.List_header_id=LL.List_header_id --and
--LL.end_date_active = '31-DEC-2012'
---and (select segment1 from apps.mtl_system_items_b where organization_id = 89 and inventory_item_id = LL.product_id) in  ('29024468','29026516','29026519')
and LH.list_header_id = (select list_header_id from apps.qp_secu_list_headers_v where name = 'GEEU_UPD_HKD_TP_LIST_USD_1')
--and lh.name = 'GEAS_770410_801110_TP_LIST_USD_1'
and msib.organization_id = 89 and msib.inventory_item_id = LL.product_id
and msib.segment1 in
(
'29060537','29089547'
)
order by
LH.name, item,7;

-- Price List Qualifiers

select 

qq.list_header_id, lh.name "PL_Name",

qq.qualifier_grouping_no "Grp",

qq.qualifier_context,

--qq.qualifier_attribute, 

qs.segment_code, qq.comparision_operator_code "Operator",

(decode (qs.segment_code,

          'BILL_TO',

            (SELECT b.org_id ||' / '|| c.account_number ||' / '|| d.party_name ||' / '|| b.site_use_id ||' / '|| b.location

                    FROM  ar.hz_cust_site_uses_all b, ar.hz_cust_accounts c, ar.hz_parties d, apps.hz_cust_acct_sites_all e 

                    WHERE c.party_id = d.party_id and b.cust_acct_site_id = e.cust_acct_site_id and e.cust_account_id = c.cust_account_id and b.site_use_id = qq.qualifier_attr_value

            ),

          'Shipping organization Id',

            (select ou.organization_id ||' / '||ou.name from apps.hr_operating_units ou  where ou.organization_id = qq.qualifier_attr_value) ,

            --'B',

          'ORDER_TYPE',

            (select NAME from APPS.OE_TRANSACTION_TYPES_TL tt where tt.transaction_type_id = qq.qualifier_attr_value

            ),

          'Customer Id',

            (select c.account_number ||' / '||d.party_name from ar.hz_cust_accounts c, ar.hz_parties d where c.cust_account_id = qq.qualifier_attr_value and c.party_id = d.party_id)

        )

  ) val,

qq.qualifier_attr_value,

qq.qualifier_precedence,

qq.creation_date, qq.created_by, qq.start_date_active, qq.end_date_active

from

apps.qp_qualifiers_v qq, apps.qp_prc_contexts_b ct , apps.qp_segments_b qs, apps.qp_secu_list_headers_v LH

where qq.list_header_id = lh.list_header_id  

--and qq.list_header_id = 10849462

and lh.name = 'GEEU_UPD_BES_TP_LIST_EUR_1'

and ct.prc_context_type ='QUALIFIER'

and qq.qualifier_context = ct.prc_context_code

and qs.prc_context_id =  ct.prc_context_id

and qs.segment_mapping_column = qq.qualifier_attribute

order by

2,3

;