[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, UPDATED_FLAG VARCHAR2(1)
, PROCESS_CODE VARCHAR2(30)
, PRICING_STATUS_CODE VARCHAR2(30)
, PRICING_STATUS_TEXT VARCHAR2(240)
, ROUNDING_FACTOR NUMBER
, CALCULATION_CODE VARCHAR2(30)
, LINE_QUANTITY NUMBER
, LIST_HEADER_ID NUMBER
, LIST_TYPE_CODE VARCHAR2(30)
, PRICE_BREAK_TYPE_CODE VARCHAR2(30)
, CHARGE_TYPE_CODE VARCHAR2(30)
, CHARGE_SUBTYPE_CODE VARCHAR2(30)
, AUTOMATIC_FLAG VARCHAR2(1)
, PRICING_PHASE_ID NUMBER
, LIMIT_CODE VARCHAR2(30)
, LIMIT_TEXT VARCHAR2(2000)
, OPERAND_CALCULATION_CODE VARCHAR2(30)
, PRICING_GROUP_SEQUENCE NUMBER
, LIST_LINE_NO VARCHAR2(240));
G_NOT_MAX_FRT_CHARGE CONSTANT VARCHAR2(100) := 'QP_PREQ_PUB:DELETED TO RETURN MAX/OVERRID FREIGHT CHARGE';
SELECT PHASE_SEQUENCE, PRICING_PHASE_ID
FROM QP_PRICING_PHASES
WHERE LIST_TYPE_CODE = 'PRL'
AND ROWNUM < 2;
SELECT b.pricing_phase_id
, nvl(b.user_freeze_override_flag, b.freeze_override_flag)
FROM qp_event_phases a, qp_pricing_phases b
WHERE instr(p_event, a.pricing_event_code || ',') > 0
AND ((G_GET_FREIGHT_FLAG = G_YES AND b.freight_exists = G_YES)
OR (G_GET_FREIGHT_FLAG = G_NO))
AND a.pricing_phase_id = G_PRICE_LIST_PHASE_ID
AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID;
SELECT currency_code
FROM qp_npreq_lines_tmp;
SELECT MIN(pricing_effective_date)
, MAX(pricing_effective_date)
FROM qp_npreq_lines_tmp;
SELECT line_index
, line_quantity
, priced_quantity
, unit_price
FROM qp_npreq_lines_tmp;
UPDATE qp_npreq_lines_tmp
SET request_type_code = p_control_rec.request_type_code
WHERE request_type_code IS NULL;
UPDATE qp_npreq_lines_tmp SET
unit_price = l_FIXED_PRICE,
adjusted_unit_price = l_FIXED_PRICE,
pricing_status_code = G_STATUS_UPDATED
WHERE unit_price IS NULL
OR adjusted_unit_price IS NULL
OR unit_price = FND_API.G_MISS_NUM
OR adjusted_unit_price = FND_API.G_MISS_NUM;
QP_PREQ_GRP.engine_debug('QP_PREQ_PUB: Init_cons update status '|| SQLERRM);
/* update qp_npreq_lines_tmp set price_flag = G_PHASE
where line_type_code = G_ORDER_LEVEL
and exists(select 'Y' from qp_npreq_lines_tmp
where price_flag in (G_PHASE,G_NO,G_CALCULATE_ONLY)
and line_type_code = G_LINE_LEVEL); */
UPDATE qp_npreq_line_attrs_tmp attr SET attr.list_header_id =
(SELECT qpl.list_header_id FROM
qp_list_lines qpl WHERE qpl.list_line_id = to_number(attr.value_from))
WHERE attr.context = 'MODLIST'
AND attribute = 'QUALIFIER_ATTRIBUTE2'
AND pricing_status_code = 'X';
QP_PREQ_GRP.engine_debug('Before update_rounding_factor:calculate_flag '
|| p_control_rec.calculate_flag ||' rounding_flag '
|| p_control_rec.rounding_flag);
QP_PREQ_GRP.update_rounding_factor(G_USE_MULTI_CURRENCY_PUB,
x_return_status,
x_return_status_text);
QP_PREQ_GRP.engine_debug('QP_PREQ_PUB before insert source line quantity ');
INSERT INTO qp_npreq_line_attrs_tmp
(LINE_INDEX
, LINE_DETAIL_INDEX
, ATTRIBUTE_LEVEL
, NO_QUALIFIERS_IN_GRP
, COMPARISON_OPERATOR_TYPE_CODE
, VALIDATED_FLAG
, APPLIED_FLAG
, PRICING_STATUS_CODE
, PRICING_STATUS_TEXT
, QUALIFIER_PRECEDENCE
, PRICING_ATTR_FLAG
, QUALIFIER_TYPE
, DATATYPE
, PRODUCT_UOM_CODE
, VALUE_TO
, SETUP_VALUE_TO
, GROUPING_NUMBER
, GROUP_AMOUNT
, DISTINCT_QUALIFIER_FLAG
, SETUP_VALUE_FROM
, ATTRIBUTE_TYPE
, LIST_HEADER_ID
, LIST_LINE_ID
, CONTEXT
, ATTRIBUTE
, VALUE_FROM
, PROCESSED_CODE
, EXCLUDER_FLAG
, GROUP_QUANTITY
)
SELECT
LINE_INDEX
, NULL
, G_LINE_LEVEL
, NULL
, NULL
, G_NO
, G_LIST_NOT_APPLIED
, G_STATUS_UNCHANGED
, NULL
, NULL
, G_YES
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, G_PRICING_TYPE
, NULL
, NULL
, G_PRIC_VOLUME_CONTEXT
, G_QUANTITY_ATTRIBUTE
, decode(l_price_phase_flag, G_YES, qp_number.number_to_canonical(nvl(LINE_QUANTITY, 0)), G_NO, qp_number.number_to_canonical(NVL(nvl(PRICED_QUANTITY, LINE_QUANTITY), 0)), 0)
, NULL
, NULL
, NULL
FROM qp_npreq_lines_tmp;
QP_PREQ_GRP.engine_debug('QP_PREQ_PUB after insert source line quantity ');
QP_PREQ_GRP.engine_debug('QP_PREQ_PUB before insert source line amt ');
INSERT INTO qp_npreq_line_attrs_tmp
(LINE_INDEX
, LINE_DETAIL_INDEX
, ATTRIBUTE_LEVEL
, NO_QUALIFIERS_IN_GRP
, COMPARISON_OPERATOR_TYPE_CODE
, VALIDATED_FLAG
, APPLIED_FLAG
, PRICING_STATUS_CODE
, PRICING_STATUS_TEXT
, QUALIFIER_PRECEDENCE
, PRICING_ATTR_FLAG
, QUALIFIER_TYPE
, DATATYPE
, PRODUCT_UOM_CODE
, VALUE_TO
, SETUP_VALUE_TO
, GROUPING_NUMBER
, GROUP_AMOUNT
, DISTINCT_QUALIFIER_FLAG
, SETUP_VALUE_FROM
, ATTRIBUTE_TYPE
, LIST_HEADER_ID
, LIST_LINE_ID
, CONTEXT
, ATTRIBUTE
, VALUE_FROM
, PROCESSED_CODE
, EXCLUDER_FLAG
, GROUP_QUANTITY
)
SELECT
LINE_INDEX
, NULL
, LINE_TYPE_CODE
, NULL
, NULL
, G_NO
, QP_PREQ_GRP.G_LIST_NOT_APPLIED
, G_STATUS_UNCHANGED
, NULL
, NULL
, G_YES
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, G_PRICING_TYPE
, NULL
, NULL
, G_PRIC_VOLUME_CONTEXT
, G_LINE_AMT_ATTRIBUTE
, decode(l_price_phase_flag, G_YES, qp_number.number_to_canonical(nvl(LINE_QUANTITY * UNIT_PRICE, 0)), G_NO, qp_number.number_to_canonical(NVL(nvl(PRICED_QUANTITY, LINE_QUANTITY), 0) * nvl(UNIT_PRICE, 0)), 0)
, NULL
, NULL
, NULL
FROM qp_npreq_lines_tmp;
QP_PREQ_GRP.engine_debug('QP_PREQ_PUB after insert source line amt ');
SELECT line_id
INTO l_order_header_id
FROM qp_npreq_lines_tmp
WHERE line_type_code = G_ORDER_LEVEL;
SELECT G_YES INTO l_null_price_req_code
FROM qp_npreq_lines_tmp
WHERE --price_request_code IS NULL AND bug#14102682
price_flag IN (G_YES, G_PHASE)
AND ROWNUM = 1;
UPDATE qp_npreq_lines_tmp SET
price_request_code = decode(line_type_code,
G_ORDER_LEVEL, QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE
, G_LINE_LEVEL, QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE || '-' || nvl(line_id, qp_limit_price_request_code_s.NEXTVAL))
WHERE --price_request_code IS NULL AND bug#14102682
price_flag IN (G_YES, G_PHASE);
(select line.line_index, ldet.line_detail_index,
ldet.created_from_list_line_id, ldet.modifier_level_code,
line.priced_quantity, line.parent_quantity, line.unit_price
from qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet
where line.line_index = l_line_index
and line.pricing_status_code in (G_STATUS_NEW, G_STATUS_UPDATED)
and line.price_flag in (G_YES, G_PHASE)
and line.line_type_code = G_LINE_LEVEL
and ldet.line_index = line.line_index
and ldet.pricing_status_code in
(QP_PREQ_GRP.G_STATUS_NEW, G_STATUS_UNCHANGED)
and ldet.applied_flag = G_YES
and nvl(ldet.operand_calculation_code,
QP_PREQ_GRP.G_LUMPSUM_DISCOUNT) = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT
and ldet.created_from_list_line_type in
('DIS', 'SUR', 'PBH', 'FREIGHT_CHARGE')
and ldet.modifier_level_code in
(QP_PREQ_GRP.G_LINE_LEVEL, QP_PREQ_GRP.G_LINE_GROUP))
loop
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('line_dtl_index of lumpsum adj '
||cl.line_detail_index||' list_line_id '||cl.created_from_list_line_id
||' modlevel '||cl.modifier_level_code||' parent_qty '
||cl.parent_quantity||' priced_qty '||cl.priced_quantity
||' unit_price '||cl.unit_price);
select attribute
into l_lgrp_vol_attr
from qp_npreq_line_attrs_tmp
where line_detail_index = cl.line_detail_index
and context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT;
for cl1 in (select line.line_index, lattr.value_from,
line.parent_quantity, ldet.line_detail_index
from qp_npreq_ldets_tmp ldet,
qp_npreq_lines_tmp line,
qp_npreq_line_attrs_tmp lattr
where ldet.created_from_list_line_id = cl.created_from_list_line_id
and ldet.pricing_status_code = G_STATUS_NEW
and ldet.applied_flag = G_YES
and line.line_index = ldet.line_index
and lattr.context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
and lattr.attribute = l_lgrp_vol_attr
and lattr.line_index = line.line_index
and lattr.line_detail_index is null)
loop
IF QP_PREQ_GRP.G_service_line_ind_tbl.exists(cl1.line_index) THEN
--this is a service line linegroup lumpsum
l_lumpsum_qty(cl.created_from_list_line_id) :=
l_lumpsum_qty(cl.created_from_list_line_id) +
cl1.parent_quantity * cl1.value_from;
for cl3 in (select sum(line.parent_quantity) lumpsum_qty
from qp_npreq_lines_tmp line
--qp_npreq_line_attrs_tmp ldetattr, qp_npreq_line_attrs_tmp lattr,
where ldetattr.line_index = cl.line_index
and line.priced_uom_code <> line.line_uom_code
and ldetattr.context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
and ldetattr.line_detail_index = cl.line_detail_index
and lattr.line_index = cl.line_index
and lattr.context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
and lattr.attribute = ldetattr.attribute
and exists (select 'Y'
from qp_npreq_ldets_tmp ldet
where ldet.created_from_list_line_id = cl.created_from_list_line_id
and ldet.modifier_level_code = QP_PREQ_GRP.G_LINE_GROUP
and ldet.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW))
loop
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('dtl_index '||cl.line_detail_index||' sum '||cl3.lumpsum_qty);
PROCEDURE Update_Adj_orderqty_cols(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
--[julin/pbperf] tuned to use QP_PREQ_LDETS_TMP_N2
CURSOR l_update_adj_cur IS
SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_N2) */ ldet.line_detail_index,
ldet.operand_value,
ldet.operand_calculation_code,
ldet.modifier_level_code,
nvl(ldet.line_quantity, line.priced_quantity) priced_qty,
line.actual_order_quantity actual_order_qty,
line.catchweight_qty,
line.line_quantity ordered_qty
-- line.unit_price,
-- line.adjusted_unit_price
FROM qp_npreq_ldets_tmp ldet,
qp_npreq_lines_tmp line
WHERE ldet.pricing_phase_id > 1
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.automatic_flag = G_NO
AND nvl(ldet.applied_flag, G_NO) = G_NO
AND nvl(ldet.updated_flag, G_NO) = G_NO
AND line.line_index = ldet.line_index;
QP_PREQ_GRP.engine_debug('Begin Update_Adj_orderqty_cols');
OPEN l_update_adj_cur;
l_ordqty_line_dtl_index.DELETE;
l_ordqty_operand.DELETE;
l_ordqty_operator.DELETE;
l_ordqty_mod_lvl.DELETE;
l_ordqty_priced_qty.DELETE;
l_ordqty_actual_order_qty.DELETE;
l_ordqty_catchweight_qty.DELETE;
l_ordqty_ordered_qty.DELETE;
l_ordqty_ord_qty_operand.DELETE;
FETCH l_update_adj_cur BULK COLLECT INTO
l_ordqty_line_dtl_index,
l_ordqty_operand,
l_ordqty_operator,
l_ordqty_mod_lvl,
l_ordqty_priced_qty,
l_ordqty_actual_order_qty,
l_ordqty_catchweight_qty,
l_ordqty_ordered_qty;
CLOSE l_update_adj_cur;
QP_PREQ_GRP.engine_debug('After cur Update_Adj_orderqty_cols');
QP_PREQ_GRP.engine_debug('Ordqty update line_dtl_index '
|| l_ordqty_line_dtl_index(j) ||' ordqty_operand '
|| l_ordqty_ord_qty_operand(j));
UPDATE qp_npreq_ldets_tmp SET order_qty_operand = l_ordqty_ord_qty_operand(i)
WHERE line_detail_index = l_ordqty_line_dtl_index(i);
QP_PREQ_GRP.engine_debug('End Update_Adj_orderqty_cols');
QP_PREQ_GRP.engine_debug('Exception in Update_Adj_orderqty_cols '|| SQLERRM);
x_return_status_text := 'Exception in Update_Adj_orderqty_cols '|| SQLERRM;
END Update_Adj_orderqty_cols;
SELECT line_id INTO l_header_id
FROM qp_npreq_lines_tmp
WHERE line_type_code = G_ORDER_LEVEL
AND pricing_status_code IN (G_STATUS_UPDATED,
G_STATUS_UNCHANGED, G_STATUS_GSA_VIOLATION);
SELECT G_YES INTO l_exist_changed_order_adj
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.process_code IN (G_STATUS_UPDATED, G_STATUS_NEW)
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.modifier_level_code = G_ORDER_LEVEL
AND ldet.applied_flag = G_YES;
SELECT SUM(operand_value) INTO l_ldet_sum_operand
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.modifier_level_code = G_ORDER_LEVEL
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.automatic_flag = G_YES
AND applied_flag = G_YES;
UPDATE qp_npreq_lines_tmp SET
processed_flag = G_CALCULATE_ONLY
WHERE line_type_code = G_ORDER_LEVEL;
SELECT line_id INTO l_header_id
FROM qp_npreq_lines_tmp
WHERE line_type_code = G_ORDER_LEVEL
AND pricing_status_code IN (G_STATUS_UPDATED,
G_STATUS_UNCHANGED, G_STATUS_GSA_VIOLATION);
SELECT G_YES INTO l_exist_changed_order_adj
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.pricing_status_code = G_STATUS_NEW
AND ldet.modifier_level_code = G_ORDER_LEVEL
AND ldet.applied_flag = G_YES;
SELECT SUM(operand_value) INTO l_ldet_sum_operand
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.modifier_level_code = G_ORDER_LEVEL
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.automatic_flag = G_YES
AND applied_flag = G_YES;
SELECT SUM(operand_value) INTO l_adj_sum_operand
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.modifier_level_code = G_ORDER_LEVEL
AND ldet.pricing_status_code = G_STATUS_UNCHANGED
AND ldet.automatic_flag = G_YES
AND applied_flag = G_YES;
UPDATE qp_npreq_lines_tmp SET
processed_flag = G_CALCULATE_ONLY
WHERE line_type_code = G_ORDER_LEVEL;
PROCEDURE INT_TABLES_PREINSERT(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_ldets_cur IS
SELECT /*+ ORDERED USE_NL(adj qplh) */
adj.price_adjustment_id line_detail_index
, adj.list_line_type_code line_detail_type_code
, adj.price_break_type_code
, NULL list_price
, line.line_index
, adj.list_header_id created_from_list_header_id
, adj.list_line_id created_from_list_line_id
, adj.list_line_type_code created_from_list_line_type
, '' created_from_list_type_code
, NULL CREATED_FROM_SQL
, adj.pricing_group_sequence
, adj.pricing_phase_id
, adj.arithmetic_operator operand_calculation_code
, nvl(adj.operand_per_pqty, adj.operand) operand_value
, NULL SUBSTITUTION_TYPE_CODE
, NULL SUBSTITUTION_VALUE_FROM
, NULL SUBSTITUTION_VALUE_TO
, NULL ASK_FOR_FLAG
, NULL PRICE_FORMULA_ID
, 'N' pricing_status_code
, ' ' pricing_status_text
, NULL PRODUCT_PRECEDENCE
, NULL INCOMPATABILITY_GRP_CODE
, NULL PROCESSED_FLAG
, adj.applied_flag
, adj.automatic_flag
, adj.update_allowed override_flag
, NULL PRIMARY_UOM_FLAG
, NULL PRINT_ON_INVOICE_FLAG
, adj.modifier_level_code
, adj.BENEFIT_QTY
, adj.BENEFIT_UOM_CODE
, adj.LIST_LINE_NO
, adj.accrual_flag
, adj.ACCRUAL_CONVERSION_RATE
, NULL ESTIM_ACCRUAL_RATE
, NULL RECURRING_FLAG
, NULL SELECTED_VOLUME_ATTR
, line.rounding_factor
, NULL HEADER_LIMIT_EXISTS
, NULL LINE_LIMIT_EXISTS
, adj.charge_type_code
, adj.charge_subtype_code
, NULL CURRENCY_DETAIL_ID
, NULL CURRENCY_HEADER_ID
, NULL SELLING_ROUNDING_FACTOR
, NULL ORDER_CURRENCY
, NULL PRICING_EFFECTIVE_DATE
, NULL BASE_CURRENCY_CODE
--, line.line_quantity
, adj.range_break_quantity line_quantity
, nvl(adj.updated_flag, QP_PREQ_PUB.G_NO) updated_flag
, NULL calculation_code
, adj.CHANGE_REASON_CODE
, adj.CHANGE_REASON_TEXT
, adj.PRICE_ADJUSTMENT_ID
, NULL ACCUM_CONTEXT
, NULL ACCUM_ATTRIBUTE
, NULL ACCUM_FLAG
, NULL BREAK_UOM_CODE
, NULL BREAK_UOM_CONTEXT
, NULL BREAK_UOM_ATTRIBUTE
, NULL PROCESS_CODE -- 3215497
FROM qp_int_lines line, oe_price_adjustments adj,
qp_list_headers_b qplh
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
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,
'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 (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.list_line_type_code = 'PRG'
AND adj.pricing_phase_id IN (SELECT ph.pricing_phase_id
FROM qp_event_phases ev, qp_pricing_phases ph
WHERE ph.pricing_phase_id = ev.pricing_phase_id
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
--and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
--or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
AND (line.price_flag = QP_PREQ_PUB.G_YES
OR (line.price_flag = QP_PREQ_PUB.G_PHASE
AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
OR adj.pricing_phase_id NOT IN (SELECT ph.pricing_phase_id
FROM qp_event_phases ev, qp_pricing_phases ph
WHERE ph.pricing_phase_id = ev.pricing_phase_id
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
--and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
--or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
AND (line.price_flag = QP_PREQ_PUB.G_YES
OR (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 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)
--commented out because we want to fetch PBH childs and PRG freeline adjs
--and not exists (select 'x'
-- from oe_price_adj_assocs a
-- where a.RLTD_PRICE_ADJ_ID = adj.price_adjustment_id)
UNION ALL
SELECT /*+ ORDERED USE_NL(adj qplh) */
adj.price_adjustment_id line_detail_index
, adj.list_line_type_code line_detail_type_code
, adj.price_break_type_code
, NULL list_price
, line.line_index
, adj.list_header_id created_from_list_header_id
, adj.list_line_id created_from_list_line_id
, adj.list_line_type_code created_from_list_line_type
, '' created_from_list_type_code
, NULL CREATED_FROM_SQL
, adj.pricing_group_sequence
, adj.pricing_phase_id
, adj.arithmetic_operator operand_calculation_code
, nvl(adj.operand_per_pqty, adj.operand) operand_value
, NULL SUBSTITUTION_TYPE_CODE
, NULL SUBSTITUTION_VALUE_FROM
, NULL SUBSTITUTION_VALUE_TO
, NULL ASK_FOR_FLAG
, NULL PRICE_FORMULA_ID
, 'N' pricing_status_code
, ' ' pricing_status_text
, NULL PRODUCT_PRECEDENCE
, NULL INCOMPATABILITY_GRP_CODE
, NULL PROCESSED_FLAG
, adj.applied_flag
, adj.automatic_flag
, adj.update_allowed override_flag
, NULL PRIMARY_UOM_FLAG
, NULL PRINT_ON_INVOICE_FLAG
, adj.modifier_level_code
, adj.BENEFIT_QTY
, adj.BENEFIT_UOM_CODE
, adj.LIST_LINE_NO
, adj.accrual_flag
, adj.ACCRUAL_CONVERSION_RATE
, NULL ESTIM_ACCRUAL_RATE
, NULL RECURRING_FLAG
, NULL SELECTED_VOLUME_ATTR
, line.rounding_factor
, NULL HEADER_LIMIT_EXISTS
, NULL LINE_LIMIT_EXISTS
, adj.charge_type_code
, adj.charge_subtype_code
, NULL CURRENCY_DETAIL_ID
, NULL CURRENCY_HEADER_ID
, NULL SELLING_ROUNDING_FACTOR
, NULL ORDER_CURRENCY
, NULL PRICING_EFFECTIVE_DATE
, NULL BASE_CURRENCY_CODE
--, line.line_quantity
, adj.range_break_quantity line_quantity
, nvl(adj.updated_flag, QP_PREQ_PUB.G_NO) updated_flag
, NULL calculation_code
, adj.CHANGE_REASON_CODE
, adj.CHANGE_REASON_TEXT
, adj.PRICE_ADJUSTMENT_ID
, NULL ACCUM_CONTEXT
, NULL ACCUM_ATTRIBUTE
, NULL ACCUM_FLAG
, NULL BREAK_UOM_CODE
, NULL BREAK_UOM_CONTEXT
, NULL BREAK_UOM_ATTRIBUTE
, NULL PROCESS_CODE -- 3215497
FROM qp_int_lines line, oe_price_adjustments adj,
qp_list_headers_b qplh
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
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,
'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
AND adj.header_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.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
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 (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
WHERE ph.pricing_phase_id = ev.pricing_phase_id
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
--and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
--or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
AND (line.price_flag = QP_PREQ_PUB.G_YES
OR (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 = QP_PREQ_PUB.G_ORDER_LEVEL
AND adj.line_id IS NULL
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);
SELECT /*+ index(ass OE_PRICE_ADJ_ASSOCS_N1)*/ --[julin/4865213] changed from N3 to N1
line.line_index line_index,
ass.price_adjustment_id line_detail_index,
QP_PREQ_PUB.G_PBH_LINE relationship_type_code,
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,
'INSERTED FOR CALCULATION' pricing_status_text
FROM qp_int_lines line
, oe_price_adjustments adj_pbh
, oe_price_adj_assocs ass
, oe_price_adjustments adj
, qp_pricing_attributes attr
WHERE pbh_exist_flag = 'Y'
AND 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.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
AND (line.price_flag = QP_PREQ_PUB.G_YES
--or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
OR (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 ALL
SELECT /*+ ordered index(ass OE_PRICE_ADJ_ASSOCS_N1)*/ --[julin/4865213] changed from N3 to N1, ordered
line.line_index line_index,
adj_prg.price_adjustment_id line_detail_index,
QP_PREQ_PUB.G_GENERATED_LINE relationship_type_code,
getline.line_index related_line_index,
ass.rltd_price_adj_id related_line_detail_index,
adj_prg.list_line_id list_line_id,
adj.list_line_id related_list_line_id,
'INSERTED FOR CALCULATION' pricing_status_text
FROM qp_int_lines line
, oe_price_adjustments adj_prg
, oe_price_adj_assocs ass
, oe_price_adjustments adj
, qp_int_lines getline
WHERE prg_exist_flag = 'Y'
AND 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.price_flag = G_YES
AND line.line_id = adj_prg.line_id
AND adj_prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
AND adj_prg.pricing_phase_id IN
(SELECT ph.pricing_phase_id
FROM qp_event_phases evt, qp_pricing_phases ph
WHERE ph.pricing_phase_id = evt.pricing_phase_id
AND instr(p_event_code, evt.pricing_event_code || ',') > 0
AND (line.price_flag = QP_PREQ_PUB.G_YES
OR (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.price_adjustment_id = adj_prg.price_adjustment_id
AND ass.rltd_price_adj_id = adj.price_adjustment_id
AND adj.list_line_type_code = 'DIS'
AND adj.line_id IS NOT NULL
AND getline.line_id = adj.line_id;
SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
FROM
qp_int_lines line,
oe_price_adjustments adj
WHERE 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_int_lines line,
oe_price_adjustments adj
WHERE 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))));
SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
FROM qp_int_lines line,
oe_price_adjustments adj
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 = 'PRG'
AND adj.pricing_phase_id IN
(SELECT ph.pricing_phase_id
FROM qp_event_phases evt, qp_pricing_phases ph
WHERE ph.pricing_phase_id = evt.pricing_phase_id
AND instr(p_event_code, evt.pricing_event_code || ',') > 0
AND (line.price_flag = QP_PREQ_PUB.G_YES
OR (line.price_flag = QP_PREQ_PUB.G_PHASE
AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES)));
SELECT /*+ ORDERED USE_NL(adj qplh) */
adj.price_adjustment_id line_detail_index
, adj.list_line_type_code line_detail_type_code
, adj.price_break_type_code
, NULL list_price
, getline.line_index
, adj.list_header_id created_from_list_header_id
, adj.list_line_id created_from_list_line_id
, adj.list_line_type_code created_from_list_line_type
, '' created_from_list_type_code
, NULL CREATED_FROM_SQL
, adj.pricing_group_sequence
, adj.pricing_phase_id
, adj.arithmetic_operator operand_calculation_code
, nvl(adj.operand_per_pqty, adj.operand) operand_value
, NULL SUBSTITUTION_TYPE_CODE
, NULL SUBSTITUTION_VALUE_FROM
, NULL SUBSTITUTION_VALUE_TO
, NULL ASK_FOR_FLAG
, NULL PRICE_FORMULA_ID
, 'N' pricing_status_code
, ' ' pricing_status_text
, NULL PRODUCT_PRECEDENCE
, NULL INCOMPATABILITY_GRP_CODE
, NULL PROCESSED_FLAG
, adj.applied_flag
, adj.automatic_flag
, adj.update_allowed override_flag
, NULL PRIMARY_UOM_FLAG
, NULL PRINT_ON_INVOICE_FLAG
, adj.modifier_level_code
, adj.BENEFIT_QTY
, adj.BENEFIT_UOM_CODE
, adj.LIST_LINE_NO
, adj.accrual_flag
, adj.ACCRUAL_CONVERSION_RATE
, NULL ESTIM_ACCRUAL_RATE
, NULL RECURRING_FLAG
, NULL SELECTED_VOLUME_ATTR
, getline.rounding_factor
, NULL HEADER_LIMIT_EXISTS
, NULL LINE_LIMIT_EXISTS
, adj.charge_type_code
, adj.charge_subtype_code
, NULL CURRENCY_DETAIL_ID
, NULL CURRENCY_HEADER_ID
, NULL SELLING_ROUNDING_FACTOR
, NULL ORDER_CURRENCY
, NULL PRICING_EFFECTIVE_DATE
, NULL BASE_CURRENCY_CODE
--, line.line_quantity
, adj.range_break_quantity line_quantity
, nvl(adj.updated_flag, QP_PREQ_PUB.G_NO) updated_flag
, NULL calculation_code
, adj.CHANGE_REASON_CODE
, adj.CHANGE_REASON_TEXT
, adj.PRICE_ADJUSTMENT_ID
, NULL ACCUM_CONTEXT
, NULL ACCUM_ATTRIBUTE
, NULL ACCUM_FLAG
, NULL BREAK_UOM_CODE
, NULL BREAK_UOM_CONTEXT
, NULL BREAK_UOM_ATTRIBUTE
, NULL PROCESS_CODE -- 3215497
FROM qp_int_lines getline, oe_price_adjustments adj,
qp_int_ldets ldet, oe_price_adj_assocs ass
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
AND ldet.CREATED_FROM_LIST_LINE_TYPE = 'PRG'
AND ass.price_adjustment_id = ldet.price_adjustment_id
AND ass.rltd_price_adj_id = adj.price_adjustment_id
AND adj.line_id = getline.line_id
AND getline.line_index = ldet.line_index -- sql repos
AND getline.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,
'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
, QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
, QP_PREQ_PUB.G_STATUS_UNCHANGED)
AND getline.price_flag = QP_PREQ_PUB.G_NO
AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
FROM qp_int_ldets);
l_SELECTED_VOLUME_ATTR QP_PREQ_GRP.VARCHAR_TYPE;
l_UPDATED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
l_LINE_DETAIL_index.DELETE;
l_LINE_DETAIL_TYPE_CODE.DELETE;
l_PRICE_BREAK_TYPE_CODE.DELETE;
l_LIST_PRICE.DELETE;
l_LINE_INDEX.DELETE;
l_CREATED_FROM_LIST_HEADER_ID.DELETE;
l_CREATED_FROM_LIST_LINE_ID.DELETE;
l_CREATED_FROM_LIST_LINE_TYPE.DELETE;
l_CREATED_FROM_LIST_TYPE_CODE.DELETE;
l_CREATED_FROM_SQL.DELETE;
l_PRICING_GROUP_SEQUENCE.DELETE;
l_PRICING_PHASE_ID.DELETE;
l_OPERAND_CALCULATION_CODE.DELETE;
l_OPERAND_VALUE.DELETE;
l_SUBSTITUTION_TYPE_CODE.DELETE;
l_SUBSTITUTION_VALUE_FROM.DELETE;
l_SUBSTITUTION_VALUE_TO.DELETE;
l_ASK_FOR_FLAG.DELETE;
l_PRICE_FORMULA_ID.DELETE;
l_PRICING_STATUS_CODE.DELETE;
l_PRICING_STATUS_TEXT.DELETE;
l_PRODUCT_PRECEDENCE.DELETE;
l_INCOMPATABLILITY_GRP_CODE.DELETE;
l_PROCESSED_FLAG.DELETE;
l_APPLIED_FLAG.DELETE;
l_AUTOMATIC_FLAG.DELETE;
l_OVERRIDE_FLAG.DELETE;
l_PRIMARY_UOM_FLAG.DELETE;
l_PRINT_ON_INVOICE_FLAG.DELETE;
l_MODIFIER_LEVEL_CODE.DELETE;
l_BENEFIT_QTY.DELETE;
l_BENEFIT_UOM_CODE.DELETE;
l_LIST_LINE_NO.DELETE;
l_ACCRUAL_FLAG.DELETE;
l_ACCRUAL_CONVERSION_RATE.DELETE;
l_ESTIM_ACCRUAL_RATE.DELETE;
l_RECURRING_FLAG.DELETE;
l_SELECTED_VOLUME_ATTR.DELETE;
l_ROUNDING_FACTOR.DELETE;
l_HEADER_LIMIT_EXISTS.DELETE;
l_LINE_LIMIT_EXISTS.DELETE;
l_CHARGE_TYPE_CODE.DELETE;
l_CHARGE_SUBTYPE_CODE.DELETE;
l_CURRENCY_DETAIL_ID.DELETE;
l_CURRENCY_HEADER_ID.DELETE;
l_SELLING_ROUNDING_FACTOR.DELETE;
l_ORDER_CURRENCY.DELETE;
l_PRICING_EFFECTIVE_DATE.DELETE;
l_BASE_CURRENCY_CODE.DELETE;
l_LINE_QUANTITY.DELETE;
l_UPDATED_FLAG.DELETE;
l_CALCULATION_CODE.DELETE;
l_CHANGE_REASON_CODE.DELETE;
l_CHANGE_REASON_TEXT.DELETE;
l_PRICE_ADJUSTMENT_ID.DELETE;
l_ACCUM_CONTEXT.DELETE;
l_ACCUM_ATTRIBUTE.DELETE;
l_ACCUM_FLAG.DELETE;
l_BREAK_UOM_CODE.DELETE;
l_BREAK_UOM_CONTEXT.DELETE;
l_BREAK_UOM_ATTRIBUTE.DELETE;
l_PROCESS_CODE.DELETE; -- 3215497
l_SELECTED_VOLUME_ATTR,
l_ROUNDING_FACTOR,
l_HEADER_LIMIT_EXISTS,
l_LINE_LIMIT_EXISTS,
l_CHARGE_TYPE_CODE,
l_CHARGE_SUBTYPE_CODE,
l_CURRENCY_DETAIL_ID,
l_CURRENCY_HEADER_ID,
l_SELLING_ROUNDING_FACTOR,
l_ORDER_CURRENCY,
l_PRICING_EFFECTIVE_DATE,
l_BASE_CURRENCY_CODE,
l_LINE_QUANTITY,
l_UPDATED_FLAG,
l_CALCULATION_CODE,
l_CHANGE_REASON_CODE,
l_CHANGE_REASON_TEXT,
l_PRICE_ADJUSTMENT_ID,
l_ACCUM_CONTEXT,
l_ACCUM_ATTRIBUTE,
l_ACCUM_FLAG,
l_BREAK_UOM_CODE,
l_BREAK_UOM_CONTEXT,
l_BREAK_UOM_ATTRIBUTE,
l_PROCESS_CODE; -- 3215497
' SELECTED_VOLUME_ATTR: '|| l_SELECTED_VOLUME_ATTR(i) ||
' ROUNDING_FACTOR: '|| l_ROUNDING_FACTOR(i) ||
' HEADER_LIMIT_EXISTS: '|| l_HEADER_LIMIT_EXISTS(i) ||
' LINE_LIMIT_EXISTS: '|| l_LINE_LIMIT_EXISTS(i));
QP_PREQ_GRP.engine_debug(' UPDATED_FLAG:'|| l_UPDATED_FLAG(i) ||
' CALCULATION_CODE:'|| l_CALCULATION_CODE(i) ||
' CHANGE_REASON_CODE:'|| l_CHANGE_REASON_CODE(i) ||
' CHANGE_REASON_TEXT:'|| l_CHANGE_REASON_TEXT(i) ||
' PRICE_ADJUSTMENT_ID:'|| l_PRICE_ADJUSTMENT_ID(i) ||
' ACCUM_CONTEXT:'|| l_ACCUM_CONTEXT(i) ||
' ACCUM_ATTRIBUTE:'|| l_ACCUM_ATTRIBUTE(i) ||
' ACCUM_FLAG:'|| l_ACCUM_FLAG(i) ||
' BREAK_UOM_CODE:'|| l_BREAK_UOM_CODE(i) ||
' BREAK_UOM_CONTEXT:'|| l_BREAK_UOM_CONTEXT(i) ||
' BREAK_UOM_ATTRIBUTE:'|| l_BREAK_UOM_ATTRIBUTE(i));
QP_PREQ_GRP.engine_debug('calling INSERT_LDETS2');
QP_PREQ_GRP.INSERT_LDETS2
(l_LINE_DETAIL_index,
l_LINE_DETAIL_TYPE_CODE,
l_PRICE_BREAK_TYPE_CODE,
l_LIST_PRICE,
l_LINE_INDEX,
l_CREATED_FROM_LIST_HEADER_ID,
l_CREATED_FROM_LIST_LINE_ID,
l_CREATED_FROM_LIST_LINE_TYPE,
l_CREATED_FROM_LIST_TYPE_CODE,
l_CREATED_FROM_SQL,
l_PRICING_GROUP_SEQUENCE,
l_PRICING_PHASE_ID,
l_OPERAND_CALCULATION_CODE,
l_OPERAND_VALUE,
l_SUBSTITUTION_TYPE_CODE,
l_SUBSTITUTION_VALUE_FROM,
l_SUBSTITUTION_VALUE_TO,
l_ASK_FOR_FLAG,
l_PRICE_FORMULA_ID,
l_PRICING_STATUS_CODE,
l_PRICING_STATUS_TEXT,
l_PRODUCT_PRECEDENCE,
l_INCOMPATABLILITY_GRP_CODE,
l_PROCESSED_FLAG,
l_APPLIED_FLAG,
l_AUTOMATIC_FLAG,
l_OVERRIDE_FLAG,
l_PRIMARY_UOM_FLAG,
l_PRINT_ON_INVOICE_FLAG,
l_MODIFIER_LEVEL_CODE,
l_BENEFIT_QTY,
l_BENEFIT_UOM_CODE,
l_SERVICE_DURATION, -- service project
l_SERVICE_PERIOD, -- service project
l_LIST_LINE_NO,
l_ACCRUAL_FLAG,
l_ACCRUAL_CONVERSION_RATE,
l_ESTIM_ACCRUAL_RATE,
l_RECURRING_FLAG,
l_SELECTED_VOLUME_ATTR,
l_ROUNDING_FACTOR,
l_HEADER_LIMIT_EXISTS,
l_LINE_LIMIT_EXISTS,
l_CHARGE_TYPE_CODE,
l_CHARGE_SUBTYPE_CODE,
l_CURRENCY_DETAIL_ID,
l_CURRENCY_HEADER_ID,
l_SELLING_ROUNDING_FACTOR,
l_ORDER_CURRENCY,
l_PRICING_EFFECTIVE_DATE,
l_BASE_CURRENCY_CODE,
l_LINE_QUANTITY,
l_UPDATED_FLAG,
l_CALCULATION_CODE,
l_CHANGE_REASON_CODE,
l_CHANGE_REASON_TEXT,
l_PRICE_ADJUSTMENT_ID,
l_ACCUM_CONTEXT,
l_ACCUM_ATTRIBUTE,
l_ACCUM_FLAG,
l_BREAK_UOM_CODE,
l_BREAK_UOM_CONTEXT,
l_BREAK_UOM_ATTRIBUTE,
l_PROCESS_CODE, -- 3215497
x_return_status,
x_return_status_text);
QP_PREQ_GRP.engine_debug('Begin Insert rltd lines');
l_LINE_index1.DELETE;
l_LINE_DETAIL_INDEX1.DELETE;
l_RELATIONSHIP_TYPE_CODE.DELETE;
l_RELATED_LINE_INDEX.DELETE;
l_RELATED_LINE_DETAIL_INDEX.DELETE;
l_LIST_LINE_ID.DELETE;
l_RLTD_LIST_LINE_ID.DELETE;
l_pricing_status_text.DELETE;
QP_PREQ_GRP.engine_debug('calling INSERT_RLTD_LDETS2');
QP_PREQ_GRP.INSERT_RLTD_LINES2
(l_LINE_INDEX1
, l_LINE_DETAIL_INDEX1
, l_RELATIONSHIP_TYPE_CODE
, l_RELATED_LINE_INDEX
, l_RELATED_LINE_DETAIL_INDEX
, x_return_status
, x_return_status_text
, l_LIST_LINE_ID
, l_RLTD_LIST_LINE_ID
, l_pricing_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;
*/ --need to delete them later
END LOOP;
l_LINE_DETAIL_index.DELETE;
l_LINE_DETAIL_TYPE_CODE.DELETE;
l_PRICE_BREAK_TYPE_CODE.DELETE;
l_LIST_PRICE.DELETE;
l_LINE_INDEX.DELETE;
l_CREATED_FROM_LIST_HEADER_ID.DELETE;
l_CREATED_FROM_LIST_LINE_ID.DELETE;
l_CREATED_FROM_LIST_LINE_TYPE.DELETE;
l_CREATED_FROM_LIST_TYPE_CODE.DELETE;
l_CREATED_FROM_SQL.DELETE;
l_PRICING_GROUP_SEQUENCE.DELETE;
l_PRICING_PHASE_ID.DELETE;
l_OPERAND_CALCULATION_CODE.DELETE;
l_OPERAND_VALUE.DELETE;
l_SUBSTITUTION_TYPE_CODE.DELETE;
l_SUBSTITUTION_VALUE_FROM.DELETE;
l_SUBSTITUTION_VALUE_TO.DELETE;
l_ASK_FOR_FLAG.DELETE;
l_PRICE_FORMULA_ID.DELETE;
l_PRICING_STATUS_CODE.DELETE;
l_PRICING_STATUS_TEXT.DELETE;
l_PRODUCT_PRECEDENCE.DELETE;
l_INCOMPATABLILITY_GRP_CODE.DELETE;
l_PROCESSED_FLAG.DELETE;
l_APPLIED_FLAG.DELETE;
l_AUTOMATIC_FLAG.DELETE;
l_OVERRIDE_FLAG.DELETE;
l_PRIMARY_UOM_FLAG.DELETE;
l_PRINT_ON_INVOICE_FLAG.DELETE;
l_MODIFIER_LEVEL_CODE.DELETE;
l_BENEFIT_QTY.DELETE;
l_BENEFIT_UOM_CODE.DELETE;
l_LIST_LINE_NO.DELETE;
l_ACCRUAL_FLAG.DELETE;
l_ACCRUAL_CONVERSION_RATE.DELETE;
l_ESTIM_ACCRUAL_RATE.DELETE;
l_RECURRING_FLAG.DELETE;
l_SELECTED_VOLUME_ATTR.DELETE;
l_ROUNDING_FACTOR.DELETE;
l_HEADER_LIMIT_EXISTS.DELETE;
l_LINE_LIMIT_EXISTS.DELETE;
l_CHARGE_TYPE_CODE.DELETE;
l_CHARGE_SUBTYPE_CODE.DELETE;
l_CURRENCY_DETAIL_ID.DELETE;
l_CURRENCY_HEADER_ID.DELETE;
l_SELLING_ROUNDING_FACTOR.DELETE;
l_ORDER_CURRENCY.DELETE;
l_PRICING_EFFECTIVE_DATE.DELETE;
l_BASE_CURRENCY_CODE.DELETE;
l_LINE_QUANTITY.DELETE;
l_UPDATED_FLAG.DELETE;
l_CALCULATION_CODE.DELETE;
l_CHANGE_REASON_CODE.DELETE;
l_CHANGE_REASON_TEXT.DELETE;
l_PRICE_ADJUSTMENT_ID.DELETE;
l_ACCUM_CONTEXT.DELETE;
l_ACCUM_ATTRIBUTE.DELETE;
l_ACCUM_FLAG.DELETE;
l_BREAK_UOM_CODE.DELETE;
l_BREAK_UOM_CONTEXT.DELETE;
l_BREAK_UOM_ATTRIBUTE.DELETE;
l_PROCESS_CODE.DELETE; -- 3215497
l_SELECTED_VOLUME_ATTR,
l_ROUNDING_FACTOR,
l_HEADER_LIMIT_EXISTS,
l_LINE_LIMIT_EXISTS,
l_CHARGE_TYPE_CODE,
l_CHARGE_SUBTYPE_CODE,
l_CURRENCY_DETAIL_ID,
l_CURRENCY_HEADER_ID,
l_SELLING_ROUNDING_FACTOR,
l_ORDER_CURRENCY,
l_PRICING_EFFECTIVE_DATE,
l_BASE_CURRENCY_CODE,
l_LINE_QUANTITY,
l_UPDATED_FLAG,
l_CALCULATION_CODE,
l_CHANGE_REASON_CODE,
l_CHANGE_REASON_TEXT,
l_PRICE_ADJUSTMENT_ID,
l_ACCUM_CONTEXT,
l_ACCUM_ATTRIBUTE,
l_ACCUM_FLAG,
l_BREAK_UOM_CODE,
l_BREAK_UOM_CONTEXT,
l_BREAK_UOM_ATTRIBUTE,
l_PROCESS_CODE; -- 3215497
' SELECTED_VOLUME_ATTR: '|| l_SELECTED_VOLUME_ATTR(i) ||
' ROUNDING_FACTOR: '|| l_ROUNDING_FACTOR(i) ||
' HEADER_LIMIT_EXISTS: '|| l_HEADER_LIMIT_EXISTS(i) ||
' LINE_LIMIT_EXISTS: '|| l_LINE_LIMIT_EXISTS(i));
QP_PREQ_GRP.engine_debug(' UPDATED_FLAG:'|| l_UPDATED_FLAG(i) ||
' CALCULATION_CODE:'|| l_CALCULATION_CODE(i) ||
' CHANGE_REASON_CODE:'|| l_CHANGE_REASON_CODE(i) ||
' CHANGE_REASON_TEXT:'|| l_CHANGE_REASON_TEXT(i) ||
' PRICE_ADJUSTMENT_ID:'|| l_PRICE_ADJUSTMENT_ID(i) ||
' ACCUM_CONTEXT:'|| l_ACCUM_CONTEXT(i) ||
' ACCUM_ATTRIBUTE:'|| l_ACCUM_ATTRIBUTE(i) ||
' ACCUM_FLAG:'|| l_ACCUM_FLAG(i) ||
' BREAK_UOM_CODE:'|| l_BREAK_UOM_CODE(i) ||
' BREAK_UOM_CONTEXT:'|| l_BREAK_UOM_CONTEXT(i) ||
' BREAK_UOM_ATTRIBUTE:'|| l_BREAK_UOM_ATTRIBUTE(i));
QP_PREQ_GRP.engine_debug('calling INSERT_LDETS2');
QP_PREQ_GRP.INSERT_LDETS2
(l_LINE_DETAIL_index,
l_LINE_DETAIL_TYPE_CODE,
l_PRICE_BREAK_TYPE_CODE,
l_LIST_PRICE,
l_LINE_INDEX,
l_CREATED_FROM_LIST_HEADER_ID,
l_CREATED_FROM_LIST_LINE_ID,
l_CREATED_FROM_LIST_LINE_TYPE,
l_CREATED_FROM_LIST_TYPE_CODE,
l_CREATED_FROM_SQL,
l_PRICING_GROUP_SEQUENCE,
l_PRICING_PHASE_ID,
l_OPERAND_CALCULATION_CODE,
l_OPERAND_VALUE,
l_SUBSTITUTION_TYPE_CODE,
l_SUBSTITUTION_VALUE_FROM,
l_SUBSTITUTION_VALUE_TO,
l_ASK_FOR_FLAG,
l_PRICE_FORMULA_ID,
l_PRICING_STATUS_CODE,
l_PRICING_STATUS_TEXT,
l_PRODUCT_PRECEDENCE,
l_INCOMPATABLILITY_GRP_CODE,
l_PROCESSED_FLAG,
l_APPLIED_FLAG,
l_AUTOMATIC_FLAG,
l_OVERRIDE_FLAG,
l_PRIMARY_UOM_FLAG,
l_PRINT_ON_INVOICE_FLAG,
l_MODIFIER_LEVEL_CODE,
l_BENEFIT_QTY,
l_BENEFIT_UOM_CODE,
l_SERVICE_DURATION, -- service project
l_SERVICE_PERIOD, -- service project
l_LIST_LINE_NO,
l_ACCRUAL_FLAG,
l_ACCRUAL_CONVERSION_RATE,
l_ESTIM_ACCRUAL_RATE,
l_RECURRING_FLAG,
l_SELECTED_VOLUME_ATTR,
l_ROUNDING_FACTOR,
l_HEADER_LIMIT_EXISTS,
l_LINE_LIMIT_EXISTS,
l_CHARGE_TYPE_CODE,
l_CHARGE_SUBTYPE_CODE,
l_CURRENCY_DETAIL_ID,
l_CURRENCY_HEADER_ID,
l_SELLING_ROUNDING_FACTOR,
l_ORDER_CURRENCY,
l_PRICING_EFFECTIVE_DATE,
l_BASE_CURRENCY_CODE,
l_LINE_QUANTITY,
l_UPDATED_FLAG,
l_CALCULATION_CODE,
l_CHANGE_REASON_CODE,
l_CHANGE_REASON_TEXT,
l_PRICE_ADJUSTMENT_ID,
l_ACCUM_CONTEXT,
l_ACCUM_ATTRIBUTE,
l_ACCUM_FLAG,
l_BREAK_UOM_CODE,
l_BREAK_UOM_CONTEXT,
l_BREAK_UOM_ATTRIBUTE,
l_PROCESS_CODE, -- 3215497
x_return_status,
x_return_status_text);
PROCEDURE Update_passed_in_pbh(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
BEGIN
l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
UPDATE qp_npreq_rltd_lines_tmp rltd
SET (setup_value_from,
setup_value_to,
relationship_type_detail,
list_line_id,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
pricing_group_sequence,
qualifier_value)
=
(SELECT
qpa.pricing_attr_value_from,
qpa.pricing_attr_value_to,
ldet_pbh.price_break_type_code,
ldet_pbh.created_from_list_line_id,
ldet.created_from_list_line_id,
ldet.created_from_list_line_type,
ldet.operand_calculation_code,
ldet.operand_value,
ldet.pricing_group_sequence,
nvl(ldet.line_quantity,
nvl(line.priced_quantity, line.line_quantity))
FROM
qp_npreq_ldets_tmp ldet,
qp_npreq_ldets_tmp ldet_pbh,
qp_npreq_lines_tmp line,
qp_pricing_attributes qpa
WHERE
ldet.line_detail_index = rltd.related_line_detail_index
AND ldet_pbh.line_detail_index = rltd.line_detail_index
AND line.line_index = ldet.line_index
AND qpa.list_line_id = ldet.created_from_list_line_id
AND ldet.pricing_status_code = G_STATUS_UNCHANGED
AND ldet.updated_flag = G_YES
AND rltd.relationship_type_code = G_PBH_LINE
AND rltd.pricing_status_code = G_STATUS_NEW)
WHERE rltd.line_detail_index IN (SELECT ldet.line_detail_index
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED
AND ldet.applied_flag = G_YES
AND ldet.updated_flag = G_YES
AND ldet.created_from_list_line_type = G_PRICE_BREAK_TYPE)
AND rltd.relationship_type_code = G_PBH_LINE
AND rltd.pricing_status_code = G_STATUS_NEW;
UPDATE qp_npreq_ldets_tmp
SET line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE
WHERE line_detail_index IN (SELECT related_line_detail_index
FROM qp_npreq_rltd_lines_tmp
WHERE relationship_type_code = G_PBH_LINE
AND pricing_status_code = G_STATUS_NEW);
FOR i IN (SELECT line_detail_index
FROM qp_npreq_ldets_tmp
WHERE line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE)
LOOP
QP_PREQ_GRP.engine_debug(' PBH Child Line Detail Index : ' || i.line_detail_index);
x_return_status_text := 'Exception in updated_passed_pbh '|| SQLERRM;
END Update_passed_in_pbh;
SELECT 'Y'
FROM qp_npreq_lines_tmp line,
qp_npreq_line_attrs_tmp gsa_attr
WHERE gsa_attr.line_index = line.line_index
AND gsa_attr.pricing_status_code = G_STATUS_UNCHANGED
AND gsa_attr.attribute_type = G_QUALIFIER_TYPE
AND gsa_attr.context = G_CUSTOMER_CONTEXT
AND gsa_attr.attribute = G_GSA_ATTRIBUTE
AND gsa_attr.value_from = G_YES;
SELECT /*+ ORDERED USE_NL(req lhdr lattr qpa ql) index(qpa qp_pricing_attributes_n5) */
line.line_index,
line.adjusted_unit_price,
MIN(ql.operand) operand
FROM qp_npreq_lines_tmp line,
qp_price_req_sources req,
qp_list_headers_b lhdr,
qp_npreq_line_attrs_tmp lattr,
qp_pricing_attributes qpa,
qp_list_lines ql
WHERE lattr.line_index = line.line_index
AND lattr.pricing_status_code IN
(G_STATUS_NEW, G_STATUS_UNCHANGED)
AND lattr.attribute_type = G_PRODUCT_TYPE
AND lattr.context = qpa.product_attribute_context
AND lattr.attribute = qpa.product_attribute
AND lattr.value_from = qpa.product_attr_value
AND qpa.excluder_flag = G_NO
AND qpa.pricing_phase_id = 2
AND qpa.qualification_ind = 6
AND lattr.line_index = line.line_index
AND req.request_type_code = line.request_type_code
AND lhdr.list_header_id = qpa.list_header_id
AND lhdr.active_flag = G_YES
AND ((lhdr.currency_code IS NOT NULL AND lhdr.currency_code = line.currency_code)
OR
lhdr.currency_code IS NULL) -- optional currency
AND lhdr.list_type_code = G_DISCOUNT_LIST_HEADER
AND lhdr.source_system_code = req.source_system_code
AND lhdr.gsa_indicator = G_YES
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(lhdr.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(lhdr.End_date_active
, line.pricing_effective_date))
AND qpa.list_line_id = ql.list_line_id
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(ql.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(ql.End_date_active
, line.pricing_effective_date))
GROUP BY line.line_index, line.adjusted_unit_price;
SELECT /*+ ORDERED USE_NL(req lhdr lattr qpa ql) index(qpa qp_pricing_attributes_n5) */
line.line_index,
line.adjusted_unit_price,
MIN(ql.operand) operand
FROM qp_npreq_lines_tmp line,
qp_price_req_sources_v req,
qp_list_headers_b lhdr,
qp_npreq_line_attrs_tmp lattr,
qp_pricing_attributes qpa,
qp_list_lines ql
WHERE lattr.line_index = line.line_index
AND lattr.pricing_status_code IN
(G_STATUS_NEW, G_STATUS_UNCHANGED)
AND lattr.attribute_type = G_PRODUCT_TYPE
AND lattr.context = qpa.product_attribute_context
AND lattr.attribute = qpa.product_attribute
AND lattr.value_from = qpa.product_attr_value
AND qpa.excluder_flag = G_NO
AND qpa.pricing_phase_id = 2
AND qpa.qualification_ind = 6
AND lattr.line_index = line.line_index
AND req.request_type_code = line.request_type_code
AND lhdr.list_header_id = qpa.list_header_id
AND lhdr.active_flag = G_YES
AND ((lhdr.currency_code IS NOT NULL AND lhdr.currency_code = line.currency_code)
OR
lhdr.currency_code IS NULL) -- optional currency
AND lhdr.list_type_code = G_DISCOUNT_LIST_HEADER
AND lhdr.source_system_code = req.source_system_code
AND lhdr.gsa_indicator = G_YES
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(lhdr.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(lhdr.End_date_active
, line.pricing_effective_date))
AND qpa.list_line_id = ql.list_line_id
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(ql.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(ql.End_date_active
, line.pricing_effective_date))
GROUP BY line.line_index, line.adjusted_unit_price;
l_gsa_line_index_tbl.DELETE;
l_gsa_sts_text_tbl.DELETE;
QP_PREQ_GRP.engine_debug('Before GSA update '|| l_gsa_line_index_tbl.COUNT);
UPDATE qp_npreq_lines_tmp
SET pricing_status_code = G_STATUS_GSA_VIOLATION,
pricing_status_text =
'GSA VIOLATION - GSA PRICE '|| l_gsa_sts_text_tbl(I)
WHERE line_index = l_gsa_line_index_tbl(I);
SELECT ldet.created_from_list_line_id
, ldet.line_index line_ind
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.applied_flag
, p_amount_changed amount_changed
, line.priced_quantity
, ldet.operand_calculation_code
, ldet.operand_value
, ldet.adjustment_amount
, ldet.modifier_level_code
, line.unit_price
, ldet.pricing_status_code
, ldet.pricing_status_text
, line.rounding_factor
, ldet.calculation_code
, ldet.line_quantity
, ldet.created_from_list_header_id
, ldet.created_from_list_type_code
, ldet.price_break_type_code
, ldet.charge_type_code
, ldet.charge_subtype_code
, ldet.automatic_flag
, ldet.pricing_phase_id
, ldet.limit_code
, ldet.limit_text
, ldet.pricing_group_sequence
, ldet.list_line_no
, ldet.calculation_code
--fix for bug 2833753
, decode(G_BACK_CALCULATION_CODE, 'DIS',
decode(ldet.calculation_code, 'BACK_CALCULATE', - 10000,
decode(ldet.created_from_list_line_type, 'DIS',
decode(ldet.applied_flag, G_YES, - 100, - 1000),
decode(ldet.applied_flag, G_YES, 1000, 100))),
decode(ldet.calculation_code, 'BACK_CALCULATE', 10000,
decode(ldet.created_from_list_line_type, 'DIS',
decode(ldet.applied_flag, G_YES, - 1000, - 100),
decode(ldet.applied_flag, G_YES, 100, 1000)))) precedence
FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
WHERE line.line_index = p_line_index
AND line.line_type_code = G_LINE_LEVEL -- sql repos
AND line.price_flag IN (G_YES, G_PHASE)
AND ldet.line_index = line.line_index
AND (ldet.pricing_status_code IN (G_STATUS_NEW, G_STATUS_UNCHANGED))
--commented for OC issue of duplicate manual adj in temp table
--as this cursor would pick up the engine returned manual adj
-- or ldet.process_code = G_STATUS_NEW)
AND ldet.created_from_list_line_type IN (G_DISCOUNT, G_SURCHARGE)
AND ldet.automatic_flag = G_NO
AND ldet.override_flag = G_YES
AND ldet.pricing_group_sequence IS NULL --only return null bucket manual modifiers
AND ldet.line_detail_index NOT IN
(SELECT rltd.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp rltd
WHERE rltd.relationship_type_code = G_PBH_LINE
AND rltd.pricing_status_code = G_STATUS_NEW)
--ORDER BY ldet.calculation_code,ldet.created_from_list_line_type,ldet.applied_flag desc;
l_back_list_line_id_tbl.DELETE;
l_back_line_index_tbl.DELETE;
l_back_line_dtl_index_tbl.DELETE;
l_back_list_line_type_tbl.DELETE;
l_back_applied_flag_tbl.DELETE;
l_back_amount_changed_tbl.DELETE;
l_back_priced_qty_tbl.DELETE;
l_back_operand_calc_code_tbl.DELETE;
l_back_operand_value_tbl.DELETE;
l_back_adj_amt_tbl.DELETE;
l_back_unit_price_tbl.DELETE;
l_back_sts_code_tbl.DELETE;
l_back_sts_txt_tbl.DELETE;
l_back_rounding_fac_tbl.DELETE;
l_back_calc_code_tbl.DELETE;
l_back_line_qty_tbl.DELETE;
l_back_list_hdr_id_tbl.DELETE;
l_back_list_type_tbl.DELETE;
l_back_price_brk_type_tbl.DELETE;
l_back_chrg_type_tbl.DELETE;
l_back_chrg_subtype_tbl.DELETE;
l_back_auto_flag_tbl.DELETE;
l_back_phase_id_tbl.DELETE;
l_back_limit_code_tbl.DELETE;
l_back_limit_text_tbl.DELETE;
l_back_bucket_tbl.DELETE;
l_back_list_line_no_tbl.DELETE;
l_back_calculation_code_tbl.DELETE;
l_back_calc_precedence_tbl.DELETE;
l_back_modifier_level_code_tbl.DELETE;
l_back_calc_rec.updated_flag := G_NO;
l_back_calc_rec.updated_flag := G_YES;
l_back_calc_rec.updated_flag := G_YES;
l_back_calc_rec.updated_flag := G_NO;
l_back_calc_rec.updated_flag := G_YES;
PROCEDURE UPDATE_UNIT_PRICE(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
CURSOR l_check_adjustments_cur IS
/*
SELECT line.line_index, line.unit_price, line.adjusted_unit_price
,line.line_quantity, line.priced_quantity, line.catchweight_qty,line.actual_order_quantity
, line.rounding_factor
FROM qp_npreq_lines_tmp line
WHERE line.price_flag in (G_YES, G_PHASE,G_CALCULATE_ONLY)
and line.pricing_status_code in (G_STATUS_UPDATED, G_STATUS_UNCHANGED,
G_STATUS_GSA_VIOLATION)
and line.line_type_code = G_LINE_LEVEL
and nvl(processed_code,'0')<> G_BY_ENGINE
and (line.adjusted_unit_price <> line.unit_price
--changed this for bug 2776800 to populate order_uom_selling_price
--this means there are no adjustments and calculation has not taken place
or line.order_uom_selling_price IS NULL )
and not exists (select ldet.line_index
from qp_npreq_ldets_tmp ldet
where (ldet.line_index = line.line_index
or ldet.modifier_level_code = G_ORDER_LEVEL)
and nvl(ldet.created_from_list_type_code,'NULL') not in
(G_PRICE_LIST_HEADER,G_AGR_LIST_HEADER)
-- and ldet.pricing_status_code = G_STATUS_NEW)
and ldet.pricing_status_code in (G_STATUS_NEW,G_STATUS_UPDATED)) --2729744
--this is for not updating the unit_price on fg lines for prg cleanup
--as they will not have any adjustments
and line.process_status = G_STATUS_UNCHANGED
--fix for bug 2691794
and nvl(line.processed_flag,'N') <> G_FREEGOOD_LINE;
and not exists (select 'Y' from qp_npreq_lines_tmp line2
where line2.line_id = line.line_id
and line2.line_index <> line.line_index);
SELECT line.line_index, line.unit_price, line.adjusted_unit_price
, line.line_quantity, line.priced_quantity, line.catchweight_qty, line.actual_order_quantity
, line.rounding_factor
, line.updated_adjusted_unit_price
, line.pricing_status_code
, line.pricing_status_text
, 0 amount_changed
, line_unit_price
FROM qp_npreq_lines_tmp line
WHERE line.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
AND line.pricing_status_code IN (G_STATUS_UPDATED, G_STATUS_UNCHANGED,
G_STATUS_GSA_VIOLATION)
AND line.line_type_code = G_LINE_LEVEL
AND nvl(processed_code, '0') <> G_BY_ENGINE
AND (line.adjusted_unit_price <> line.unit_price
--changed this for bug 2776800 to populate order_uom_selling_price
--this means there are no adjustments and calculation has not taken place
OR line.updated_adjusted_unit_price IS NOT NULL
OR line.order_uom_selling_price IS NULL )
AND nvl(line.QUALIFIERS_EXIST_FLAG, G_NO) <> G_CALCULATE_ONLY
AND nvl(line.processed_flag, 'N') <> G_FREEGOOD_LINE; -- added for bug 3116349 /*avallark*/
l_routine VARCHAR2(100) := 'QP_PREQ_PUB.UPDATE_UNIT_PRICE';
l_ldet_updated_flag QP_PREQ_GRP.FLAG_TYPE;
QP_PREQ_GRP.engine_debug('Begin update_unit_price');
l_line_index_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_adj_unit_price_tbl.DELETE;
l_ord_uom_selling_price_tbl.DELETE;
l_line_qty_tbl.DELETE;
l_priced_qty_tbl.DELETE;
l_catchwt_qty_tbl.DELETE;
l_actual_order_qty_tbl.DELETE;
l_rounding_factor_tbl.DELETE;
l_upd_adj_unit_price_tbl.DELETE;
l_pricing_sts_code_tbl.DELETE;
l_pricing_sts_text_tbl.DELETE;
l_amount_changed_tbl.DELETE;
l_line_unit_price_tbl.DELETE;
l_ldet_dtl_index.DELETE;
l_ldet_line_index.DELETE;
l_ldet_operand_value.DELETE;
l_ldet_adjamt.DELETE;
l_ldet_applied_flag.DELETE;
l_ldet_updated_flag.DELETE;
l_ldet_process_code.DELETE;
l_ldet_sts_code.DELETE;
l_ldet_sts_text.DELETE;
l_ldet_calc_code.DELETE;
l_ldet_ord_qty_operand.DELETE; --3057395
l_ldet_ord_qty_adj_amt.DELETE; --3057395
QP_PREQ_GRP.engine_debug('back cal succ insert rec ');
l_ldet_updated_flag(l_ldet_dtl_index.COUNT) :=
l_back_calc_ret_rec.updated_flag;
QP_PREQ_GRP.engine_debug('back cal fail no insert rec');
UPDATE qp_npreq_ldets_tmp ldet
SET ldet.operand_value = l_ldet_operand_value(i)
, ldet.adjustment_amount = l_ldet_adjamt(i)
, ldet.applied_flag = l_ldet_applied_flag(i)
, ldet.updated_flag = l_ldet_updated_flag(i)
, ldet.process_code = l_ldet_process_code(i)
, ldet.pricing_status_code = l_ldet_sts_code(i)
, ldet.pricing_status_text = l_ldet_sts_text(i)
, ldet.calculation_code = l_ldet_calc_code(i)
, ldet.order_qty_operand = l_ldet_ord_qty_operand(i) --3057395
, ldet.order_qty_adj_amt = l_ldet_ord_qty_adj_amt(i) --3057395
WHERE ldet.line_detail_index = l_ldet_dtl_index(i)
AND ldet.line_index = l_ldet_line_index(i);
QP_PREQ_GRP.engine_debug('unit price needs to be updated');
UPDATE qp_npreq_lines_tmp
SET adjusted_unit_price = l_adj_unit_price_tbl(i)
--, adjusted_unit_price_ur = l_adjusted_unit_price_ur(i) --[prarasto:Post Round], [julin/postround] redesign
, line_unit_price = l_line_unit_price_tbl(i)
, pricing_status_code = nvl(l_pricing_sts_code_tbl(i), G_STATUS_UPDATED) /* bug 3248475 */
, pricing_status_text = l_pricing_sts_text_tbl(i) /* bug 3248475 */
, order_uom_selling_price = l_unit_selling_price(i) --[prarasto:Post Round]
--, order_uom_selling_price_ur = l_unit_selling_price_ur(i) --[prarasto:Post Round], [julin/postround] redesign
, extended_price = l_extended_selling_price(i) --[prarasto:Post Round]
--, extended_selling_price_ur = l_extended_selling_price_ur(i) --[prarasto:Post Round], [julin/postround] redesign
WHERE line_index = l_line_index_tbl(i)
-- and (l_adj_unit_price_tbl(i) <> l_unit_price_tbl(i) --3524967
AND (adjusted_unit_price <> unit_price --3524967
--changes for bug 2776800 to populate order_uom_selling_price
OR updated_adjusted_unit_price IS NOT NULL
OR order_uom_selling_price IS NULL);
END UPDATE_UNIT_PRICE;
PROCEDURE Update_Line_Status(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
BEGIN
l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
QP_PREQ_GRP.engine_debug('Begin Update_Line_Status:calculate_flag '|| G_CALCULATE_FLAG);
UPDATE qp_npreq_lines_tmp line
SET pricing_status_code = G_STATUS_UPDATED
--for bug 2812738 not to update if back_calc_error/gsa_violatn
WHERE line.pricing_status_code = G_STATUS_UNCHANGED
--fix for bug 3425569 where frozen lines were set to status updated
--and processing constraints cause error in OM when they update frozen lines
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 (G_CALCULATE_FLAG = G_CALCULATE_ONLY
OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp ldet
WHERE ldet.line_index = line.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.applied_flag = G_YES -- bug 6111048/6328486
AND ldet.process_code IN
(G_STATUS_NEW, G_STATUS_UPDATED))
--for bug#3224658
OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp
WHERE modifier_level_code = 'ORDER'
AND applied_flag = 'YES' -- bug 6628324
AND pricing_status_code = G_STATUS_NEW
AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
WHERE line.line_type_code = G_LINE_LEVEL
AND line.line_id = adj.line_id
AND adj.applied_flag = G_YES
AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
WHERE ldet.line_index = line.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
AND ldet.applied_flag = G_YES))
OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
WHERE line.line_type_code = G_ORDER_LEVEL
AND line.line_id = adj.header_id
AND adj.applied_flag = G_YES
AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
WHERE ldet.line_index = line.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
AND ldet.applied_flag = G_YES))
);
UPDATE qp_int_lines line
SET pricing_status_code = G_STATUS_UPDATED
--for bug 2812738 not to update if back_calc_error/gsa_violatn
WHERE line.pricing_status_code = G_STATUS_UNCHANGED
--fix for bug 3425569 where frozen lines were set to status updated
--and processing constraints cause error in OM when they update frozen lines
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 (G_CALCULATE_FLAG = G_CALCULATE_ONLY
OR EXISTS (SELECT 'Y' FROM qp_int_ldets ldet
WHERE ldet.line_index = line.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.applied_flag = G_YES -- bug 6111048/6328486
AND ldet.process_code IN
(G_STATUS_NEW, G_STATUS_UPDATED))
--for bug#3224658
OR EXISTS (SELECT 'Y' FROM qp_int_ldets
WHERE modifier_level_code = 'ORDER'
AND applied_flag = 'YES' -- bug 6628324
AND pricing_status_code = G_STATUS_NEW
AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
WHERE line.line_type_code = G_LINE_LEVEL
AND line.line_id = adj.line_id
AND adj.applied_flag = G_YES
AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_int_ldets ldet
WHERE ldet.line_index = line.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
AND ldet.applied_flag = G_YES))
OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
WHERE line.line_type_code = G_ORDER_LEVEL
AND line.line_id = adj.header_id
AND adj.applied_flag = G_YES
AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_int_ldets ldet
WHERE ldet.line_index = line.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
AND ldet.applied_flag = G_YES))
);
x_return_status_text := 'Exception in QP_PREQ_PUB.Update_Line_Status: '|| SQLERRM;
END Update_Line_Status;
SELECT line_index,
related_line_index,
line_detail_index,
related_line_detail_index,
relationship_type_code,
list_line_id,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
pricing_group_sequence,
setup_value_from,
setup_value_to,
qualifier_value
FROM qp_npreq_rltd_lines_tmp
WHERE pricing_status_code = G_STATUS_NEW;
SELECT net_amount_flag
FROM qp_list_lines
WHERE list_line_id = p_list_line_id;
l_ldet_updated_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
SELECT DISTINCT ldet.line_index
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.created_from_list_line_id = p_list_line_id
AND pricing_status_code IN (G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED);
SELECT DISTINCT qla.line_index, ql.priced_quantity, ql.unit_price
FROM qp_preq_line_attrs_tmp qla, qp_pricing_attributes qpa, qp_preq_lines_tmp ql
WHERE qpa.list_line_id = p_list_line_id
AND qla.context = qpa.product_attribute_context
AND qla.attribute = qpa.product_attribute
AND qla.value_from = qpa.product_attr_value
AND qla.line_index = ql.line_index
AND ql.price_flag <> G_PHASE
AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
QP_PREQ_PUB.G_STATUS_UNCHANGED)
AND NOT EXISTS (SELECT qla2.line_index
FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
WHERE qpa2.list_line_id = p_list_line_id
AND qpa2.excluder_flag = G_YES
AND qla2.line_index = qla.line_index
AND qla2.context = qpa2.product_attribute_context
AND qla2.attribute = qpa2.product_attribute
AND qla2.value_from = qpa2.product_attr_value);
l_ord_dtl_index_tbl.DELETE;
l_ord_adj_amt_tbl.DELETE;
l_ord_qty_adj_amt_tbl.DELETE;
l_ord_qty_operand_tbl.DELETE;
G_ldet_plsql_index_tbl.DELETE;
l_adj_tbl.DELETE;
l_frt_tbl.DELETE;
l_line_index_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_adj_unit_price_tbl.DELETE;
l_amount_changed_tbl.DELETE;
l_ordered_qty_tbl.DELETE;
l_line_unit_price_tbl.DELETE;
l_line_priced_qty_tbl.DELETE;
l_catchweight_qty_tbl.DELETE;
l_actual_order_qty_tbl.DELETE;
l_pricing_sts_code_tbl.DELETE;
l_pricing_sts_txt_tbl.DELETE;
l_process_code_tbl.DELETE;
l_upd_adj_unit_price_tbl.DELETE;
l_processed_flag_tbl.DELETE;
l_rounding_factor_tbl.DELETE;
l_ordqty_unit_price_tbl.DELETE;
l_ordqty_selling_price_tbl.DELETE;
l_ldet_line_dtl_index_tbl.DELETE;
l_ldet_price_adjust_id_tbl.delete; --bug 12731184
l_ldet_line_index_tbl.DELETE;
l_ldet_list_hdr_id_tbl.DELETE;
l_ldet_list_line_id_tbl.DELETE;
l_ldet_list_line_type_tbl.DELETE;
l_ldet_operand_value_tbl.DELETE;
l_ldet_adj_amt_tbl.DELETE;
l_ldet_applied_flag_tbl.DELETE;
l_ldet_updated_flag_tbl.DELETE;
l_ldet_pricing_sts_code_tbl.DELETE;
l_ldet_process_code_tbl.DELETE;
l_ldet_pricing_sts_txt_tbl.DELETE;
l_ldet_price_break_type_tbl.DELETE;
l_ldet_line_quantity_tbl.DELETE;
l_ldet_operand_calc_tbl.DELETE;
l_ldet_pricing_grp_seq_tbl.DELETE;
l_ldet_list_type_code_tbl.DELETE;
l_ldet_limit_code_tbl.DELETE;
l_ldet_limit_text_tbl.DELETE;
l_ldet_list_line_no_tbl.DELETE;
l_ldet_charge_type_tbl.DELETE;
l_ldet_charge_subtype_tbl.DELETE;
l_ldet_updated_flag_tbl.DELETE;
l_ldet_automatic_flag_tbl.DELETE;
l_ldet_pricing_phase_id_tbl.DELETE;
l_ldet_modifier_level_tbl.DELETE;
l_ldet_is_max_frt_tbl.DELETE;
l_ldet_calc_code_tbl.DELETE;
l_ldet_ordqty_operand_tbl.DELETE;
l_ldet_ordqty_adjamt_tbl.DELETE;
l_back_calc_dtl_index.DELETE;
l_back_calc_adj_amt.DELETE;
l_back_calc_plsql_tbl_index.DELETE;
l_bucket_amt_tbl.DELETE; -- 2892848 SL_latest
l_prev_bucket_amt_tbl.DELETE;
l_mod_lg_net_amt_tbl.DELETE; -- 2892848 SL_latest
l_mod_lg_prod_net_amt_tbl.DELETE; -- -- [julin/4112395/4220399]
l_ntamt_adj_unit_price.DELETE; --3126019
QP_PREQ_GRP.engine_debug('SL, this direct insert path'); -- 2892848
' l_adj_tbl(j).updated_adjusted_unit_price: '|| l_adj_tbl(j).updated_adjusted_unit_price ||
' previous l_adjusted_price: '|| l_adjusted_price || -- 2892848
' list line id: '|| l_adj_tbl(j).created_from_list_line_id ||
' bucket: '|| l_adj_tbl(j).pricing_group_sequence || -- 2892848
' net_amount_flag: '|| l_adj_tbl(j).net_amount_flag); -- 2892828
l_mod_lg_net_amt_tbl.DELETE; -- clear this table upon bucket change to keep it small
l_mod_lg_prod_net_amt_tbl.DELETE; -- [julin/4112395/4220399]
l_mod_lg_net_amt_tbl.DELETE;
l_mod_lg_prod_net_amt_tbl.DELETE; -- [julin/4112395/4220399]
IN (G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED)
THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Processing Price Break. call Price_Break_Calculation');
(G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED)
THEN
/*
IF l_adj_tbl(j).updated_flag = G_YES
and nvl(l_adj_tbl(j).automatic_flag,G_NO) = G_NO
and (l_adj_tbl(j).adjustment_amount IS NOT NULL
and l_adj_tbl(j).adjustment_amount <> g_miss_num) --FND_API.G_MISS_NUM)
THEN
--to avoid rounding issues in rev calculations
--for user-overridden adjustments
IF l_debug = FND_API.G_TRUE THEN
qp_preq_grp.engine_debug(' adj amt manual adj '||
l_adj_tbl(j).adjustment_amount);
SELECT pricing_attribute INTO l_pricing_attribute
FROM qp_pricing_attributes
WHERE list_line_id =
l_adj_tbl(j).created_from_list_line_id
AND pricing_attribute_context =
G_PRIC_VOLUME_CONTEXT;
l_ldet_updated_flag_tbl(i) := l_adj_tbl(j).updated_flag;
and nvl(l_adj_tbl(j).updated_flag,G_NO) = G_YES)
THEN
l_ldet_applied_flag_tbl(i) := G_YES;
(G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED)
THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('looping thru FRT for list_line id '
|| l_adj_tbl(j).created_from_list_line_id ||' adj amt '
|| l_ldet_adj_amt_tbl(i) ||' upd '
|| l_adj_tbl(j).updated_flag
||' level '|| l_adj_tbl(j).modifier_level_code);
l_ldet_pricing_sts_code_tbl(i) := G_STATUS_DELETED;
QP_PREQ_GRP.engine_debug('No frtcharge in tbl: insert rec');
l_frt_tbl(1).updated_flag :=
nvl(l_adj_tbl(j).updated_flag, G_NO);
IF nvl(l_frt_tbl(N).updated_flag, G_NO) = G_NO
THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Rec is NOT overriden');
IF nvl(l_adj_tbl(j).updated_flag, G_NO) = G_YES
THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Repl overridden rec');
l_frt_tbl(N).updated_flag
:= l_adj_tbl(j).updated_flag;
ELSIF nvl(l_adj_tbl(j).updated_flag, G_NO) = G_NO
AND l_ldet_adj_amt_tbl(i)
> l_frt_tbl(N).adjustment_amount
THEN
--if ct adj's adj amt is greater
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('replace high adjamt');
l_frt_tbl(N).updated_flag
:= l_adj_tbl(j).updated_flag;
END IF; --l_adj_tbl(j).updated_flag
END IF; --frt_tbl.updated_flag
QP_PREQ_GRP.engine_debug('lastrec:insert new record');
l_frt_tbl(N + 1).updated_flag :=
nvl(l_adj_tbl(j).updated_flag, G_NO);
(G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED)
--added for auto overr deleted adj
AND l_adj_tbl(j).applied_flag = G_YES
AND nvl(l_adj_tbl(j).accrual_flag, G_NO) = G_NO
THEN
--Update the adjustment amount for each adjustment
-- 2892848_latest
-- so we have correct current USP, considered each adj shall be rounded
IF G_ROUND_INDIVIDUAL_ADJ = G_ROUND_ADJ
AND l_adj_tbl(j).rounding_factor IS NOT NULL
THEN
l_return_adjustment := round (l_return_adjustment, - 1 * l_adj_tbl(j).rounding_factor);
QP_PREQ_GRP.engine_debug('Update the adjustment amount for each adjustment...');
l_adj_tbl(j).updated_adjusted_unit_price;
IF l_adj_tbl(j).line_pricing_status_code <> G_STATUS_UPDATED
AND l_adj_tbl(j).PROCESS_CODE = G_STATUS_UPDATED
THEN
l_pricing_sts_code_tbl(x) := G_STATUS_UPDATED;
QP_PREQ_GRP.engine_debug('update ord lvl adj amt '
||'looping thru dtlind '|| l_ldet_line_dtl_index_tbl(x));
QP_PREQ_GRP.engine_debug('update ord lvl '
||' line_dtl_index '|| l_ord_dtl_index_tbl(i)
||'adj amt '|| l_ord_adj_amt_tbl(i));
l_ord_dtl_index_tbl.DELETE(i);
l_ord_adj_amt_tbl.DELETE(i);
l_frt_tbl.DELETE(y);
QP_PREQ_GRP.engine_debug('back cal succ insert rec '
|| l_tbl_index);
l_ldet_updated_flag_tbl(l_tbl_index) :=
l_back_calc_ret_rec.updated_flag;
:= G_STATUS_DELETED;
:= 'DELETED IN BACK CALC';
QP_PREQ_GRP.engine_debug('back cal fail no insert rec');
||' updated flag '|| l_ldet_updated_flag_tbl(i)
||' pricingstatus code '|| l_ldet_pricing_sts_code_tbl(i)
||' process code '|| l_ldet_process_code_tbl(i));
QP_PREQ_GRP.engine_debug('line details: before update ');
UPDATE qp_npreq_ldets_tmp
SET adjustment_amount = l_ldet_adj_amt_tbl(i),
operand_value = l_ldet_operand_value_tbl(i),
line_quantity = l_ldet_line_quantity_tbl(i),
applied_flag = l_ldet_applied_flag_tbl(i),
updated_flag = l_ldet_updated_flag_tbl(i),
pricing_status_code = l_ldet_pricing_sts_code_tbl(i),
pricing_status_text = l_ldet_pricing_sts_txt_tbl(i),
process_code = l_ldet_process_code_tbl(i),
calculation_code = l_ldet_calc_code_tbl(i),
order_qty_operand = l_ldet_ordqty_operand_tbl(i),
order_qty_adj_amt = nvl(l_ldet_ordqty_adjamt_tbl(i), l_ldet_adj_amt_tbl(i))
WHERE line_detail_index = l_ldet_line_dtl_index_tbl(i)
-- AND line_index = l_ldet_line_index_tbl(i)
AND pricing_status_code <> G_STATUS_DELETED --[julin/4671446]
AND l_ldet_process_code_tbl(i) IN (G_STATUS_NEW,
G_STATUS_DELETED);
QP_PREQ_GRP.engine_debug('line details: before insert ');
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
, calculation_code
, order_qty_operand
, order_qty_adj_amt
, price_adjustment_id --bug 12731184
)
-- VALUES
SELECT
l_ldet_line_dtl_index_tbl(i)
, l_ldet_line_index_tbl(i)
, 'NULL'
, l_ldet_pricing_sts_code_tbl(i)
, l_ldet_pricing_sts_txt_tbl(i)
, l_ldet_process_code_tbl(i)
, l_ldet_list_hdr_id_tbl(i)
, l_ldet_list_line_id_tbl(i)
, l_ldet_list_line_type_tbl(i)
, l_ldet_adj_amt_tbl(i)
, l_ldet_operand_value_tbl(i)
, l_ldet_modifier_level_tbl(i)
, l_ldet_price_break_type_tbl(i)
, l_ldet_line_quantity_tbl(i)
, l_ldet_operand_calc_tbl(i)
, l_ldet_pricing_grp_seq_tbl(i)
, l_ldet_list_type_code_tbl(i)
, l_ldet_applied_flag_tbl(i)
, l_ldet_limit_code_tbl(i)
, l_ldet_limit_text_tbl(i)
, l_ldet_list_line_no_tbl(i)
, l_ldet_charge_type_tbl(i)
, l_ldet_charge_subtype_tbl(i)
, l_ldet_updated_flag_tbl(i)
, l_ldet_automatic_flag_tbl(i)
, l_ldet_pricing_phase_id_tbl(i)
, l_ldet_calc_code_tbl(i)
, l_ldet_ordqty_operand_tbl(i)
, nvl(l_ldet_ordqty_adjamt_tbl(i), l_ldet_adj_amt_tbl(i))
, l_ldet_price_adjust_id_tbl(i) --bug 12731184
FROM dual
WHERE l_ldet_process_code_tbl(i) = G_STATUS_UPDATED;
UPDATE qp_npreq_lines_tmp
SET unit_price = l_unit_price_tbl(i),
adjusted_unit_price = l_adj_unit_price_tbl(i), --[prarasto:Post Round]
--adjusted_unit_price_ur = l_adjusted_unit_price_ur(i), --[prarasto:Post Round], [julin/postround] redesign
/* decode(G_ROUND_INDIVIDUAL_ADJ,
G_NO_ROUND, l_adj_unit_price_tbl(i),
decode(l_rounding_factor_tbl(i),
NULL, l_adj_unit_price_tbl(i),
round(l_adj_unit_price_tbl(i), - 1 * l_rounding_factor_tbl(i)))),
*/
-- updated_adjusted_unit_price = l_upd_adj_unit_price_tbl(i)
pricing_status_code = l_pricing_sts_code_tbl(i),
pricing_status_text = l_pricing_sts_txt_tbl(i),
processed_flag = G_PROCESSED,
line_unit_price = l_ordqty_unit_price_tbl(i), --[prarasto:Post Round]
/* decode(G_ROUND_INDIVIDUAL_ADJ,
G_NO_ROUND, l_ordqty_unit_price_tbl(i),
decode(l_rounding_factor_tbl(i),
NULL, l_ordqty_unit_price_tbl(i),
round(l_ordqty_unit_price_tbl(i), - 1 * l_rounding_factor_tbl(i)))),
*/
order_uom_selling_price = l_unit_selling_price(i), --[prarasto:Post Round]
/* decode(G_ROUND_INDIVIDUAL_ADJ,
G_NO_ROUND, l_ordqty_selling_price_tbl(i),
decode(l_rounding_factor_tbl(i),
NULL, l_ordqty_selling_price_tbl(i),
round(l_ordqty_selling_price_tbl(i), - 1 * l_rounding_factor_tbl(i)))),
*/
--order_uom_selling_price_ur = l_unit_selling_price_ur(i), --[prarasto:Post Round], [julin/postround] redesign
extended_price = l_extended_selling_price(i), --[prarasto:Post Round]
--extended_selling_price_ur = l_extended_selling_price_ur(i), --[prarasto:Post Round], [julin/postround] redesign
QUALIFIERS_EXIST_FLAG = G_CALCULATE_ONLY
WHERE line_index = l_line_index_tbl(i);
CURSOR l_line_cur IS SELECT
line.line_index
, line.unit_price adjusted_unit_price -- bug 3820859
, line.unit_price
, line.processed_flag
, line.processed_code
, line.updated_adjusted_unit_price
, line.rounding_factor
, line.pricing_status_code, line.pricing_status_text
FROM qp_npreq_lines_tmp line
WHERE
line.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
AND line.line_type_code IN (G_LINE_LEVEL, G_ORDER_LEVEL)
AND line.pricing_status_code IN (G_STATUS_UPDATED
, G_STATUS_GSA_VIOLATION
, G_STATUS_UNCHANGED)
AND nvl(processed_code, '0') <> G_BY_ENGINE
AND line.usage_pricing_type IN
(QP_PREQ_GRP.G_BILLING_TYPE, QP_PREQ_GRP.G_REGULAR_USAGE_TYPE);
SELECT ldet.created_from_list_line_id
, line.line_index line_ind
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.modifier_level_code
, ldet.applied_flag
, 1.0 amount_changed
, line.adjusted_unit_price
--changed to make sure lumpsum on order level frt charge divide by 1 quantity
, ldet.line_quantity priced_quantity
, line.priced_quantity priced_qty
, ldet.group_quantity
, ldet.group_amount
, 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
, ldet.updated_flag
, 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
, G_LINE_LEVEL line_type
, 'N' is_max_frt
, ldet.net_amount_flag
FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
--where line.line_index = p_line_index -- 2892848
WHERE ldet.line_index = line.line_index -- 2892848
--and ldet.line_index = line.line_index --2892848
AND line.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
AND ldet.process_code = G_STATUS_NEW
AND (ldet.applied_flag = G_YES
OR ldet.created_from_list_line_type = G_FREIGHT_CHARGE)
AND ldet.created_from_list_line_type IN (G_DISCOUNT
, G_SURCHARGE, G_PRICE_BREAK_TYPE, G_FREIGHT_CHARGE)
AND nvl(ldet.created_from_list_type_code, 'NULL') NOT IN
(G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
AND ldet.line_detail_index NOT IN
(SELECT rltd.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp rltd
WHERE rltd.pricing_status_code = G_STATUS_NEW
AND rltd.relationship_type_code = G_PBH_LINE)
-- next 4 conditions added for 3435240
AND line.line_type_code = G_LINE_LEVEL
AND line.pricing_status_code IN (G_STATUS_UPDATED
, G_STATUS_GSA_VIOLATION
, G_STATUS_UNCHANGED)
AND nvl(line.processed_code, '0') <> G_BY_ENGINE
AND line.usage_pricing_type IN
(QP_PREQ_GRP.G_BILLING_TYPE, QP_PREQ_GRP.G_REGULAR_USAGE_TYPE)
UNION
SELECT ldet.created_from_list_line_id
, line.line_index line_ind
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.modifier_level_code
, ldet.applied_flag
, 1.0 amount_changed
, line.adjusted_unit_price
--changed to make sure lumpsum on order level frt charge divide by 1 quantity
, ldet.line_quantity priced_quantity
, line.priced_quantity priced_qty
, ldet.group_quantity
, ldet.group_amount
, 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
, ldet.updated_flag
, 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
, G_ORDER_LEVEL line_type
, 'N' is_max_frt
, ldet.net_amount_flag
FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
, qp_npreq_lines_tmp line1
--where line.line_index = p_line_index -- 2892848
--and ldet.line_index = line1.line_index -- 2892848
WHERE ldet.line_index = line1.line_index -- 2892848
AND line1.line_type_code = G_ORDER_LEVEL
AND line1.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
AND line.line_type_code = G_LINE_LEVEL
AND ldet.process_code = G_STATUS_NEW
AND (ldet.applied_flag = G_YES
OR ldet.created_from_list_line_type = G_FREIGHT_CHARGE)
AND ldet.created_from_list_line_type IN (G_DISCOUNT,
G_SURCHARGE, G_PRICE_BREAK_TYPE, G_FREIGHT_CHARGE)
AND nvl(ldet.created_from_list_type_code, 'NULL') NOT IN
(G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
AND ldet.line_detail_index NOT IN
(SELECT rltd.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp rltd
WHERE rltd.pricing_status_code = G_STATUS_NEW
AND rltd.relationship_type_code = G_PBH_LINE)
-- next 4 conditions added for 3435240
AND line.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
AND line.pricing_status_code IN (G_STATUS_UPDATED
, G_STATUS_GSA_VIOLATION
, G_STATUS_UNCHANGED)
AND nvl(line.processed_code, '0') <> G_BY_ENGINE
AND line.usage_pricing_type IN
(QP_PREQ_GRP.G_BILLING_TYPE, QP_PREQ_GRP.G_REGULAR_USAGE_TYPE)
--order by line_ind,pricing_group_sequence; -- 2892848
SELECT ldet.line_detail_index
, ldet.adjustment_amount
FROM qp_npreq_ldets_tmp ldet
WHERE line_index = p_line_index
AND calculation_code = G_BACK_CALCULATE
AND applied_flag = G_YES
AND updated_flag = G_YES;
SELECT net_amount_flag
FROM qp_list_lines
WHERE list_line_id = p_list_line_id;
l_updated_adj_unit_price_ur QP_PREQ_GRP.NUMBER_TYPE; --[prarasto:Post Round]
SELECT DISTINCT ldet.line_index
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.created_from_list_line_id = p_list_line_id
AND pricing_status_code IN (G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED);
SELECT DISTINCT qla.line_index, ql.priced_quantity, ql.unit_price
FROM qp_preq_line_attrs_tmp qla, qp_pricing_attributes qpa, qp_preq_lines_tmp ql
WHERE qpa.list_line_id = p_list_line_id
AND qla.context = qpa.product_attribute_context
AND qla.attribute = qpa.product_attribute
AND qla.value_from = qpa.product_attr_value
AND qla.line_index = ql.line_index
AND ql.price_flag <> G_PHASE
AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
QP_PREQ_PUB.G_STATUS_UNCHANGED)
AND NOT EXISTS (SELECT qla2.line_index
FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
WHERE qpa2.list_line_id = p_list_line_id
AND qpa2.excluder_flag = G_YES
AND qla2.line_index = qla.line_index
AND qla2.context = qpa2.product_attribute_context
AND qla2.attribute = qpa2.product_attribute
AND qla2.value_from = qpa2.product_attr_value);
l_ord_dtl_index_tbl.DELETE;
l_ord_adj_amt_tbl.DELETE;
l_bucket_amt_tbl.DELETE; -- 2892848 SL_latest
l_prev_bucket_amt_tbl.DELETE;
l_mod_lg_net_amt_tbl.DELETE; -- 2892848 SL_latest
l_mod_lg_prod_net_amt_tbl.DELETE; -- [julin/4112395/4220399]
l_lines_tbl.DELETE;
l_ldet_tbl.DELETE;
' updated adjusted unit price '|| l_line_rec.updated_adjusted_unit_price);
' updated adjusted price '||l_lines_tbl(j).updated_adjusted_unit_price);
l_frt_charge_tbl.DELETE;
l_ldet_tbl.DELETE;
l_mod_lg_net_amt_tbl.DELETE; -- clear this table upon bucket change to keep it small
l_mod_lg_prod_net_amt_tbl.DELETE; -- [julin/4112395/4220399]
l_mod_lg_net_amt_tbl.DELETE; -- clear this table upon bucket change to keep it small
l_mod_lg_prod_net_amt_tbl.DELETE; -- [julin/4112395/4220399]
SELECT pricing_attribute
INTO l_pbh_pricing_attr
FROM qp_pricing_attributes
WHERE list_line_id =
l_bucket_price_rec.created_from_list_line_id;
IF l_bucket_price_rec.updated_flag = G_YES
and nvl(l_bucket_price_rec.automatic_flag,G_NO) = G_NO
and (l_bucket_price_rec.adjustment_amount IS NOT NULL
and l_bucket_price_rec.adjustment_amount <>
G_MISS_NUM) --FND_API.G_MISS_NUM)
THEN
--to avoid rounding issues in rev calculations
--for user-overridden adjustments
l_return_adjustment :=
l_bucket_price_rec.adjustment_amount;
AND nvl(l_bucket_price_rec.updated_flag, G_NO) = G_YES)
THEN
l_bucket_price_rec.applied_flag := G_YES;
|| l_bucket_price_rec.updated_flag);
l_ldet_tbl(i).pricing_status_code := G_STATUS_DELETED;
||' insert new frt record ');
l_frt_charge_tbl(1).updated_flag :=
nvl(l_bucket_price_rec.updated_flag, G_NO);
|| l_frt_charge_tbl(N).updated_flag);
IF nvl(l_frt_charge_tbl(N).updated_flag, G_NO) = G_NO
THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Rec is NOT overriden');
IF nvl(l_bucket_price_rec.updated_flag, G_NO) =
G_YES
THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Repl overridden rec');
l_frt_charge_tbl(N).updated_flag
:= l_bucket_price_rec.updated_flag;
ELSIF nvl(l_bucket_price_rec.updated_flag, G_NO) =
G_NO
AND l_bucket_price_rec.adjustment_amount
> l_frt_charge_tbl(N).adjustment_amount
THEN
--if ct adj's adj amt is greater
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('replace high adjamt');
l_frt_charge_tbl(N).updated_flag
:= l_bucket_price_rec.updated_flag;
END IF; --bucket_price_rec.updated_flag
END IF; --frt_charge_tbl.updated_flag
||' insert new record ');
l_frt_charge_tbl(N + 1).updated_flag :=
nvl(l_bucket_price_rec.updated_flag, G_NO);
l_frt_charge_tbl.DELETE(M);
||' updated adjusted price '
|| l_lines_tbl(j).updated_adjusted_unit_price
||' adjustment count '|| l_ldet_tbl.COUNT);
UPDATE qp_npreq_ldets_tmp SET
adjustment_amount = l_ldet_tbl(x).adjustment_amount,
applied_flag = l_ldet_tbl(x).applied_flag,
line_quantity = l_ldet_tbl(x).priced_quantity,
--included for freight charge functionality
pricing_status_code = l_ldet_tbl(x).pricing_status_code,
pricing_status_text = l_ldet_tbl(x).pricing_status_text
WHERE line_detail_index = l_ldet_tbl(x).line_detail_index;
l_ldet_tbl.DELETE;
|| l_lines_tbl(j).updated_adjusted_unit_price
||' adj price '|| l_lines_tbl(j).adjusted_unit_price);
l_updated_adj_unit_price_ur(j) := l_lines_tbl(j).updated_adjusted_unit_price; --[prarasto:Post Round]
l_lines_tbl(j).updated_adjusted_unit_price :=
round(l_lines_tbl(j).updated_adjusted_unit_price,
- 1 * l_lines_tbl(j).rounding_factor);
||' updated_adjusted_unit_price: '|| l_lines_tbl(j).adjusted_unit_price);
l_amount_changed := l_lines_tbl(j).updated_adjusted_unit_price
- l_lines_tbl(j).adjusted_unit_price;
IF (l_lines_tbl(j).updated_adjusted_unit_price IS NULL
OR l_amount_changed = 0) --no back cal if no amt change
THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.ENGINE_DEBUG('no back calculation');
||'before update ldets: line_detail_index '
|| l_back_calc_ret_rec.line_detail_index
||' list line id '
|| l_back_calc_ret_rec.list_line_id
||' adjustment amount '
|| l_back_calc_ret_rec.adjustment_amount
||' operand '|| l_back_calc_ret_rec.operand_value
||' calculation_code '
|| l_back_calc_ret_rec.calculation_code);
UPDATE qp_npreq_ldets_tmp
SET applied_flag = G_NO,
calculation_code = NULL,
pricing_status_code = G_STATUS_DELETED,
pricing_status_text =
'DELETED IN BACK CALC DUE TO CHANGE IN ADJ AMT'
WHERE line_detail_index = l_back_calc_dtl_index;
UPDATE qp_npreq_ldets_tmp ldet
SET ldet.operand_value =
l_back_calc_ret_rec.operand_value
, ldet.adjustment_amount =
l_back_calc_ret_rec.adjustment_amount
, ldet.applied_flag =
l_back_calc_ret_rec.applied_flag
, ldet.updated_flag =
l_back_calc_ret_rec.updated_flag
, ldet.process_code =
l_back_calc_ret_rec.process_code
, ldet.pricing_status_text =
l_back_calc_ret_rec.pricing_status_text
, ldet.calculation_code =
l_back_calc_ret_rec.calculation_code
WHERE ldet.line_detail_index =
l_back_calc_ret_rec.line_detail_index
AND ldet.line_index =
l_back_calc_ret_rec.line_index
AND ldet.created_from_list_line_id =
l_back_calc_ret_rec.list_line_id;
UPDATE qp_npreq_lines_tmp SET adjusted_unit_price
= nvl(l_lines_tbl(j).updated_adjusted_unit_price,
l_lines_tbl(j).adjusted_unit_price)
--, adjusted_unit_price_ur = nvl(l_updated_adj_unit_price_ur(j), --[prarasto:Post Round] added unrounded adjusted unit price, [julin/postround] redesign
-- l_adjusted_unit_price_ur(j))
, processed_flag = l_lines_tbl(j).processed_flag
, processed_code = l_lines_tbl(j).processed_code
, pricing_status_code =
l_lines_tbl(j).pricing_status_code
, pricing_status_text =
l_lines_tbl(j).pricing_status_text
, QUALIFIERS_EXIST_FLAG = G_CALCULATE_ONLY
WHERE line_index = l_lines_tbl(j).line_index;
UPDATE qp_npreq_lines_tmp SET adjusted_unit_price
= l_lines_tbl(j).adjusted_unit_price
--, adjusted_unit_price_ur = l_adjusted_unit_price_ur(j) --[prarasto:Post Round] added unrounded adjusted unit price, [julin/postround] redesign
, processed_flag = l_lines_tbl(j).processed_flag
, processed_code = l_lines_tbl(j).processed_code
, pricing_status_code =
l_lines_tbl(j).pricing_status_code
, pricing_status_text =
l_lines_tbl(j).pricing_status_text
, QUALIFIERS_EXIST_FLAG = G_CALCULATE_ONLY
WHERE line_index = l_lines_tbl(j).line_index;
UPDATE qp_npreq_ldets_tmp
SET adjustment_amount = l_ord_adj_amt_tbl(i)
WHERE line_detail_index = l_ord_dtl_index_tbl(i);
UPDATE qp_npreq_lines_tmp line
SET line.pricing_status_code = G_STATUS_GSA_VIOLATION
, line.pricing_status_text = 'QP_PREQ_PUB: GSA VIOLATION'
WHERE line.line_type_code = G_LINE_LEVEL
--made this change look at only successful lines
AND line.pricing_status_code IN
(G_STATUS_UPDATED, G_STATUS_UNCHANGED)
AND NOT EXISTS (SELECT 'X'
FROM qp_npreq_line_attrs_tmp gsa_attr
WHERE gsa_attr.line_index = line.line_index
--fix for bug 2080187
AND gsa_attr.pricing_status_code = G_STATUS_UNCHANGED
AND gsa_attr.attribute_type = G_QUALIFIER_TYPE
AND gsa_attr.context = G_CUSTOMER_CONTEXT
AND gsa_attr.attribute = G_GSA_ATTRIBUTE
AND gsa_attr.value_from = G_YES)
AND line.adjusted_unit_price <=
--(SELECT /*+ ORDERED USE_NL(qpa ql req lhdr) */ MIN(ql.operand) -- 7323912
(SELECT /*+ ORDERED USE_NL(qpa ql req lhdr) INDEX(LHDR QP_LIST_HEADERS_B_N9) */ MIN(ql.operand) -- 7323912
FROM qp_npreq_line_attrs_tmp lattr,
qp_pricing_attributes qpa,
qp_list_headers_b lhdr, --7323912
qp_price_req_sources req, --7323912
qp_list_lines ql
WHERE lattr.line_index = line.line_index
AND lattr.attribute_type = G_PRODUCT_TYPE
AND lattr.context = qpa.product_attribute_context
AND lattr.attribute = qpa.product_attribute
AND lattr.value_from = qpa.product_attr_value
AND qpa.excluder_flag = G_NO
AND qpa.pricing_phase_id = 2
AND qpa.qualification_ind = 6
AND lattr.line_index = line.line_index
AND req.request_type_code = line.request_type_code
AND lhdr.list_header_id = qpa.list_header_id
AND lhdr.active_flag = G_YES
AND ((lhdr.currency_code IS NOT NULL AND lhdr.currency_code = line.currency_code)
OR
lhdr.currency_code IS NULL) -- optional currency
AND lhdr.list_type_code = G_DISCOUNT_LIST_HEADER
AND lhdr.source_system_code = req.source_system_code
AND lhdr.gsa_indicator = G_YES
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(lhdr.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(lhdr.End_date_active
, line.pricing_effective_date))
AND qpa.list_line_id = ql.list_line_id
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(ql.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(ql.End_date_active
, line.pricing_effective_date)));
UPDATE qp_npreq_lines_tmp line
SET line.pricing_status_code = G_STATUS_GSA_VIOLATION
, line.pricing_status_text = 'QP_PREQ_PUB: GSA VIOLATION'
WHERE line.line_type_code = G_LINE_LEVEL
--made this change look at only successful lines
AND line.pricing_status_code IN
(G_STATUS_UPDATED, G_STATUS_UNCHANGED)
AND NOT EXISTS (SELECT 'X'
FROM qp_npreq_line_attrs_tmp gsa_attr
WHERE gsa_attr.line_index = line.line_index
--fix for bug 2080187
AND gsa_attr.pricing_status_code = G_STATUS_UNCHANGED
AND gsa_attr.attribute_type = G_QUALIFIER_TYPE
AND gsa_attr.context = G_CUSTOMER_CONTEXT
AND gsa_attr.attribute = G_GSA_ATTRIBUTE
AND gsa_attr.value_from = G_YES)
AND line.adjusted_unit_price <=
--(SELECT /*+ ORDERED USE_NL(qpa ql req lhdr) */ MIN(ql.operand) --7323912
(SELECT /*+ ORDERED USE_NL(qpa ql req lhdr) INDEX(LHDR QP_LIST_HEADERS_B_N9) */ MIN(ql.operand) --7323912
FROM qp_npreq_line_attrs_tmp lattr,
qp_pricing_attributes qpa,
qp_list_headers_b lhdr, --7323912
qp_price_req_sources_v req, --7323912
qp_list_lines ql
WHERE lattr.line_index = line.line_index
AND lattr.attribute_type = G_PRODUCT_TYPE
AND lattr.context = qpa.product_attribute_context
AND lattr.attribute = qpa.product_attribute
AND lattr.value_from = qpa.product_attr_value
AND qpa.excluder_flag = G_NO
AND qpa.pricing_phase_id = 2
AND qpa.qualification_ind = 6
AND lattr.line_index = line.line_index
AND req.request_type_code = line.request_type_code
AND lhdr.list_header_id = qpa.list_header_id
AND lhdr.active_flag = G_YES
AND ((lhdr.currency_code IS NOT NULL AND lhdr.currency_code = line.currency_code)
OR
lhdr.currency_code IS NULL) -- optional currency
AND lhdr.list_type_code = G_DISCOUNT_LIST_HEADER
AND lhdr.source_system_code = req.source_system_code
AND lhdr.gsa_indicator = G_YES
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(lhdr.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(lhdr.End_date_active
, line.pricing_effective_date))
AND qpa.list_line_id = ql.list_line_id
AND trunc(line.pricing_effective_date) BETWEEN
trunc(nvl(ql.start_date_active
, line.pricing_effective_date))
AND trunc(nvl(ql.End_date_active
, line.pricing_effective_date)));
SELECT rltd.line_detail_index,
rltd.related_line_detail_index,
rltd.list_line_id,
rltd.related_list_line_id,
rltd.pricing_status_code
FROM qp_npreq_rltd_lines_tmp rltd
WHERE rltd.relationship_type_code = G_PBH_LINE;
UPDATE qp_npreq_rltd_lines_tmp SET pricing_status_code = G_STATUS_DELETED
WHERE line_detail_index IN (SELECT line_detail_index
FROM qp_npreq_ldets_tmp ldet
WHERE (ldet.process_code = G_STATUS_DELETED
OR ldet.pricing_status_code = G_STATUS_DELETED))
--fix for bug 2515762 automatic overrideable break
-- and ldet.automatic_flag = G_NO)
AND pricing_status_code = G_STATUS_NEW
AND relationship_type_code = G_PBH_LINE;
UPDATE qp_npreq_ldets_tmp ldet SET pricing_status_code = G_STATUS_DELETED
WHERE ldet.line_detail_index IN
(SELECT rltd.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp rltd
WHERE rltd.relationship_type_code = G_PBH_LINE
AND rltd.pricing_status_code = G_STATUS_DELETED);
PROCEDURE Update_Related_Line_Info(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
CURSOR l_dbg_rltd_cur IS
SELECT
qpa.pricing_attr_value_from,
qpa.pricing_attr_value_to,
ldet_pbh.price_break_type_code,
ldet_pbh.created_from_list_line_id list_line_id,
ldet.created_from_list_line_id related_list_line_id,
ldet.created_from_list_line_type related_list_line_type,
ldet.operand_calculation_code operand_calculation_code,
ldet.operand_value operand,
ldet.pricing_group_sequence pricing_group_sequence,
nvl(ldet.line_quantity,
nvl(line.priced_quantity, line.line_quantity))
qualifier_value
FROM
qp_npreq_rltd_lines_tmp rltd,
qp_npreq_lines_tmp line,
qp_npreq_ldets_tmp ldet,
qp_npreq_ldets_tmp ldet_pbh,
qp_pricing_attributes qpa
WHERE
rltd.pricing_status_code = G_STATUS_NEW
AND ldet.line_detail_index =
rltd.related_line_detail_index
AND ldet_pbh.line_detail_index = rltd.line_detail_index
AND line.line_index = ldet.line_index
AND qpa.list_line_id = ldet.created_from_list_line_id
AND qpa.pricing_attribute_context = G_PRIC_VOLUME_CONTEXT
AND ldet.process_code = G_STATUS_NEW
AND ldet.pricing_status_code = G_STATUS_UNCHANGED
AND rltd.relationship_type_code = G_PBH_LINE;
SELECT setup_value_from,
setup_value_to,
relationship_type_detail,
list_line_id,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
pricing_group_sequence,
qualifier_value
FROM qp_npreq_rltd_lines_tmp rltd
WHERE rltd.pricing_status_code = G_STATUS_NEW
AND rltd.relationship_type_code = G_PBH_LINE;
UPDATE qp_npreq_rltd_lines_tmp rltd
SET (setup_value_from,
setup_value_to,
relationship_type_detail,
list_line_id,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
pricing_group_sequence,
qualifier_value)
=
(SELECT
qpa.pricing_attr_value_from,
qpa.pricing_attr_value_to,
ldet_pbh.price_break_type_code,
ldet_pbh.created_from_list_line_id,
ldet.created_from_list_line_id,
ldet.created_from_list_line_type,
ldet.operand_calculation_code,
ldet.operand_value,
ldet.pricing_group_sequence,
nvl(ldet.line_quantity,
nvl(line.priced_quantity, line.line_quantity))
FROM
qp_npreq_lines_tmp line,
qp_npreq_ldets_tmp ldet,
qp_npreq_ldets_tmp ldet_pbh,
qp_pricing_attributes qpa
WHERE
ldet.line_detail_index = rltd.related_line_detail_index
AND ldet_pbh.line_detail_index = rltd.line_detail_index
AND line.line_index = ldet.line_index
AND qpa.list_line_id = ldet.created_from_list_line_id
AND ldet.process_code = G_STATUS_NEW
AND ldet.pricing_status_code = G_STATUS_UNCHANGED
AND rltd.relationship_type_code = G_PBH_LINE
AND rltd.pricing_status_code = G_STATUS_NEW)
WHERE rltd.line_detail_index IN (SELECT ldet.line_detail_index
FROM qp_npreq_ldets_tmp ldet
WHERE ldet.process_code = G_STATUS_NEW
AND ldet.pricing_status_code = G_STATUS_UNCHANGED
AND ldet.created_from_list_line_type = G_PRICE_BREAK_TYPE)
AND rltd.relationship_type_code = G_PBH_LINE
AND rltd.pricing_status_code = G_STATUS_NEW;
x_return_status_text := 'Exception in QP_PREQ_PUB.Update_Related_Line_Info '|| SQLERRM;
END Update_Related_Line_Info;
SELECT /*+ index (ldeta qp_preq_ldets_tmp_n5) */ -- 9362867
ldeta.line_detail_index
, ldeta.line_index
, ldeta.created_from_list_line_id
, ldeta.pricing_status_code
, ldeta.process_code
, ldeta.pricing_status_text
, ldeta.applied_flag
, ldeta.updated_flag
FROM qp_npreq_ldets_tmp ldeta
WHERE --ldeta.pricing_status_code = 'X'
ldeta.process_code = G_STATUS_NEW
AND ldeta.created_from_list_line_id IN
(SELECT /*+ index (ldetb qp_preq_ldets_tmp_n5) */ -- 9362867
ldetb.created_from_list_line_id
FROM qp_npreq_ldets_tmp ldetb
WHERE ldetb.created_from_list_line_id =
ldeta.created_from_list_line_id
AND ldetb.line_index = ldeta.line_index
AND ldetb.process_code = G_STATUS_NEW
GROUP BY ldetb.created_from_list_line_id
HAVING COUNT( * ) > 1)
ORDER BY ldeta.line_index
, ldeta.created_from_list_line_id
, ldeta.pricing_status_code DESC;
SELECT line_index
, created_from_list_line_id
, pricing_status_code, applied_flag
, updated_flag, operand_value
FROM qp_npreq_ldets_tmp
WHERE PRICING_STATUS_CODE = G_STATUS_UNCHANGED;
SELECT created_from_list_line_id
, line_detail_index
, line_quantity
, line_index
, applied_flag
, updated_flag
, pricing_status_code
, process_code
FROM qp_npreq_ldets_tmp
ORDER BY line_index;
' updated_flag '||lrec.updated_flag||
' operand '||lrec.operand_value);
UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_DELETED
WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED;
UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED AND
ldet.applied_flag = G_YES AND
ldet.pricing_phase_id NOT IN (SELECT ev.pricing_phase_id
FROM qp_event_phases ev, qp_pricing_phases ph
, qp_npreq_lines_tmp line
WHERE instr(p_pricing_event, ev.pricing_event_code || ',') > 0
AND ev.pricing_phase_id = ph.pricing_phase_id
AND line.price_flag <> G_CALCULATE_ONLY
AND ((G_GET_FREIGHT_FLAG = G_YES AND ph.freight_exists = G_YES)
OR (G_GET_FREIGHT_FLAG = G_NO))
AND line.line_index = ldet.line_index
AND ((nvl(Get_buy_line_price_flag(ldet.created_from_list_line_id, ldet.line_index), line.price_flag) = G_YES)
OR ((nvl(Get_buy_line_price_flag(ldet.created_from_list_line_id, ldet.line_index), line.price_flag) = G_PHASE)
AND (nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = G_YES))));
UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED AND
--fix for bug 2515762 automatic overrideable break
ldet.updated_flag = G_YES;
UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
WHERE ldet.line_index IN
(SELECT line.line_index FROM qp_npreq_lines_tmp line
WHERE line.line_type_code = G_ORDER_LEVEL
AND line.price_flag = G_NO);
UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_DELETED
WHERE ldet.process_code = G_STATUS_NEW
AND ldet.pricing_status_code NOT IN
(G_STATUS_NEW, G_STATUS_UNCHANGED); -- and ldet.applied_flag = 'Y';
l_dup_adj_tbl.DELETE;
' updated_flag '|| l_duplicate_rec.updated_flag);
' updated_flag '|| l_duplicate_rec1.updated_flag);
((nvl(l_duplicate_rec.updated_flag, G_NO) = G_YES)
OR (nvl(l_duplicate_rec.applied_flag, G_NO) = G_YES))) THEN
--retain adj passed by user
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.ENGINE_DEBUG('retain user passed');
l_duplicate_rec1.pricing_status_code := G_STATUS_DELETED;
((nvl(l_duplicate_rec.updated_flag, G_NO) = G_NO) OR
(nvl(l_duplicate_rec.updated_flag, G_NO) = G_NO))) THEN
--retain adj passed by user
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.ENGINE_DEBUG('retain engine passed');
l_duplicate_rec.pricing_status_code := G_STATUS_DELETED;
l_duplicate_rec.pricing_status_code := G_STATUS_DELETED;
' updated_flag '|| l_dup_adj_tbl(i).updated_flag);
' updated_flag '|| l_dup_adj_tbl(i).updated_flag);
UPDATE qp_npreq_ldets_tmp SET
process_code = l_dup_adj_tbl(i).process_code
, pricing_status_code = l_dup_adj_tbl(i).pricing_status_code
, pricing_status_text = l_dup_adj_tbl(i).pricing_status_text
WHERE line_detail_index = l_dup_adj_tbl(i).line_detail_index;
QP_PREQ_GRP.ENGINE_DEBUG('Update processed adjustments:
list_line_id '|| l_dup_adj_tbl(i).created_from_list_line_id ||
' pricing_status_code '|| l_dup_adj_tbl(i).pricing_status_code ||
'process_code '|| l_dup_adj_tbl(i).process_code);
' updated '|| lrec1.updated_flag ||' process '|| lrec1.process_code);
Update_Related_Line_Info(x_return_status, x_return_status_text);
' updated '|| lrec1.updated_flag ||' process '|| lrec1.process_code);
PROCEDURE UPDATE_QUALIFIER_VALUE(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2)
IS
BEGIN
l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
QP_PREQ_GRP.engine_debug('Begin UPDATE_QUALIFIER_VALUE routine');
UPDATE qp_npreq_rltd_lines_tmp rltd
SET rltd.qualifier_value = (SELECT
decode(qpa.pricing_attribute,
G_LINE_AMT_ATTRIBUTE,
(nvl(line.priced_quantity, line.line_quantity)
* nvl(line.unit_price, 0)),
nvl(line.priced_quantity, line.line_quantity))
FROM qp_npreq_lines_tmp line,
qp_pricing_attributes qpa
WHERE line.line_index = rltd.line_index
AND qpa.list_line_id = rltd.related_list_line_id
AND qpa.pricing_attribute_context =
G_PRIC_VOLUME_CONTEXT)
WHERE rltd.line_index IN (SELECT line.line_index
FROM qp_npreq_lines_tmp line
WHERE line.line_index = rltd.line_index
AND rltd.relationship_type_code = G_PBH_LINE
AND line.pricing_status_code IN
(G_STATUS_UNCHANGED, G_STATUS_UPDATED,
G_STATUS_GSA_VIOLATION)
AND line.usage_pricing_type =
QP_PREQ_GRP.G_BILLING_TYPE);
QP_PREQ_GRP.engine_debug('End UPDATE_QUALIFIER_VALUE routine');
QP_PREQ_GRP.engine_debug('Error UPDATE_QUALIFIER_VALUE routine '|| SQLERRM);
x_return_status_text := 'QP_PREQ_PUB.UPDATE_QUALIFIER_VALUE: '|| SQLERRM;
END UPDATE_QUALIFIER_VALUE;
PROCEDURE Update_Child_Break_Lines(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
--[julin/pbperf] tuned to use QP_PREQ_LDETS_TMP_U1
CURSOR l_child_break_cur IS
SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_U1) */ldet.process_code,
rltd.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp rltd,
-- qp_npreq_ldets_tmp ldet1,
qp_npreq_ldets_tmp ldet
WHERE rltd.relationship_type_code = G_PBH_LINE
AND rltd.pricing_status_code = G_STATUS_NEW
AND ldet.line_index = rltd.line_index
AND ldet.line_detail_index = rltd.line_detail_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.process_code = G_STATUS_NEW;
QP_PREQ_GRP.engine_debug('In Update_Child_break_lines routine');
l_child_sts_code_tbl.DELETE;
l_line_dtl_index_tbl.DELETE;
QP_PREQ_GRP.engine_debug('before update');
UPDATE qp_npreq_ldets_tmp
SET process_code = l_child_sts_code_tbl(j)
WHERE line_detail_index = l_line_dtl_index_tbl(j);
QP_PREQ_GRP.engine_debug('after update');
QP_PREQ_GRP.engine_debug('End Update_Child_break_lines routine');
QP_PREQ_GRP.engine_debug('Error Update_Child_break_lines routine '|| SQLERRM);
x_return_status_text := 'QP_PREQ_PUB.Update_Child_Break_lines: '|| SQLERRM;
END Update_Child_Break_Lines;
SELECT rltd.line_detail_index parent_detail_index,
rltd.line_index parent_line_index,
ldet.line_detail_index child_detail_index,
ldet.line_index child_line_index,
ldet.line_detail_type_code,
ldet.created_from_list_line_type,
qpa.pricing_attr_value_from value_from,
qpa.pricing_attr_value_to value_to,
line.priced_quantity ordered_quantity,
attr.value_from break_quantity,
line.uom_quantity service_duration,
line.rounding_factor,
-- ldet.line_quantity line_qty,
ldet.created_from_list_line_id,
ldet.price_break_type_code price_break_type,
ldet.modifier_level_code,
ldet.group_quantity,
ldet.operand_calculation_code,
ldet.operand_value,
ldet.group_amount,
line.parent_price
FROM qp_npreq_lines_tmp line,
qp_npreq_rltd_lines_tmp rltd,
qp_npreq_ldets_tmp ldet,
qp_pricing_attributes qpa,
qp_npreq_line_attrs_tmp attr
WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED, G_STATUS_UPDATED)
AND line.price_flag = G_CALCULATE_ONLY
AND nvl(line.processing_order, 1) = p_processing_order
AND line.usage_pricing_type = QP_PREQ_GRP.G_BILLING_TYPE
AND line.line_index = rltd.line_index
AND rltd.relationship_type_code = G_PBH_LINE
AND ldet.line_index = line.line_index
AND ldet.pricing_status_code = 'X'
AND ldet.line_detail_index = rltd.related_line_detail_index
AND qpa.list_line_id = ldet.created_from_list_line_id
AND qpa.pricing_attribute_context = G_PRIC_VOLUME_CONTEXT
AND attr.line_index = line.line_index
AND attr.line_detail_index IS NULL
AND attr.context = G_PRIC_VOLUME_CONTEXT
AND attr.attribute = qpa.pricing_attribute
ORDER BY child_detail_index, parent_detail_index;
G_STATUS_UPDATED;
G_STATUS_UPDATED;
UPDATE qp_npreq_ldets_tmp
SET line_quantity = l_line_quantity_tbl(i)
, applied_flag = l_applied_flag_tbl(i)
, adjustment_amount = l_adj_amt_tbl(i)
WHERE line_detail_index = l_line_dtl_index_tbl(i);
QP_PREQ_GRP.engine_debug('update PBH ');
UPDATE qp_npreq_ldets_tmp
SET line_quantity = l_total_qty_tbl(i)
, applied_flag = G_YES
, adjustment_amount = x_list_price_tbl(i)
WHERE line_index = x_line_index_tbl(i)
AND created_from_list_line_type = G_BY_PBH
AND created_from_list_type_code IN
(G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
AND x_pricing_sts_code_tbl(i) = G_STATUS_UPDATED;
UPDATE qp_npreq_ldets_tmp
SET applied_flag = G_NO
WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
AND created_from_list_type_code IN
(G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
AND adjustment_amount IS NULL;
PROCEDURE Update_Service_Lines(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
/*
INDX,QP_PREQ_PUB.Usage_pricing.l_Service_Cur,-No Index Used-,NA,NA
*/
CURSOR l_Service_Cur IS
SELECT rltd.line_index,
rltd.related_line_index,
line.unit_price,
line.priced_quantity,
line.priced_uom_code
FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
WHERE rltd.line_index IS NOT NULL
AND rltd.related_line_index IS NOT NULL
AND rltd.relationship_type_code = G_SERVICE_LINE
AND line.line_index = rltd.line_index;
QP_PREQ_GRP.engine_debug('Begin Update_Service_Lines');
UPDATE qp_npreq_lines_tmp
SET parent_price = l_unit_price_tbl(i),
parent_uom_code = l_priced_uom_code_tbl(i),
processing_order = 2,
parent_quantity = l_priced_quantity_tbl(i)
WHERE line_index = l_service_line_tbl(i);
UPDATE qp_npreq_lines_tmp
SET processing_order = 1
WHERE line_index = l_parent_line_tbl(i);
QP_PREQ_GRP.engine_debug('End Update_Service_Lines');
x_return_status_text := 'QP_PREQ_PUB.Update_Service_Lines Exception: '
|| SQLERRM;
QP_PREQ_GRP.engine_debug('Exception Update_Service_Lines '|| SQLERRM);
END Update_Service_Lines;
SELECT ldet.created_from_list_line_id
, line.line_index line_ind
, ldet.line_detail_index
, ldet.created_from_list_line_type
, ldet.modifier_level_code
, ldet.applied_flag
-- , 1.0 amount_changed
-- , line.adjusted_unit_price
--changed to make sure lumpsum on order level frt charge divide by 1 quantity
, ldet.line_quantity priced_quantity
, line.priced_quantity priced_qty
, ldet.group_quantity
, ldet.group_amount
, ldet.operand_calculation_code
, ldet.operand_value
, ldet.adjustment_amount
, line.unit_price
, ldet.process_code
, ldet.price_break_type_code
, line.rounding_factor
, line.uom_quantity service_duration
, line.processing_order
, line.parent_price
FROM qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet
WHERE line.usage_pricing_type = QP_PREQ_GRP.G_BILLING_TYPE
AND ldet.line_index = line.line_index
AND line.price_flag = G_CALCULATE_ONLY
AND nvl(processing_order, 1) = p_processing_order
AND ldet.applied_flag = G_YES
AND ldet.created_from_list_type_code IN
(G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
AND ldet.created_from_list_line_type = G_PRICE_LIST_TYPE
AND nvl(ldet.line_detail_type_code, 'NULL') <>
G_CHILD_DETAIL_TYPE
ORDER BY line_ind;
:= G_STATUS_UPDATED;
UPDATE qp_npreq_ldets_tmp
SET adjustment_amount = l_adj_amt_tbl(i)
WHERE line_detail_index = l_line_dtl_index_tbl(i);
UPDATE qp_npreq_lines_tmp
SET unit_price = l_unit_price_tbl(i),
pricing_status_code = l_pricing_sts_code_tbl(i),
pricing_status_text = l_pricing_sts_txt_tbl(i)
WHERE line_index = l_line_index_tbl(i);
l_line_index_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_pricing_sts_code_tbl.DELETE;
l_pricing_sts_txt_tbl.DELETE;
l_adj_amt_tbl.DELETE;
l_line_dtl_index_tbl.DELETE;
Update_Service_Lines(x_return_status, x_return_status_text);
:= G_STATUS_UPDATED;
UPDATE qp_npreq_ldets_tmp
SET adjustment_amount = l_adj_amt_tbl(i)
WHERE line_detail_index = l_line_dtl_index_tbl(i);
UPDATE qp_npreq_lines_tmp
SET unit_price = l_unit_price_tbl(i),
pricing_status_code = l_pricing_sts_code_tbl(i),
pricing_status_text = l_pricing_sts_txt_tbl(i)
WHERE line_index = l_line_index_tbl(i);
SELECT G_YES
FROM qp_npreq_lines_tmp line
WHERE line.pricing_status_code IN
(G_STATUS_UPDATED, G_STATUS_UNCHANGED, G_STATUS_GSA_VIOLATION)
AND line.usage_pricing_type = QP_PREQ_GRP.G_BILLING_TYPE;
SELECT /*+ ORDERED USE_NL(ev ph oldprg oldrltd oldfgdis oldfreeline)*/
oldfreeline.line_index,
oldfgdis.list_line_id,
nvl(oldfgdis.operand_per_pqty, oldfgdis.operand) operand_value,
oldfgdis.arithmetic_operator operand_calculation_code,
buyline.line_index,
oldprg.list_line_id,
oldprg.updated_flag
FROM qp_npreq_lines_tmp buyline
, qp_event_phases ev
, qp_pricing_phases ph
, oe_price_adjustments oldprg
, oe_price_adj_assocs oldrltd
, oe_price_adjustments oldfgdis
, qp_npreq_lines_tmp oldfreeline
--where G_REQUEST_TYPE_CODE = 'ONT'
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
--need not do freegoods cleanup if PRG line has price_flag 'P' or 'N'
AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
AND instr(p_event_code, ev.pricing_event_code || ',') > 0
AND ph.pricing_phase_id = ev.pricing_phase_id
AND NVL(ev.start_date_active,sysdate) >= SYSDATE --9446733
AND NVL(ev.end_date_active,sysdate) <= SYSDATE --9446733
AND ((G_GET_FREIGHT_FLAG = G_YES AND ph.freight_exists = 'Y')
OR (G_GET_FREIGHT_FLAG = G_NO))
AND (buyline.line_type_code = G_LINE_LEVEL
AND oldprg.line_id = buyline.line_id)
AND oldprg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
--we need to look for in phase PRGs only otherwise
--OM will keep deleting the fg lines between LINE and ORDER event
--after every reprice
AND oldprg.pricing_phase_id = ph.pricing_phase_id
--commented this out as OM passes price_flag as 'N' on fg line
--and ((oldprg.pricing_phase_id = ev.pricing_phase_id
--and buyline.price_flag = G_YES)
--or (oldprg.pricing_phase_id = ev.pricing_phase_id
--and buyline.price_flag = G_PHASE
--and ph.freeze_override_flag = G_YES))
AND oldrltd.price_adjustment_id = oldprg.price_adjustment_id
AND oldfgdis.price_adjustment_id = oldrltd.rltd_price_adj_id
AND oldfgdis.list_line_type_code = 'DIS'
--and ((oldfgdis.line_id is null
--and oldfreeline.line_type_code = G_ORDER_LEVEL
--and oldfreeline.line_id = oldfgdis.header_id)
--freegood line is always a line need not match header
AND (oldfgdis.line_id IS NOT NULL
AND oldfreeline.line_type_code = G_LINE_LEVEL
AND oldfreeline.line_id = oldfgdis.line_id)
--Commented this UNION as there wont be any FREEGOOD type modifier
--at ORDER level. this is causing perf issue for 8407425
-- UNION
-- SELECT /*+ ORDERED USE_NL(ev ph oldprg oldrltd oldfgdis oldfreeline)*/
-- oldfreeline.line_index,
-- oldfgdis.list_line_id,
-- nvl(oldfgdis.operand_per_pqty, oldfgdis.operand) operand_value,
-- oldfgdis.arithmetic_operator operand_calculation_code,
-- buyline.line_index,
-- oldprg.list_line_id,
-- oldprg.updated_flag
-- FROM qp_npreq_lines_tmp buyline
-- , qp_event_phases ev
-- , qp_pricing_phases ph
-- , oe_price_adjustments oldprg
-- , oe_price_adj_assocs oldrltd
-- , oe_price_adjustments oldfgdis
-- , qp_npreq_lines_tmp oldfreeline
--where G_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 = G_YES
--need not do freegoods cleanup if PRG line has price_flag 'P' or 'N'
-- AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
-- AND instr(p_event_code, ev.pricing_event_code || ',') > 0
-- AND ph.pricing_phase_id = ev.pricing_phase_id
-- AND ((G_GET_FREIGHT_FLAG = G_YES AND ph.freight_exists = 'Y')
-- OR (G_GET_FREIGHT_FLAG = G_NO))
-- AND (buyline.line_type_code = G_ORDER_LEVEL
-- AND oldprg.header_id = buyline.line_id
-- AND oldprg.line_id IS NULL)
-- AND oldprg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
--we need to look for in phase PRGs only otherwise
--OM will keep deleting the fg lines between LINE and ORDER event
--after every reprice
-- AND oldprg.pricing_phase_id = ph.pricing_phase_id
--commented this out as OM passes price_flag as 'N' on fg line
--and ((oldprg.pricing_phase_id = ev.pricing_phase_id
--and buyline.price_flag = G_YES)
--or (oldprg.pricing_phase_id = ev.pricing_phase_id
--and buyline.price_flag = G_PHASE
--and ph.freeze_override_flag = G_YES))
-- AND oldrltd.price_adjustment_id = oldprg.price_adjustment_id
-- AND oldfgdis.price_adjustment_id = oldrltd.rltd_price_adj_id
-- AND oldfgdis.list_line_type_code = 'DIS'
--and ((oldfgdis.line_id is null
--and oldfreeline.line_type_code = G_ORDER_LEVEL
--and oldfreeline.line_id = oldfgdis.header_id)
--freegood line is always a line need not match header
-- AND (oldfgdis.line_id IS NOT NULL
-- AND oldfreeline.line_type_code = G_LINE_LEVEL
-- AND oldfreeline.line_id = oldfgdis.line_id)
-- End comment 8407425
UNION
SELECT /*+ ORDERED USE_NL(ev ph oldprg oldrltd oldfgdis oldfreeline)*/
oldfreeline.line_index,
oldfgdis.created_from_list_line_id,
oldfgdis.operand_value,
oldfgdis.operand_calculation_code,
buyline.line_index,
oldprg.created_from_list_line_id,
oldprg.updated_flag
FROM qp_npreq_lines_tmp buyline
, qp_event_phases ev
, qp_pricing_phases ph
, qp_npreq_ldets_tmp oldprg
, qp_npreq_rltd_lines_tmp oldrltd
, qp_npreq_ldets_tmp oldfgdis
, qp_npreq_lines_tmp oldfreeline
--where G_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 <> G_YES
AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
AND instr(p_event_code, ev.pricing_event_code || ',') > 0
AND ph.pricing_phase_id = ev.pricing_phase_id
AND NVL(ev.start_date_active,sysdate) >= SYSDATE --9446733
AND NVL(ev.end_date_active,sysdate) <= SYSDATE --9446733
AND ((G_GET_FREIGHT_FLAG = G_YES AND ph.freight_exists = 'Y')
OR (G_GET_FREIGHT_FLAG = G_NO))
AND oldprg.line_index = buyline.line_index
AND oldprg.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
--we need to look for in phase PRGs only otherwise
--OC will keep deleting the fg lines between LINE and ORDER event
AND oldprg.pricing_phase_id = ph.pricing_phase_id
-- and oldprg.pricing_status_code = G_STATUS_UNCHANGED
AND oldprg.applied_flag = G_YES
AND oldrltd.line_detail_index = oldprg.line_detail_index
AND oldrltd.relationship_type_code = G_GENERATED_LINE
-- and oldrltd.pricing_status_code = G_STATUS_UNCHANGED
AND oldfgdis.line_detail_index = oldrltd.related_line_detail_index
AND oldfgdis.pricing_status_code = G_STATUS_UNCHANGED
AND oldfgdis.applied_flag = G_YES
AND oldfgdis.created_from_list_line_type = 'DIS'
AND oldfreeline.line_index = oldrltd.related_line_index;
l_buy_updated_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
SELECT prg.line_id buy_line_id, prg.list_line_id prg_list_line_id,
fgdis.list_line_id fgdis_list_line_id, prg.pricing_phase_id prg_phase_id,
prg.price_adjustment_id prg_price_adj_id,
fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
prg.updated_flag
FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
oe_price_adj_assocs ass, oe_price_adjustments fgdis
WHERE line.line_type_code = G_LINE_LEVEL
AND prg.line_id = line.line_id
AND prg.list_line_type_code = 'PRG'
AND ass.price_adjustment_id = prg.price_adjustment_id
AND fgdis.price_adjustment_id = ass.rltd_price_adj_id
UNION
SELECT prg.line_id buy_line_id, prg.list_line_id prg_list_line_id,
fgdis.list_line_id fgdis_list_line_id, prg.pricing_phase_id prg_phase_id,
prg.price_adjustment_id prg_price_adj_id,
fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
prg.updated_flag
FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
oe_price_adj_assocs ass, oe_price_adjustments fgdis
WHERE line.line_type_code = G_ORDER_LEVEL
AND prg.header_id = line.line_id
AND prg.line_id IS NULL
AND prg.list_line_type_code = 'PRG'
AND ass.price_adjustment_id = prg.price_adjustment_id
AND fgdis.price_adjustment_id = ass.rltd_price_adj_id;
||' prgpriceadjid '|| cl.prg_price_adj_id ||' prgupdatedflag '|| cl.updated_flag
||' fglineid '|| cl.fg_line_id ||' fgdis_listlineid '|| cl.fgdis_list_line_id
||' fgpriceadjid '|| cl.fg_price_adj_id ||' prg_phase_id '|| cl.prg_phase_id);
(SELECT buyline.line_index
, oldprg.list_line_id, oldfgdis.line_id
, oldprg.updated_flag
FROM qp_npreq_lines_tmp buyline
, qp_event_phases ev
, qp_pricing_phases ph
, oe_price_adjustments oldprg
, oe_price_adj_assocs oldrltd
, oe_price_adjustments oldfgdis
-- where G_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 = G_YES
AND instr(p_event_code, ev.pricing_event_code || ',') > 0
AND ph.pricing_phase_id = ev.pricing_phase_id
AND ((buyline.line_type_code = G_LINE_LEVEL
AND oldprg.line_id = buyline.line_id)
OR (buyline.line_type_code = G_ORDER_LEVEL
AND oldprg.header_id = buyline.line_id
AND oldprg.line_id IS NULL))
AND oldprg.list_line_type_code = 'PRG'
AND oldprg.pricing_phase_id = ph.pricing_phase_id
-- and ((oldprg.pricing_phase_id = ev.pricing_phase_id
-- and buyline.price_flag = G_YES)
-- or (oldprg.pricing_phase_id = ev.pricing_phase_id
-- and buyline.price_flag = G_PHASE
-- and ph.freeze_override_flag = G_YES))
AND oldrltd.price_adjustment_id = oldprg.price_adjustment_id
AND oldfgdis.price_adjustment_id = oldrltd.rltd_price_adj_id
AND oldfgdis.list_line_type_code = 'DIS')
LOOP
IF l_debug = FND_API.G_TRUE THEN
qp_preq_grp.engine_debug('ident fg dtls: buylineindex '|| cl.line_index
||' prg-list_line_id '|| cl.list_line_id
||' fgline_id '|| cl.line_id ||' updated_flag '|| cl.updated_flag);
SELECT 1
INTO l_fgdline_exist
FROM dual
WHERE EXISTS (SELECT 1
FROM qp_npreq_rltd_lines_tmp oldrltd,
qp_npreq_ldets_tmp oldprg,
qp_event_phases ev
WHERE oldrltd.relationship_type_code = G_GENERATED_LINE
AND oldrltd.line_detail_index = oldprg.line_detail_index
AND oldrltd.line_index = oldprg.line_index
AND oldprg.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
AND oldprg.applied_flag = G_YES
AND oldprg.pricing_phase_id = ev.pricing_phase_id
AND instr(p_event_code, ev.pricing_event_code || ',') > 0);
l_fg_line_index_tbl.DELETE;
l_fg_list_line_id_tbl.DELETE;
l_fg_operand_tbl.DELETE;
l_fg_operator_tbl.DELETE;
l_buy_line_index_tbl.DELETE;
l_buy_list_line_id_tbl.DELETE;
, l_buy_updated_flag_tbl;
|| l_buy_list_line_id_tbl(i) ||' prg-updated_flag '
|| l_buy_updated_flag_tbl(i));
UPDATE qp_npreq_lines_tmp
SET process_status = G_FREEGOOD || l_fg_list_line_id_tbl(i) || G_BUYLINE
|| l_buy_line_index_tbl(i) || G_PROMO_GOODS_DISCOUNT || l_buy_list_line_id_tbl(i)
|| G_PROMO_GOODS_DISCOUNT || nvl(l_buy_updated_flag_tbl(i), G_NO)
|| G_STATUS_UPDATED || l_fg_operand_tbl(i) || l_fg_operator_tbl(i)
|| G_PROMO_GOODS_DISCOUNT
, priced_quantity = nvl(priced_quantity, line_quantity) -- 2970402, 2997007
, priced_uom_code = nvl(priced_uom_code, line_uom_code) -- 2970402, 2997007
WHERE line_index = l_fg_line_index_tbl(i);
FOR cl IN (SELECT line_index, process_status FROM qp_npreq_lines_tmp
WHERE instr(process_status, G_FREEGOOD) > 0)
LOOP
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Freegood line dtl: line_ind '
|| cl.line_index ||' process_sts '|| cl.process_status);
PROCEDURE Update_prg_pricing_status(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
CURSOR l_mark_prg_delete_cur IS
SELECT rltd.line_detail_index, rltd.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
WHERE rltd.pricing_status_code = G_STATUS_NEW
AND rltd.relationship_type_code = G_GENERATED_LINE
AND line.line_index = rltd.related_line_index
AND line.process_status IN (G_NOT_VALID, G_STATUS_DELETED); --bug 3126969
l_mark_prg_delete_index QP_PREQ_GRP.number_type;
l_mark_fgdis_delete_index QP_PREQ_GRP.number_type;
l_routine VARCHAR2(50) := 'QP_PREQ_PUB.Update_prg_pricing_status';
QP_PREQ_GRP.engine_debug('Begin Update_prg_pricing_status');
OPEN l_mark_prg_delete_cur;
l_mark_prg_delete_index.DELETE;
l_mark_fgdis_delete_index.DELETE;
FETCH l_mark_prg_delete_cur BULK COLLECT INTO
l_mark_prg_delete_index, l_mark_fgdis_delete_index;
CLOSE l_mark_prg_delete_cur;
IF l_mark_prg_delete_index.COUNT > 0 THEN
IF l_debug = FND_API.G_TRUE THEN
FOR i IN l_mark_prg_delete_index.FIRST..l_mark_prg_delete_index.LAST
LOOP
BEGIN
SELECT created_from_list_line_id INTO l_prg_list_line_id
FROM qp_npreq_ldets_tmp
WHERE line_detail_index = l_mark_prg_delete_index(i);
QP_PREQ_GRP.engine_debug('Mark delete prg '|| l_mark_prg_delete_index(i)
||' list_line_id '|| l_prg_list_line_id);
QP_PREQ_GRP.engine_debug('Mark delete prg list_line_id -1');
FORALL i IN l_mark_prg_delete_index.FIRST..l_mark_prg_delete_index.LAST
UPDATE qp_npreq_ldets_tmp SET pricing_status_code = G_STATUS_DELETED
WHERE line_detail_index = l_mark_prg_delete_index(i);
END IF; --l_mark_prg_delete_index.COUNT
IF l_mark_fgdis_delete_index.COUNT > 0 THEN
IF l_debug = FND_API.G_TRUE THEN
FOR i IN l_mark_fgdis_delete_index.FIRST..l_mark_fgdis_delete_index.LAST
LOOP
BEGIN
SELECT created_from_list_line_id INTO l_prg_list_line_id
FROM qp_npreq_ldets_tmp
WHERE line_detail_index = l_mark_fgdis_delete_index(i);
QP_PREQ_GRP.engine_debug('Mark delete fgdis '|| l_mark_fgdis_delete_index
(i)
||' list_line_id '|| l_prg_list_line_id);
QP_PREQ_GRP.engine_debug('Mark delete fgdis list_line_id -1');
FORALL i IN l_mark_fgdis_delete_index.FIRST..l_mark_fgdis_delete_index.LAST
UPDATE qp_npreq_ldets_tmp SET pricing_status_code = G_STATUS_DELETED
WHERE line_detail_index = l_mark_fgdis_delete_index(i);
END IF; --l_mark_fgdis_delete_index.count
QP_PREQ_GRP.engine_debug('End Update_prg_pricing_status');
END Update_prg_pricing_status;
SELECT newfgline.line_index new_line_index,
newfgline.line_quantity new_line_quantity,
newfgline.line_uom_code new_line_uom_code,
newfgline.priced_quantity new_priced_quantity,
newfgline.priced_uom_code new_priced_uom_code,
newfgitem.value_from new_item,
newfgline.UOM_QUANTITY new_UOM_QUANTITY,
newfgline.CURRENCY_CODE new_CURRENCY_CODE,
newfgline.UNIT_PRICE new_UNIT_PRICE,
newfgline.PERCENT_PRICE new_PERCENT_PRICE,
newfgline.ADJUSTED_UNIT_PRICE new_ADJUSTED_UNIT_PRICE,
newfgline.PARENT_PRICE new_PARENT_PRICE,
newfgline.PARENT_QUANTITY new_PARENT_QUANTITY,
newfgline.PARENT_UOM_CODE new_PARENT_UOM_CODE,
newfgline.PROCESSING_ORDER new_PROCESSING_ORDER,
newfgline.PROCESSED_FLAG new_PROCESSED_FLAG,
newfgline.PROCESSED_CODE new_PROCESSED_CODE,
newfgline.PRICE_FLAG new_PRICE_FLAG,
newfgline.PRICING_STATUS_CODE new_PRICING_STATUS_CODE,
newfgline.PRICING_STATUS_TEXT new_PRICING_STATUS_TEXT,
newfgline.START_DATE_ACTIVE_FIRST new_START_DATE_ACTIVE_FIRST,
newfgline.ACTIVE_DATE_FIRST_TYPE new_ACTIVE_DATE_FIRST_TYPE,
newfgline.START_DATE_ACTIVE_SECOND new_START_DATE_ACTIVE_SECOND,
newfgline.ACTIVE_DATE_SECOND_TYPE new_ACTIVE_DATE_SECOND_TYPE,
newfgline.GROUP_QUANTITY new_GROUP_QUANTITY,
newfgline.GROUP_AMOUNT new_GROUP_AMOUNT,
newfgline.LINE_AMOUNT new_LINE_AMOUNT,
newfgline.ROUNDING_FLAG new_ROUNDING_FLAG,
newfgline.ROUNDING_FACTOR new_ROUNDING_FACTOR,
newfgline.UPDATED_ADJUSTED_UNIT_PRICE new_upd_ADJUSTED_UNIT_PRICE,
newfgline.PRICE_REQUEST_CODE new_PRICE_REQUEST_CODE,
newfgline.HOLD_CODE new_HOLD_CODE,
newfgline.HOLD_TEXT new_HOLD_TEXT,
newfgline.PRICE_LIST_HEADER_ID new_PRICE_LIST_HEADER_ID,
newfgline.VALIDATED_FLAG new_VALIDATED_FLAG,
newfgline.QUALIFIERS_EXIST_FLAG new_QUALIFIERS_EXIST_FLAG,
newfgline.PRICING_ATTRS_EXIST_FLAG new_PRICING_ATTRS_EXIST_FLAG,
newfgline.PRIMARY_QUALIFIERS_MATCH_FLAG new_PRIMARY_QUAL_MATCH_FLAG,
newfgline.USAGE_PRICING_TYPE new_USAGE_PRICING_TYPE,
newfgline.LINE_CATEGORY new_LINE_CATEGORY,
newfgline.CONTRACT_START_DATE new_CONTRACT_START_DATE,
newfgline.CONTRACT_END_DATE new_CONTRACT_END_DATE,
newfgline.LINE_UNIT_PRICE new_LINE_UNIT_PRICE,
newfgline.SERVICE_DURATION new_SERVICE_DURATION, -- service project
newfgline.SERVICE_PERIOD new_SERVICE_PERIOD, -- service project
oldfreeline.line_index old_line_index,
oldfreeline.line_id old_line_id,
oldfreeline.line_quantity old_line_quantity,
oldfreeline.line_uom_code old_line_uom_code,
oldfreeline.priced_quantity old_priced_quantity,
oldfreeline.priced_uom_code old_priced_uom_code,
oldfreeitem.value_from old_item,
oldfreeline.UOM_QUANTITY old_UOM_QUANTITY,
oldfreeline.CURRENCY_CODE old_CURRENCY_CODE,
oldfreeline.UNIT_PRICE old_UNIT_PRICE,
oldfreeline.PERCENT_PRICE old_PERCENT_PRICE,
oldfreeline.ADJUSTED_UNIT_PRICE old_ADJUSTED_UNIT_PRICE,
oldfreeline.PARENT_PRICE old_PARENT_PRICE,
oldfreeline.PARENT_QUANTITY old_PARENT_QUANTITY,
oldfreeline.PARENT_UOM_CODE old_PARENT_UOM_CODE,
oldfreeline.PROCESSING_ORDER old_PROCESSING_ORDER,
oldfreeline.PROCESSED_FLAG old_PROCESSED_FLAG,
oldfreeline.PROCESSED_CODE old_PROCESSED_CODE,
oldfreeline.PRICE_FLAG old_PRICE_FLAG,
oldfreeline.PRICING_STATUS_CODE old_PRICING_STATUS_CODE,
oldfreeline.PRICING_STATUS_TEXT old_PRICING_STATUS_TEXT,
oldfreeline.START_DATE_ACTIVE_FIRST old_START_DATE_ACTIVE_FIRST,
oldfreeline.ACTIVE_DATE_FIRST_TYPE old_ACTIVE_DATE_FIRST_TYPE,
oldfreeline.START_DATE_ACTIVE_SECOND old_START_DATE_ACTIVE_SECOND,
oldfreeline.ACTIVE_DATE_SECOND_TYPE old_ACTIVE_DATE_SECOND_TYPE,
oldfreeline.GROUP_QUANTITY old_GROUP_QUANTITY,
oldfreeline.GROUP_AMOUNT old_GROUP_AMOUNT,
oldfreeline.LINE_AMOUNT old_LINE_AMOUNT,
oldfreeline.ROUNDING_FLAG old_ROUNDING_FLAG,
oldfreeline.ROUNDING_FACTOR old_ROUNDING_FACTOR,
oldfreeline.UPDATED_ADJUSTED_UNIT_PRICE
old_upd_ADJUSTED_UNIT_PRICE,
oldfreeline.PRICE_REQUEST_CODE old_PRICE_REQUEST_CODE,
oldfreeline.HOLD_CODE old_HOLD_CODE,
oldfreeline.HOLD_TEXT old_HOLD_TEXT,
oldfreeline.PRICE_LIST_HEADER_ID old_PRICE_LIST_HEADER_ID,
oldfreeline.VALIDATED_FLAG old_VALIDATED_FLAG,
oldfreeline.QUALIFIERS_EXIST_FLAG old_QUALIFIERS_EXIST_FLAG,
oldfreeline.PRICING_ATTRS_EXIST_FLAG old_PRICING_ATTRS_EXIST_FLAG,
oldfreeline.PRIMARY_QUALIFIERS_MATCH_FLAG
old_PRIMARY_QUAL_MATCH_FLAG,
oldfreeline.USAGE_PRICING_TYPE old_USAGE_PRICING_TYPE,
oldfreeline.LINE_CATEGORY old_LINE_CATEGORY,
oldfreeline.CONTRACT_START_DATE old_CONTRACT_START_DATE,
oldfreeline.CONTRACT_END_DATE old_CONTRACT_END_DATE,
oldfreeline.LINE_UNIT_PRICE old_LINE_UNIT_PRICE,
oldfreeline.process_status old_list_line_id,
newfgdis.created_from_list_line_id newfgdis_list_line_id,
newfgdis.operand_value || newfgdis.operand_calculation_code newfgdis_operand,
oldfreeline.SERVICE_DURATION old_SERVICE_DURATION, -- service project
oldfreeline.SERVICE_PERIOD old_SERVICE_PERIOD -- service project
FROM qp_npreq_lines_tmp oldfreeline
, qp_npreq_rltd_lines_tmp newrltd
, qp_npreq_ldets_tmp newfgdis
, qp_npreq_lines_tmp newfgline
, qp_npreq_line_attrs_tmp oldfreeitem
, qp_npreq_line_attrs_tmp newfgitem
--where G_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 = G_YES
AND instr(oldfreeline.process_status, G_FREEGOOD) > 0
AND newrltd.pricing_status_code = G_STATUS_NEW
AND instr(oldfreeline.process_status
, G_BUYLINE || newrltd.line_index || G_PROMO_GOODS_DISCOUNT) > 0
AND newfgdis.pricing_status_code = G_STATUS_NEW
AND newfgdis.applied_flag = G_YES
AND instr(oldfreeline.process_status
, G_FREEGOOD || newfgdis.created_from_list_line_id || G_BUYLINE) > 0
AND newfgdis.line_detail_index = newrltd.related_line_detail_index
AND newfgline.line_index = newfgdis.line_index
AND oldfreeitem.line_index = oldfreeline.line_index
AND oldfreeitem.line_detail_index IS NULL
AND oldfreeitem.context = G_ITEM_CONTEXT
AND oldfreeitem.attribute = G_PRIC_ATTRIBUTE1
AND newfgitem.line_index = newfgline.line_index
AND newfgitem.context = G_ITEM_CONTEXT
AND newfgitem.attribute = G_PRIC_ATTRIBUTE1
UNION
SELECT newfgline.line_index new_line_index,
newfgline.line_quantity new_line_quantity,
newfgline.line_uom_code new_line_uom_code,
newfgline.priced_quantity new_priced_quantity,
newfgline.priced_uom_code new_priced_uom_code,
newfgitem.value_from new_item,
newfgline.UOM_QUANTITY new_UOM_QUANTITY,
newfgline.CURRENCY_CODE new_CURRENCY_CODE,
newfgline.UNIT_PRICE new_UNIT_PRICE,
newfgline.PERCENT_PRICE new_PERCENT_PRICE,
newfgline.ADJUSTED_UNIT_PRICE new_ADJUSTED_UNIT_PRICE,
newfgline.PARENT_PRICE new_PARENT_PRICE,
newfgline.PARENT_QUANTITY new_PARENT_QUANTITY,
newfgline.PARENT_UOM_CODE new_PARENT_UOM_CODE,
newfgline.PROCESSING_ORDER new_PROCESSING_ORDER,
newfgline.PROCESSED_FLAG new_PROCESSED_FLAG,
newfgline.PROCESSED_CODE new_PROCESSED_CODE,
newfgline.PRICE_FLAG new_PRICE_FLAG,
newfgline.PRICING_STATUS_CODE new_PRICING_STATUS_CODE,
newfgline.PRICING_STATUS_TEXT new_PRICING_STATUS_TEXT,
newfgline.START_DATE_ACTIVE_FIRST new_START_DATE_ACTIVE_FIRST,
newfgline.ACTIVE_DATE_FIRST_TYPE new_ACTIVE_DATE_FIRST_TYPE,
newfgline.START_DATE_ACTIVE_SECOND new_START_DATE_ACTIVE_SECOND,
newfgline.ACTIVE_DATE_SECOND_TYPE new_ACTIVE_DATE_SECOND_TYPE,
newfgline.GROUP_QUANTITY new_GROUP_QUANTITY,
newfgline.GROUP_AMOUNT new_GROUP_AMOUNT,
newfgline.LINE_AMOUNT new_LINE_AMOUNT,
newfgline.ROUNDING_FLAG new_ROUNDING_FLAG,
newfgline.ROUNDING_FACTOR new_ROUNDING_FACTOR,
newfgline.UPDATED_ADJUSTED_UNIT_PRICE new_upd_ADJUSTED_UNIT_PRICE,
newfgline.PRICE_REQUEST_CODE new_PRICE_REQUEST_CODE,
newfgline.HOLD_CODE new_HOLD_CODE,
newfgline.HOLD_TEXT new_HOLD_TEXT,
newfgline.PRICE_LIST_HEADER_ID new_PRICE_LIST_HEADER_ID,
newfgline.VALIDATED_FLAG new_VALIDATED_FLAG,
newfgline.QUALIFIERS_EXIST_FLAG new_QUALIFIERS_EXIST_FLAG,
newfgline.PRICING_ATTRS_EXIST_FLAG new_PRICING_ATTRS_EXIST_FLAG,
newfgline.PRIMARY_QUALIFIERS_MATCH_FLAG new_PRIMARY_QUAL_MATCH_FLAG,
newfgline.USAGE_PRICING_TYPE new_USAGE_PRICING_TYPE,
newfgline.LINE_CATEGORY new_LINE_CATEGORY,
newfgline.CONTRACT_START_DATE new_CONTRACT_START_DATE,
newfgline.CONTRACT_END_DATE new_CONTRACT_END_DATE,
newfgline.LINE_UNIT_PRICE new_LINE_UNIT_PRICE,
newfgline.SERVICE_DURATION new_SERVICE_DURATION, -- service project
newfgline.SERVICE_PERIOD new_SERVICE_PERIOD, -- service project
oldfreeline.line_index old_line_index,
oldfreeline.line_id old_line_id,
oldfreeline.line_quantity old_line_quantity,
oldfreeline.line_uom_code old_line_uom_code,
oldfreeline.priced_quantity old_priced_quantity,
oldfreeline.priced_uom_code old_priced_uom_code,
oldfreeitem.value_from old_item,
oldfreeline.UOM_QUANTITY old_UOM_QUANTITY,
oldfreeline.CURRENCY_CODE old_CURRENCY_CODE,
oldfreeline.UNIT_PRICE old_UNIT_PRICE,
oldfreeline.PERCENT_PRICE old_PERCENT_PRICE,
oldfreeline.ADJUSTED_UNIT_PRICE old_ADJUSTED_UNIT_PRICE,
oldfreeline.PARENT_PRICE old_PARENT_PRICE,
oldfreeline.PARENT_QUANTITY old_PARENT_QUANTITY,
oldfreeline.PARENT_UOM_CODE old_PARENT_UOM_CODE,
oldfreeline.PROCESSING_ORDER old_PROCESSING_ORDER,
oldfreeline.PROCESSED_FLAG old_PROCESSED_FLAG,
oldfreeline.PROCESSED_CODE old_PROCESSED_CODE,
oldfreeline.PRICE_FLAG old_PRICE_FLAG,
oldfreeline.PRICING_STATUS_CODE old_PRICING_STATUS_CODE,
oldfreeline.PRICING_STATUS_TEXT old_PRICING_STATUS_TEXT,
oldfreeline.START_DATE_ACTIVE_FIRST old_START_DATE_ACTIVE_FIRST,
oldfreeline.ACTIVE_DATE_FIRST_TYPE old_ACTIVE_DATE_FIRST_TYPE,
oldfreeline.START_DATE_ACTIVE_SECOND old_START_DATE_ACTIVE_SECOND,
oldfreeline.ACTIVE_DATE_SECOND_TYPE old_ACTIVE_DATE_SECOND_TYPE,
oldfreeline.GROUP_QUANTITY old_GROUP_QUANTITY,
oldfreeline.GROUP_AMOUNT old_GROUP_AMOUNT,
oldfreeline.LINE_AMOUNT old_LINE_AMOUNT,
oldfreeline.ROUNDING_FLAG old_ROUNDING_FLAG,
oldfreeline.ROUNDING_FACTOR old_ROUNDING_FACTOR,
oldfreeline.UPDATED_ADJUSTED_UNIT_PRICE
old_upd_ADJUSTED_UNIT_PRICE,
oldfreeline.PRICE_REQUEST_CODE old_PRICE_REQUEST_CODE,
oldfreeline.HOLD_CODE old_HOLD_CODE,
oldfreeline.HOLD_TEXT old_HOLD_TEXT,
oldfreeline.PRICE_LIST_HEADER_ID old_PRICE_LIST_HEADER_ID,
oldfreeline.VALIDATED_FLAG old_VALIDATED_FLAG,
oldfreeline.QUALIFIERS_EXIST_FLAG old_QUALIFIERS_EXIST_FLAG,
oldfreeline.PRICING_ATTRS_EXIST_FLAG old_PRICING_ATTRS_EXIST_FLAG,
oldfreeline.PRIMARY_QUALIFIERS_MATCH_FLAG
old_PRIMARY_QUAL_MATCH_FLAG,
oldfreeline.USAGE_PRICING_TYPE old_USAGE_PRICING_TYPE,
oldfreeline.LINE_CATEGORY old_LINE_CATEGORY,
oldfreeline.CONTRACT_START_DATE old_CONTRACT_START_DATE,
oldfreeline.CONTRACT_END_DATE old_CONTRACT_END_DATE,
oldfreeline.LINE_UNIT_PRICE old_LINE_UNIT_PRICE,
oldfreeline.process_status old_list_line_id,
newfgdis.created_from_list_line_id newfgdis_list_line_id,
newfgdis.operand_value || newfgdis.operand_calculation_code newfgdis_operand,
oldfreeline.SERVICE_DURATION old_SERVICE_DURATION, -- service project
oldfreeline.SERVICE_PERIOD old_SERVICE_PERIOD -- service project
FROM qp_npreq_lines_tmp oldfreeline
, qp_npreq_rltd_lines_tmp newrltd
, qp_npreq_ldets_tmp newfgdis
, qp_npreq_lines_tmp newfgline
, qp_npreq_line_attrs_tmp newfgitem
, qp_npreq_line_attrs_tmp oldfreeitem
--WHERE G_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 <> G_YES
AND instr(oldfreeline.process_status, G_FREEGOOD) > 0
AND newfgdis.pricing_status_code = G_STATUS_NEW
AND newfgdis.applied_flag = G_YES
AND instr(oldfreeline.process_status, G_FREEGOOD || newfgdis.created_from_list_line_id || G_BUYLINE) > 0
AND newrltd.pricing_status_code = G_STATUS_NEW
AND newrltd.related_line_index = newfgdis.line_index -- 2970380
AND newrltd.related_line_detail_index = newfgdis.line_detail_index -- 2970380
AND instr(oldfreeline.process_status
, G_BUYLINE || newrltd.line_index || G_PROMO_GOODS_DISCOUNT) > 0
AND newfgline.line_index = newfgdis.line_index
AND newfgline.line_index = newrltd.related_line_index -- 2970380
AND newfgitem.line_detail_index = newfgdis.line_detail_index
AND newfgitem.context = G_ITEM_CONTEXT
AND newfgitem.attribute = G_PRIC_ATTRIBUTE1
AND oldfreeitem.line_index = oldfreeline.line_index
AND oldfreeitem.line_detail_index IS NULL
AND oldfreeitem.context = G_ITEM_CONTEXT
AND oldfreeitem.attribute = G_PRIC_ATTRIBUTE1;
CURSOR l_updated_prg_fg_cur IS
SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N2) */ rltd.related_line_index
FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
--WHERE G_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 = G_YES
AND buyline.line_type_code = G_LINE_LEVEL
AND prg.line_id = buyline.line_id
AND prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
AND prg.updated_flag = G_YES
AND ldet.line_index = buyline.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.created_from_list_line_id = prg.list_line_id
AND ldet.applied_flag = G_YES
AND rltd.line_index = ldet.line_index
AND rltd.line_detail_index = ldet.line_detail_index
AND rltd.pricing_status_code = G_STATUS_NEW
UNION
SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N1) */ rltd.related_line_index
FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
--WHERE G_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 = G_YES
AND buyline.line_type_code = G_ORDER_LEVEL
AND prg.line_id IS NULL
AND prg.header_id = buyline.line_id
AND prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
AND prg.updated_flag = G_YES
AND ldet.line_index = buyline.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.created_from_list_line_id = prg.list_line_id
AND ldet.applied_flag = G_YES
AND rltd.line_index = ldet.line_index
AND rltd.line_detail_index = ldet.line_detail_index
AND rltd.pricing_status_code = G_STATUS_NEW
UNION
-- hint added for 5575718
SELECT /*+ ORDERED */ rltd.related_line_index
FROM qp_npreq_lines_tmp buyline, qp_npreq_ldets_tmp prg,
qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
--WHERE G_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 <> G_YES
AND prg.line_index = buyline.line_index
AND prg.pricing_status_code = G_STATUS_UNCHANGED
AND prg.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
AND prg.updated_flag = G_YES
AND ldet.line_index = buyline.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.created_from_list_line_id = prg.created_from_list_line_id
AND ldet.applied_flag = G_YES
AND rltd.line_index = ldet.line_index
AND rltd.line_detail_index = ldet.line_detail_index
AND rltd.pricing_status_code = G_STATUS_NEW;
l_prg_line_ind_tbl.DELETE;
l_prg_process_sts_tbl.DELETE;
l_prg_line_id_tbl.DELETE;
l_prg_price_flag_tbl.DELETE;
G_prg_unch_calc_price_tbl.DELETE;
G_prg_unch_line_id_tbl.DELETE;
G_prg_unch_line_ind_tbl.DELETE;
G_prg_unch_new_index_tbl.DELETE;
G_prg_unch_line_index_tbl.DELETE;
G_prg_unch_process_sts_tbl.DELETE;
SELECT 'Y' INTO l_Process_PRG
FROM qp_npreq_ldets_tmp
WHERE created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
AND pricing_status_code = G_STATUS_NEW
AND applied_flag = 'Y'
AND ROWNUM = 1;
SELECT 'Y' INTO l_Process_PRG
FROM qp_npreq_lines_tmp line
WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED,
G_STATUS_UPDATED, G_STATUS_NEW, G_STATUS_GSA_VIOLATION, G_STATUS_INVALID_PRICE_LIST)
AND instr(line.process_status, G_FREEGOOD) > 0
AND ROWNUM = 1;
SELECT 'Y' INTO l_Process_PRG
FROM qp_npreq_lines_tmp line
WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED,
G_STATUS_UPDATED, G_STATUS_NEW, G_STATUS_GSA_VIOLATION)
AND EXISTS
(SELECT 'Y' FROM oe_price_adjustments adj
WHERE line.line_type_code = G_LINE_LEVEL
AND adj.line_id = line.line_id
AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
AND adj.updated_flag = G_YES
UNION
SELECT 'Y' FROM oe_price_adjustments adj
WHERE line.line_type_code = G_ORDER_LEVEL
AND adj.header_id = line.line_id
AND adj.line_id IS NULL
AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
AND adj.updated_flag = G_YES
UNION
SELECT 'Y' FROM qp_npreq_ldets_tmp adj
WHERE adj.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
AND adj.pricing_status_code = G_STATUS_UNCHANGED
AND adj.updated_flag = G_YES);
(SELECT adj.list_line_id, adj.updated_flag, line.line_index, --removed index oe_price_adjustments_n1 smbalara 14393164
adj.pricing_phase_id, adj.price_adjustment_id, adj.line_id
FROM qp_npreq_lines_tmp line, oe_price_adjustments adj
WHERE line.line_type_code = G_LINE_LEVEL
AND adj.line_id = line.line_id
AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
UNION
SELECT adj.list_line_id, adj.updated_flag, line.line_index,
adj.pricing_phase_id, adj.price_adjustment_id, adj.line_id
FROM qp_npreq_lines_tmp line, oe_price_adjustments adj
WHERE line.line_type_code = G_ORDER_LEVEL
AND adj.header_id = line.line_id
AND adj.line_id IS NULL
AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT)
LOOP
QP_PREQ_GRP.engine_debug('PRGs check whether update_flag: '
||'prg listlineid '|| cl.list_line_id ||' updatedflag '|| cl.updated_flag
||' buylineid '|| cl.line_id ||' prgphaseid '|| cl.pricing_phase_id);
(SELECT rltd.related_line_index, ldet.created_from_list_line_id
FROM qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
WHERE ldet.line_index = cl.line_index
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.applied_flag = G_YES
AND ldet.created_from_list_line_id = cl.list_line_id
AND rltd.line_index = ldet.line_index
AND rltd.line_detail_index = ldet.line_detail_index)
LOOP
QP_PREQ_GRP.engine_debug('PRGs check whether engine_prg: '
||'engine prg listlineid '|| fg.created_from_list_line_id
||' fg line_index '|| fg.related_line_index);
QP_PREQ_GRP.engine_debug('Updated PRGs No. of passed-in fg lines updated '
|| SQL%ROWCOUNT);
OPEN l_updated_prg_fg_cur;
l_upd_engine_fg_index.DELETE;
FETCH l_updated_prg_fg_cur
BULK COLLECT INTO
l_upd_engine_fg_index; --, l_upd_passedin_fg_index;
CLOSE l_updated_prg_fg_cur;
UPDATE qp_npreq_lines_tmp SET
pricing_status_code = G_NOT_VALID, process_status = G_NOT_VALID
WHERE line_index = l_upd_engine_fg_index(i);
UPDATE qp_npreq_lines_tmp oldfg SET process_status = G_STATUS_UNCHANGED
--fix for bug 2691794
, processed_flag = G_FREEGOOD_LINE
WHERE oldfg.pricing_status_code IN (G_STATUS_UPDATED, G_STATUS_UNCHANGED)
AND instr(oldfg.process_status, G_PROMO_GOODS_DISCOUNT || G_YES || G_STATUS_UPDATED) > 0;
FOR cl IN (SELECT newprg.line_index line_ind
, newprg.created_from_list_line_id
FROM qp_npreq_lines_tmp oldfgline
, qp_npreq_ldets_tmp newprg
WHERE --ldet.line_index = oldfgline.line_index
instr(oldfgline.process_status
, G_BUYLINE || newprg.line_index || G_PROMO_GOODS_DISCOUNT) > 0
AND newprg.applied_flag = G_YES
AND newprg.pricing_status_code = G_STATUS_NEW
AND instr(oldfgline.process_status
, G_PROMO_GOODS_DISCOUNT || newprg.created_from_list_line_id || G_PROMO_GOODS_DISCOUNT) > 0)
LOOP
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('prg fgline: ind '|| cl.line_ind
||' prg '|| cl.created_from_list_line_id);
UPDATE qp_npreq_lines_tmp oldfgline
SET oldfgline.process_status = G_STATUS_DELETED
WHERE NOT EXISTS (SELECT newprg.line_index
FROM qp_npreq_ldets_tmp newprg
--, qp_npreq_line_attrs_tmp newfgitem <-- commented out, sql repos
WHERE --G_REQUEST_TYPE_CODE = 'ONT'
newprg.pricing_status_code = G_STATUS_NEW
AND newprg.applied_flag = G_YES
AND instr(oldfgline.process_status
, G_BUYLINE || newprg.line_index || G_PROMO_GOODS_DISCOUNT) > 0
AND newprg.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
AND instr(oldfgline.process_status
, G_PROMO_GOODS_DISCOUNT || newprg.created_from_list_line_id || G_PROMO_GOODS_DISCOUNT) > 0)
AND instr(oldfgline.process_status, G_FREEGOOD) > 0;
QP_PREQ_GRP.engine_debug('updated delete prg lines rowcnt: '|| SQL%ROWCOUNT);
instr(freegood.old_list_line_id, G_STATUS_UPDATED) + 7),
G_PROMO_GOODS_DISCOUNT, '');
QP_PREQ_GRP.engine_debug('In PRG if data updated ');
l_prg_process_sts_tbl(l_prg_line_ind_tbl.COUNT) := G_STATUS_UPDATED;
QP_PREQ_GRP.engine_debug('passed in frg update '|| l_prg_line_ind_tbl.COUNT);
UPDATE qp_npreq_lines_tmp line SET
line.process_status = l_prg_process_sts_tbl(i)
--pricing_status_code needs to be set to G_NOT_VALID so that
--this does not get into OM's
--update statement to update lines with UPDATE status
, line.pricing_status_code = decode(l_prg_process_sts_tbl(i), G_NOT_VALID
, l_prg_process_sts_tbl(i), line.pricing_status_code)
--fix for bug 2691794
, line.processed_flag = G_FREEGOOD_LINE
, line.line_id = l_prg_line_id_tbl(i)
, line.price_flag = l_prg_price_flag_tbl(i) -- Ravi
WHERE line.line_index = l_prg_line_ind_tbl(i);
UPDATE qp_npreq_lines_tmp SET pricing_status_code = G_NOT_VALID, process_status = G_NOT_VALID
WHERE PROCESSED_CODE = G_BY_ENGINE AND SERVICE_PERIOD IS NOT NULL;
QP_PREQ_GRP.engine_debug('deleted service rows'||SQL%rowcount);
update_prg_pricing_status(x_return_status, x_return_status_text);
PROCEDURE Update_PRG_Process_status(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Update_PRG_Process_status ';
QP_PREQ_GRP.engine_debug('Begin Update_PRG_Process_status');
QP_PREQ_GRP.engine_debug('unchanged prg update '
|| G_prg_unch_line_index_tbl.COUNT);
UPDATE qp_npreq_lines_tmp line SET line.process_status =
--fix for bug 2831270
decode(line.adjusted_unit_price, G_prg_unch_calc_price_tbl(i),
G_NOT_VALID, G_STATUS_UPDATED)
--Calling application looks at pricing_status_code this also needs to be updated
--pricing_status_code needs to be set to G_NOT_VALID
--so that this does not get into OM's
--update statement to update lines with UPDATE status
, line.pricing_status_code = decode(line.adjusted_unit_price,
G_prg_unch_calc_price_tbl(i), G_NOT_VALID, line.pricing_status_code)
, line.line_id = G_prg_unch_line_id_tbl(i)
WHERE line.line_index = G_prg_unch_new_index_tbl(i)
RETURNING line.process_status BULK COLLECT INTO G_prg_unch_process_sts_tbl;
UPDATE qp_npreq_lines_tmp line SET line.process_status =
decode(G_prg_unch_process_sts_tbl(i), G_NOT_VALID
, G_STATUS_UNCHANGED, G_STATUS_UPDATED, G_NOT_VALID)
--pricing_status_code needs to be set
--to G_NOT_VALID so that this does not get into OM's
--update statement to update lines with UPDATE status
, line.pricing_status_code = decode(G_prg_unch_process_sts_tbl(i)
, G_NOT_VALID, line.pricing_status_code, G_STATUS_UPDATED, G_NOT_VALID)
WHERE line.process_status = 'OLD' || G_STATUS_UNCHANGED
AND line.line_index = G_prg_unch_line_index_tbl(i);
update_prg_pricing_status(x_return_status, x_return_status_text);
QP_PREQ_GRP.engine_debug('End Update_PRG_Process_status');
END Update_PRG_Process_status;
SELECT /*+ ORDERED USE_NL(buyline prg dis)*/
dis.created_from_list_line_id, prg.line_detail_index, buyline.price_flag, 'Y' is_ldet, dis.line_index
FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp buyline
, qp_npreq_ldets_tmp prg, qp_npreq_ldets_tmp dis
WHERE rltd.pricing_status_code = G_STATUS_NEW
AND rltd.relationship_type_code = G_GENERATED_LINE
AND buyline.line_index = rltd.line_index
AND prg.line_detail_index = rltd.line_detail_index
AND prg.created_from_list_line_type IN
(G_OTHER_ITEM_DISCOUNT, G_PROMO_GOODS_DISCOUNT)
AND prg.pricing_status_code = G_STATUS_NEW
AND dis.line_detail_index = rltd.related_line_detail_index
AND dis.pricing_status_code = G_STATUS_NEW;
G_BUYLINE_INDEXES_FOR_LINE_ID.DELETE;
FOR c1 IN (SELECT line_detail_index, related_line_detail_index, line_index
FROM qp_npreq_rltd_lines_tmp WHERE pricing_status_code = G_STATUS_NEW
AND relationship_type_code = G_GENERATED_LINE)
LOOP
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('loop linedtl '|| c1.line_detail_index ||' rltddtl '
|| c1.related_line_detail_index ||' lineind '|| c1.line_index);
FOR c2 IN (SELECT line_index, line_detail_index FROM qp_npreq_ldets_tmp
WHERE line_detail_index = c1.line_detail_index
AND pricing_status_code = G_STATUS_NEW
AND created_from_list_line_type IN
(G_OTHER_ITEM_DISCOUNT, G_PROMO_GOODS_DISCOUNT))
LOOP
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('loop ldet linedtl '|| c2.line_detail_index);
UPDATE qp_npreq_ldets_tmp freegood_dis set buy_line_price_flag =
(select buyline.price_flag
from qp_npreq_rltd_lines_tmp rl, qp_npreq_lines_tmp buyline
where rl.pricing_status_code = G_STATUS_NEW
and rl.relationship_type_code = G_GENERATED_LINE
and rl.related_line_detail_index = freegood_dis.line_detail_index
and buyline.line_index = rl.line_index)
where freegood_dis.pricing_status_code = G_STATUS_NEW
and freegood_dis.applied_flag = G_YES
and freegood_dis.created_from_list_line_type in (G_OTHER_ITEM_DISCOUNT,
G_PROMO_GOODS_DISCOUNT)
and freegood_dis.line_detail_index in (select rltd.related_line_detail_index
from qp_npreq_rltd_lines_tmp rltd
where rltd.pricing_status_code = G_STATUS_NEW
and rltd.relationship_type_code = G_GENERATED_LINE
and rltd.line_detail_index = freegood_dis.line_detail_index);
SELECT /*+ index (ldets qp_preq_ldets_tmp_N2) */
created_from_list_line_id
, line_quantity
, line_detail_index
, pricing_phase_id
FROM qp_npreq_ldets_tmp ldets
WHERE created_from_list_line_type = G_COUPON_ISSUE
AND applied_flag = G_YES
/*
--you don't need to match the phase as this will be called after cleanup
--and by the time adjustments w/b picked up from oe_price_adj and ldets
--from the right phases
AND pricing_phase_id in (select ph.pricing_phase_id
from qp_event_phases evt , qp_pricing_phases ph, qp_npreq_lines_tmp line
where ph.pricing_phase_id = evt.pricing_phase_id
and instr(p_pricing_event,evt.pricing_event_code||',') > 0
and line.line_index = ldets.line_index
and (line.price_flag = G_YES
or (line.price_flag = G_PHASE
and ph.freeze_override_flag = G_YES)
*/
-- AND LINE_INDEX = p_line_index
-- AND ASK_FOR_FLAG IN (G_YES,G_NO)
AND pricing_status_code = G_STATUS_NEW
AND process_code = G_STATUS_NEW;
PROCEDURE Update_Recurring_Quantity(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
--[julin/pbperf] tuned to use QP_PREQ_LDETS_TMP_N2
CURSOR l_get_recurring_cur IS
SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_N2) */ ldet.line_detail_index
, lattr.context
, lattr.attribute
, lattr.value_from
, lattr.setup_value_from
, ldet.modifier_level_code
-- , ldet.line_detail_index
, ldet.line_quantity
, ldet.group_quantity
, ldet.group_amount
, ldet.created_from_list_line_type
, ldet.operand_value
, qpl.operand
, ldet.benefit_qty
, ldet.accrual_flag
, qpl.accrual_conversion_rate
, qpl.estim_accrual_rate
-- , qpl.benefit_qty
FROM qp_npreq_ldets_tmp ldet, qp_npreq_line_attrs_tmp lattr, qp_list_lines qpl
WHERE ldet.pricing_phase_id > 1
AND ldet.pricing_status_code = G_STATUS_NEW
AND ldet.line_index > -1
AND ldet.created_from_list_line_type IN ('DIS', 'SUR', 'FREIGHT_CHARGE',
'CIE', 'PBH', 'IUE', 'TSN')
AND ldet.price_break_type_code = G_RECURRING_BREAK
AND nvl(ldet.created_from_list_type_code, 'NULL') NOT IN ('PRL', 'AGR')
AND lattr.line_detail_index = ldet.line_detail_index
AND lattr.context = G_PRIC_VOLUME_CONTEXT
AND qpl.list_line_id = ldet.created_from_list_line_id
AND qpl.automatic_flag = 'N' ; -- this procedure is needed only for manual modifiers since for automatic modifiers will be calculated in QPXGPREB
l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Update_recurring_quantity';
l_recur_dtl_index_tbl.DELETE;
l_recur_tot_benefit_qty_tbl.DELETE;
UPDATE qp_npreq_ldets_tmp recur SET operand_value = -- shu fix 2702384
l_recur_tot_benefit_qty_tbl(i)
, benefit_qty = l_recur_benefit_qty_tbl(i) --Bug 2804053
WHERE recur.line_detail_index = l_recur_dtl_index_tbl(i);
END Update_recurring_quantity;
SELECT LINE_INDEX,
LINE_ID,
LINE_TYPE_CODE,
LINE_QUANTITY,
LINE_UOM_CODE,
LINE_UNIT_PRICE, --shu_latest
PRICED_QUANTITY,
UOM_QUANTITY,
PRICED_UOM_CODE,
CURRENCY_CODE,
UNIT_PRICE,
PERCENT_PRICE,
PARENT_PRICE,
PARENT_QUANTITY,
PARENT_UOM_CODE,
PRICE_FLAG,
ADJUSTED_UNIT_PRICE,
UPDATED_ADJUSTED_UNIT_PRICE,
PROCESSING_ORDER,
PROCESSED_CODE,
PROCESSED_FLAG,
PRICING_STATUS_CODE,
PRICING_STATUS_TEXT,
HOLD_CODE,
HOLD_TEXT,
PRICE_REQUEST_CODE,
PRICING_EFFECTIVE_DATE,
PRICE_LIST_HEADER_ID,
PROCESS_STATUS,
CATCHWEIGHT_QTY,
ACTUAL_ORDER_QUANTITY,
ORDER_UOM_SELLING_PRICE
FROM QP_INT_LINES;
SELECT /*+ ORDERED USE_NL(A B C) l_ldets_cur */
a.LINE_DETAIL_INDEX,
a.LINE_DETAIL_TYPE_CODE,
a.LINE_INDEX,
a.CREATED_FROM_LIST_HEADER_ID LIST_HEADER_ID,
a.CREATED_FROM_LIST_LINE_ID LIST_LINE_ID,
a.CREATED_FROM_LIST_LINE_TYPE LIST_LINE_TYPE_CODE,
a.PRICE_BREAK_TYPE_CODE,
a.LINE_QUANTITY,
a.ADJUSTMENT_AMOUNT,
a.AUTOMATIC_FLAG,
a.PRICING_PHASE_ID,
a.OPERAND_CALCULATION_CODE,
a.OPERAND_VALUE,
a.PRICING_GROUP_SEQUENCE,
a.CREATED_FROM_LIST_TYPE_CODE,
a.APPLIED_FLAG,
a.PRICING_STATUS_CODE,
a.PRICING_STATUS_TEXT,
a.LIMIT_CODE,
a.LIMIT_TEXT,
a.LIST_LINE_NO,
a.GROUP_QUANTITY,
a.UPDATED_FLAG,
a.PROCESS_CODE,
a.CALCULATION_CODE,
a.PRICE_ADJUSTMENT_ID,
b.SUBSTITUTION_VALUE SUBSTITUTION_VALUE_TO,
b.SUBSTITUTION_ATTRIBUTE,
b.ACCRUAL_FLAG,
a.modifier_level_code,
b.ESTIM_GL_VALUE,
b.ACCRUAL_CONVERSION_RATE,
--Pass throuh components
b.OVERRIDE_FLAG,
b.PRINT_ON_INVOICE_FLAG,
b.INVENTORY_ITEM_ID,
b.ORGANIZATION_ID,
b.RELATED_ITEM_ID,
b.RELATIONSHIP_TYPE_ID,
b.ESTIM_ACCRUAL_RATE,
b.EXPIRATION_DATE,
b.BENEFIT_PRICE_LIST_LINE_ID,
b.RECURRING_FLAG,
b.BENEFIT_LIMIT,
b.CHARGE_TYPE_CODE,
b.CHARGE_SUBTYPE_CODE,
a.BENEFIT_QTY, --bug 2804053
b.BENEFIT_UOM_CODE,
b.PRORATION_TYPE_CODE,
b.INCLUDE_ON_RETURNS_FLAG,
b.REBATE_TRANSACTION_TYPE_CODE,
b.NUMBER_EXPIRATION_PERIODS,
b.EXPIRATION_PERIOD_UOM,
b.COMMENTS,
a.ORDER_QTY_OPERAND,
a.ORDER_QTY_ADJ_AMT
FROM QP_INT_LDETS a,
QP_LIST_LINES b
WHERE a.line_index = p_line_index
AND a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
QP_PREQ_GRP.engine_debug('UPDATED_FLAG :='|| l_dets.UPDATED_FLAG);
SELECT LINE_INDEX,
LINE_ID,
LINE_TYPE_CODE,
LINE_QUANTITY,
LINE_UOM_CODE,
LINE_UNIT_PRICE, --shu_latest
PRICED_QUANTITY,
UOM_QUANTITY,
PRICED_UOM_CODE,
CURRENCY_CODE,
UNIT_PRICE,
PERCENT_PRICE,
PARENT_PRICE,
PARENT_QUANTITY,
PARENT_UOM_CODE,
PRICE_FLAG,
ADJUSTED_UNIT_PRICE,
UPDATED_ADJUSTED_UNIT_PRICE,
PROCESSING_ORDER,
PROCESSED_CODE,
PROCESSED_FLAG,
PRICING_STATUS_CODE,
PRICING_STATUS_TEXT,
HOLD_CODE,
HOLD_TEXT,
PRICE_REQUEST_CODE,
PRICING_EFFECTIVE_DATE,
PRICE_LIST_HEADER_ID,
PROCESS_STATUS,
CATCHWEIGHT_QTY,
ACTUAL_ORDER_QUANTITY,
ORDER_UOM_SELLING_PRICE,
SERVICE_DURATION,
SERVICE_PERIOD
FROM qp_npreq_lines_tmp;
SELECT /*+ ORDERED USE_NL(A B C) l_ldets_cur */
a.LINE_DETAIL_INDEX,
a.LINE_DETAIL_TYPE_CODE,
a.LINE_INDEX,
a.CREATED_FROM_LIST_HEADER_ID LIST_HEADER_ID,
a.CREATED_FROM_LIST_LINE_ID LIST_LINE_ID,
a.CREATED_FROM_LIST_LINE_TYPE LIST_LINE_TYPE_CODE,
a.PRICE_BREAK_TYPE_CODE,
a.LINE_QUANTITY,
a.ADJUSTMENT_AMOUNT,
a.AUTOMATIC_FLAG,
a.PRICING_PHASE_ID,
a.OPERAND_CALCULATION_CODE,
a.OPERAND_VALUE,
a.PRICING_GROUP_SEQUENCE,
a.CREATED_FROM_LIST_TYPE_CODE,
a.APPLIED_FLAG,
a.PRICING_STATUS_CODE,
a.PRICING_STATUS_TEXT,
a.LIMIT_CODE,
a.LIMIT_TEXT,
a.LIST_LINE_NO,
a.GROUP_QUANTITY,
a.UPDATED_FLAG,
a.PROCESS_CODE,
a.CALCULATION_CODE,
a.PRICE_ADJUSTMENT_ID,
b.SUBSTITUTION_VALUE SUBSTITUTION_VALUE_TO,
b.SUBSTITUTION_ATTRIBUTE,
b.ACCRUAL_FLAG,
a.modifier_level_code,
b.ESTIM_GL_VALUE,
b.ACCRUAL_CONVERSION_RATE,
--Pass throuh components
b.OVERRIDE_FLAG,
b.PRINT_ON_INVOICE_FLAG,
b.INVENTORY_ITEM_ID,
b.ORGANIZATION_ID,
b.RELATED_ITEM_ID,
b.RELATIONSHIP_TYPE_ID,
b.ESTIM_ACCRUAL_RATE,
b.EXPIRATION_DATE,
b.BENEFIT_PRICE_LIST_LINE_ID,
b.RECURRING_FLAG,
b.BENEFIT_LIMIT,
b.CHARGE_TYPE_CODE,
b.CHARGE_SUBTYPE_CODE,
a.BENEFIT_QTY, --bug 2804053
b.BENEFIT_UOM_CODE,
b.SERVICE_DURATION, -- service project
b.SERVICE_PERIOD, -- service project
b.PRORATION_TYPE_CODE,
b.INCLUDE_ON_RETURNS_FLAG,
b.REBATE_TRANSACTION_TYPE_CODE,
b.NUMBER_EXPIRATION_PERIODS,
b.EXPIRATION_PERIOD_UOM,
b.COMMENTS,
a.ORDER_QTY_OPERAND,
a.ORDER_QTY_ADJ_AMT
FROM qp_npreq_ldets_tmp a,
QP_LIST_LINES b
WHERE a.line_index = p_line_index
AND a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
QP_PREQ_GRP.engine_debug('UPDATED_FLAG :='|| l_dets.UPDATED_FLAG);
SELECT ldet.created_from_list_line_id
, line.line_index line_ind
, line.price_flag
, line.process_status
, ldet.applied_flag
, ldet.pricing_status_code
, ldet.line_detail_index
, ldet.process_code
, ldet.automatic_flag
, ldet.override_flag
, line.unit_price
, line.adjusted_unit_price
, ldet.updated_flag
, ldet.calculation_code
, line.qualifiers_exist_flag
, ldet.pricing_group_sequence bucket
FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
WHERE line.line_index = ldet.line_index
ORDER BY line_ind;
SELECT 'X'
FROM
QP_LIST_LINES A
WHERE A.PRICING_PHASE_ID >1
AND modifier_level_code IN ('LINE', 'LINEGROUP', 'ORDER')
AND ROWNUM = 1;
IF nvl(p_control_rec.temp_table_insert_flag, G_YES) = G_NO
THEN
-- temp table/direct insert path
select count(*) into l_no_of_lines
from qp_npreq_lines_tmp;
SELECT VALUE INTO l_pricing_start_redo
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.name = 'redo size';
AND nvl(p_control_rec.temp_table_insert_flag, G_YES) = G_YES
AND p_control_rec.rounding_flag IS NULL THEN
G_ROUND_INDIVIDUAL_ADJ := nvl(G_ROUNDING_OPTIONS, G_NO_ROUND);
ELSIF nvl(p_control_rec.rounding_flag, G_YES) = 'P' --[prarasto:Post Round] added condition to update
--G_ROUND_INDIVIDUAL_ADJ for post rounding
THEN
G_ROUND_INDIVIDUAL_ADJ := G_POST_ROUND;
QP_PREQ_GRP.ENGINE_DEBUG('BEGIN PRICE REQUEST PUB: temp table insert flag '|| p_control_rec.temp_table_insert_flag);
l_control_rec.temp_table_insert_flag :=
nvl(p_control_rec.temp_table_insert_flag, G_YES);
AND l_control_rec.temp_table_insert_flag = G_YES
AND G_REQUEST_TYPE_CODE = 'ASO' THEN
l_control_rec.rounding_flag := G_ROUNDING_PROFILE;
IF l_control_rec.temp_table_insert_flag = G_NO THEN
--removing this for bug 2830206 as calculate_flag can be 'Y', 'C' or 'N'.
--l_control_rec.calculate_flag := G_SEARCH_ONLY;
SELECT VALUE INTO l_old_dynamic_sampling_level
FROM v$parameter
WHERE name = 'optimizer_dynamic_sampling';
||' process_code '|| lrec1.process_code ||' updated '|| lrec1.updated_flag
||' calc_code '|| lrec1.calculation_code ||' qualex '
|| lrec1.qualifiers_exist_flag ||
' bucket '|| nvl(lrec1.bucket, - 1));
IF nvl(l_control_rec.temp_table_insert_flag, G_YES) = G_YES
AND nvl(l_control_rec.check_cust_view_flag, G_NO) = G_NO
AND nvl(Call_Usage_Pricing, G_NO) = G_YES
THEN
--rounding of list price will be taken care of in Calculate_price_pub
Usage_pricing(l_control_rec.rounding_flag,
x_return_status, x_return_status_text);
Update_Qualifier_Value(x_return_status, x_return_status_text);
AND nvl(l_control_rec.temp_table_insert_flag, G_YES) = G_YES
THEN
--call calculate portion only if calculate flag is calculate or
--calculate and search and overridden adjustments exist
Process_Adjustments(l_CONTROL_REC.PRICING_EVENT
, x_return_status
, x_return_status_text);
AND nvl(l_control_rec.temp_table_insert_flag, G_YES) = G_YES
THEN
--call calculate portion only if calculate flag is calculate or
--calculate and search and overridden adjustments exist
Calculate_price(nvl(l_control_rec.rounding_flag, G_YES)
, x_return_status
, x_return_status_text);
Update_Unit_Price (x_return_status
, x_return_status_text);
QP_PREQ_GRP.ENGINE_DEBUG('Exception Update_Unit_Price: '|| x_return_status_text);
AND nvl(l_control_rec.temp_table_insert_flag, G_YES) = G_NO
AND G_NO_ADJ_PROCESSING = G_YES -- Added for 3169430
THEN
--before doing calculation we need to populate g_buyline_price_flag
Populate_buy_line_price_flag(x_return_status, x_return_status_text);
Update_recurring_quantity(x_return_status, x_return_status_text);
QP_PREQ_GRP.ENGINE_DEBUG('Exception Update_recurring_quantity: '
|| x_return_status_text);
Update_Adj_orderqty_cols(x_return_status, x_return_status_text);
QP_PREQ_GRP.ENGINE_DEBUG('Exception Update_Adj_orderqty_cols: '
|| x_return_status_text);
QP_PREQ_GRP.ENGINE_DEBUG('Exception Update_PRG_Process_status: '
|| x_return_status_text);
UPDATE qp_npreq_ldets_tmp
SET pricing_status_code = G_STATUS_NEW
WHERE process_code = G_STATUS_NEW
--changes for bug 2264566
--changed to populate w/updated_flag = Y for child lines of manualPBH
AND (applied_flag = G_YES OR updated_flag = G_YES)
AND pricing_status_code = G_STATUS_UNCHANGED;
FOR cl IN (SELECT line_index,
related_line_index,
line_detail_index,
related_line_detail_index,
relationship_type_code,
list_line_id,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
pricing_group_sequence,
setup_value_from,
setup_value_to,
qualifier_value
FROM qp_npreq_rltd_lines_tmp
WHERE pricing_status_code = G_STATUS_NEW)
LOOP
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('related records with status N '
||' line_dtl_index '|| cl.line_detail_index
||' rltd_line_dtl_index '|| cl.related_line_detail_index
||' line_index '|| cl.line_index
||' rltd_line_index '|| cl.related_line_index
||' list_line_id '|| cl.list_line_id
||' rltd_list_line_id '|| cl.related_list_line_id
||' rltd_list_line_type '|| cl.related_list_line_type
||' operand '|| cl.operand
||' operator '|| cl.operand_calculation_code
||' bucket '|| cl.pricing_group_sequence
||' setval_from '|| cl.setup_value_from
||' setval_to '|| cl.setup_value_to
||' qual_value '|| cl.qualifier_value);
QP_PREQ_GRP.ENGINE_DEBUG('temp_table_insert_flag:' || l_control_rec.temp_table_insert_flag);
IF l_control_rec.temp_table_insert_flag = G_NO THEN
--Initialize_Constants() logic for Interface Tables
-- IF G_REQUEST_TYPE_CODE = 'ONT' THEN
--bug 3085453 handle pricing availability UI
-- they pass reqtype ONT and insert adj into ldets
IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES THEN
--Preinsert Logic for OM call
--to fetch out-of-phases modifiers and in-phase PRG modifiers
--to fetch rltd information
IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
QP_PREQ_GRP.ENGINE_DEBUG('Before calling INT_TABLES_PREINSERT');
INT_TABLES_PREINSERT(p_calculate_flag => l_control_rec.calculate_flag,
p_event_code => l_control_rec.pricing_event,
x_return_status => l_return_status,
x_return_status_text => l_status_Text);
QP_PREQ_GRP.ENGINE_DEBUG('Existing INT_TABLES_PREINSERT');
l_status_text := 'INT_TABLES_PREINSERT:' || l_status_text;
IF l_control_rec.temp_table_insert_flag = G_NO THEN
l_request_id := QP_Price_Request_Context.GET_REQUEST_ID;
END IF; --TEMP_TABLE_INSERT_FLAG=Y
DELETE FROM qp_int_rltd_lines
WHERE pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
AND relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
AND pricing_status_text = 'INSERTED FOR CALCULATION';
QP_PREQ_GRP.engine_debug('deleted related lines '|| SQL%ROWCOUNT);
Update_Line_Status(x_return_status, x_return_status_text);
IF l_control_rec.temp_table_insert_flag = G_YES
AND l_control_rec.check_cust_view_flag = G_NO
THEN
--added by yangli for Java Engine PUB 3086881
IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N' THEN
------------------------------------------------------------
--POPULATE OUT PROCESS CODE IN PRICING_STATUS_CODE
--ONLY UPDATE THE RECORDS INPUT BY USER WHCH WERE USED IN CALCULATION
------------------------------------------------------------
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.ENGINE_DEBUG('Java Engine not Installed ----------');
QP_PREQ_GRP.ENGINE_DEBUG('Update Process code to Pricing Code ');
UPDATE qp_npreq_ldets_tmp
SET pricing_status_code = G_STATUS_NEW
WHERE process_code = G_STATUS_NEW
--changes for bug 2264566
--changed to populate w/updated_flag = Y for child lines of manualPBH
AND (applied_flag = G_YES OR updated_flag = G_YES)
AND pricing_status_code = G_STATUS_UNCHANGED;
' up adj price '|| x_line_tbl(j).updated_adjusted_unit_price ||
' process code '|| x_line_tbl(j).processed_code);
||' updated '|| x_line_detail_tbl(i).updated_flag
||' process_code '|| x_line_detail_tbl(i).process_code
||' pricing_status '|| x_line_detail_tbl(i).status_code);
END IF; --temp_table_insert_flag = G_YES and check_cust_view_flag = G_NO
SELECT VALUE INTO l_pricing_end_redo
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.name = 'redo size';
execute immediate 'select '||''''|| ' Total Time in QP_PREQ_PUB(in sec) : ' ||
l_time_difference||' Total redo in QP_PREQ_PUB : '|| l_redo_difference||''''||' from dual ';
select 'Total Time in QP_PREQ_PUB(in sec) : ' ||l_time_difference ||
' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
l_time_difference ||' Total redo in QP_PREQ_PUB : '|| l_redo_difference || '''' ||' from dual ';
SELECT NVL(gsa_indicator, 'N')
INTO l_gsa_indicator_flag
FROM hz_cust_site_uses_all hsu
WHERE site_use_id = OE_ORDER_PUB.G_HDR.invoice_to_org_id
AND NVL(hsu.org_id,
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'), 1, 1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))), - 99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'), 1, 1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))), - 99);
SELECT NVL(gsa_indicator_flag, 'N')
INTO l_gsa_indicator_flag
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hca.cust_account_id = p_cust_account_id ;
/* SELECT nvl(gsa_indicator_flag,'N') into l_gsa_indicator_flag
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id and hca.cust_account_id = p_cust_account_id;
SELECT MIN(qpll.operand) INTO l_operand
FROM
QP_QUALIFIERS qpq,
QP_PRICING_ATTRIBUTES qppa,
QP_LIST_LINES qpll,
QP_LIST_HEADERS_B qplh,
QP_PRICE_REQ_SOURCES_V qpprs
WHERE
qpq.qualifier_context = 'CUSTOMER' AND
qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE15' AND
qpq.qualifier_attr_value = 'Y' AND
qppa.list_header_id = qplh.list_header_id AND
qplh.active_flag = 'Y' AND
qpprs.request_type_code = p_request_type_code AND
qpprs.source_system_code = qplh.source_system_code AND
qppa.pricing_phase_id = 2 AND
qppa.qualification_ind = 6 AND
qppa.product_attribute_context = 'ITEM' AND
qppa.product_attribute = 'PRICING_ATTRIBUTE1' AND
qppa.product_attr_value = p_inventory_item_id AND
qppa.excluder_flag = 'N' AND
qppa.list_header_id = qpq.list_header_id AND
qppa.list_line_id = qpll.list_line_id AND
p_pricing_date BETWEEN nvl(trunc(qplh.start_date_active), p_pricing_date) AND nvl(trunc(qplh.end_date_active), p_pricing_date);
SELECT line_unit_price,
order_uom_selling_price,
pricing_status_code,
pricing_status_text
INTO x_line_unit_price,
x_adjusted_unit_price,
x_pricing_status_code,
x_pricing_status_text
FROM QP_NPREQ_LINES_TMP
WHERE line_index = p_line_index;
SELECT line_unit_price,
order_uom_selling_price,
pricing_status_code,
pricing_status_text
INTO x_line_unit_price,
x_adjusted_unit_price,
x_pricing_status_code,
x_pricing_status_text
FROM QP_NPREQ_LINES_TMP
WHERE line_id = p_line_id;
SELECT line_unit_price,
order_uom_selling_price,
pricing_status_code,
pricing_status_text
INTO x_line_unit_price,
x_adjusted_unit_price,
x_pricing_status_code,
x_pricing_status_text
FROM QP_INT_LINES
WHERE line_index = p_line_index;
SELECT line_unit_price,
order_uom_selling_price,
pricing_status_code,
pricing_status_text
INTO x_line_unit_price,
x_adjusted_unit_price,
x_pricing_status_code,
x_pricing_status_text
FROM QP_INT_LINES
WHERE line_id = p_line_id;
IF x_pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_UNCHANGED) THEN
x_return_status := FND_API.G_RET_STS_SUCCESS;