[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_CALC_INSERT VARCHAR2(30) := 'INSERTED FOR CALCULATION';
Update qp_npreq_ldets_tmp set price_adjustment_id =
OE_PRICE_ADJUSTMENTS_S.NEXTVAL
where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and process_code = QP_PREQ_PUB.G_STATUS_NEW
and (automatic_flag = QP_PREQ_PUB.G_YES
or created_from_list_line_type = QP_PREQ_PUB.G_FREIGHT_CHARGE)
and nvl(created_from_list_type_code,'NULL') not in
(QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
Update qp_int_ldets set price_adjustment_id =
OE_PRICE_ADJUSTMENTS_S.NEXTVAL
where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and process_code = QP_PREQ_PUB.G_STATUS_NEW
and (applied_flag = QP_PREQ_PUB.G_YES
or automatic_flag = QP_PREQ_PUB.G_YES)
-- or created_from_list_line_type = QP_PREQ_PUB.G_FREIGHT_CHARGE)
and nvl(created_from_list_type_code,'NULL') not in
(QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
Update qp_int_ldets set price_adjustment_id =
OE_PRICE_ADJUSTMENTS_S.NEXTVAL
where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and process_code = QP_PREQ_PUB.G_STATUS_NEW
and (automatic_flag = QP_PREQ_PUB.G_YES)
-- or created_from_list_line_type = QP_PREQ_PUB.G_FREIGHT_CHARGE)
and nvl(created_from_list_type_code,'NULL') not in
(QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
select sum(operand) into l_adj_sum_operand
from oe_price_adjustments adj
where adj.header_id = p_header_id
and adj.line_id is null
and adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
and adj.automatic_flag = QP_PREQ_PUB.G_YES
and applied_flag = QP_PREQ_PUB.G_YES;
CURSOR l_update_cur IS
SELECT /*+ ORDERED USE_NL(adj ldet ql) dynamic_sampling(1) index(LDET QP_PREQ_LDETS_TMP_N1)*/ -- Bug No: 6753550
ldet.line_index line_index
, ldet.line_detail_index line_detail_index
-- Begin Bug No: 6753550
--, ldet.list_line_id created_from_list_line_id
,ldet.CREATED_FROM_LIST_LINE_ID
-- End Bug No: 6753550
, ldet.process_code process_code
, ldet.price_break_type_code price_break_type_code
, ldet.pricing_group_sequence pricing_group_sequence
, ldet.operand_calculation_code operand_calculation_code
, ldet.operand_value operand_value
, ldet.adjustment_amount adjustment_amount
-- Begin Bug No: 6753550
--, ldet.substitution_attribute substitution_type_code
,ql_det.substitution_attribute substitution_type_code
-- End Bug No: 6753550
, ldet.substitution_value_to substitution_value_to
, ldet.pricing_phase_id pricing_phase_id
, ldet.applied_flag applied_flag
, ldet.automatic_flag automatic_flag
-- Begin Bug No: 6753550
--, ldet.override_flag override_flag
, ql_det.override_flag override_flag
-- End Bug No: 6753550
, ldet.benefit_qty benefit_qty
-- Begin Bug No: 6753550
--, ldet.benefit_uom_code benefit_uom_code
, ql_det.benefit_uom_code benefit_uom_code
--, ldet.accrual_flag accrual_flag
, ql_det.accrual_flag accrual_flag
--, ldet.accrual_conversion_rate accrual_conversion_rate
, ql_det.accrual_conversion_rate accrual_conversion_rate
-- End Bug No: 6753550
, ldet.charge_type_code charge_type_code
, ldet.charge_subtype_code charge_subtype_code
, ldet.line_quantity line_quantity
, adj.automatic_flag adj_automatic_flag
, adj.line_id adj_line_id
-- , adj.header_id adj_header_id
-- , adj.list_line_id adj_list_line_id
, adj.modified_from adj_modified_from
, adj.modified_to adj_modified_to
, adj.update_allowed adj_update_allowed
, adj.updated_flag adj_updated_flag
, adj.applied_flag adj_applied_flag
, adj.pricing_phase_id adj_pricing_phase_id
, adj.charge_type_code adj_charge_type_code
, adj.charge_subtype_code adj_charge_subtype_code
, adj.range_break_quantity adj_range_break_quantity
, adj.accrual_conversion_rate adj_accrual_conv_rate
, adj.pricing_group_sequence adj_pricing_group_seq
, adj.accrual_flag adj_accrual_flag
, adj.benefit_qty adj_benefit_qty
, adj.benefit_uom_code adj_benefit_uom_code
, adj.expiration_date adj_expiration_date
, adj.rebate_transaction_type_code adj_rebate_txn_type_code
, adj.price_break_type_code adj_price_break_type_code
, adj.substitution_attribute adj_substitution_attribute
, adj.proration_type_code adj_proration_type_code
, adj.include_on_returns_flag adj_include_on_returns
, nvl(adj.operand_per_pqty, adj.operand) adj_operand
, adj.adjusted_amount_per_pqty adj_adjusted_amount
, adj.arithmetic_operator adj_arithmetic_operator
, ql.expiration_date
, ql.proration_type_code
, ql.include_on_returns_flag
, ql.rebate_transaction_type_code
, ldet.pricing_status_text
, adj.price_adjustment_id
, ldet.order_qty_adj_amt ord_qty_adjamt
, adj.adjusted_amount adj_ord_qty_adjamt
, ldet.order_qty_operand ord_qty_operand
, adj.operand adj_ord_qty_operand
FROM qp_npreq_lines_tmp line
, oe_price_adjustments adj
-- Begin Bug No: 6753550
--,qp_ldets_v ldet
,qp_npreq_ldets_tmp ldet
,qp_list_lines ql_det
-- End Bug No: 6753550
, qp_list_lines ql
-- WHERE p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
and line.line_index = ldet.line_index
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
,QP_PREQ_PUB.G_STATUS_UPDATED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and ((line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.line_id = adj.line_id)
OR
(line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and line.line_id = adj.header_id))
-- Begin Bug No: 6753550
--and ldet.list_line_id = to_number(adj.list_line_id) -- bug 6023524
--and ldet.list_line_id = ql.list_line_id;
SELECT /*+ ORDERED USE_NL(adj ldet ql)/
ldet.line_index line_index
, ldet.line_detail_index line_detail_index
, ldet.list_line_id created_from_list_line_id
, ldet.process_code process_code
, ldet.price_break_type_code price_break_type_code
, ldet.pricing_group_sequence pricing_group_sequence
, ldet.operand_calculation_code operand_calculation_code
, ldet.operand_value operand_value
, ldet.adjustment_amount adjustment_amount
, ldet.substitution_attribute substitution_type_code
, ldet.substitution_value_to substitution_value_to
, ldet.pricing_phase_id pricing_phase_id
, ldet.applied_flag applied_flag
, ldet.automatic_flag automatic_flag
, ldet.override_flag override_flag
, ldet.benefit_qty benefit_qty
, ldet.benefit_uom_code benefit_uom_code
, ldet.accrual_flag accrual_flag
, ldet.accrual_conversion_rate accrual_conversion_rate
, ldet.charge_type_code charge_type_code
, ldet.charge_subtype_code charge_subtype_code
, ldet.line_quantity line_quantity
, adj.automatic_flag adj_automatic_flag
, adj.line_id adj_line_id
-- , adj.header_id adj_header_id
-- , adj.list_line_id adj_list_line_id
, adj.modified_from adj_modified_from
, adj.modified_to adj_modified_to
, adj.update_allowed adj_update_allowed
, adj.updated_flag adj_updated_flag
, adj.applied_flag adj_applied_flag
, adj.pricing_phase_id adj_pricing_phase_id
, adj.charge_type_code adj_charge_type_code
, adj.charge_subtype_code adj_charge_subtype_code
, adj.range_break_quantity adj_range_break_quantity
, adj.accrual_conversion_rate adj_accrual_conv_rate
, adj.pricing_group_sequence adj_pricing_group_seq
, adj.accrual_flag adj_accrual_flag
, adj.benefit_qty adj_benefit_qty
, adj.benefit_uom_code adj_benefit_uom_code
, adj.expiration_date adj_expiration_date
, adj.rebate_transaction_type_code adj_rebate_txn_type_code
, adj.price_break_type_code adj_price_break_type_code
, adj.substitution_attribute adj_substitution_attribute
, adj.proration_type_code adj_proration_type_code
, adj.include_on_returns_flag adj_include_on_returns
, nvl(adj.operand_per_pqty, adj.operand) adj_operand
, adj.adjusted_amount_per_pqty adj_adjusted_amount
, adj.arithmetic_operator adj_arithmetic_operator
, ql.expiration_date
, ql.proration_type_code
, ql.include_on_returns_flag
, ql.rebate_transaction_type_code
, ldet.pricing_status_text
, adj.price_adjustment_id
, ldet.order_qty_adj_amt ord_qty_adjamt
, adj.adjusted_amount adj_ord_qty_adjamt
, ldet.order_qty_operand ord_qty_operand
, adj.operand adj_ord_qty_operand
FROM qp_npreq_lines_tmp line
, oe_price_adjustments adj
,qp_ldets_v ldet
, qp_list_lines ql
-- WHERE p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
and line.line_index = ldet.line_index
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
,QP_PREQ_PUB.G_STATUS_UPDATED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and (line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and line.line_id = adj.header_id)
and ldet.list_line_id = adj.list_line_id
and ldet.list_line_id = ql.list_line_id
UNION
SELECT ldet.line_index line_index
, ldet.line_detail_index line_detail_index
, ldet.list_line_id created_from_list_line_id
, ldet.process_code process_code
, ldet.price_break_type_code price_break_type_code
, ldet.pricing_group_sequence pricing_group_sequence
, ldet.operand_calculation_code operand_calculation_code
, ldet.operand_value operand_value
, ldet.adjustment_amount adjustment_amount
, ldet.substitution_attribute substitution_type_code
, ldet.substitution_value_to substitution_value_to
, ldet.pricing_phase_id pricing_phase_id
, ldet.applied_flag applied_flag
, ldet.automatic_flag automatic_flag
, ldet.override_flag override_flag
, ldet.benefit_qty benefit_qty
, ldet.benefit_uom_code benefit_uom_code
, ldet.accrual_flag accrual_flag
, ldet.accrual_conversion_rate accrual_conversion_rate
, ldet.charge_type_code charge_type_code
, ldet.charge_subtype_code charge_subtype_code
, ldet.line_quantity line_quantity
, adj.automatic_flag adj_automatic_flag
, adj.created_from_list_line_id adj_list_line_id
, adj.substitution_value_from adj_modified_from
, adj.substitution_value_to adj_modified_to
, adj.override_flag adj_update_allowed
, adj.updated_flag adj_updated_flag
, adj.applied_flag adj_applied_flag
, adj.pricing_phase_id adj_pricing_phase_id
, adj.charge_type_code adj_charge_type_code
, adj.charge_subtype_code adj_charge_subtype_code
, adj.line_quantity adj_range_break_quantity
, adj.accrual_conversion_rate adj_accrual_conv_rate
, adj.pricing_group_sequence adj_pricing_group_seq
, adj.accrual_flag adj_accrual_flag
, adj.benefit_qty adj_benefit_qty
, adj.benefit_uom_code adj_benefit_uom_code
, ql.expiration_date adj_expiration_date
, ql.rebate_transaction_type_code adj_rebate_txn_type_code
, adj.price_break_type_code adj_price_break_type_code
, adj.substitution_type_code adj_substitution_attribute
, ql.proration_type_code adj_proration_type_code
, ql.include_on_returns_flag adj_include_on_returns
, adj.operand_value adj_operand
, adj.adjustment_amount adj_adjusted_amount
, adj.operand_calculation_code adj_arithmetic_operator
, ql.expiration_date
, ql.proration_type_code
, ql.include_on_returns_flag
, ql.rebate_transaction_type_code
, ldet.pricing_status_text
, adj.price_adjustment_id
, ldet.order_qty_adj_amt ord_qty_adjamt
, adj.order_qty_adj_amt adj_ord_qty_adjamt
, ldet.order_qty_operand ord_qty_operand
, adj.order_qty_operand adj_ord_qty_operand
FROM qp_npreq_lines_tmp line
, qp_npreq_ldets_tmp adj
,qp_ldets_v ldet
, qp_list_lines ql
-- WHERE p_request_type_code <> 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
and line.line_index = ldet.line_index
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
,QP_PREQ_PUB.G_STATUS_UPDATED)
and adj.line_index = line.line_index
and adj.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
and ldet.list_line_id = adj.created_from_list_line_id
and ldet.list_line_id = ql.list_line_id;
CURSOR l_update_int_cur IS
SELECT /*+ ORDERED USE_NL(adj ldet ql)*/
ldet.line_index line_index
, ldet.line_detail_index line_detail_index
, ldet.list_line_id created_from_list_line_id
, ldet.process_code process_code
, ldet.price_break_type_code price_break_type_code
, ldet.pricing_group_sequence pricing_group_sequence
, ldet.operand_calculation_code operand_calculation_code
, ldet.operand_value operand_value
, ldet.adjustment_amount adjustment_amount
, ldet.substitution_attribute substitution_type_code
, ldet.substitution_value_to substitution_value_to
, ldet.pricing_phase_id pricing_phase_id
, ldet.applied_flag applied_flag
, ldet.automatic_flag automatic_flag
, ldet.override_flag override_flag
, ldet.benefit_qty benefit_qty
, ldet.benefit_uom_code benefit_uom_code
, ldet.accrual_flag accrual_flag
, ldet.accrual_conversion_rate accrual_conversion_rate
, ldet.charge_type_code charge_type_code
, ldet.charge_subtype_code charge_subtype_code
, ldet.line_quantity line_quantity
, adj.automatic_flag adj_automatic_flag
, adj.line_id adj_line_id
-- , adj.header_id adj_header_id
-- , adj.list_line_id adj_list_line_id
, adj.modified_from adj_modified_from
, adj.modified_to adj_modified_to
, adj.update_allowed adj_update_allowed
, adj.updated_flag adj_updated_flag
, adj.applied_flag adj_applied_flag
, adj.pricing_phase_id adj_pricing_phase_id
, adj.charge_type_code adj_charge_type_code
, adj.charge_subtype_code adj_charge_subtype_code
, adj.range_break_quantity adj_range_break_quantity
, adj.accrual_conversion_rate adj_accrual_conv_rate
, adj.pricing_group_sequence adj_pricing_group_seq
, adj.accrual_flag adj_accrual_flag
, adj.benefit_qty adj_benefit_qty
, adj.benefit_uom_code adj_benefit_uom_code
, adj.expiration_date adj_expiration_date
, adj.rebate_transaction_type_code adj_rebate_txn_type_code
, adj.price_break_type_code adj_price_break_type_code
, adj.substitution_attribute adj_substitution_attribute
, adj.proration_type_code adj_proration_type_code
, adj.include_on_returns_flag adj_include_on_returns
, nvl(adj.operand_per_pqty, adj.operand) adj_operand
, adj.adjusted_amount_per_pqty adj_adjusted_amount
, adj.arithmetic_operator adj_arithmetic_operator
, ql.expiration_date
, ql.proration_type_code
, ql.include_on_returns_flag
, ql.rebate_transaction_type_code
, ldet.pricing_status_text
, adj.price_adjustment_id
, ldet.order_qty_adj_amt ord_qty_adjamt
, adj.adjusted_amount adj_ord_qty_adjamt
, ldet.order_qty_operand ord_qty_operand
, adj.operand adj_ord_qty_operand
FROM qp_int_lines line
, oe_price_adjustments adj
,qp_ldets_v ldet
, qp_list_lines ql
-- WHERE p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
and line.line_index = ldet.line_index
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
,QP_PREQ_PUB.G_STATUS_UPDATED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and ((line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.line_id = adj.line_id)
OR
(line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and line.line_id = adj.header_id))
and ldet.list_line_id = adj.list_line_id
and ldet.list_line_id = ql.list_line_id;
SELECT /*+ ORDERED USE_NL(adj ldet ql)/
ldet.line_index line_index
, ldet.line_detail_index line_detail_index
, ldet.list_line_id created_from_list_line_id
, ldet.process_code process_code
, ldet.price_break_type_code price_break_type_code
, ldet.pricing_group_sequence pricing_group_sequence
, ldet.operand_calculation_code operand_calculation_code
, ldet.operand_value operand_value
, ldet.adjustment_amount adjustment_amount
, ldet.substitution_attribute substitution_type_code
, ldet.substitution_value_to substitution_value_to
, ldet.pricing_phase_id pricing_phase_id
, ldet.applied_flag applied_flag
, ldet.automatic_flag automatic_flag
, ldet.override_flag override_flag
, ldet.benefit_qty benefit_qty
, ldet.benefit_uom_code benefit_uom_code
, ldet.accrual_flag accrual_flag
, ldet.accrual_conversion_rate accrual_conversion_rate
, ldet.charge_type_code charge_type_code
, ldet.charge_subtype_code charge_subtype_code
, ldet.line_quantity line_quantity
, adj.automatic_flag adj_automatic_flag
, adj.line_id adj_line_id
-- , adj.header_id adj_header_id
-- , adj.list_line_id adj_list_line_id
, adj.modified_from adj_modified_from
, adj.modified_to adj_modified_to
, adj.update_allowed adj_update_allowed
, adj.updated_flag adj_updated_flag
, adj.applied_flag adj_applied_flag
, adj.pricing_phase_id adj_pricing_phase_id
, adj.charge_type_code adj_charge_type_code
, adj.charge_subtype_code adj_charge_subtype_code
, adj.range_break_quantity adj_range_break_quantity
, adj.accrual_conversion_rate adj_accrual_conv_rate
, adj.pricing_group_sequence adj_pricing_group_seq
, adj.accrual_flag adj_accrual_flag
, adj.benefit_qty adj_benefit_qty
, adj.benefit_uom_code adj_benefit_uom_code
, adj.expiration_date adj_expiration_date
, adj.rebate_transaction_type_code adj_rebate_txn_type_code
, adj.price_break_type_code adj_price_break_type_code
, adj.substitution_attribute adj_substitution_attribute
, adj.proration_type_code adj_proration_type_code
, adj.include_on_returns_flag adj_include_on_returns
, nvl(adj.operand_per_pqty, adj.operand) adj_operand
, adj.adjusted_amount_per_pqty adj_adjusted_amount
, adj.arithmetic_operator adj_arithmetic_operator
, ql.expiration_date
, ql.proration_type_code
, ql.include_on_returns_flag
, ql.rebate_transaction_type_code
, ldet.pricing_status_text
, adj.price_adjustment_id
, ldet.order_qty_adj_amt ord_qty_adjamt
, adj.adjusted_amount adj_ord_qty_adjamt
, ldet.order_qty_operand ord_qty_operand
, adj.operand adj_ord_qty_operand
FROM qp_int_lines line
, oe_price_adjustments adj
,qp_ldets_v ldet
, qp_list_lines ql
-- WHERE p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
and line.line_index = ldet.line_index
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
,QP_PREQ_PUB.G_STATUS_UPDATED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and (line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and line.line_id = adj.header_id)
and ldet.list_line_id = adj.list_line_id
and ldet.list_line_id = ql.list_line_id
UNION
SELECT ldet.line_index line_index
, ldet.line_detail_index line_detail_index
, ldet.list_line_id created_from_list_line_id
, ldet.process_code process_code
, ldet.price_break_type_code price_break_type_code
, ldet.pricing_group_sequence pricing_group_sequence
, ldet.operand_calculation_code operand_calculation_code
, ldet.operand_value operand_value
, ldet.adjustment_amount adjustment_amount
, ldet.substitution_attribute substitution_type_code
, ldet.substitution_value_to substitution_value_to
, ldet.pricing_phase_id pricing_phase_id
, ldet.applied_flag applied_flag
, ldet.automatic_flag automatic_flag
, ldet.override_flag override_flag
, ldet.benefit_qty benefit_qty
, ldet.benefit_uom_code benefit_uom_code
, ldet.accrual_flag accrual_flag
, ldet.accrual_conversion_rate accrual_conversion_rate
, ldet.charge_type_code charge_type_code
, ldet.charge_subtype_code charge_subtype_code
, ldet.line_quantity line_quantity
, adj.automatic_flag adj_automatic_flag
, adj.created_from_list_line_id adj_list_line_id
, adj.substitution_value_from adj_modified_from
, adj.substitution_value_to adj_modified_to
, adj.override_flag adj_update_allowed
, adj.updated_flag adj_updated_flag
, adj.applied_flag adj_applied_flag
, adj.pricing_phase_id adj_pricing_phase_id
, adj.charge_type_code adj_charge_type_code
, adj.charge_subtype_code adj_charge_subtype_code
, adj.line_quantity adj_range_break_quantity
, adj.accrual_conversion_rate adj_accrual_conv_rate
, adj.pricing_group_sequence adj_pricing_group_seq
, adj.accrual_flag adj_accrual_flag
, adj.benefit_qty adj_benefit_qty
, adj.benefit_uom_code adj_benefit_uom_code
, ql.expiration_date adj_expiration_date
, ql.rebate_transaction_type_code adj_rebate_txn_type_code
, adj.price_break_type_code adj_price_break_type_code
, adj.substitution_type_code adj_substitution_attribute
, ql.proration_type_code adj_proration_type_code
, ql.include_on_returns_flag adj_include_on_returns
, adj.operand_value adj_operand
, adj.adjustment_amount adj_adjusted_amount
, adj.operand_calculation_code adj_arithmetic_operator
, ql.expiration_date
, ql.proration_type_code
, ql.include_on_returns_flag
, ql.rebate_transaction_type_code
, ldet.pricing_status_text
, adj.price_adjustment_id
, ldet.order_qty_adj_amt ord_qty_adjamt
, adj.order_qty_adj_amt adj_ord_qty_adjamt
, ldet.order_qty_operand ord_qty_operand
, adj.order_qty_operand adj_ord_qty_operand
FROM qp_int_lines line
, qp_int_ldets adj
,qp_ldets_v ldet
, qp_list_lines ql
-- WHERE p_request_type_code <> 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
and line.line_index = ldet.line_index
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
,QP_PREQ_PUB.G_STATUS_UPDATED)
and adj.line_index = line.line_index
and adj.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
and ldet.list_line_id = adj.created_from_list_line_id
and ldet.list_line_id = ql.list_line_id;
SELECT line_index,
line_detail_index,
pricing_status_code,
context,
attribute,
value_from
FROM qp_npreq_line_attrs_tmp
WHERE line_detail_index is not null;
SELECT line_index,
line_detail_index,
pricing_status_code,
context,
attribute,
value_from
FROM qp_int_line_attrs
WHERE line_detail_index is not null;
l_adj_update_allowed_tbl QP_PREQ_GRP.FLAG_TYPE;
l_adj_updated_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
qp_preq_grp.engine_debug('begin update check clnup '||p_cleanup_flag);
qp_preq_grp.engine_debug('begin update check reqtype '||p_request_type_code);
qp_preq_grp.engine_debug('begin update check viewcode '||p_view_code);
qp_preq_grp.engine_debug('in update cleanup '||p_cleanup_flag);
OPEN l_update_cur;
l_cur_line_index_tbl.delete;
l_cur_dtl_index_tbl.delete;
l_cur_list_line_id_tbl.delete;
l_cur_process_code_tbl.delete;
l_cur_price_brk_type_tbl.delete;
l_cur_pricing_grp_seq_tbl.delete;
l_cur_operator_tbl.delete;
l_cur_operand_tbl.delete;
l_cur_adj_amt_tbl.delete;
l_cur_substn_attr_tbl.delete;
l_cur_substn_val_to_tbl.delete;
l_cur_phase_id_tbl.delete;
l_cur_applied_flag_tbl.delete;
l_cur_automatic_flag_tbl.delete;
l_cur_override_flag_tbl.delete;
l_cur_benefit_qty_tbl.delete;
l_cur_benefit_uom_code_tbl.delete;
l_cur_accrual_flag_tbl.delete;
l_cur_accr_conv_rate_tbl.delete;
l_cur_charge_type_tbl.delete;
l_cur_charge_subtype_tbl.delete;
l_cur_line_qty_tbl.delete;
l_adj_automatic_flag_tbl.delete;
l_adj_line_id_tbl.delete;
--l_adj_header_id_tbl.delete;
--l_adj_list_line_id_tbl.delete;
l_adj_modified_from_tbl.delete;
l_adj_modified_to_tbl.delete;
l_adj_update_allowed_tbl.delete;
l_adj_updated_flag_tbl.delete;
l_adj_applied_flag_tbl.delete;
l_adj_phase_id_tbl.delete;
l_adj_charge_type_tbl.delete;
l_adj_charge_subtype_tbl.delete;
l_adj_range_break_qty_tbl.delete;
l_adj_accrual_conv_rate_tbl.delete;
l_adj_pricing_grp_seq_tbl.delete;
l_adj_accrual_flag_tbl.delete;
l_adj_benefit_qty_tbl.delete;
l_adj_benefit_uom_code_tbl.delete;
l_adj_exp_date_tbl.delete;
l_adj_rebate_txn_type_tbl.delete;
l_adj_price_brk_type_tbl.delete;
l_adj_substn_attr_tbl.delete;
l_adj_prorat_type_tbl.delete;
l_adj_include_ret_flag_tbl.delete;
l_adj_operand_pqty_tbl.delete;
l_adj_adj_amt_pqty_tbl.delete;
l_adj_operator_tbl.delete;
l_cur_exp_date_tbl.delete;
l_cur_prorat_type_tbl.delete;
l_cur_include_ret_flag_tbl.delete;
l_cur_reb_txn_type_tbl.delete;
l_cur_prc_sts_txt_tbl.delete;
l_cur_price_adj_id_tbl.delete;
l_cur_ord_qty_adjamt.delete;
l_adj_ord_qty_adjamt.delete;
l_cur_ord_qty_operand.delete;
l_adj_ord_qty_operand.delete;
FETCH l_update_cur
BULK COLLECT INTO
l_cur_line_index_tbl,
l_cur_dtl_index_tbl,
l_cur_list_line_id_tbl,
l_cur_process_code_tbl,
l_cur_price_brk_type_tbl,
l_cur_pricing_grp_seq_tbl,
l_cur_operator_tbl,
l_cur_operand_tbl,
l_cur_adj_amt_tbl,
l_cur_substn_attr_tbl,
l_cur_substn_val_to_tbl,
-- l_cur_prc_sts_txt_tbl,
l_cur_phase_id_tbl,
l_cur_applied_flag_tbl,
l_cur_automatic_flag_tbl,
l_cur_override_flag_tbl,
l_cur_benefit_qty_tbl,
l_cur_benefit_uom_code_tbl,
l_cur_accrual_flag_tbl,
l_cur_accr_conv_rate_tbl,
l_cur_charge_type_tbl,
l_cur_charge_subtype_tbl,
l_cur_line_qty_tbl,
l_adj_automatic_flag_tbl,
l_adj_line_id_tbl,
--l_adj_header_id_tbl,
--l_adj_list_line_id_tbl,
l_adj_modified_from_tbl,
l_adj_modified_to_tbl,
l_adj_update_allowed_tbl,
l_adj_updated_flag_tbl,
l_adj_applied_flag_tbl,
l_adj_phase_id_tbl,
l_adj_charge_type_tbl,
l_adj_charge_subtype_tbl,
l_adj_range_break_qty_tbl,
l_adj_accrual_conv_rate_tbl,
l_adj_pricing_grp_seq_tbl,
l_adj_accrual_flag_tbl,
l_adj_benefit_qty_tbl,
l_adj_benefit_uom_code_tbl,
l_adj_exp_date_tbl,
l_adj_rebate_txn_type_tbl,
l_adj_price_brk_type_tbl,
l_adj_substn_attr_tbl,
l_adj_prorat_type_tbl,
l_adj_include_ret_flag_tbl,
l_adj_operand_pqty_tbl,
l_adj_adj_amt_pqty_tbl,
l_adj_operator_tbl,
l_cur_exp_date_tbl,
l_cur_prorat_type_tbl,
l_cur_include_ret_flag_tbl,
l_cur_reb_txn_type_tbl,
l_cur_prc_sts_txt_tbl,
l_cur_price_adj_id_tbl,
l_cur_ord_qty_adjamt,
l_adj_ord_qty_adjamt,
l_cur_ord_qty_operand,
l_adj_ord_qty_operand
LIMIT nrows;
qp_preq_grp.engine_debug('loop update cur line ind '
||l_cur_line_index_tbl(i)||' line_dtl_index '
||l_cur_dtl_index_tbl(i)||' price_adj_id '
||l_cur_price_adj_id_tbl(i));
||' adj override '||l_adj_update_allowed_tbl(i));
nvl(l_adj_update_allowed_tbl(i),' ')
AND nvl(l_cur_substn_attr_tbl(i),'NULL') =
nvl(l_adj_substn_attr_tbl(i),'NULL')
AND nvl(l_cur_substn_val_to_tbl(i), 'NULL') =
nvl(l_adj_modified_to_tbl(i), 'NULL')
AND nvl(l_cur_benefit_qty_tbl(i),0) =
nvl(l_adj_benefit_qty_tbl(i),0)
AND nvl(l_cur_benefit_uom_code_tbl(i),'NULL') =
nvl(l_adj_benefit_uom_code_tbl(i),'NULL')
AND nvl(l_cur_accrual_flag_tbl(i),' ') =
nvl(l_adj_accrual_flag_tbl(i),'NULL')
AND nvl(l_cur_accr_conv_rate_tbl(i),0) =
nvl(l_adj_accrual_conv_rate_tbl(i),0)
AND nvl(l_cur_reb_txn_type_tbl(i),'NULL') =
nvl(l_adj_rebate_txn_type_tbl(i),'NULL')
AND nvl(l_cur_prorat_type_tbl(i),'NULL') =
nvl(l_adj_prorat_type_tbl(i),'NULL')
AND nvl(l_cur_include_ret_flag_tbl(i), ' ') =
nvl(l_adj_include_ret_flag_tbl(i),' ')
AND nvl(l_cur_exp_date_tbl(i),FND_API.G_MISS_DATE) =
nvl(l_adj_exp_date_tbl(i), FND_API.G_MISS_DATE)
AND nvl(l_cur_line_qty_tbl(i),FND_API.G_MISS_NUM) =
nvl(l_adj_range_break_qty_tbl(i), FND_API.G_MISS_NUM)
AND nvl(l_cur_ord_qty_adjamt(i),FND_API.G_MISS_NUM) =
nvl(l_adj_ord_qty_adjamt(i), FND_API.G_MISS_NUM)
AND nvl(l_cur_ord_qty_operand(i),FND_API.G_MISS_NUM) =
nvl(l_adj_ord_qty_operand(i), FND_API.G_MISS_NUM)
THEN
x:=x+1;
qp_preq_grp.engine_debug('if update check '||x);
qp_preq_grp.engine_debug('update check dtls '
||l_cur_dtl_index_tbl(i)||' adj_id '
||l_cur_price_adj_id_tbl(i)||' id '
||l_cur_list_line_id_tbl(i)||' adj adj amt '
||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
||l_cur_adj_amt_tbl(i));
qp_preq_grp.engine_debug('else update check '||x);
qp_preq_grp.engine_debug('update check dtls '
||l_cur_dtl_index_tbl(i)||' adj_id '
||l_cur_price_adj_id_tbl(i)||' id '
||l_cur_list_line_id_tbl(i)||' adj adj amt '
||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
||l_cur_adj_amt_tbl(i));
l_process_code_tbl(x) := QP_PREQ_PUB.G_STATUS_UPDATED;
CLOSE l_update_cur;
OPEN l_update_int_cur;
l_cur_line_index_tbl.delete;
l_cur_dtl_index_tbl.delete;
l_cur_list_line_id_tbl.delete;
l_cur_process_code_tbl.delete;
l_cur_price_brk_type_tbl.delete;
l_cur_pricing_grp_seq_tbl.delete;
l_cur_operator_tbl.delete;
l_cur_operand_tbl.delete;
l_cur_adj_amt_tbl.delete;
l_cur_substn_attr_tbl.delete;
l_cur_substn_val_to_tbl.delete;
l_cur_phase_id_tbl.delete;
l_cur_applied_flag_tbl.delete;
l_cur_automatic_flag_tbl.delete;
l_cur_override_flag_tbl.delete;
l_cur_benefit_qty_tbl.delete;
l_cur_benefit_uom_code_tbl.delete;
l_cur_accrual_flag_tbl.delete;
l_cur_accr_conv_rate_tbl.delete;
l_cur_charge_type_tbl.delete;
l_cur_charge_subtype_tbl.delete;
l_cur_line_qty_tbl.delete;
l_adj_automatic_flag_tbl.delete;
l_adj_line_id_tbl.delete;
--l_adj_header_id_tbl.delete;
--l_adj_list_line_id_tbl.delete;
l_adj_modified_from_tbl.delete;
l_adj_modified_to_tbl.delete;
l_adj_update_allowed_tbl.delete;
l_adj_updated_flag_tbl.delete;
l_adj_applied_flag_tbl.delete;
l_adj_phase_id_tbl.delete;
l_adj_charge_type_tbl.delete;
l_adj_charge_subtype_tbl.delete;
l_adj_range_break_qty_tbl.delete;
l_adj_accrual_conv_rate_tbl.delete;
l_adj_pricing_grp_seq_tbl.delete;
l_adj_accrual_flag_tbl.delete;
l_adj_benefit_qty_tbl.delete;
l_adj_benefit_uom_code_tbl.delete;
l_adj_exp_date_tbl.delete;
l_adj_rebate_txn_type_tbl.delete;
l_adj_price_brk_type_tbl.delete;
l_adj_substn_attr_tbl.delete;
l_adj_prorat_type_tbl.delete;
l_adj_include_ret_flag_tbl.delete;
l_adj_operand_pqty_tbl.delete;
l_adj_adj_amt_pqty_tbl.delete;
l_adj_operator_tbl.delete;
l_cur_exp_date_tbl.delete;
l_cur_prorat_type_tbl.delete;
l_cur_include_ret_flag_tbl.delete;
l_cur_reb_txn_type_tbl.delete;
l_cur_prc_sts_txt_tbl.delete;
l_cur_price_adj_id_tbl.delete;
l_cur_ord_qty_adjamt.delete;
l_adj_ord_qty_adjamt.delete;
l_cur_ord_qty_operand.delete;
l_adj_ord_qty_operand.delete;
FETCH l_update_int_cur
BULK COLLECT INTO
l_cur_line_index_tbl,
l_cur_dtl_index_tbl,
l_cur_list_line_id_tbl,
l_cur_process_code_tbl,
l_cur_price_brk_type_tbl,
l_cur_pricing_grp_seq_tbl,
l_cur_operator_tbl,
l_cur_operand_tbl,
l_cur_adj_amt_tbl,
l_cur_substn_attr_tbl,
l_cur_substn_val_to_tbl,
-- l_cur_prc_sts_txt_tbl,
l_cur_phase_id_tbl,
l_cur_applied_flag_tbl,
l_cur_automatic_flag_tbl,
l_cur_override_flag_tbl,
l_cur_benefit_qty_tbl,
l_cur_benefit_uom_code_tbl,
l_cur_accrual_flag_tbl,
l_cur_accr_conv_rate_tbl,
l_cur_charge_type_tbl,
l_cur_charge_subtype_tbl,
l_cur_line_qty_tbl,
l_adj_automatic_flag_tbl,
l_adj_line_id_tbl,
--l_adj_header_id_tbl,
--l_adj_list_line_id_tbl,
l_adj_modified_from_tbl,
l_adj_modified_to_tbl,
l_adj_update_allowed_tbl,
l_adj_updated_flag_tbl,
l_adj_applied_flag_tbl,
l_adj_phase_id_tbl,
l_adj_charge_type_tbl,
l_adj_charge_subtype_tbl,
l_adj_range_break_qty_tbl,
l_adj_accrual_conv_rate_tbl,
l_adj_pricing_grp_seq_tbl,
l_adj_accrual_flag_tbl,
l_adj_benefit_qty_tbl,
l_adj_benefit_uom_code_tbl,
l_adj_exp_date_tbl,
l_adj_rebate_txn_type_tbl,
l_adj_price_brk_type_tbl,
l_adj_substn_attr_tbl,
l_adj_prorat_type_tbl,
l_adj_include_ret_flag_tbl,
l_adj_operand_pqty_tbl,
l_adj_adj_amt_pqty_tbl,
l_adj_operator_tbl,
l_cur_exp_date_tbl,
l_cur_prorat_type_tbl,
l_cur_include_ret_flag_tbl,
l_cur_reb_txn_type_tbl,
l_cur_prc_sts_txt_tbl,
l_cur_price_adj_id_tbl,
l_cur_ord_qty_adjamt,
l_adj_ord_qty_adjamt,
l_cur_ord_qty_operand,
l_adj_ord_qty_operand
LIMIT nrows;
qp_preq_grp.engine_debug('loop update cur line ind '
||l_cur_line_index_tbl(i)||' line_dtl_index '
||l_cur_dtl_index_tbl(i)||' price_adj_id '
||l_cur_price_adj_id_tbl(i));
||' adj override '||l_adj_update_allowed_tbl(i));
nvl(l_adj_update_allowed_tbl(i),' ')
AND nvl(l_cur_substn_attr_tbl(i),'NULL') =
nvl(l_adj_substn_attr_tbl(i),'NULL')
AND nvl(l_cur_substn_val_to_tbl(i), 'NULL') =
nvl(l_adj_modified_to_tbl(i), 'NULL')
AND nvl(l_cur_benefit_qty_tbl(i),0) =
nvl(l_adj_benefit_qty_tbl(i),0)
AND nvl(l_cur_benefit_uom_code_tbl(i),'NULL') =
nvl(l_adj_benefit_uom_code_tbl(i),'NULL')
AND nvl(l_cur_accrual_flag_tbl(i),' ') =
nvl(l_adj_accrual_flag_tbl(i),'NULL')
AND nvl(l_cur_accr_conv_rate_tbl(i),0) =
nvl(l_adj_accrual_conv_rate_tbl(i),0)
AND nvl(l_cur_reb_txn_type_tbl(i),'NULL') =
nvl(l_adj_rebate_txn_type_tbl(i),'NULL')
AND nvl(l_cur_prorat_type_tbl(i),'NULL') =
nvl(l_adj_prorat_type_tbl(i),'NULL')
AND nvl(l_cur_include_ret_flag_tbl(i), ' ') =
nvl(l_adj_include_ret_flag_tbl(i),' ')
AND nvl(l_cur_exp_date_tbl(i),FND_API.G_MISS_DATE) =
nvl(l_adj_exp_date_tbl(i), FND_API.G_MISS_DATE)
AND nvl(l_cur_line_qty_tbl(i),FND_API.G_MISS_NUM) =
nvl(l_adj_range_break_qty_tbl(i), FND_API.G_MISS_NUM)
AND nvl(l_cur_ord_qty_adjamt(i),FND_API.G_MISS_NUM) =
nvl(l_adj_ord_qty_adjamt(i), FND_API.G_MISS_NUM)
AND nvl(l_cur_ord_qty_operand(i),FND_API.G_MISS_NUM) =
nvl(l_adj_ord_qty_operand(i), FND_API.G_MISS_NUM)
THEN
x:=x+1;
qp_preq_grp.engine_debug('if update check '||x);
qp_preq_grp.engine_debug('update check dtls '
||l_cur_dtl_index_tbl(i)||' adj_id '
||l_cur_price_adj_id_tbl(i)||' id '
||l_cur_list_line_id_tbl(i)||' adj adj amt '
||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
||l_cur_adj_amt_tbl(i));
qp_preq_grp.engine_debug('else update check '||x);
qp_preq_grp.engine_debug('update check dtls '
||l_cur_dtl_index_tbl(i)||' adj_id '
||l_cur_price_adj_id_tbl(i)||' id '
||l_cur_list_line_id_tbl(i)||' adj adj amt '
||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
||l_cur_adj_amt_tbl(i));
l_process_code_tbl(x) := QP_PREQ_PUB.G_STATUS_UPDATED;
CLOSE l_update_int_cur;
UPDATE qp_npreq_ldets_tmp
SET process_code = l_process_code_tbl(x)
, price_adjustment_id = l_price_adj_id_tbl(x)
, pricing_status_text = l_pricing_sts_text_tbl(x)
WHERE line_index = l_line_index_tbl(x)
and line_detail_index = l_line_dtl_index_tbl(x);
UPDATE qp_int_ldets
SET process_code = l_process_code_tbl(x)
, price_adjustment_id = l_price_adj_id_tbl(x)
, pricing_status_text = l_pricing_sts_text_tbl(x)
WHERE line_index = l_line_index_tbl(x)
and line_detail_index = l_line_dtl_index_tbl(x);
UPDATE qp_npreq_line_attrs_tmp lattr
SET lattr.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
WHERE lattr.attribute_type = QP_PREQ_PUB.G_PRICING_TYPE
and EXISTS ( SELECT 'X' FROM
qp_npreq_lines_tmp line,
qp_npreq_ldets_tmp ldet,
oe_price_adjustments adj,
oe_price_adj_attribs_v attr
WHERE line.price_flag in
(QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.pricing_status_code in
(QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and ldet.line_index = line.line_index
and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and adj.line_id = line.line_id
and attr.price_adjustment_id = adj.price_adjustment_id
and attr.pricing_context = lattr.context
and attr.pricing_attribute = lattr.attribute
and attr.pricing_attr_value_from = lattr.value_from
UNION
SELECT 'X' FROM
qp_npreq_lines_tmp line,
qp_npreq_ldets_tmp ldet,
oe_price_adjustments adj,
oe_price_adj_attribs_v attr
WHERE line.price_flag in
(QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
and line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and line.pricing_status_code in
(QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
and ldet.line_index = line.line_index
and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and adj.header_id = line.line_id
and adj.line_id is null
and attr.price_adjustment_id = adj.price_adjustment_id
and attr.pricing_context = lattr.context
and attr.pricing_attribute = lattr.attribute
and attr.pricing_attr_value_from = lattr.value_from);
PROCEDURE Insert_Rltd_Lines(p_request_type_code IN VARCHAR2,
p_calculate_flag IN VARCHAR2,
p_event_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
CURSOR l_rltd_line_info_cur IS
SELECT /*+ ORDERED USE_NL(adj_pbh ass adj attr) */
/*index(ass OE_PRICE_ADJ_ASSOCS_N3)*/
line.line_index line_index,
adj_pbh.price_adjustment_id line_detail_index,
line.line_index related_line_index,
ass.rltd_price_adj_id related_line_detail_index,
adj_pbh.list_line_id list_line_id,
adj.list_line_id related_list_line_id,
adj.list_line_type_code related_list_line_type,
adj.arithmetic_operator,
nvl(adj.operand_per_pqty, adj.operand) operand,
adj_pbh.pricing_group_sequence,
adj_pbh.price_break_type_code,
adj_pbh.modifier_level_code,
attr.pricing_attr_value_from setup_value_from,
attr.pricing_attr_value_to setup_value_to,
adj_pbh.range_break_quantity,
--added these columns to insert child break lines for bug 3314259
adj.pricing_phase_id,
adj.automatic_flag,
adj.applied_flag,
adj.updated_flag,
attr.list_header_id,
adj.list_line_no
FROM qp_npreq_lines_tmp line
,oe_price_adjustments adj_pbh
,oe_price_adj_assocs ass
,oe_price_adjustments adj
, qp_pricing_attributes attr
-- WHERE p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
and line.line_id = adj_pbh.line_id
and adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
or p_event_code = ',' --we pad comma when it is null
or adj_pbh.pricing_phase_id not in
(select ph.pricing_phase_id
from qp_event_phases evt , qp_pricing_phases ph
where ph.pricing_phase_id = evt.pricing_phase_id
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,evt.pricing_event_code||',') > 0
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id,line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
QP_PREQ_PUB.G_YES))))
and adj_pbh.price_adjustment_id = ass.price_adjustment_id
and ass.rltd_price_adj_id = adj.price_adjustment_id
and attr.list_line_id = adj.list_line_id
and attr.pricing_attribute_context = QP_PREQ_PUB.G_PRIC_VOLUME_CONTEXT
UNION
SELECT /*+ ORDERED USE_NL(adj_pbh ass adj attr) */
--/*+ index(ass OE_PRICE_ADJ_ASSOCS_N3)*/
-- (adj_pbh.quote_header_id+nvl(adj_pbh.quote_line_id,0)) line_index,
line.line_index,
adj_pbh.line_detail_index line_detail_index,
-- adj.quote_line_id related_line_index,
line.line_index related_line_index,
ass.related_line_detail_index related_line_detail_index,
adj_pbh.created_from_list_line_id list_line_id,
adj.created_from_list_line_id related_list_line_id,
adj.created_from_list_line_type related_list_line_type,
adj.operand_calculation_code arithmetic_operator,
adj.operand_value operand,
adj_pbh.pricing_group_sequence,
adj_pbh.price_break_type_code,
adj_pbh.modifier_level_code,
attr.pricing_attr_value_from setup_value_from,
attr.pricing_attr_value_to setup_value_to,
adj_pbh.line_quantity range_break_quantity,
--added these columns to insert child break lines for bug 3314259
adj.pricing_phase_id,
adj.automatic_flag,
adj.applied_flag,
adj.updated_flag,
attr.list_header_id,
adj.list_line_no
FROM qp_npreq_lines_tmp line
,qp_npreq_ldets_tmp adj_pbh
,qp_npreq_rltd_lines_tmp ass
,qp_npreq_ldets_tmp adj
, qp_pricing_attributes attr
-- WHERE p_request_type_code <> 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
and line.line_index = adj_pbh.line_index
and adj_pbh.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
and adj_pbh.created_from_list_line_type = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
or p_event_code = ',' --we pad comma when it is null
or adj_pbh.pricing_phase_id not in
(select ph.pricing_phase_id
from qp_event_phases evt, qp_pricing_phases ph
where ph.pricing_phase_id = evt.pricing_phase_id
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,evt.pricing_event_code||',') > 0
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
QP_PREQ_PUB.G_YES))))
and ass.line_detail_index = adj_pbh.line_detail_index
and adj.line_detail_index = ass.related_line_detail_index
and attr.list_line_id = adj.created_from_list_line_id
and attr.pricing_attribute_context = QP_PREQ_PUB.G_PRIC_VOLUME_CONTEXT;
SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
FROM
qp_npreq_lines_tmp line,
oe_price_adjustments adj
-- WHERE p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
and adj.line_id = line.line_id
and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or adj.updated_flag = QP_PREQ_PUB.G_YES
or p_event_code = ',' -- we pad ',' when it is null
or adj.pricing_phase_id not in
(select ph.pricing_phase_id
from qp_event_phases evt, qp_pricing_phases ph
where ph.pricing_phase_id = evt.pricing_phase_id
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,evt.pricing_event_code||',') > 0
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
QP_PREQ_PUB.G_YES))))
UNION
SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N1) */ 'Y'
FROM
qp_npreq_lines_tmp line,
oe_price_adjustments adj
-- WHERE p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
and adj.header_id = line.line_id
and line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or p_event_code = ',' -- we pad ',' when it is null
or adj.pricing_phase_id not in
(select ph.pricing_phase_id
from qp_event_phases evt, qp_pricing_phases ph
where ph.pricing_phase_id = evt.pricing_phase_id
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,evt.pricing_event_code||',') > 0
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
QP_PREQ_PUB.G_YES))))
UNION
SELECT 'Y'
FROM
qp_npreq_lines_tmp line,
qp_npreq_ldets_tmp adj
-- WHERE p_request_type_code <> 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
and adj.line_index = line.line_index
and adj.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
and adj.created_from_list_line_type = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or p_event_code = ',' -- we pad ',' when it is null
or adj.pricing_phase_id not in
(select ph.pricing_phase_id
from qp_event_phases evt, qp_pricing_phases ph
where ph.pricing_phase_id = evt.pricing_phase_id
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,evt.pricing_event_code||',') > 0
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))));
l_updated_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
QP_PREQ_GRP.engine_debug('Begin Insert_rltd_lines rqtyp '||p_request_type_code);
QP_PREQ_GRP.engine_debug('Begin Insert_rltd_lines calcflag '||p_calculate_flag);
QP_PREQ_GRP.engine_debug('Begin Insert_rltd_lines event '||p_event_code);
l_line_index_tbl.delete;
l_line_dtl_index_tbl.delete;
l_rltd_line_index_tbl.delete;
l_rltd_line_dtl_index_tbl.delete;
l_list_line_id_tbl.delete;
l_rltd_list_line_id_tbl.delete;
l_list_line_type_code_tbl.delete;
l_operand_calc_code_tbl.delete;
l_operand_tbl.delete;
l_pricing_group_seq_tbl.delete;
l_price_brk_type_code_tbl.delete;
l_setup_value_from_tbl.delete;
l_setup_value_to_tbl.delete;
l_qualifier_value_tbl.delete;
l_mod_level_code_tbl.delete;
l_pricing_phase_id_tbl.delete;
l_auto_flag_tbl.delete;
l_applied_flag_tbl.delete;
l_updated_flag_tbl.delete;
l_list_hdr_id_tbl.delete;
l_list_line_no_tbl.delete;
l_updated_flag_tbl(N) := I.updated_flag;
QP_PREQ_GRP.engine_debug('price brk before insert ');
INSERT INTO qp_npreq_rltd_lines_tmp
(
pricing_status_text,
line_index,
line_detail_index,
relationship_type_code,
related_line_index,
related_line_detail_index,
pricing_status_code,
list_line_id,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
pricing_group_sequence,
relationship_type_detail,
setup_value_from,
setup_value_to,
qualifier_value
)
VALUES
(
G_CALC_INSERT,
l_line_index_tbl(I),
l_line_dtl_index_tbl(I),
QP_PREQ_PUB.G_PBH_LINE,
l_rltd_line_index_tbl(I),
l_rltd_line_dtl_index_tbl(I),
QP_PREQ_PUB.G_STATUS_NEW,
l_list_line_id_tbl(I),
l_rltd_list_line_id_tbl(I),
l_list_line_type_code_tbl(I),
l_operand_calc_code_tbl(I),
l_operand_tbl(I),
l_pricing_group_seq_tbl(I),
l_price_brk_type_code_tbl(I),
l_setup_value_from_tbl(I),
l_setup_value_to_tbl(I),
l_qualifier_value_tbl(I)
);
--added this insert for bug 3314259 to insert child break lines to tmp table
FORALL I IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
INSERT INTO qp_npreq_ldets_tmp
(
line_detail_index
,line_index
,line_detail_type_code
,pricing_status_code
,pricing_status_text
,process_code
,created_from_list_header_id
,created_from_list_line_id
,created_from_list_line_type
,adjustment_amount
,operand_value
,modifier_level_code
,price_break_type_code
-- ,line_quantity
,operand_calculation_code
,pricing_group_sequence
-- ,created_from_list_type_code
,applied_flag
-- ,limit_code
-- ,limit_text
,list_line_no
-- ,charge_type_code
-- ,charge_subtype_code
,updated_flag
,automatic_flag
,pricing_phase_id)
values
(l_rltd_line_dtl_index_tbl(I)
,l_rltd_line_index_tbl(I)
,QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- bug 3487840, pass QP_PREQ_GRP.G_CHILD_DETAIL_TYPE instead of null
,QP_PREQ_PUB.G_STATUS_NEW
,''
,QP_PREQ_PUB.G_STATUS_NEW
,l_list_hdr_id_tbl(I) --put list_header_id
,l_rltd_list_line_id_tbl(I)
,l_list_line_type_code_tbl(I)
,0
,l_operand_tbl(I)
,l_mod_level_code_tbl(I) --modifier_level_code
,l_price_brk_type_code_tbl(I)
,l_operand_calc_code_tbl(I)
,l_pricing_group_seq_tbl(I)
,l_applied_flag_tbl(I) --QP_PREQ_PUB.G_YES
,l_list_line_no_tbl(I)
,l_updated_flag_tbl(I) --QP_PREQ_PUB.G_YES
,l_auto_flag_tbl(I) --QP_PREQ_PUB.G_NO
,l_pricing_phase_id_tbl(I));
QP_PREQ_GRP.engine_debug('price brk after insert ');
QP_PREQ_GRP.engine_debug('End Insert_rltd_lines');
QP_PREQ_GRP.engine_debug('Error Insert_rltd_lines'||SQLERRM);
x_return_status_text := 'Error in QP_CLEANUP_ADJUSTMENTS.Insert_Rltd_Lines '||SQLERRM;
END Insert_Rltd_Lines;
select /*+ ORDERED USE_NL(adj qplh) */
adj.list_line_id created_from_list_line_id
, line.line_index line_ind
, line.line_index curr_line_index
, line.line_id line_id
, adj.price_adjustment_id line_detail_index
, adj.list_line_type_code created_from_list_line_type
, adj.list_header_id created_from_list_header_id
, adj.applied_flag
, (line.updated_adjusted_unit_price
- line.adjusted_unit_price) amount_changed
, line.adjusted_unit_price
, adj.range_break_quantity priced_quantity
, line.priced_quantity line_priced_quantity
, line.updated_adjusted_unit_price
, adj.automatic_flag
, adj.update_allowed override_flag
, adj.pricing_group_sequence
, adj.arithmetic_operator operand_calculation_code
, nvl(adj.operand_per_pqty,adj.operand) operand_value
, nvl(adj.adjusted_amount_per_pqty,
adj.adjusted_amount) adjustment_amount -- 4757680
, line.unit_price
, adj.accrual_flag
, nvl(adj.updated_flag, QP_PREQ_PUB.G_NO)
, 'N' process_code
, 'N' pricing_status_code
, ' ' pricing_status_text
, adj.price_break_type_code
, adj.charge_type_code
, adj.charge_subtype_code
, line.rounding_factor
, adj.pricing_phase_id
, qplh.list_type_code created_from_list_type_code -- [4222237/4500246]
, '' limit_code
, '' limit_text
, adj.list_line_no
, adj.modifier_level_code
, adj.range_break_quantity group_quantity
, adj.range_break_quantity group_amount
, line.pricing_status_code line_pricing_status_code
, QP_PREQ_PUB.G_ADJ_LINE_TYPE is_ldet_rec
, line.line_type_code
, NULL net_amount_flag --bucketed_flag
, NULL calculation_code
, line.catchweight_qty
, line.actual_order_quantity
, line.line_unit_price
, line.line_quantity ordered_qty
, NULL line_detail_type_code
, line.line_category
, line.price_flag
from qp_npreq_lines_tmp line, oe_price_adjustments adj,
qp_list_headers_b qplh
-- where p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
where QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
-- bug# 2739322
-- not needed as this condition will always be false having this was causing the bug where
-- the adjustments against the freegood line are not getting selected
-- also we would like to get the adjustments for the freegood line
-- and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and adj.line_id = line.line_id
and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE,QP_PREQ_PUB.G_CALCULATE_ONLY)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.unit_price is not null -- bug 3501866, calculation to be done for line having unit price
and (adj.updated_flag = QP_PREQ_PUB.G_YES
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or (p_event_code is null and adj.updated_flag is null)
or adj.pricing_phase_id not in (select ph.pricing_phase_id
from qp_event_phases ev, qp_pricing_phases ph
--changes to enable multiple events passed as a string
where ph.pricing_phase_id = ev.pricing_phase_id
---introduced the end date condition for bug 3376902
and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,ev.pricing_event_code||',') > 0
-- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
QP_PREQ_PUB.G_YES))))
and adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL,QP_PREQ_PUB.G_LINE_GROUP)
-- and adj.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)
--commented to fetch auto overridden unapplied adj(user deleted)
-- and adj.applied_flag = QP_PREQ_PUB.G_YES
-- and adj.list_line_type_code IN (QP_PREQ_PUB.G_DISCOUNT
-- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
and qplh.list_header_id = adj.list_header_id
and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
QP_PREQ_PUB.G_AGR_LIST_HEADER)
and not exists (select 'x'
from oe_price_adj_assocs a, oe_price_adjustments b
where a.RLTD_PRICE_ADJ_ID = adj.price_adjustment_id
and b.price_adjustment_id = a.price_adjustment_id
and b.list_line_type_code = QP_PREQ_GRP.G_PRICE_BREAK_TYPE)
UNION
select /*+ ORDERED USE_NL(adj line qplh) index(adj OE_PRICE_ADJUSTMENTS_N1) dynamic_sampling(1) */ -- Bug No: 6753550
adj.list_line_id created_from_list_line_id
, line.line_index line_ind
, line.line_index curr_line_index
, line.line_id line_id
, adj.price_adjustment_id line_detail_index
, adj.list_line_type_code created_from_list_line_type
, adj.list_header_id created_from_list_header_id
, adj.applied_flag
, (line.updated_adjusted_unit_price
- line.adjusted_unit_price) amount_changed
, line.adjusted_unit_price
, adj.range_break_quantity priced_quantity
, line.priced_quantity line_priced_quantity
, line.updated_adjusted_unit_price
, adj.automatic_flag
, adj.update_allowed override_flag
, adj.pricing_group_sequence
, adj.arithmetic_operator operand_calculation_code
, nvl(adj.operand_per_pqty, adj.operand) operand_value
, adj.adjusted_amount_per_pqty adjustment_amount
, line.unit_price
, adj.accrual_flag
, nvl(adj.updated_flag, QP_PREQ_PUB.G_NO)
, 'N' process_code
, 'N' pricing_status_code
, ' ' pricing_status_text
, adj.price_break_type_code
, adj.charge_type_code
, adj.charge_subtype_code
, line.rounding_factor
, adj.pricing_phase_id
, qplh.list_type_code created_from_list_type_code -- [4222237/4500246]
, '' limit_code
, '' limit_text
, adj.list_line_no
, adj.modifier_level_code
, adj.range_break_quantity group_quantity
, adj.range_break_quantity group_amount
, line.pricing_status_code line_pricing_status_code
, QP_PREQ_PUB.G_ADJ_ORDER_TYPE is_ldet_rec
, line.line_type_code
, NULL net_amount_flag --bucketed_flag
, NULL calculation_code
, line.catchweight_qty
, line.actual_order_quantity
, line.line_unit_price
, line.line_quantity ordered_qty
, NULL line_detail_type_code
, line.line_category
, line.price_flag
from qp_npreq_lines_tmp line1, oe_price_adjustments adj
,qp_npreq_lines_tmp line, qp_list_headers_b qplh
-- where p_request_type_code = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
where QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
--and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
and line1.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line1.line_id = adj.header_id
and line1.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line1.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and (line1.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
OR nvl(line1.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and (line.unit_price is not null or line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL) -- bug 3501866
and (adj.updated_flag = QP_PREQ_PUB.G_YES
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
or (p_event_code is null and adj.updated_flag is null)
or adj.pricing_phase_id not in (select ph.pricing_phase_id
from qp_event_phases ev, qp_pricing_phases ph
--changes to enable multiple events passed as a string
where ph.pricing_phase_id = ev.pricing_phase_id
---introduced the end date condition for bug 3376902
and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,ev.pricing_event_code||',') > 0
-- Ravi changed from line.price_flag to line1.price_flag , bug# 2739322 where the order level discounts are not
-- getting selected with each line, since line has price_flag = 'Y' and is in phase
-- by making it line1.price_flag , the summary line's price_flag = 'P'(when there is a freegood line created with
-- partial price) it works as though it is out of phase as price_flag = 'P' and freeze_override_flag = 'N' and the
-- modifier is selected across all the lines
-- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
and adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
and adj.line_id is null
-- and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)
-- and ldet.applied_flag = QP_PREQ_PUB.G_YES
-- and adj.list_line_type_code IN (QP_PREQ_PUB.G_DISCOUNT
-- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
and qplh.list_header_id = adj.list_header_id
and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
QP_PREQ_PUB.G_AGR_LIST_HEADER)
UNION
select /*+ ORDERED USE_NL(ldet) index(ldet QP_PREQ_LDETS_TMP_N1) */
ldet.created_from_list_line_id
, line.line_index line_ind
, line.line_index curr_line_index
, line.line_id line_id
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.created_from_list_header_id
, ldet.applied_flag
, (line.updated_adjusted_unit_price
- line.adjusted_unit_price) amount_changed
, line.adjusted_unit_price
, ldet.line_quantity priced_quantity
, line.priced_quantity line_priced_quantity
, line.updated_adjusted_unit_price
, ldet.automatic_flag
, ldet.override_flag
, ldet.pricing_group_sequence
, ldet.operand_calculation_code
, ldet.operand_value
, ldet.adjustment_amount
, line.unit_price
, ldet.accrual_flag
, nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
, ldet.process_code
, ldet.pricing_status_code
, ldet.pricing_status_text
, ldet.price_break_type_code
, ldet.charge_type_code
, ldet.charge_subtype_code
, line.rounding_factor
, ldet.pricing_phase_id
, ldet.created_from_list_type_code
, ldet.limit_code
, substr(ldet.limit_text,1,240)
, ldet.list_line_no
, ldet.modifier_level_code
, ldet.group_quantity group_quantity
, ldet.group_amount group_amount
, line.pricing_status_code line_pricing_status_code
, QP_PREQ_PUB.G_LDET_LINE_TYPE is_ldet_rec
, line.line_type_code
, ldet.net_amount_flag net_amount_flag --bucketed_flag
, ldet.calculation_code calculation_code
, line.catchweight_qty
, line.actual_order_quantity
, line.line_unit_price
, line.line_quantity ordered_qty
, ldet.line_detail_type_code line_detail_type_code
, line.line_category
, line.price_flag
from qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet
where line.line_index = ldet.line_index
--and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.unit_price is not null -- bug 3501866, calculation to be done for line having unit price
and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)--,QP_PREQ_PUB.G_STATUS_UNCHANGED)
--fix for frt charge issue to calculate adj amt for manual frt charge
and (ldet.applied_flag = QP_PREQ_PUB.G_YES
or (ldet.applied_flag = QP_PREQ_PUB.G_NO
and ldet.created_from_list_line_type =
QP_PREQ_PUB.G_FREIGHT_CHARGE
and ldet.automatic_flag = QP_PREQ_PUB.G_NO))
-- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT
-- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
and ldet.created_from_list_type_code not in
(QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER)
and ldet.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL,QP_PREQ_PUB.G_LINE_GROUP)
and nvl(ldet.line_detail_type_code,'NULL') <> QP_PREQ_PUB.G_CHILD_DETAIL_TYPE
UNION
select /*+ ORDERED USE_NL(ldet line) index(ldet QP_PREQ_LDETS_TMP_N1) */
ldet.created_from_list_line_id
, line.line_index line_ind
, line.line_index curr_line_index
, line.line_id line_id
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.created_from_list_header_id
, ldet.applied_flag
, (line.updated_adjusted_unit_price
- line.adjusted_unit_price) amount_changed
, line.adjusted_unit_price
, ldet.line_quantity priced_quantity
, line.priced_quantity line_priced_quantity
, line.updated_adjusted_unit_price
, ldet.automatic_flag
, ldet.override_flag
, ldet.pricing_group_sequence
, ldet.operand_calculation_code
, ldet.operand_value
, ldet.adjustment_amount
, line.unit_price
, ldet.accrual_flag
, nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
, ldet.process_code
, ldet.pricing_status_code
, ldet.pricing_status_text
, ldet.price_break_type_code
, ldet.charge_type_code
, ldet.charge_subtype_code
, line.rounding_factor
, ldet.pricing_phase_id
, ldet.created_from_list_type_code
, ldet.limit_code
, substr(ldet.limit_text,1,240)
, ldet.list_line_no
, ldet.modifier_level_code
, ldet.group_quantity group_quantity
, ldet.group_amount group_amount
, line.pricing_status_code line_pricing_status_code
, QP_PREQ_PUB.G_LDET_ORDER_TYPE is_ldet_rec
, line.line_type_code
, ldet.net_amount_flag net_amount_flag --bucketed_flag
, ldet.calculation_code calculation_code
, line.catchweight_qty
, line.actual_order_quantity
, line.line_unit_price
, line.line_quantity ordered_qty
, ldet.line_detail_type_code line_detail_type_code
, line.line_category
, line.price_flag
from qp_npreq_lines_tmp line1, qp_npreq_ldets_tmp ldet
, qp_npreq_lines_tmp line
where --line.line_index = p_line_index
ldet.line_index = line1.line_index
--and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
and line1.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line1.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line1.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
and line1.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
-- and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and (line.unit_price is not null or line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL) -- bug 3501866
and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
AND ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)
--fix for frt charge issue to calculate adj amt for manual frt charge
and (ldet.applied_flag = QP_PREQ_PUB.G_YES
or (ldet.applied_flag = QP_PREQ_PUB.G_NO
and ldet.created_from_list_line_type =
QP_PREQ_PUB.G_FREIGHT_CHARGE
and ldet.automatic_flag = QP_PREQ_PUB.G_NO))
-- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT,
-- QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and ldet.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
and ldet.created_from_list_type_code not in
(QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER)
UNION
select /*+ ORDERED USE_NL(ldet qplh) index(ldet QP_PREQ_LDETS_TMP_N1) */
ldet.created_from_list_line_id
, line.line_index line_ind
, line.line_index curr_line_index
, line.line_id line_id
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.created_from_list_header_id
, ldet.applied_flag
, (line.updated_adjusted_unit_price
- line.adjusted_unit_price) amount_changed
, line.adjusted_unit_price
, ldet.line_quantity priced_quantity
, line.priced_quantity line_priced_quantity
, line.updated_adjusted_unit_price
, ldet.automatic_flag
, ldet.override_flag
, ldet.pricing_group_sequence
, ldet.operand_calculation_code
, ldet.operand_value
, ldet.adjustment_amount
, line.unit_price
, ldet.accrual_flag
, nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
, ldet.process_code
, ldet.pricing_status_code
, ldet.pricing_status_text
, ldet.price_break_type_code
, ldet.charge_type_code
, ldet.charge_subtype_code
, line.rounding_factor
, ldet.pricing_phase_id
, ldet.created_from_list_type_code
, ldet.limit_code
, substr(ldet.limit_text,1,240)
, ldet.list_line_no
, ldet.modifier_level_code
, ldet.group_quantity group_quantity
, ldet.group_amount group_amount
, line.pricing_status_code line_pricing_status_code
, QP_PREQ_PUB.G_ASO_LINE_TYPE is_ldet_rec
, line.line_type_code
, ldet.net_amount_flag net_amount_flag --bucketed_flag
, ldet.calculation_code calculation_code
, line.catchweight_qty
, line.actual_order_quantity
, line.line_unit_price
, line.line_quantity ordered_qty
, ldet.line_detail_type_code line_detail_type_code
, line.line_category
, line.price_flag
from qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet,
qp_list_headers_b qplh
-- where p_request_type_code <> 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
where nvl(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG,QP_PREQ_PUB.G_NO) <> QP_PREQ_PUB.G_YES
--and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
and line.unit_price is not null -- bug 3501866, calculation to be done for line having unit price
and ldet.line_index = line.line_index
and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
and (ldet.updated_flag = QP_PREQ_PUB.G_YES
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
-- or (p_event_code is null and ldet.updated_flag is null)
or ldet.pricing_phase_id not in (select ph.pricing_phase_id
from qp_event_phases ev, qp_pricing_phases ph
--changes to enable multiple events passed as a string
where ph.pricing_phase_id = ev.pricing_phase_id
---introduced the end date condition for bug 3376902
and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,ev.pricing_event_code||',') > 0
-- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
QP_PREQ_PUB.G_YES))))
and ldet.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL,QP_PREQ_PUB.G_LINE_GROUP)
--commented to fetch auto overridden unapplied adj(user deleted)
-- and ldet.applied_flag = QP_PREQ_PUB.G_YES
-- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT
-- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
and qplh.list_header_id = ldet.created_from_list_header_id
and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
QP_PREQ_PUB.G_AGR_LIST_HEADER)
and nvl(ldet.line_detail_type_code,'NULL') <> QP_PREQ_PUB.G_CHILD_DETAIL_TYPE -- updated in update_passed_in_pbh
UNION
select /*+ ORDERED USE_NL(ldet line qplh) index(ldet QP_PREQ_LDETS_TMP_N1) */
ldet.created_from_list_line_id
, line.line_index line_ind
, line.line_index curr_line_index
, line.line_id line_id
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.created_from_list_header_id
, ldet.applied_flag
, (line.updated_adjusted_unit_price
- line.adjusted_unit_price) amount_changed
, line.adjusted_unit_price
, ldet.line_quantity priced_quantity
, line.priced_quantity line_priced_quantity
, line.updated_adjusted_unit_price
, ldet.automatic_flag
, ldet.override_flag
, ldet.pricing_group_sequence
, ldet.operand_calculation_code
, ldet.operand_value
, ldet.adjustment_amount
, line.unit_price
, ldet.accrual_flag
, nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
, ldet.process_code
, ldet.pricing_status_code
, ldet.pricing_status_text
, ldet.price_break_type_code
, ldet.charge_type_code
, ldet.charge_subtype_code
, line.rounding_factor
, ldet.pricing_phase_id
, ldet.created_from_list_type_code
, ldet.limit_code
, substr(ldet.limit_text,1,240)
, ldet.list_line_no
, ldet.modifier_level_code
, ldet.group_quantity group_quantity
, ldet.group_amount group_amount
, line.pricing_status_code line_pricing_status_code
, QP_PREQ_PUB.G_ASO_ORDER_TYPE is_ldet_rec
, line.line_type_code
, ldet.net_amount_flag net_amount_flag --bucketed_flag
, ldet.calculation_code calculation_code
, line.catchweight_qty
, line.actual_order_quantity
, line.line_unit_price
, line.line_quantity ordered_qty
, ldet.line_detail_type_code line_detail_type_code
, line.line_category
, line.price_flag
from qp_npreq_lines_tmp line1, qp_npreq_ldets_tmp ldet
,qp_npreq_lines_tmp line, qp_list_headers_b qplh
-- where p_request_type_code <> 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
where nvl(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG,QP_PREQ_PUB.G_NO) <> QP_PREQ_PUB.G_YES
--and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
and line1.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line1.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
--fix for bug 2823886 to do cleanup and calc for old fg line
'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line1.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
and (line1.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
OR nvl(line1.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
and (line.unit_price is not null or line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL) -- bug 3501866
and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
,QP_PREQ_PUB.G_STATUS_UNCHANGED)
and line1.line_index = ldet.line_index
and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
and (ldet.updated_flag = QP_PREQ_PUB.G_YES
or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
-- or (p_event_code is null and ldet.updated_flag is null)
or ldet.pricing_phase_id not in (select ph.pricing_phase_id
from qp_event_phases ev, qp_pricing_phases ph
--changes to enable multiple events passed as a string
where ph.pricing_phase_id = ev.pricing_phase_id
---introduced the end date condition for bug 3376902
and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
--introduced for freight_rating functionality to return only modifiers in
--phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
and ph.freight_exists = QP_PREQ_PUB.G_YES)
or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
and instr(p_event_code,ev.pricing_event_code||',') > 0
-- Ravi changed from line.price_flag to line1.price_flag
-- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_YES
or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_PHASE
and nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
QP_PREQ_PUB.G_YES))))
and ldet.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
--commented to fetch auto overridden unapplied adj(user deleted)
-- and ldet.applied_flag = QP_PREQ_PUB.G_YES
-- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT
-- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
and qplh.list_header_id = ldet.created_from_list_header_id
and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
QP_PREQ_PUB.G_AGR_LIST_HEADER)
--order by line_ind,pricing_group_sequence,is_ldet_rec; -- 2892848 net_amt
SELECT max(line_detail_index)
FROM qp_npreq_ldets_tmp;
l_updated_flag_tbl QP_PREQ_GRP.flag_type;
l_dup_updated_flag QP_PREQ_GRP.FLAG_TYPE;
QP_PREQ_PUB.Update_passed_in_pbh(x_return_status, x_return_status_text);
G_PBH_LINE_DTL_INDEX.delete;
G_PBH_LINE_INDEX.delete;
G_PBH_PRICE_ADJ_ID.delete;
G_PBH_PLSQL_IND.delete;
G_ORD_LVL_LDET_INDEX.delete; -- 3031108
UPDATE qp_npreq_ldets_tmp ldet2
set ldet2.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED,
pricing_status_text = 'DUPLICATE MANUAL-OVERRIDEABLE'
where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and nvl(ldet2.updated_flag,QP_PREQ_PUB.G_NO) = QP_PREQ_PUB.G_NO
and exists ( select 'X'
from qp_npreq_ldets_tmp ldet
where nvl(ldet.updated_flag,QP_PREQ_PUB.G_NO) =QP_PREQ_PUB.G_YES
and ldet.line_index = ldet2.line_index
and ldet.created_from_list_line_id =
ldet2.created_from_list_line_id);
UPDATE qp_npreq_rltd_lines_tmp rltd
set rltd.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED,
rltd.pricing_status_text = 'DUPLICATE MANUAL-OVERRIDEABLE'
where rltd.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and rltd.line_detail_index in (select ldet.line_detail_index
from qp_npreq_ldets_tmp ldet
where ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED
and ldet.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_PUB.G_BY_PBH);
l_dup_updated_flag.delete;
l_dup_is_ldet_rec.delete;
l_dup_plsql_ind.delete;
l_dup_uniq_ind.delete;
l_list_line_id_tbl.delete;
l_line_index_tbl.delete;
l_curr_line_index_tbl.delete;
l_line_id_tbl.delete;
l_line_dtl_index_tbl.delete;
l_list_line_type_code_tbl.delete;
l_list_header_id_tbl.delete;
l_applied_flag_tbl.delete;
l_amount_changed_tbl.delete;
l_adjusted_unit_price_tbl.delete;
l_priced_quantity_tbl.delete;
l_line_priced_qty_tbl.delete;
l_upd_adj_unit_price_tbl.delete;
l_automatic_flag_tbl.delete;
l_override_flag_tbl.delete;
l_pricing_group_sequence_tbl.delete;
l_operand_calc_code_tbl.delete;
l_operand_value_tbl.delete;
l_adjustment_amount_tbl.delete;
l_unit_price_tbl.delete;
l_accrual_flag_tbl.delete;
l_updated_flag_tbl.delete;
l_process_code_tbl.delete;
l_status_code_tbl.delete;
l_status_text_tbl.delete;
l_price_break_type_code_tbl.delete;
l_charge_type_code_tbl.delete;
l_charge_subtype_code_tbl.delete;
l_rounding_factor_tbl.delete;
l_pricing_phase_id_tbl.delete;
l_list_line_no_tbl.delete;
l_limit_text_tbl.delete;
l_limit_code_tbl.delete;
l_list_type_code_tbl.delete;
l_modifier_level_tbl.delete;
l_group_qty_tbl.delete;
l_group_amt_tbl.delete;
l_line_sts_code_tbl.delete;
l_is_ldet_rec_tbl.delete;
l_line_type_code_tbl.delete;
l_net_amount_flag_tbl.delete;
l_calculation_code_tbl.delete;
l_catchweight_qty_tbl.delete;
l_actual_order_qty_tbl.delete;
l_line_unit_price_tbl.delete;
l_ord_qty_tbl.delete;
l_line_detail_type_code_tbl.delete;
l_line_category_tbl.delete;
l_price_flag_tbl.delete;
l_adj_overflow_tbl.delete;
,l_updated_flag_tbl
,l_process_code_tbl
,l_status_code_tbl
,l_status_text_tbl
,l_price_break_type_code_tbl
,l_charge_type_code_tbl
,l_charge_subtype_code_tbl
,l_rounding_factor_tbl
,l_pricing_phase_id_tbl
,l_list_type_code_tbl
,l_limit_code_tbl
,l_limit_text_tbl
,l_list_line_no_tbl
,l_modifier_level_tbl
,l_group_qty_tbl
,l_group_amt_tbl
,l_line_sts_code_tbl
,l_is_ldet_rec_tbl
,l_line_type_code_tbl
--2388011
,l_net_amount_flag_tbl
--2388011
,l_calculation_code_tbl
,l_catchweight_qty_tbl
,l_actual_order_qty_tbl
,l_line_unit_price_tbl
,l_ord_qty_tbl
,l_line_detail_type_code_tbl
,l_line_category_tbl
,l_price_flag_tbl;
||' updated '||l_updated_flag_tbl(i)
||' applied '||l_applied_flag_tbl(i)
||' hdr id '||l_list_header_id_tbl(i)||' is ldet '
||l_is_ldet_rec_tbl(i)||' linetype '||l_line_type_code_tbl(i));
select net_amount_flag
into l_net_amount_flag_tbl(i)
from qp_list_lines
where list_line_id = l_list_line_id_tbl(i);
IF l_dup_updated_flag.exists(l_dup_ind) THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Duplicate list line exists ');
IF l_dup_updated_flag(l_dup_ind) = QP_PREQ_PUB.G_YES
and l_updated_flag_tbl(i) = QP_PREQ_PUB.G_NO THEN
--in this case the oe_price_adj record must be applied
--so engine selected needs to be marked as deleted
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Duplicate adj overridden ');
||'as deleted ');
l_status_code_tbl(i) := QP_PREQ_PUB.G_STATUS_DELETED;
ELSIF l_updated_flag_tbl(i) = QP_PREQ_PUB.G_YES
and l_dup_updated_flag(l_dup_ind) = QP_PREQ_PUB.G_YES THEN
--current adj is overridden
l_adj_tbl(l_dup_plsql_ind(l_dup_ind)).pricing_status_code
:= QP_PREQ_PUB.G_STATUS_DELETED;
l_dup_updated_flag(l_dup_ind) := l_updated_flag_tbl(i);
ELSE --updated_flag is 'N'
--in this case the engine selected record must be applied
--so adj from oe_price_adj needs to be marked as deleted
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Duplicate adj not overridden ');
--mark the current record as deleted as
--it is from oe_price_adj
l_status_code_tbl(i) := QP_PREQ_PUB.G_STATUS_DELETED;
QP_PREQ_GRP.engine_debug('Marking curr adj deleted ');
:= QP_PREQ_PUB.G_STATUS_DELETED;
l_dup_updated_flag(l_dup_ind) := l_updated_flag_tbl(i);
QP_PREQ_GRP.engine_debug('Marking prev adj deleted ');
ELSE--both the records have same is_ldet so delete one
l_status_code_tbl(i) := QP_PREQ_PUB.G_STATUS_DELETED;
END IF;--l_dup_updated_flag
l_dup_updated_flag(l_dup_ind) := l_updated_flag_tbl(i);
END IF;--l_dup_updated_flag.exists
l_adj_tbl(d).updated_adjusted_unit_price :=
l_upd_adj_unit_price_tbl(i);
l_adj_tbl(d).updated_flag := l_updated_flag_tbl(i);
l_auto_line_dtl_index_tbl.delete;
l_auto_override_dtl_id_tbl.delete;
and nvl(l_updated_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_YES
and nvl(l_applied_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_YES)
or
(nvl(l_automatic_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_YES
and nvl(l_applied_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_NO))
*/
--commented out because we need to look at all updated adj
--and retain the updated(overridden) adj ,delete engine picked
and nvl(l_updated_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_YES
THEN
x := x+1;
--commented out because we need to look at all updated adj
--and retain the updated(overridden) adj ,delete engine picked
/*
and ((nvl(l_automatic_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_YES
and nvl(l_override_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_YES
and nvl(l_updated_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_NO)
or (nvl(l_automatic_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_NO
and nvl(l_applied_flag_tbl(i),QP_PREQ_PUB.G_NO) =
QP_PREQ_PUB.G_NO))
*/
THEN
IF l_debug = FND_API.G_TRUE THEN
qp_preq_grp.engine_debug('auto_overr: from ldet tbl '
||l_adj_tbl(d).created_from_list_line_id
||' adj index '||l_adj_tbl(d).line_detail_index);
QP_PREQ_GRP.G_STATUS_DELETED;
l_auto_override_dtl_id_tbl.delete(m);
l_auto_line_dtl_index_tbl.delete(m);
select pricing_attribute
into QP_PREQ_PUB.G_pbhvolattr_attribute(l_adj_tbl(d).created_from_list_line_id)
from qp_pricing_attributes
where list_line_id =
l_adj_tbl(d).created_from_list_line_id
and excluder_flag='N'; --3607956
l_adj_tbl(d).process_code := QP_PREQ_PUB.G_STATUS_UPDATED;
l_adj_tbl.delete(l_prev_line_start_index,l_adj_tbl.COUNT);
Insert_rltd_lines(p_request_type_code,
p_calculate_flag, p_event_code,
x_return_status, x_return_status_text);
QP_PREQ_PUB.Update_passed_in_pbh(x_return_status, x_return_status_text);
delete from qp_npreq_rltd_lines_tmp
where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
and pricing_status_text = G_CALC_INSERT;
QP_PREQ_GRP.engine_debug('deleted related lines '||SQL%ROWCOUNT);
QP_PREQ_PUB.Update_Child_Break_Lines(x_return_status,
x_return_status_text);
UPDATE qp_npreq_ldets_tmp ldet2
set ldet2.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED,
pricing_status_text = 'DUPLICATE MANUAL-OVERRIDEABLE'
where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
and nvl(ldet2.updated_flag,QP_PREQ_PUB.G_NO) = QP_PREQ_PUB.G_NO
and exists ( select 'X'
from qp_npreq_ldets_tmp ldet
where nvl(ldet.updated_flag,QP_PREQ_PUB.G_NO) =QP_PREQ_PUB.G_YES
and ldet.line_index = ldet2.line_index
and ldet.created_from_list_line_id =
ldet2.created_from_list_line_id);
UPDATE qp_npreq_ldets_tmp
SET pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED
WHERE pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
AND automatic_flag = QP_PREQ_PUB.G_NO
AND applied_flag = QP_PREQ_PUB.G_NO
and updated_flag = QP_PREQ_PUB.G_NO; -- 5413797
QP_PREQ_GRP.engine_debug(SQL%ROWCOUNT||' new manual modifier(s) marked as DELETED');
QP_PREQ_PUB.Update_Line_Status(x_return_status,x_return_status_text);
--update needs to be done
-- IF p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
-- THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('calculate_only call check to see'
||' if there are adjustments');
QP_PREQ_PUB.update_unit_price(x_return_status,
x_return_status_text);