QP Price List Upload

➜ Back to Library
Description

QP Price List Upload creates and updates Oracle Advanced Pricing standard price lists from Excel – the price-list header plus its lines (item or item-category list prices), price breaks, line pricing attributes, header qualifiers and secondary price list assignments – with selective deletion of individual sub-entities.

When to use it

  • Load brand-new standard price lists with many item or item-category list prices in one pass.
  • Mass-update prices on existing price lists.
  • Add or maintain price breaks (quantity-based pricing tiers) across many lines.
  • Attach header qualifiers in bulk, including copying a whole qualifier group onto a price list.
  • Assign secondary price lists to many price lists at once.
  • Replace prices effective a new date while auto-end-dating the superseded lines.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the price list’s operating unit.
  • The inventory items (or item categories) exist before pricing them.
  • Reference values used – currency, payment/freight terms, ship method, UOMs, qualifier groups, pricing attributes – exist and are valid.

Step 1 – Choose a mode, set the parameters and download

In Blitz Report, open QP Price List Upload, choose an Upload Mode (Create, Update – the default – downloads existing price-list data to edit; Create gives an empty template), and set the parameters:

ParameterPurpose
Upload ModeCreate or Create, Update. Default is Create, Update.
End Date Matching List Lines?Yes auto-end-dates an existing active matching line when a new line is created.
Price List / Price List Like / Product Value / datesRestrict which existing price-list data is downloaded.
Download Price List Lines / Pricing Attributes / Qualifiers / Secondary Price ListsChoose which child rows to include in the download.

Run the upload to download and open the Excel file. Each entity is automatically handled as a create or an update depending on whether it already exists.

Step 2 – Enter the price lists

One row can carry the header plus a line, a break, a pricing attribute, a qualifier and a secondary price list. On the header enter the Name, Currency and effective dates; on lines the Line Product Value (item), Line Product UOM and Line Value (the list price); add price-break columns for quantity tiers.

Step 3 – Delete sub-entities (optional)

Set the matching Delete column (Delete Line, Delete Price Break, Delete Pricing Attribute, Delete Qualifier, Delete Secondary Price List) to Yes to remove that sub-entity. The price list header itself is never deleted by this upload.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.

Step 5 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates or updates the price list, lines and child records. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created and updated price lists with their lines, price breaks, pricing attributes, qualifiers and secondary price lists.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Does it create the price list header and the lines together?
Yes – one row can carry the header plus a line, break, pricing attribute, qualifier and secondary price list; the upload routes each part to the right operation.

Can I delete a whole price list with this upload?
No – it deletes only individual sub-entities via the Delete columns. The header itself is never deleted.

What is “Line No” for – does it go to Oracle?
No. It is only used by the upload to tell apart multiple lines that share the same product, UOM and start date but have different pricing attributes.

How does it know whether to create or update?
It looks each entity up; found = update, not found = create, unchanged = no operation.

How do I supersede prices on a date?
Set End Date Matching List Lines? = Yes; new lines end-date the matching active lines, and downloaded lines come down flagged as Create to use as a starting point.

Troubleshooting

MessageCauseWhat to do
A Price List with this name and a different Orig Sys Ref already existsA list with that name exists under a different reference.Use the existing list (download it) or change the name.
Price List no longer existsThe referenced price list was deleted/changed between download and upload.Re-download the current data and re-apply your changes.
Upload Failed (Oracle pricing message)The Oracle pricing API rejected the row (invalid value, missing field, bad attribute/UOM/currency).Read the appended Oracle message, fix the cell, and re-upload that row.
Row shows “None” / no operationThe downloaded values were not changed.Expected – edit a value if you intended a change.
Qualifier group copy did nothingThe group was specified on a row that already had downloaded qualifier detail.Use a row with no existing qualifier detail and specify only the qualifier group to copy all its qualifiers.
select
case when :p_override_list_line = 'Y' then xxen_upload.action_meaning(xxen_upload.action_create) else null end action_,
case when :p_override_list_line = 'Y' then xxen_upload.status_meaning(xxen_upload.status_new) else null end status_,
case when :p_override_list_line = 'Y' then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) else null end message_,
null request_id_,
null modified_columns_,
:p_upload_mode upload_mode_,
:p_override_list_line override_list_line_,
to_number(null) price_list_row_id,
to_number(null) qualifier_group_row_id,
to_number(null) qualifier_row_id,
to_number(null) secondary_price_list_row_id,
to_number(null) price_list_line_row_id,
to_number(null) price_break_line_row_id,
to_number(null) pricing_attribute_row_id,
qp.*
from
(
--
-- Q1 List Header Qualifiers
--
select
-- Price List Header
qslhv.pte_code pte_code,
qslhv.source_system_code source_system_code,
xxen_util.meaning(qslhv.list_type_code,'LIST_TYPE_CODE',661) type,
qslhv.orig_system_header_ref list_orig_sys_ref,
qslhv.name name,
qslhv.description description,
xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global,
qp_util.get_ou_name(qslhv.orig_org_id) operating_unit,
qslhv.currency_code currency,
(select qclv.name from qp_currency_lists_vl qclv where qslhv.currency_header_id=qclv.currency_header_id) multi_currency_conversion,
qslhv.rounding_factor round_to,
xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) active,
qslhv.start_date_active effective_from,
qslhv.end_date_active effective_to,
xxen_util.meaning(qslhv.freight_terms_code,'FREIGHT_TERMS',660) freight_terms,
qslhv.ship_method_code ship_method,
(select rtt.name from ra_terms_tl rtt where qslhv.terms_id=rtt.term_id and rtt.language(+)=userenv('lang')) payment_term,
xxen_util.meaning(qslhv.mobile_download,'YES_NO',0) mobile_download,
qslhv.comments comments,
-- Price List DFF Attributes
xxen_util.display_flexfield_context(661,'QP_LIST_HEADERS',qslhv.context) list_header_dff_context,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE1',qslhv.row_id,qslhv.attribute1) list_header_attribute1,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE2',qslhv.row_id,qslhv.attribute2) list_header_attribute2,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE3',qslhv.row_id,qslhv.attribute3) list_header_attribute3,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE4',qslhv.row_id,qslhv.attribute4) list_header_attribute4,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE5',qslhv.row_id,qslhv.attribute5) list_header_attribute5,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE6',qslhv.row_id,qslhv.attribute6) list_header_attribute6,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE7',qslhv.row_id,qslhv.attribute7) list_header_attribute7,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE8',qslhv.row_id,qslhv.attribute8) list_header_attribute8,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE9',qslhv.row_id,qslhv.attribute9) list_header_attribute9,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE10',qslhv.row_id,qslhv.attribute10) list_header_attribute10,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE11',qslhv.row_id,qslhv.attribute11) list_header_attribute11,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE12',qslhv.row_id,qslhv.attribute12) list_header_attribute12,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE13',qslhv.row_id,qslhv.attribute13) list_header_attribute13,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE14',qslhv.row_id,qslhv.attribute14) list_header_attribute14,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE15',qslhv.row_id,qslhv.attribute15) list_header_attribute15,
-- Price List Lines
null line_orig_sys_ref,
to_number(null) line_no,
null line_product_context,
null line_product_attribute,
null line_product_value,
null line_product_attribute_desc,
null line_product_uom,
null line_primary_uom_flag,
null line_type,
null line_price_break_type,
null line_application_method,
null line_accumulation_attribute,
null line_break_uom,
null line_break_uom_attribute,
to_number(null) line_value,
null line_dynamic_formula,
null line_static_formula,
to_date(null) line_start_date,
to_date(null) line_end_date,
to_number(null) line_precedence,
null delete_line,
-- Price Breaks
null price_break_orig_sys_ref,
null price_break_pricing_context,
null price_break_pricing_attribute,
to_number(null) price_break_value_from,
to_number(null) price_break_value_to,
to_number(null) price_break_price,
null price_break_application_method,
null price_break_formula,
to_number(null) price_break_recurring_value,
null delete_price_break,
-- Price List Line Pricing Attributes
null pricing_attribute_orig_sys_ref,
null pricing_attribute_context,
null pricing_attribute,
null pricing_attribute_operator,
null pricing_attribute_value_from,
null pricing_attribute_val_fr_desc,
null pricing_attribute_value_to,
null delete_pricing_attribute,
-- Price List Line DFF Attributes
null list_line_dff_context,
null list_line_attribute1,
null list_line_attribute2,
null list_line_attribute3,
null list_line_attribute4,
null list_line_attribute5,
null list_line_attribute6,
null list_line_attribute7,
null list_line_attribute8,
null list_line_attribute9,
null list_line_attribute10,
null list_line_attribute11,
null list_line_attribute12,
null list_line_attribute13,
null list_line_attribute14,
null list_line_attribute15,
-- Price List Qualifiers
xxen_qp_upload.get_orig_sys_ref('QUALIFIER',qqv.qualifier_id) qualifier_orig_sys_ref,
qqv.rule_name qualifier_group,
to_number(null) qualifier_group_qualifier_id,
qqv.qualifier_grouping_no qualifier_grouping_number,
qp_util.get_context('QP_ATTR_DEFNS_QUALIFIER',qqv.qualifier_context) qualifier_context,
qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_QUALIFIER',qqv.qualifier_context,qqv.qualifier_attribute) qualifier_attribute,
xxen_util.meaning(qqv.qualify_hier_descendents_flag,'YES_NO',0) qualifier_applies_party_hier,
qqv.qualifier_precedence qualifier_precedence,
qqv.comparision_operator_code qualifier_operator,
rtrim(replace(
 qp_util.get_attribute_value
 ('QP_ATTR_DEFNS_QUALIFIER',
  qqv.qualifier_context,
  qp_qp_form_pricing_attr.get_segment_name('QP_ATTR_DEFNS_QUALIFIER',qqv.qualifier_context, qqv.qualifier_attribute),
  qqv.qualifier_attr_value,
  qqv.comparision_operator_code
 ),chr(0),null)
) qualifier_value_from,
rtrim(replace(
 qp_util.get_attribute_value_meaning
 ('QP_ATTR_DEFNS_QUALIFIER',
  qqv.qualifier_context,
  qp_qp_form_pricing_attr.get_segment_name('QP_ATTR_DEFNS_QUALIFIER',qqv.qualifier_context, qqv.qualifier_attribute),
  qqv.qualifier_attr_value,
  qqv.comparision_operator_code
 ),chr(0),null)
) qualifier_value_from_desc,
qp_util.get_attribute_value
('QP_ATTR_DEFNS_QUALIFIER',
 qqv.qualifier_context,
 qp_qp_form_pricing_attr.get_segment_name('QP_ATTR_DEFNS_QUALIFIER',qqv.qualifier_context, qqv.qualifier_attribute),
 qqv.qualifier_attr_value_to,
 qqv.comparision_operator_code
) qualifier_value_to,
qqv.start_date_active qualifier_start_date,
qqv.end_date_active qualifier_end_date,
null delete_qualifier,
-- Qualifier DFF Attributes
xxen_util.display_flexfield_context(661,'QP_QUALIFIERS',qqv.context) qualifier_dff_context,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE1',qqv.row_id,qqv.attribute1) qualifier_attribute1,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE2',qqv.row_id,qqv.attribute2) qualifier_attribute2,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE3',qqv.row_id,qqv.attribute3) qualifier_attribute3,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE4',qqv.row_id,qqv.attribute4) qualifier_attribute4,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE5',qqv.row_id,qqv.attribute5) qualifier_attribute5,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE6',qqv.row_id,qqv.attribute6) qualifier_attribute6,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE7',qqv.row_id,qqv.attribute7) qualifier_attribute7,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE8',qqv.row_id,qqv.attribute8) qualifier_attribute8,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE9',qqv.row_id,qqv.attribute9) qualifier_attribute9,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE10',qqv.row_id,qqv.attribute10) qualifier_attribute10,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE11',qqv.row_id,qqv.attribute11) qualifier_attribute11,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE12',qqv.row_id,qqv.attribute12) qualifier_attribute12,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE13',qqv.row_id,qqv.attribute13) qualifier_attribute13,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE14',qqv.row_id,qqv.attribute14) qualifier_attribute14,
xxen_util.display_flexfield_value(661,'QP_QUALIFIERS',qqv.context,'ATTRIBUTE15',qqv.row_id,qqv.attribute15) qualifier_attribute15,
-- Secondary Price Lists
null sec_price_list_orig_sys_ref,
null secondary_price_list,
to_number(null) secondary_price_list_precedenc,
null delete_secondary_price_list,
-- IDs
qslhv.list_header_id price_list_id,
qqv.qualifier_id qualifier_id,
to_number(null) sec_price_list_qualifier_id,
to_number(null) price_list_line_id,
to_number(null) price_break_line_id,
to_number(null) pricing_attribute_id,
'Qualifier' row_type,
1 upload_row
from
qp_secu_list_headers_vl qslhv,
qp_qualifiers_v qqv
where
1=1 and
:p_show_qualifiers = 'Y' and
nvl(qslhv.pte_code,'ORDFUL') <> 'LOGSTX' and
nvl(qslhv.pte_code,'ORDFUL') = :p_pte_code and
nvl(qslhv.source_system_code,'QP') = :p_appl_sn and
nvl(qslhv.list_source_code,'null') not in ('BSO','OKS') and
qslhv.update_flag = 'Y' and
(qslhv.global_flag = 'Y' or mo_global.check_access(qslhv.orig_org_id) = 'Y') and
--
qslhv.list_header_id = qqv.list_header_id and
qqv.list_line_id = -1 and
qqv.qualifier_context != 'MODLIST' and
qqv.qualifier_attribute != 'QUALIFIER_ATTRIBUTE4' and
--
exists
(select
 null
 from
 qp_list_lines_v qllv
 where
 2=2 and
 qslhv.list_header_id = qllv.list_header_id and
 qllv.list_line_type_code in ('PBH','PLL') and
 qllv.product_attribute_context = 'ITEM' and
 ( (qllv.product_attribute = 'PRICING_ATTRIBUTE1' and
    exists (select null from mtl_system_items msi where msi.inventory_item_id = to_number(decode(translate(qllv.product_attr_value,'.0123456789','.'),null,qllv.product_attr_value,null)) and msi.organization_id = qp_util.get_item_validation_org)
   ) or
   (qllv.product_attribute != 'PRICING_ATTRIBUTE1'
   )
 )
)
--
union
--
-- Q2 List Header Secondary Price Lists
--
select
-- Price List Header
qslhv.pte_code pte_code,
qslhv.source_system_code source_system_code,
xxen_util.meaning(qslhv.list_type_code,'LIST_TYPE_CODE',661) type,
qslhv.orig_system_header_ref list_orig_sys_ref,
qslhv.name name,
qslhv.description description,
xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global,
qp_util.get_ou_name(qslhv.orig_org_id) operating_unit,
qslhv.currency_code currency,
(select qclv.name from qp_currency_lists_vl qclv where qslhv.currency_header_id=qclv.currency_header_id) multi_currency_conversion,
qslhv.rounding_factor round_to,
xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) active,
qslhv.start_date_active effective_from,
qslhv.end_date_active effective_to,
xxen_util.meaning(qslhv.freight_terms_code,'FREIGHT_TERMS',660) freight_terms,
qslhv.ship_method_code ship_method,
(select rtt.name from ra_terms_tl rtt where qslhv.terms_id=rtt.term_id and rtt.language(+)=userenv('lang')) payment_term,
xxen_util.meaning(qslhv.mobile_download,'YES_NO',0) mobile_download,
qslhv.comments comments,
-- Price List DFF Attributes
xxen_util.display_flexfield_context(661,'QP_LIST_HEADERS',qslhv.context) list_header_dff_context,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE1',qslhv.row_id,qslhv.attribute1) list_header_attribute1,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE2',qslhv.row_id,qslhv.attribute2) list_header_attribute2,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE3',qslhv.row_id,qslhv.attribute3) list_header_attribute3,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE4',qslhv.row_id,qslhv.attribute4) list_header_attribute4,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE5',qslhv.row_id,qslhv.attribute5) list_header_attribute5,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE6',qslhv.row_id,qslhv.attribute6) list_header_attribute6,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE7',qslhv.row_id,qslhv.attribute7) list_header_attribute7,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE8',qslhv.row_id,qslhv.attribute8) list_header_attribute8,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE9',qslhv.row_id,qslhv.attribute9) list_header_attribute9,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE10',qslhv.row_id,qslhv.attribute10) list_header_attribute10,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE11',qslhv.row_id,qslhv.attribute11) list_header_attribute11,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE12',qslhv.row_id,qslhv.attribute12) list_header_attribute12,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE13',qslhv.row_id,qslhv.attribute13) list_header_attribute13,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE14',qslhv.row_id,qslhv.attribute14) list_header_attribute14,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE15',qslhv.row_id,qslhv.attribute15) list_header_attribute15,
-- Price List Lines
null line_orig_sys_ref,
to_number(null) line_no,
null line_product_context,
null line_product_attribute,
null line_product_value,
null line_product_attribute_desc,
null line_product_uom,
null line_primary_uom_flag,
null line_type,
null line_price_break_type,
null line_application_method,
null line_accumulation_attribute,
null line_break_uom,
null line_break_uom_attribute,
to_number(null) line_value,
null line_dynamic_formula,
null line_static_formula,
to_date(null) line_start_date,
to_date(null) line_end_date,
to_number(null) line_precedence,
null delete_line,
-- Price Breaks
null price_break_orig_sys_ref,
null price_break_pricing_context,
null price_break_pricing_attribute,
to_number(null) price_break_value_from,
to_number(null) price_break_value_to,
to_number(null) price_break_price,
null price_break_application_method,
null price_break_formula,
to_number(null) price_break_recurring_value,
null delete_price_break,
-- Price List Line Pricing Attributes
null pricing_attribute_orig_sys_ref,
null pricing_attribute_context,
null pricing_attribute,
null pricing_attribute_operator,
null pricing_attribute_value_from,
null pricing_attribute_val_fr_desc,
null pricing_attribute_value_to,
null delete_pricing_attribute,
-- Price List Line DFF Attributes
null list_line_dff_context,
null list_line_attribute1,
null list_line_attribute2,
null list_line_attribute3,
null list_line_attribute4,
null list_line_attribute5,
null list_line_attribute6,
null list_line_attribute7,
null list_line_attribute8,
null list_line_attribute9,
null list_line_attribute10,
null list_line_attribute11,
null list_line_attribute12,
null list_line_attribute13,
null list_line_attribute14,
null list_line_attribute15,
-- Price List Qualifiers
null qualifier_orig_sys_ref,
null qualifier_group,
to_number(null) qualifier_group_qualifier_id,
to_number(null) qualifier_grouping_number,
null qualifier_context,
null qualifier_attribute,
null qualifier_applies_party_hier,
to_number(null) qualifier_precedence,
null qualifier_operator,
null qualifier_value_from,
null qualifier_value_from_desc,
null qualifier_value_to,
to_date(null) qualifier_start_date,
to_date(null) qualifier_end_date,
null delete_qualifier,
-- Qualifier DFF Attributes
null qualifier_dff_context,
null qualifier_attribute1,
null qualifier_attribute2,
null qualifier_attribute3,
null qualifier_attribute4,
null qualifier_attribute5,
null qualifier_attribute6,
null qualifier_attribute7,
null qualifier_attribute8,
null qualifier_attribute9,
null qualifier_attribute10,
null qualifier_attribute11,
null qualifier_attribute12,
null qualifier_attribute13,
null qualifier_attribute14,
null qualifier_attribute15,
-- Secondary Price Lists
xxen_qp_upload.get_orig_sys_ref('LIST_HEADER',qsplv.list_header_id) sec_price_list_orig_sys_ref,
qsplv.name secondary_price_list,
qsplv.precedence secondary_price_list_precedenc,
null delete_secondary_price_list,
-- IDs
qslhv.list_header_id price_list_id,
to_number(null) qualifier_id,
qsplv.qualifier_id sec_price_list_qualifier_id,
to_number(null) price_list_line_id,
to_number(null) price_break_line_id,
to_number(null) pricing_attribute_id,
'Secondary Price List' row_type,
2 upload_row
from
qp_secu_list_headers_vl qslhv,
qp_secondary_price_lists_v qsplv
where
1=1 and
:p_show_secondary_price_lists = 'Y' and
nvl(qslhv.pte_code,'ORDFUL') <> 'LOGSTX' and
nvl(qslhv.pte_code,'ORDFUL') = :p_pte_code and
nvl(qslhv.source_system_code,'QP') = :p_appl_sn and
nvl(qslhv.list_source_code,'null') not in ('BSO','OKS') and
qslhv.update_flag = 'Y' and
(qslhv.global_flag = 'Y' or mo_global.check_access(qslhv.orig_org_id) = 'Y') and
--
qslhv.list_header_id = qsplv.parent_price_list_id and
--
exists
(select
 null
 from
 qp_list_lines_v qllv
 where
 2=2 and
 qslhv.list_header_id = qllv.list_header_id and
 qllv.list_line_type_code in ('PBH','PLL') and
 qllv.product_attribute_context = 'ITEM' and
 ( (qllv.product_attribute = 'PRICING_ATTRIBUTE1' and
    exists (select null from mtl_system_items msi where msi.inventory_item_id = to_number(decode(translate(qllv.product_attr_value,'.0123456789','.'),null,qllv.product_attr_value,null)) and msi.organization_id = qp_util.get_item_validation_org)
   ) or
   (qllv.product_attribute != 'PRICING_ATTRIBUTE1'
   )
 )
)
--
union
--
-- Q3 Price Lists Lines - Pricing Attributes
--
select
-- Price List Header
qslhv.pte_code pte_code,
qslhv.source_system_code source_system_code,
xxen_util.meaning(qslhv.list_type_code,'LIST_TYPE_CODE',661) type,
qslhv.orig_system_header_ref list_orig_sys_ref,
qslhv.name name,
qslhv.description description,
xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global,
qp_util.get_ou_name(qslhv.orig_org_id) operating_unit,
qslhv.currency_code currency,
(select qclv.name from qp_currency_lists_vl qclv where qslhv.currency_header_id=qclv.currency_header_id) multi_currency_conversion,
qslhv.rounding_factor round_to,
xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) active,
qslhv.start_date_active effective_from,
qslhv.end_date_active effective_to,
xxen_util.meaning(qslhv.freight_terms_code,'FREIGHT_TERMS',660) freight_terms,
qslhv.ship_method_code ship_method,
(select rtt.name from ra_terms_tl rtt where qslhv.terms_id=rtt.term_id and rtt.language(+)=userenv('lang'))payment_term,
xxen_util.meaning(qslhv.mobile_download,'YES_NO',0) mobile_download,
qslhv.comments comments,
-- Price List DFF Attributes
xxen_util.display_flexfield_context(661,'QP_LIST_HEADERS',qslhv.context) list_header_dff_context,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE1',qslhv.row_id,qslhv.attribute1) list_header_attribute1,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE2',qslhv.row_id,qslhv.attribute2) list_header_attribute2,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE3',qslhv.row_id,qslhv.attribute3) list_header_attribute3,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE4',qslhv.row_id,qslhv.attribute4) list_header_attribute4,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE5',qslhv.row_id,qslhv.attribute5) list_header_attribute5,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE6',qslhv.row_id,qslhv.attribute6) list_header_attribute6,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE7',qslhv.row_id,qslhv.attribute7) list_header_attribute7,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE8',qslhv.row_id,qslhv.attribute8) list_header_attribute8,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE9',qslhv.row_id,qslhv.attribute9) list_header_attribute9,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE10',qslhv.row_id,qslhv.attribute10) list_header_attribute10,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE11',qslhv.row_id,qslhv.attribute11) list_header_attribute11,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE12',qslhv.row_id,qslhv.attribute12) list_header_attribute12,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE13',qslhv.row_id,qslhv.attribute13) list_header_attribute13,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE14',qslhv.row_id,qslhv.attribute14) list_header_attribute14,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE15',qslhv.row_id,qslhv.attribute15) list_header_attribute15,
-- Price List Lines
xxen_qp_upload.get_orig_sys_ref('LINE',qllv.list_line_id) line_orig_sys_ref,
qllv.list_line_no line_no,
qp_util.get_context('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context) line_product_context,
qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context, qllv.product_attribute) line_product_attribute,
qllv.product_attr_val_disp line_product_value,
xxen_qp_upload.get_product_description(qslhv.pte_code,qp_util.get_context('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context),qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context,qllv.product_attribute),qllv.product_attr_value,qllv.product_attr_val_disp) line_product_attribute_desc,
qllv.product_uom_code line_product_uom,
xxen_util.meaning(qllv.primary_uom_flag,'YES_NO',0) line_primary_uom_flag,
xxen_util.meaning(qllv.list_line_type_code,'LIST_LINE_TYPE_CODE',661) line_type,
xxen_util.meaning(qllv.price_break_type_code,'PRICE_BREAK_TYPE_CODE',661) line_price_break_type,
xxen_util.meaning(qllv.arithmetic_operator,'ARITHMETIC_OPERATOR',661) line_application_method,
replace(qllv.accum_attribute,chr(0),'') line_accumulation_attribute,
qllv.break_uom_code line_break_uom,
qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qllv.break_uom_context,qllv.break_uom_attribute) line_break_uom_attribute,
qllv.operand line_value,
nvl2(qllv.price_by_formula_id,qp_qp_form_pricing_attr.get_formula(qllv.price_by_formula_id),null) line_dynamic_formula,
nvl2(qllv.generate_using_formula_id,qp_qp_form_pricing_attr.get_formula(qllv.generate_using_formula_id),null) line_static_formula,
qllv.start_date_active line_start_date,
qllv.end_date_active line_end_date,
qllv.product_precedence line_precedence,
null delete_line,
-- Price Breaks
null price_break_orig_sys_ref,
null price_break_pricing_context,
null price_break_pricing_attribute,
to_number(null) price_break_value_from,
to_number(null) price_break_value_to,
to_number(null) price_break_price,
null price_break_application_method,
null price_break_formula,
to_number(null) price_break_recurring_value,
null delete_price_break,
-- Price List Line Pricing Attributes
qpa.orig_sys_pricing_attr_ref pricing_attribute_orig_sys_ref,
qp_util.get_context('QP_ATTR_DEFNS_PRICING',qpa.pricing_attribute_context) pricing_attribute_context,
qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qpa.pricing_attribute_context, qpa.pricing_attribute) pricing_attribute,
qpa.comparison_operator_code pricing_attribute_operator,
rtrim(replace(
 qp_util.get_attribute_value
  ('QP_ATTR_DEFNS_PRICING',
   qpa.pricing_attribute_context,
   qp_qp_form_pricing_attr.get_segment_name('QP_ATTR_DEFNS_PRICING',qpa.pricing_attribute_context, qpa.pricing_attribute),
   qpa.pricing_attr_value_from,
   qpa.comparison_operator_code
  ),chr(0),null)
) pricing_attribute_value_from,
rtrim(replace(
 qp_util.get_attribute_value_meaning
 ('QP_ATTR_DEFNS_PRICING',
  qpa.pricing_attribute_context,
  qp_qp_form_pricing_attr.get_segment_name('QP_ATTR_DEFNS_PRICING',qpa.pricing_attribute_context, qpa.pricing_attribute),
  qpa.pricing_attr_value_from,
  qpa.comparison_operator_code
 ),chr(0),null)
) pricing_attribute_val_fr_desc,
qp_util.get_attribute_value
('QP_ATTR_DEFNS_PRICING',
 qpa.pricing_attribute_context,
 qp_qp_form_pricing_attr.get_segment_name('QP_ATTR_DEFNS_PRICING',qpa.pricing_attribute_context, qpa.pricing_attribute),
 qpa.pricing_attr_value_to,
 qpa.comparison_operator_code
) pricing_attribute_value_to,
null delete_pricing_attribute,
-- Price List Line DFF Attributes
xxen_util.display_flexfield_context(661,'QP_LIST_LINES',qllv.context) list_line_dff_context,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE1',qllv.row_id,qllv.attribute1) list_line_attribute1,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE2',qllv.row_id,qllv.attribute2) list_line_attribute2,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE3',qllv.row_id,qllv.attribute3) list_line_attribute3,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE4',qllv.row_id,qllv.attribute4) list_line_attribute4,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE5',qllv.row_id,qllv.attribute5) list_line_attribute5,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE6',qllv.row_id,qllv.attribute6) list_line_attribute6,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE7',qllv.row_id,qllv.attribute7) list_line_attribute7,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE8',qllv.row_id,qllv.attribute8) list_line_attribute8,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE9',qllv.row_id,qllv.attribute9) list_line_attribute9,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE10',qllv.row_id,qllv.attribute10) list_line_attribute10,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE11',qllv.row_id,qllv.attribute11) list_line_attribute11,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE12',qllv.row_id,qllv.attribute12) list_line_attribute12,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE13',qllv.row_id,qllv.attribute13) list_line_attribute13,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE14',qllv.row_id,qllv.attribute14) list_line_attribute14,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE15',qllv.row_id,qllv.attribute15) list_line_attribute15,
-- Price List Qualifiers
null qualifier_orig_sys_ref,
null qualifier_group,
to_number(null) qualifier_group_qualifier_id,
to_number(null) qualifier_grouping_number,
null qualifier_context,
null qualifier_attribute,
null qualifier_applies_party_hier,
to_number(null) qualifier_precedence,
null qualifier_operator,
null qualifier_value_from,
null qualifier_value_from_desc,
null qualifier_value_to,
to_date(null) qualifier_start_date,
to_date(null) qualifier_end_date,
null delete_qualifier,
-- Qualifier DFF Attributes
null qualifier_dff_context,
null qualifier_attribute1,
null qualifier_attribute2,
null qualifier_attribute3,
null qualifier_attribute4,
null qualifier_attribute5,
null qualifier_attribute6,
null qualifier_attribute7,
null qualifier_attribute8,
null qualifier_attribute9,
null qualifier_attribute10,
null qualifier_attribute11,
null qualifier_attribute12,
null qualifier_attribute13,
null qualifier_attribute14,
null qualifier_attribute15,
-- Secondary Price Lists
null sec_price_list_orig_sys_ref,
null secondary_price_list,
to_number(null) secondary_price_list_precedenc,
null delete_secondary_price_list,
-- IDs
qslhv.list_header_id price_list_id,
to_number(null)qualifier_id,
to_number(null) sec_price_list_qualifier_id,
qllv.list_line_id price_list_line_id,
to_number(null) price_break_line_id,
qpa.pricing_attribute_id pricing_attribute_id,
'List Line' row_type,
3 upload_row
from
qp_secu_list_headers_vl qslhv,
qp_list_lines_v qllv,
qp_pricing_attributes qpa
where
1=1 and
2=2 and
:p_show_price_list_lines = 'Y' and
nvl(qslhv.pte_code,'ORDFUL') <> 'LOGSTX' and
nvl(qslhv.pte_code,'ORDFUL') = :p_pte_code and
nvl(qslhv.source_system_code,'QP') = :p_appl_sn and
nvl(qslhv.list_source_code,'null') not in ('BSO','OKS') and
qslhv.update_flag = 'Y' and
(qslhv.global_flag = 'Y' or mo_global.check_access(qslhv.orig_org_id) = 'Y') and
--
qslhv.list_header_id = qllv.list_header_id and
qllv.list_line_type_code in ('PBH','PLL') and
qllv.product_attribute_context = 'ITEM' and
( (qllv.product_attribute= 'PRICING_ATTRIBUTE1' and
   exists (select null from mtl_system_items msi where msi.inventory_item_id = to_number(decode(translate(qllv.product_attr_value,'.0123456789','.'),null,qllv.product_attr_value,null)) and msi.organization_id = qp_util.get_item_validation_org)
  ) or
  (qllv.product_attribute != 'PRICING_ATTRIBUTE1'
  )
) and
--
nvl2(:p_show_pricing_attributes,qllv.list_line_id,null) = qpa.list_line_id (+) and
qpa.pricing_attribute (+) is not null and
--
( (qllv.list_line_type_code = 'PLL') or
  (qllv.list_line_type_code = 'PBH' and
   (qpa.pricing_attribute is not null or
    not exists (select null from qp_price_breaks_v qpbv where qpbv.parent_list_line_id = qllv.list_line_id)
   )
  )
)
union
--
-- Q4 Price Breaks
--
select
-- Price List Header
qslhv.pte_code pte_code,
qslhv.source_system_code source_system_code,
xxen_util.meaning(qslhv.list_type_code,'LIST_TYPE_CODE',661) type,
qslhv.orig_system_header_ref list_orig_sys_ref,
qslhv.name name,
qslhv.description description,
xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global,
qp_util.get_ou_name(qslhv.orig_org_id) operating_unit,
qslhv.currency_code currency,
(select qclv.name from qp_currency_lists_vl qclv where qslhv.currency_header_id=qclv.currency_header_id) multi_currency_conversion,
qslhv.rounding_factor round_to,
xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) active,
qslhv.start_date_active effective_from,
qslhv.end_date_active effective_to,
xxen_util.meaning(qslhv.freight_terms_code,'FREIGHT_TERMS',660) freight_terms,
qslhv.ship_method_code ship_method,
(select rtt.name from ra_terms_tl rtt where qslhv.terms_id=rtt.term_id and rtt.language(+)=userenv('lang'))payment_term,
xxen_util.meaning(qslhv.mobile_download,'YES_NO',0) mobile_download,
qslhv.comments comments,
-- Price List DFF Attributes
xxen_util.display_flexfield_context(661,'QP_LIST_HEADERS',qslhv.context) list_header_dff_context,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE1',qslhv.row_id,qslhv.attribute1) list_header_attribute1,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE2',qslhv.row_id,qslhv.attribute2) list_header_attribute2,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE3',qslhv.row_id,qslhv.attribute3) list_header_attribute3,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE4',qslhv.row_id,qslhv.attribute4) list_header_attribute4,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE5',qslhv.row_id,qslhv.attribute5) list_header_attribute5,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE6',qslhv.row_id,qslhv.attribute6) list_header_attribute6,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE7',qslhv.row_id,qslhv.attribute7) list_header_attribute7,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE8',qslhv.row_id,qslhv.attribute8) list_header_attribute8,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE9',qslhv.row_id,qslhv.attribute9) list_header_attribute9,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE10',qslhv.row_id,qslhv.attribute10) list_header_attribute10,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE11',qslhv.row_id,qslhv.attribute11) list_header_attribute11,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE12',qslhv.row_id,qslhv.attribute12) list_header_attribute12,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE13',qslhv.row_id,qslhv.attribute13) list_header_attribute13,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE14',qslhv.row_id,qslhv.attribute14) list_header_attribute14,
xxen_util.display_flexfield_value(661,'QP_LIST_HEADERS',qslhv.context,'ATTRIBUTE15',qslhv.row_id,qslhv.attribute15) list_header_attribute15,
-- Price List Lines
xxen_qp_upload.get_orig_sys_ref('LINE',qllv.list_line_id) line_orig_sys_ref,
qllv.list_line_no line_no,
qp_util.get_context('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context) line_product_context,
qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context, qllv.product_attribute) line_product_attribute,
qllv.product_attr_val_disp line_product_value,
xxen_qp_upload.get_product_description(qslhv.pte_code,qp_util.get_context('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context),qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qllv.product_attribute_context,qllv.product_attribute),qllv.product_attr_value,qllv.product_attr_val_disp) line_product_attribute_desc,
qllv.product_uom_code line_product_uom,
xxen_util.meaning(qllv.primary_uom_flag,'YES_NO',0) line_primary_uom_flag,
xxen_util.meaning(qllv.list_line_type_code,'LIST_LINE_TYPE_CODE',661) line_type,
xxen_util.meaning(qllv.price_break_type_code,'PRICE_BREAK_TYPE_CODE',661) line_price_break_type,
xxen_util.meaning(qllv.arithmetic_operator,'ARITHMETIC_OPERATOR',661) line_application_method,
replace(qllv.accum_attribute,chr(0),'') line_accumulation_attribute,
qllv.break_uom_code line_break_uom,
qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qllv.break_uom_context,qllv.break_uom_attribute) line_break_uom_attribute,
qllv.operand line_value,
nvl2(qllv.price_by_formula_id,qp_qp_form_pricing_attr.get_formula(qllv.price_by_formula_id),null) line_dynamic_formula,
nvl2(qllv.generate_using_formula_id,qp_qp_form_pricing_attr.get_formula(qllv.generate_using_formula_id),null) line_static_formula,
qllv.start_date_active line_start_date,
qllv.end_date_active line_end_date,
qllv.product_precedence line_precedence,
null delete_line,
-- Price Breaks
xxen_qp_upload.get_orig_sys_ref('LINE',qpbv.list_line_id) price_break_orig_sys_ref,
qp_util.Get_Context('QP_ATTR_DEFNS_PRICING',qpbv.pricing_attribute_context) price_break_pricing_context,
qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qpbv.pricing_attribute_context,qpbv.pricing_attribute) price_break_pricing_attribute,
qpbv.pricing_attr_value_from_number price_break_value_from,
qpbv.pricing_attr_value_to_number price_break_value_to,
qpbv.operand price_break_price,
xxen_util.meaning(qpbv.arithmetic_operator,'ARITHMETIC_OPERATOR',661) price_break_application_method,
nvl2(qpbv.price_by_formula_id,qp_qp_form_pricing_attr.get_formula(qpbv.price_by_formula_id),null) price_break_formula,
qpbv.recurring_value price_break_recurring_value,
null delete_price_break,
-- Price List Line Pricing Attributes
null pricing_attribute_orig_sys_ref,
null pricing_attribute_context,
null pricing_attribute,
null pricing_attribute_operator,
null pricing_attribute_value_from,
null pricing_attribute_val_fr_desc,
null pricing_attribute_value_to,
null delete_pricing_attribute,
-- Price List Line DFF Attributes
xxen_util.display_flexfield_context(661,'QP_LIST_LINES',qllv.context) list_line_dff_context,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE1',qllv.row_id,qllv.attribute1) list_line_attribute1,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE2',qllv.row_id,qllv.attribute2) list_line_attribute2,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE3',qllv.row_id,qllv.attribute3) list_line_attribute3,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE4',qllv.row_id,qllv.attribute4) list_line_attribute4,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE5',qllv.row_id,qllv.attribute5) list_line_attribute5,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE6',qllv.row_id,qllv.attribute6) list_line_attribute6,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE7',qllv.row_id,qllv.attribute7) list_line_attribute7,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE8',qllv.row_id,qllv.attribute8) list_line_attribute8,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE9',qllv.row_id,qllv.attribute9) list_line_attribute9,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE10',qllv.row_id,qllv.attribute10) list_line_attribute10,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE11',qllv.row_id,qllv.attribute11) list_line_attribute11,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE12',qllv.row_id,qllv.attribute12) list_line_attribute12,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE13',qllv.row_id,qllv.attribute13) list_line_attribute13,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE14',qllv.row_id,qllv.attribute14) list_line_attribute14,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qllv.context,'ATTRIBUTE15',qllv.row_id,qllv.attribute15) list_line_attribute15,
-- Price List Qualifiers
null qualifier_orig_sys_ref,
null qualifier_group,
to_number(null) qualifier_group_qualifier_id,
to_number(null) qualifier_grouping_number,
null qualifier_context,
null qualifier_attribute,
null qualifier_applies_party_hier,
to_number(null) qualifier_precedence,
null qualifier_operator,
null qualifier_value_from,
null qualifier_value_from_desc,
null qualifier_value_to,
to_date(null) qualifier_start_date,
to_date(null) qualifier_end_date,
null delete_qualifier,
-- Qualifier DFF Attributes
null qualifier_dff_context,
null qualifier_attribute1,
null qualifier_attribute2,
null qualifier_attribute3,
null qualifier_attribute4,
null qualifier_attribute5,
null qualifier_attribute6,
null qualifier_attribute7,
null qualifier_attribute8,
null qualifier_attribute9,
null qualifier_attribute10,
null qualifier_attribute11,
null qualifier_attribute12,
null qualifier_attribute13,
null qualifier_attribute14,
null qualifier_attribute15,
-- Secondary Price Lists
null sec_price_list_orig_sys_ref,
null secondary_price_list,
to_number(null) secondary_price_list_precedenc,
null delete_secondary_price_list,
-- IDs
qslhv.list_header_id price_list_id,
to_number(null)qualifier_id,
to_number(null) sec_price_list_qualifier_id,
qllv.list_line_id price_list_line_id,
qpbv.list_line_id price_break_line_id,
to_number(null) pricing_attribute_id,
'Price Break' row_type,
3 upload_row
from
qp_secu_list_headers_vl qslhv,
qp_list_lines_v qllv,
qp_price_breaks_v qpbv
where
1=1 and
2=2 and
:p_show_price_list_lines = 'Y' and
nvl(qslhv.pte_code,'ORDFUL') <> 'LOGSTX' and
nvl(qslhv.pte_code,'ORDFUL') = :p_pte_code and
nvl(qslhv.source_system_code,'QP') = :p_appl_sn and
nvl(qslhv.list_source_code,'null') not in ('BSO','OKS') and
qslhv.update_flag = 'Y' and
(qslhv.global_flag = 'Y' or mo_global.check_access(qslhv.orig_org_id) = 'Y') and
--
qslhv.list_header_id = qllv.list_header_id and
qllv.list_line_type_code = 'PBH' and
qllv.product_attribute_context = 'ITEM' and
( (qllv.product_attribute= 'PRICING_ATTRIBUTE1' and
   exists (select null from mtl_system_items msi where msi.inventory_item_id = to_number(decode(translate(qllv.product_attr_value,'.0123456789','.'),null,qllv.product_attr_value,null)) and msi.organization_id = qp_util.get_item_validation_org)
  ) or
  (qllv.product_attribute != 'PRICING_ATTRIBUTE1'
  )
) and
--
qllv.list_line_id = qpbv.parent_list_line_id
--
) qp
where
:p_upload_mode like '%' || xxen_upload.action_update and
nvl(:p_category_set_id,-99) = nvl(:p_category_set_id,-99)
Parameter NameSQL textValidation
Upload Mode
 
LOV
End Date Matching List Lines?
 
LOV
Price List
qslhv.list_header_id=:p_price_list
LOV
Price List Like
lower(qslhv.name) like lower(:p_price_list_like) || '%'
Char
Product Attribute
qllv.product_attribute=:p_prod_attribute
LOV
Product Value (Item/Item Cat)
qllv.product_attr_value=to_char(:p_prod_attribute_value)
LOV
Product Value (Other)
qllv.product_attr_value=:p_prod_attrib_val_oth
LOV
Item Category Set
 
LOV
Item Category
qllv.product_attr_value in (
select
to_char(mic.inventory_item_id)
from
mtl_item_categories mic,
mtl_categories_kfv mck
where
mic.category_id = mck.category_id and
mic.category_set_id = :p_category_set_id and
mic.organization_id=fnd_profile.value('QP_ORGANIZATION_ID') and
mck.concatenated_segments = :p_category
) and
qllv.product_attribute_context='ITEM' and
qllv.product_attribute='PRICING_ATTRIBUTE1'
LOV
Effective Date
:p_effective_date between nvl(qllv.start_date_active,:p_effective_date) and nvl(qllv.end_date_active,:p_effective_date)
Date
Effective Date From
nvl(qllv.end_date_active,:p_effective_date_from) >=  :p_effective_date_from
Date
Effective Date To
nvl(qllv.start_date_active,:p_effective_date_to) <= :p_effective_date_to
Date
Download Price List Lines
 
LOV
Line Type
qllv.list_line_type_code=:p_list_line_type_code
LOV
Download Pricing Attributes
 
LOV
Download Qualifiers
 
LOV
Download Secondary Price Lists
 
LOV
Download
Blitz Report™