QP GSA Pricing Upload

Description

QP GSA Pricing Upload creates, updates and deletes GSA (government) pricing in Oracle Advanced Pricing from Excel – GSA price lists, their list lines and the customer-eligibility qualifiers – via the standard Oracle Pricing public APIs.

When to use it

Mass-load new GSA price lines (item to government price) into a GSA list.
Bulk-update GSA prices, effective dates or units of measure on existing GSA list lines.
Add or amend customer/site eligibility (qualifiers) on a GSA list.
Roll out a new GSA list version with its lines and qualifiers in one spreadsheet.
Remove obsolete qualifiers from a GSA list.
Auto-end-date superseded matching modifier lines when loading replacements.

Before you start

Blitz Report is installed and you are signed in to a responsibility with access to the relevant operating unit.
Oracle Advanced Pricing is installed and GSA pricing is in use.
Items referenced exist; for customer qualifiers, the customer/site is GSA-eligible.

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

In Blitz Report, open QP GSA Pricing Upload, choose an Upload Mode (Create for an empty template, or Create, Update to download existing GSA lists/lines/qualifiers to edit), and set the parameters:

Parameter	Purpose
Upload Mode	Create or Create, Update. Default is Create.
Modifier Numbering	Automatic (system generates line numbers) or Manual (keep your Line No). Default Automatic.
End Date Matching Modifiers?	Yes auto-end-dates any active matching line on upload.
List Number / Name / Version / Operating Unit / Item / dates	Restrict which existing GSA pricing is downloaded.

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

Step 2 – Enter the GSA pricing

On each row enter the List Number, List Name and List Currency, the Line No and Line Product Value (the item), and the Value (the GSA price). Use the qualifier columns (Qualifier Context, Attribute, Operator, Value From) to set who the GSA price applies to.

Step 3 – Delete qualifiers (optional)

Set Delete Qualifier to Yes to remove an existing qualifier from the list.

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 GSA list, lines and qualifiers. When it finishes, a result report opens showing each row as success or error.

What’s produced

Created and updated GSA price lists, lines and qualifiers.
A result report listing every row with a status (success or error) and a message.

Common questions

How does it know whether to create or update a line?
By the combination of List Number, List Version and Line No – if it exists in Oracle it updates that line, otherwise it creates a new one.

What is the GSA price column?
Value – the new price for the item. The line’s application method is always New Price.

Automatic vs Manual line numbering?
Set by the Modifier Numbering parameter; Automatic ignores your Line No on new lines and lets Oracle assign numbers; Manual keeps the Line No you enter.

Can I delete data?
You can delete qualifiers via Delete Qualifier = Yes; the upload otherwise creates and updates lists and lines.

Why is a customer rejected on a GSA qualifier?
GSA customer/site qualifiers are validated to be GSA-eligible.

Troubleshooting

Message	Cause	What to do
Line No is required when entering a modifier line	A line detail row has no Line No.	Enter a Line No for every line row (it’s the line key with List Number and Version).
A Modifier List with this name and a different Orig Sys Ref already exists	The list name collides with an existing list under a different reference.	Use the existing list’s identifiers, or a unique name.
Modifier List / Line no longer exists	The targeted list/line was removed in Oracle since download.	Re-download the current data and retry.
Qualifier is not unique / no longer exists	The qualifier key matches several rows or none.	Refine the grouping number/context/attribute/operator to identify exactly one qualifier; re-download if deleted.
Line cannot be created when a delete flag is set to Yes	A delete flag is Yes on a row with no existing entity to delete.	Clear the delete flag to create, or only set delete on existing records.

Copy

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) upload_row,
:p_modifier_numbering modifier_numbering,
to_number(null) mod_list_row_id,
to_number(null) mod_line_row_id,
to_number(null) qualifier_group_row_id,
to_number(null) qualifier_row_id,
to_number(null) price_break_line_row_id,
to_number(null) pricing_attribute_row_id,
to_number(null) excluder_row_id,
to_number(null) buy_line_row_id,
to_number(null) get_line_row_id,
to_number(null) limit_row_id,
to_number(null) limit_att_row_id,
qp.*
from
(
--
-- Q1 List Header Qualifiers
--
select
--
-- Modifier 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) list_type,
qslhv.orig_system_header_ref list_orig_sys_ref,
qslhv.name list_number,
qslhv.version_no list_version,
qslhv.description list_name,
qslhv.comments list_description,
qslhv.currency_code list_currency,
xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global_list,
qp_util.get_ou_name(qslhv.orig_org_id) operating_unit,
qslhv.start_date_active list_effective_from,
qslhv.end_date_active list_effective_to,
xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) list_is_active,
xxen_util.meaning(decode(qslhv.automatic_flag,'Y','Y',null),'YES_NO',0) list_is_automatic,
xxen_util.meaning(decode(qslhv.gsa_indicator,'Y','Y',null),'YES_NO',0) list_gsa_indicator,
--
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,
--
-- Modifier Line
-- summary
null line_orig_sys_ref,
null line_no,
null line_modifier_level,
null line_modifier_type,
to_date(null) line_effective_from,
to_date(null) line_effective_to,
null line_automatic,
null line_allow_override,
null line_proration_type,
null line_pricing_phase,
null line_incompatibility_group,
to_number(null) line_bucket,
null line_product_attribute,
null line_product_value,
null line_product_description,
to_number(null) line_precedence,
null line_product_uom,
-- Discounts/Charges
null application_method,
to_number(null) value,
-- 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,
--
-- Qualifiers
xxen_util.meaning('HEADER','ZX_ROUNDING_LEVEL',0) qualifier_assignment_level,
xxen_qp_upload.get_orig_sys_ref('QUALIFIER',qqv.qualifier_id) qualifier_orig_sys_ref,
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(decode(qqv.qualify_hier_descendents_flag,'Y','Y',null),'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,
--
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,
--
-- IDs
qslhv.list_header_id modifier_list_id,
to_number(null) modifier_line_id,
qqv.qualifier_id qualifier_id,
'Modifier List' row_type,
1.0 seq
from
qp_secu_list_headers_vl qslhv,
qp_qualifiers_v qqv
where
1=1 and
:p_show_header_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
((nvl(:p_gsa_pricing,'N') = 'N' and
  nvl(qslhv.gsa_indicator,'N') = 'N' and
  ((qp_util.get_qp_status = 'I' and qslhv.list_type_code in ('DLT', 'SLT', 'PRO', 'DEL', 'CHARGES')) or
   (qp_util.get_qp_status = 'S' and qslhv.list_type_code in ('DLT', 'SLT', 'CHARGES'))
  )
 ) or
 (nvl(:p_gsa_pricing,'N') = 'Y' and
  nvl(qslhv.gsa_indicator,'N') = 'Y' and
  qslhv.list_type_code = 'DLT' and
  qslhv.active_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
(nvl(:p_gsa_pricing,'N') = 'N' or
 not (qqv.qualifier_context = 'CUSTOMER' and qqv.qualifier_attribute = 'QUALIFIER_ATTRIBUTE15' and qqv.qualifier_attr_value = 'Y')
) and
exists
(select
 null
 from
 qp_modifier_summary_v qmsv
 where
 2=2 and
 qslhv.list_header_id = qmsv.list_header_id and
 (qmsv.pricing_attribute_context is null or qmsv.pricing_attribute_context = 'VOLUME') and
 nvl(qmsv.excluder_flag,'N') = 'N'
)
--
union
--
-- Q2 Modifier Lines
--
select
--
-- Modifier 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) list_type,
qslhv.orig_system_header_ref list_orig_sys_ref,
qslhv.name list_number,
qslhv.version_no list_version,
qslhv.description list_name,
qslhv.comments list_description,
qslhv.currency_code list_currency,
xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global_list,
qp_util.get_ou_name(qslhv.orig_org_id) operating_unit,
qslhv.start_date_active list_effective_from,
qslhv.end_date_active list_effective_to,
xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) list_is_active,
xxen_util.meaning(decode(qslhv.automatic_flag,'Y','Y',null),'YES_NO',0) list_is_automatic,
xxen_util.meaning(decode(qslhv.gsa_indicator,'Y','Y',null),'YES_NO',0) list_gsa_indicator,
--
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,
--
-- Modifier Line
-- summary
xxen_qp_upload.get_orig_sys_ref('LINE',qmsv.list_line_id) line_orig_sys_ref,
qmsv.list_line_no line_no,
qmsv.modifier_level line_modifier_level,
qmsv.list_line_type line_modifier_type,
qmsv.start_date_active line_effective_from,
qmsv.end_date_active line_effective_to,
xxen_util.meaning(decode(qmsv.automatic_flag,'Y','Y',null),'YES_NO',0) line_automatic,
xxen_util.meaning(decode(qmsv.override_flag,'Y','Y',null),'YES_NO',0) line_allow_override,
qmsv.proration_type line_proration_type,
qmsv.pricing_phase line_pricing_phase,
qmsv.incompatibility_grp line_incompatibility_group,
qmsv.pricing_group_sequence line_bucket,
qmsv.product_attribute_type line_product_attribute,
qmsv.product_attr_value line_product_value,
xxen_qp_upload.get_product_description
(qslhv.pte_code,
 qp_util.get_context('QP_ATTR_DEFNS_PRICING',qmsv.product_attribute_context),
 qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qmsv.product_attribute_context,qmsv.product_attr),
 qmsv.product_attr_val,
 qmsv.product_attr_value
) line_product_description,
qmsv.product_precedence line_precedence,
qmsv.product_uom_code line_product_uom,
-- Discounts/Charges
qmsv.arithmetic_operator_type application_method,
qmsv.operand value,
-- Line DFF Attributes
xxen_util.display_flexfield_context(661,'QP_LIST_LINES',qmsv.context) list_line_dff_context,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE1',qmsv.row_id,qmsv.attribute1) list_line_attribute1,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE2',qmsv.row_id,qmsv.attribute2) list_line_attribute2,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE3',qmsv.row_id,qmsv.attribute3) list_line_attribute3,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE4',qmsv.row_id,qmsv.attribute4) list_line_attribute4,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE5',qmsv.row_id,qmsv.attribute5) list_line_attribute5,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE6',qmsv.row_id,qmsv.attribute6) list_line_attribute6,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE7',qmsv.row_id,qmsv.attribute7) list_line_attribute7,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE8',qmsv.row_id,qmsv.attribute8) list_line_attribute8,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE9',qmsv.row_id,qmsv.attribute9) list_line_attribute9,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE10',qmsv.row_id,qmsv.attribute10) list_line_attribute10,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE11',qmsv.row_id,qmsv.attribute11) list_line_attribute11,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE12',qmsv.row_id,qmsv.attribute12) list_line_attribute12,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE13',qmsv.row_id,qmsv.attribute13) list_line_attribute13,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE14',qmsv.row_id,qmsv.attribute14) list_line_attribute14,
xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE15',qmsv.row_id,qmsv.attribute15) list_line_attribute15,
--
-- Qualifiers
null qualifier_assignment_level,
null qualifier_orig_sys_ref,
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,
--
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,
--
-- IDs
qslhv.list_header_id modifier_list_id,
qmsv.list_line_id modifier_line_id,
to_number(null) qualifier_id,
'Modifier Line' row_type,
2.0 seq
from
qp_secu_list_headers_vl qslhv,
qp_modifier_summary_v qmsv
where
1=1 and
2=2 and
:p_show_modifier_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
((nvl(:p_gsa_pricing,'N') = 'N' and
  nvl(qslhv.gsa_indicator,'N') = 'N' and
  ((qp_util.get_qp_status = 'I' and qslhv.list_type_code in ('DLT', 'SLT', 'PRO', 'DEL', 'CHARGES')) or
   (qp_util.get_qp_status = 'S' and qslhv.list_type_code in ('DLT', 'SLT', 'CHARGES'))
  )
 ) or
 (nvl(:p_gsa_pricing,'N') = 'Y' and
  nvl(qslhv.gsa_indicator,'N') = 'Y' and
  qslhv.list_type_code = 'DLT' and
  qslhv.active_flag = 'Y'
 )
) and
(qslhv.global_flag = 'Y' or mo_global.check_access(qslhv.orig_org_id) = 'Y') and
-- modifier lines
qslhv.list_header_id = qmsv.list_header_id and
(qmsv.pricing_attribute_context is null or qmsv.pricing_attribute_context = 'VOLUME') and
nvl(qmsv.excluder_flag,'N') = 'N'
) qp
where
:p_upload_mode like '%' || xxen_upload.action_update and
nvl(:p_category_set_id,-99) = nvl(:p_category_set_id,-99)

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) upload_row, :p_modifier_numbering modifier_numbering, to_number(null) mod_list_row_id, to_number(null) mod_line_row_id, to_number(null) qualifier_group_row_id, to_number(null) qualifier_row_id, to_number(null) price_break_line_row_id, to_number(null) pricing_attribute_row_id, to_number(null) excluder_row_id, to_number(null) buy_line_row_id, to_number(null) get_line_row_id, to_number(null) limit_row_id, to_number(null) limit_att_row_id, qp.* from ( -- -- Q1 List Header Qualifiers -- select -- -- Modifier 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) list_type, qslhv.orig_system_header_ref list_orig_sys_ref, qslhv.name list_number, qslhv.version_no list_version, qslhv.description list_name, qslhv.comments list_description, qslhv.currency_code list_currency, xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global_list, qp_util.get_ou_name(qslhv.orig_org_id) operating_unit, qslhv.start_date_active list_effective_from, qslhv.end_date_active list_effective_to, xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) list_is_active, xxen_util.meaning(decode(qslhv.automatic_flag,'Y','Y',null),'YES_NO',0) list_is_automatic, xxen_util.meaning(decode(qslhv.gsa_indicator,'Y','Y',null),'YES_NO',0) list_gsa_indicator, -- 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, -- -- Modifier Line -- summary null line_orig_sys_ref, null line_no, null line_modifier_level, null line_modifier_type, to_date(null) line_effective_from, to_date(null) line_effective_to, null line_automatic, null line_allow_override, null line_proration_type, null line_pricing_phase, null line_incompatibility_group, to_number(null) line_bucket, null line_product_attribute, null line_product_value, null line_product_description, to_number(null) line_precedence, null line_product_uom, -- Discounts/Charges null application_method, to_number(null) value, -- 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, -- -- Qualifiers xxen_util.meaning('HEADER','ZX_ROUNDING_LEVEL',0) qualifier_assignment_level, xxen_qp_upload.get_orig_sys_ref('QUALIFIER',qqv.qualifier_id) qualifier_orig_sys_ref, 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(decode(qqv.qualify_hier_descendents_flag,'Y','Y',null),'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, -- 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, -- -- IDs qslhv.list_header_id modifier_list_id, to_number(null) modifier_line_id, qqv.qualifier_id qualifier_id, 'Modifier List' row_type, 1.0 seq from qp_secu_list_headers_vl qslhv, qp_qualifiers_v qqv where 1=1 and :p_show_header_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 ((nvl(:p_gsa_pricing,'N') = 'N' and nvl(qslhv.gsa_indicator,'N') = 'N' and ((qp_util.get_qp_status = 'I' and qslhv.list_type_code in ('DLT', 'SLT', 'PRO', 'DEL', 'CHARGES')) or (qp_util.get_qp_status = 'S' and qslhv.list_type_code in ('DLT', 'SLT', 'CHARGES')) ) ) or (nvl(:p_gsa_pricing,'N') = 'Y' and nvl(qslhv.gsa_indicator,'N') = 'Y' and qslhv.list_type_code = 'DLT' and qslhv.active_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 (nvl(:p_gsa_pricing,'N') = 'N' or not (qqv.qualifier_context = 'CUSTOMER' and qqv.qualifier_attribute = 'QUALIFIER_ATTRIBUTE15' and qqv.qualifier_attr_value = 'Y') ) and exists (select null from qp_modifier_summary_v qmsv where 2=2 and qslhv.list_header_id = qmsv.list_header_id and (qmsv.pricing_attribute_context is null or qmsv.pricing_attribute_context = 'VOLUME') and nvl(qmsv.excluder_flag,'N') = 'N' ) -- union -- -- Q2 Modifier Lines -- select -- -- Modifier 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) list_type, qslhv.orig_system_header_ref list_orig_sys_ref, qslhv.name list_number, qslhv.version_no list_version, qslhv.description list_name, qslhv.comments list_description, qslhv.currency_code list_currency, xxen_util.meaning(nvl(qslhv.global_flag,'Y'),'YES_NO',0) global_list, qp_util.get_ou_name(qslhv.orig_org_id) operating_unit, qslhv.start_date_active list_effective_from, qslhv.end_date_active list_effective_to, xxen_util.meaning(nvl(qslhv.active_flag,'N'),'YES_NO',0) list_is_active, xxen_util.meaning(decode(qslhv.automatic_flag,'Y','Y',null),'YES_NO',0) list_is_automatic, xxen_util.meaning(decode(qslhv.gsa_indicator,'Y','Y',null),'YES_NO',0) list_gsa_indicator, -- 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, -- -- Modifier Line -- summary xxen_qp_upload.get_orig_sys_ref('LINE',qmsv.list_line_id) line_orig_sys_ref, qmsv.list_line_no line_no, qmsv.modifier_level line_modifier_level, qmsv.list_line_type line_modifier_type, qmsv.start_date_active line_effective_from, qmsv.end_date_active line_effective_to, xxen_util.meaning(decode(qmsv.automatic_flag,'Y','Y',null),'YES_NO',0) line_automatic, xxen_util.meaning(decode(qmsv.override_flag,'Y','Y',null),'YES_NO',0) line_allow_override, qmsv.proration_type line_proration_type, qmsv.pricing_phase line_pricing_phase, qmsv.incompatibility_grp line_incompatibility_group, qmsv.pricing_group_sequence line_bucket, qmsv.product_attribute_type line_product_attribute, qmsv.product_attr_value line_product_value, xxen_qp_upload.get_product_description (qslhv.pte_code, qp_util.get_context('QP_ATTR_DEFNS_PRICING',qmsv.product_attribute_context), qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING',qmsv.product_attribute_context,qmsv.product_attr), qmsv.product_attr_val, qmsv.product_attr_value ) line_product_description, qmsv.product_precedence line_precedence, qmsv.product_uom_code line_product_uom, -- Discounts/Charges qmsv.arithmetic_operator_type application_method, qmsv.operand value, -- Line DFF Attributes xxen_util.display_flexfield_context(661,'QP_LIST_LINES',qmsv.context) list_line_dff_context, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE1',qmsv.row_id,qmsv.attribute1) list_line_attribute1, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE2',qmsv.row_id,qmsv.attribute2) list_line_attribute2, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE3',qmsv.row_id,qmsv.attribute3) list_line_attribute3, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE4',qmsv.row_id,qmsv.attribute4) list_line_attribute4, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE5',qmsv.row_id,qmsv.attribute5) list_line_attribute5, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE6',qmsv.row_id,qmsv.attribute6) list_line_attribute6, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE7',qmsv.row_id,qmsv.attribute7) list_line_attribute7, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE8',qmsv.row_id,qmsv.attribute8) list_line_attribute8, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE9',qmsv.row_id,qmsv.attribute9) list_line_attribute9, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE10',qmsv.row_id,qmsv.attribute10) list_line_attribute10, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE11',qmsv.row_id,qmsv.attribute11) list_line_attribute11, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE12',qmsv.row_id,qmsv.attribute12) list_line_attribute12, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE13',qmsv.row_id,qmsv.attribute13) list_line_attribute13, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE14',qmsv.row_id,qmsv.attribute14) list_line_attribute14, xxen_util.display_flexfield_value(661,'QP_LIST_LINES',qmsv.context,'ATTRIBUTE15',qmsv.row_id,qmsv.attribute15) list_line_attribute15, -- -- Qualifiers null qualifier_assignment_level, null qualifier_orig_sys_ref, 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, -- 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, -- -- IDs qslhv.list_header_id modifier_list_id, qmsv.list_line_id modifier_line_id, to_number(null) qualifier_id, 'Modifier Line' row_type, 2.0 seq from qp_secu_list_headers_vl qslhv, qp_modifier_summary_v qmsv where 1=1 and 2=2 and :p_show_modifier_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 ((nvl(:p_gsa_pricing,'N') = 'N' and nvl(qslhv.gsa_indicator,'N') = 'N' and ((qp_util.get_qp_status = 'I' and qslhv.list_type_code in ('DLT', 'SLT', 'PRO', 'DEL', 'CHARGES')) or (qp_util.get_qp_status = 'S' and qslhv.list_type_code in ('DLT', 'SLT', 'CHARGES')) ) ) or (nvl(:p_gsa_pricing,'N') = 'Y' and nvl(qslhv.gsa_indicator,'N') = 'Y' and qslhv.list_type_code = 'DLT' and qslhv.active_flag = 'Y' ) ) and (qslhv.global_flag = 'Y' or mo_global.check_access(qslhv.orig_org_id) = 'Y') and -- modifier lines qslhv.list_header_id = qmsv.list_header_id and (qmsv.pricing_attribute_context is null or qmsv.pricing_attribute_context = 'VOLUME') and nvl(qmsv.excluder_flag,'N') = 'N' ) qp where :p_upload_mode like '%' || xxen_upload.action_update and nvl(:p_category_set_id,-99) = nvl(:p_category_set_id,-99)

Parameter Name

SQL text

Validation

Upload Mode

LOV

End Date Matching Modifiers?

LOV

Modifier Numbering

LOV

List Number

qslhv.name=:p_modifier_list_name

LOV

List Number Like

lower(qslhv.name) like lower(:p_mod_list_name_like) || '%'

Char

List Name

qslhv.description=:p_modifier_list_desc

LOV

List Name Like

lower(qslhv.description) like lower(:p_mod_list_desc_like) || '%'

Char

Version Number

qslhv.version_no=:p_version_no

LOV

Global List

nvl(qslhv.global_flag,'N') = :p_global_flag

LOV Oracle

Operating Unit

qp_util.get_ou_name(qslhv.orig_org_id)=:p_op_unit

LOV

Product Value (Item)

qmsv.product_attr_val=to_char(:p_prod_attribute_value)

LOV

Item Category Set

LOV

Item Category

qmsv.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(qmsv.start_date_active,:p_effective_date) and nvl(qmsv.end_date_active,:p_effective_date)

Date

Download Modifier Lines

LOV

Download List Qualifiers

LOV

Download

XML SQL

Blitz Report™

Blitz Report™ provides multiple benefits:

Update thousands of records in seconds
Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption