[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Utility Function to Update Limit Balance and the adjustment on the
ldets table(s).
***********************************************************************/
l_debug VARCHAR2(3);
select name into l_modifier_name from qp_list_headers_vl
where list_header_id = p_List_Header_Id;
select list_line_no into l_list_line_no from qp_list_lines
where list_line_id = p_List_Line_Id;
select limit_number into l_limit_number from qp_limits
where limit_id = p_Limit_Id;
select meaning into l_operator_name from qp_lookups
where LOOKUP_TYPE = 'ARITHMETIC_OPERATOR' and lookup_code = p_operand_calculation_code;
FUNCTION Update_Balance (x_return_text OUT NOCOPY VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
QP_PREQ_GRP.engine_debug('***Begin Update_Balance***');
g_limit_balance_line.DELETE; -- No need to process balances further
IF g_limit_balance_line(i).process_action = g_update THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Update Required');
INDX,qp_limit_balance_check_pvt.update_balance.upd1,QP_LIMIT_BALANCES_U1,LIMIT_BALANCE_ID,1
*/
--sql statement upd1
-- 9938422 limit available amount becomes zero when it is less than 0.04 with
/* UPDATE qp_limit_balances
SET available_amount = round(available_amount,2) - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0),*/
UPDATE qp_limit_balances
SET available_amount = DECODE (ROUND(round(available_amount,2) - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0),1),0,0,round(available_amount,2) - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE round(available_amount,2) >=
DECODE(g_limit_balance_line(i).limit_exceed_action_code,
'HARD', l_given_amount -
nvl(g_limit_balance_line(i).transaction_amount, 0),
-999999999999999999999999999
)
AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
INDX,qp_limit_balance_check_pvt.update_balance.upd2,QP_LIMIT_BALANCES_U1,LIMIT_BALANCE_ID,1
*/
--sql statement upd2
-- 9938422 limit available amount becomes zero when it is less than 0.04 with
UPDATE qp_limit_balances
SET available_amount = DECODE (ROUND(round(available_amount,2) - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0),1),0,0,round(available_amount,2) - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE round(available_amount,2) >=
DECODE(g_limit_balance_line(i).limit_exceed_action_code,
'HARD',l_given_amount -
nvl(g_limit_balance_line(i).transaction_amount, 0),
-99999999999999999999999999999
)
AND limit_balance_id = g_limit_balance_line(i).limit_balance_id
AND price_request_code =
g_limit_balance_line(i).bal_price_request_code;
/* UPDATE qp_limit_balances
SET available_amount = round(available_amount,2) - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE round(available_amount,2) >=
DECODE(g_limit_balance_line(i).limit_exceed_action_code,
'HARD',l_given_amount -
nvl(g_limit_balance_line(i).transaction_amount, 0),
-99999999999999999999999999999
)
AND limit_balance_id = g_limit_balance_line(i).limit_balance_id
AND price_request_code =
g_limit_balance_line(i).bal_price_request_code; */
ELSIF g_limit_balance_line(i).process_action = g_insert THEN
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('Insert Required');
INSERT INTO qp_limit_balances
(limit_id,
limit_balance_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
available_amount,
reserved_amount,
consumed_amount,
organization_attr_context,
organization_attribute,
organization_attr_value,
multival_attr1_context,
multival_attribute1,
multival_attr1_value,
multival_attr1_type,
multival_attr1_datatype,
multival_attr2_context,
multival_attribute2,
multival_attr2_value,
multival_attr2_type,
multival_attr2_datatype,
price_request_code
)
VALUES
(g_limit_balance_line(i).limit_id,
g_limit_balance_line(i).limit_balance_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
DECODE(ROUND(g_limit_balance_line(i).limit_amount - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0),1),0,0,g_limit_balance_line(i).limit_amount - l_given_amount
+ nvl(g_limit_balance_line(i).transaction_amount, 0)),
0,
0,
g_limit_balance_line(i).organization_attr_context,
g_limit_balance_line(i).organization_attribute,
g_limit_balance_line(i).organization_attr_value,
g_limit_balance_line(i).multival_attr1_context,
g_limit_balance_line(i).multival_attribute1,
g_limit_balance_line(i).multival_attr1_value,
g_limit_balance_line(i).multival_attr1_type,
g_limit_balance_line(i).multival_attr1_datatype,
g_limit_balance_line(i).multival_attr2_context,
g_limit_balance_line(i).multival_attribute2,
g_limit_balance_line(i).multival_attr2_value,
g_limit_balance_line(i).multival_attr2_type,
g_limit_balance_line(i).multival_attr2_datatype,
g_limit_balance_line(i).bal_price_request_code
);
QP_PREQ_GRP.engine_debug('***End Update_Balance***');
QP_PREQ_GRP.engine_debug('Insert Failed with Duplicate Value on Index');
x_return_text := 'Insert Failed with Duplicate Value on Index error ' ||
'in procedure Update_Balance';
QP_PREQ_GRP.engine_debug('Limit Balance no longer available.Update Failed.');
x_return_text := 'Update Failed in procedure Update_Balance because ' ||
'Limit Balance no longer available';
QP_PREQ_GRP.engine_debug('Other Exception in Update_Balance');
END Update_Balance;
limit_balance table. Called after update_balance fails the first time.
***********************************************************************/
FUNCTION Recheck_Balance
RETURN BOOLEAN
IS
l_full_available_amount NUMBER := 0;
SELECT available_amount
INTO l_full_available_amount
FROM qp_limit_balances
WHERE limit_id = g_limit_balance_line(i).limit_id;
SELECT available_amount
INTO l_full_available_amount
FROM qp_limit_balances
WHERE limit_id = g_limit_balance_line(i).limit_id
AND price_request_code =
g_limit_balance_line(i).bal_price_request_code;
g_limit_balance_line(i).process_action := g_insert;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
END;--End of Block around Select Stmt when limit does not have each attrs
SELECT available_amount
INTO l_full_available_amount
FROM qp_limit_balances
WHERE limit_balance_id = g_limit_balance_line(i).limit_balance_id;
SELECT available_amount
INTO l_full_available_amount
FROM qp_limit_balances
WHERE limit_id = g_limit_balance_line(i).limit_id
AND price_request_code =
g_limit_balance_line(i).bal_price_request_code;
g_limit_balance_line(i).process_action := g_insert;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
END;--End of Block around Select Stmt when limit has each attrs
SELECT amount
INTO g_limit_balance_line(i).transaction_amount
FROM qp_limit_transactions
WHERE price_request_code = g_limit_balance_line(i).price_request_code
AND list_header_id = g_limit_balance_line(i).list_header_id
AND list_line_id = g_limit_balance_line(i).list_line_id
AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
SELECT context, attribute, value_from, attribute_type, datatype
FROM qp_npreq_line_attrs_tmp
WHERE line_index = a_line_index
AND context = a_context
AND attribute = a_attribute
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
SELECT available_amount, limit_balance_id
INTO l_full_available_amount, g_limit_balance_line(i).limit_balance_id
FROM qp_limit_balances
WHERE limit_id = p_limit_rec.limit_id;
SELECT available_amount, limit_balance_id
INTO l_full_available_amount, g_limit_balance_line(i).limit_balance_id
FROM qp_limit_balances
WHERE limit_id = p_limit_rec.limit_id
AND price_request_code =
g_limit_balance_line(i).bal_price_request_code;
g_limit_balance_line(i).process_action := g_update;
select qp_limit_balances_s.nextval
into g_limit_balance_line(i).limit_balance_id from dual;
g_limit_balance_line(i).process_action := g_insert;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
END;--Block around select stmt when no each attr exists
SELECT amount
INTO g_limit_balance_line(i).transaction_amount
FROM qp_limit_transactions
WHERE price_request_code = p_limit_rec.price_request_code
AND list_header_id = p_limit_rec.created_from_list_header_id
AND list_line_id = p_limit_rec.created_from_list_line_id
AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
SELECT available_amount, limit_balance_id,
organization_attr_context, organization_attribute,
organization_attr_value,
multival_attr1_context, multival_attribute1,
multival_attr1_value, multival_attr1_type,
multival_attr1_datatype,
multival_attr2_context, multival_attribute2,
multival_attr2_value, multival_attr2_type,
multival_attr2_datatype
INTO l_full_available_amount,
g_limit_balance_line(i).limit_balance_id,
g_limit_balance_line(i).organization_attr_context,
g_limit_balance_line(i).organization_attribute,
g_limit_balance_line(i).organization_attr_value,
g_limit_balance_line(i).multival_attr1_context,
g_limit_balance_line(i).multival_attribute1,
g_limit_balance_line(i).multival_attr1_value,
g_limit_balance_line(i).multival_attr1_type,
g_limit_balance_line(i).multival_attr1_datatype,
g_limit_balance_line(i).multival_attr2_context,
g_limit_balance_line(i).multival_attribute2,
g_limit_balance_line(i).multival_attr2_value,
g_limit_balance_line(i).multival_attr2_type,
g_limit_balance_line(i).multival_attr2_datatype
FROM qp_limit_balances
WHERE limit_id = p_limit_rec.limit_id
AND organization_attr_context = l_org_table(j).context
AND organization_attribute = l_org_table(j).attribute
AND organization_attr_value = l_org_table(j).value
AND multival_attr1_context = l_cust_table(k).context
AND multival_attribute1 = l_cust_table(k).attribute
AND multival_attr1_value = l_cust_table(k).value
AND multival_attr2_context = l_item_table(m).context
AND multival_attribute2 = l_item_table(m).attribute
AND multival_attr2_value = l_item_table(m).value;
SELECT available_amount, limit_balance_id,
organization_attr_context, organization_attribute,
organization_attr_value,
multival_attr1_context, multival_attribute1,
multival_attr1_value, multival_attr1_type,
multival_attr1_datatype,
multival_attr2_context, multival_attribute2,
multival_attr2_value, multival_attr2_type,
multival_attr2_datatype
INTO l_full_available_amount,
g_limit_balance_line(i).limit_balance_id,
g_limit_balance_line(i).organization_attr_context,
g_limit_balance_line(i).organization_attribute,
g_limit_balance_line(i).organization_attr_value,
g_limit_balance_line(i).multival_attr1_context,
g_limit_balance_line(i).multival_attribute1,
g_limit_balance_line(i).multival_attr1_value,
g_limit_balance_line(i).multival_attr1_type,
g_limit_balance_line(i).multival_attr1_datatype,
g_limit_balance_line(i).multival_attr2_context,
g_limit_balance_line(i).multival_attribute2,
g_limit_balance_line(i).multival_attr2_value,
g_limit_balance_line(i).multival_attr2_type,
g_limit_balance_line(i).multival_attr2_datatype
FROM qp_limit_balances
WHERE limit_id = p_limit_rec.limit_id
AND organization_attr_context = l_org_table(j).context
AND organization_attribute = l_org_table(j).attribute
AND organization_attr_value = l_org_table(j).value
AND multival_attr1_context = l_cust_table(k).context
AND multival_attribute1 = l_cust_table(k).attribute
AND multival_attr1_value = l_cust_table(k).value
AND multival_attr2_context = l_item_table(m).context
AND multival_attribute2 = l_item_table(m).attribute
AND multival_attr2_value = l_item_table(m).value
AND price_request_code =
g_limit_balance_line(i).bal_price_request_code;
g_limit_balance_line(i).process_action := g_update;
select qp_limit_balances_s.nextval
into g_limit_balance_line(i).limit_balance_id from dual;
g_limit_balance_line(i).process_action := g_insert;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code =
QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
UPDATE qp_npreq_lines_tmp
SET pricing_status_text = l_message,
pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_text = l_message,
pricing_status_code =
QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
WHERE line_index = g_limit_balance_line(i).line_index
AND created_from_list_line_id =
g_limit_balance_line(i).list_line_id;
END;--Block around select stmt when no each attr exists
SELECT amount
INTO g_limit_balance_line(i).transaction_amount
FROM qp_limit_transactions
WHERE price_request_code = p_limit_rec.price_request_code
AND list_header_id = p_limit_rec.created_from_list_header_id
AND list_line_id = p_limit_rec.created_from_list_line_id
AND limit_balance_id = g_limit_balance_line(i).limit_balance_id;
select MODIFIER_LEVEL_CODE into l_modifier_level_code
from qp_list_lines where list_line_id = p_limit_rec.created_from_list_line_id;
select 'Y' into l_amt_exist_flag
from qp_pricing_attributes
where list_line_id = p_limit_rec.created_from_list_line_id
AND pricing_attribute_context = 'VOLUME'
AND pricing_attribute = 'PRICING_ATTRIBUTE12';
update the request line.
***************************************************************************/
PROCEDURE Process_Limits(x_return_status OUT NOCOPY VARCHAR2,
x_return_text OUT NOCOPY VARCHAR2)
IS
/*
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,2
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,QP_LIMITS_N1,LIST_HEADER_ID,1
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,QP_LIMITS_N1,LIST_LINE_ID,2
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,QP_LIMIT_ATTRIBUTES_N1,LIMIT_ID,1
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,5
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,6
INDX,qp_limit_balance_check_pvt.process_limits.limits_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
*/
CURSOR limits_cur
IS
--Statement to select line-level limits for pure Non-each and mixed cases
SELECT /*+ ordered use_nl (l a rl q) index(rl qp_preq_line_attrs_tmp_N2) */
r.line_index, r.created_from_list_header_id,
r.created_from_list_line_id, 'L' limit_level, l.limit_id,
l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
l.limit_level_code, r.adjustment_amount, r.benefit_qty,
r.created_from_list_line_type, r.pricing_group_sequence,
r.operand_calculation_code, q.price_request_code,
q.request_type_code, q.line_category,
r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
decode(l.organization_flag,
'Y','PARTY','NA') organization_attr_context,
decode(l.organization_flag,
'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
nvl(l.multival_attr1_context,'NA') multival_attr1_context,
nvl(l.multival_attribute1,'NA') multival_attribute1,
nvl(l.multival_attr1_type,'NA') multival_attr1_type,
nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
nvl(l.multival_attr2_context,'NA') multival_attr2_context,
nvl(l.multival_attribute2,'NA') multival_attribute2,
nvl(l.multival_attr2_type,'NA') multival_attr2_type,
nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
(q.priced_quantity * q.unit_price) gross_revenue_wanted,
-(decode(q.line_type_code,
'ORDER', decode(r.operand_calculation_code,
'%', q.unit_price * r.operand_value/100,
r.operand_value),
r.adjustment_amount * q.priced_quantity)) cost_wanted,
/*decode(r.operand_calculation_code,
QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
q.priced_quantity quantity_wanted
FROM qp_npreq_ldets_tmp r, qp_limits l,
qp_limit_attributes a, qp_npreq_line_attrs_tmp rl, qp_npreq_lines_tmp q
WHERE r.created_from_list_header_id = l.list_header_id
AND r.created_from_list_line_id = l.list_line_id
AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND r.applied_flag = 'Y' -- [5385851/5322832]
AND r.header_limit_exists = 'Y' --common flag for both header and line
AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
AND l.limit_id = a.limit_id
AND a.limit_attribute_context = rl.context
AND a.limit_attribute = rl.attribute
AND a.limit_attr_value = rl.value_from
AND a.limit_attribute_type = rl.attribute_type
AND rl.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
AND r.line_index = rl.line_index
AND r.line_index = q.line_index
GROUP BY r.line_index, r.created_from_list_header_id,
r.created_from_list_line_id, 'L', l.limit_id,
l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
l.limit_level_code, r.adjustment_amount, r.benefit_qty,
r.created_from_list_line_type, r.pricing_group_sequence,
r.operand_calculation_code, q.price_request_code,
q.request_type_code, q.line_category, r.operand_value, q.unit_price,
l.each_attr_exists, r.pricing_phase_id, l.non_each_attr_count,
l.total_attr_count, r.line_detail_index, l.organization_flag,
l.multival_attr1_context, l.multival_attribute1,
l.multival_attr1_type, l.multival_attr1_datatype,
l.multival_attr2_context, l.multival_attribute2,
l.multival_attr2_type, l.multival_attr2_datatype,
q.priced_quantity, q.line_type_code
HAVING count(*) = (select count(*)
from qp_limit_attributes la
where la.limit_id = l.limit_id)
UNION
--Statement to select line-level limits for pure Each and no limit attrs cases
SELECT r.line_index, r.created_from_list_header_id,
r.created_from_list_line_id, 'L' limit_level, l.limit_id,
l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
l.limit_level_code, r.adjustment_amount, r.benefit_qty,
r.created_from_list_line_type, r.pricing_group_sequence,
r.operand_calculation_code, q.price_request_code,
q.request_type_code, q.line_category,
r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
decode(l.organization_flag,
'Y','PARTY','NA') organization_attr_context,
decode(l.organization_flag,
'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
nvl(l.multival_attr1_context,'NA') multival_attr1_context,
nvl(l.multival_attribute1,'NA') multival_attribute1,
nvl(l.multival_attr1_type,'NA') multival_attr1_type,
nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
nvl(l.multival_attr2_context,'NA') multival_attr2_context,
nvl(l.multival_attribute2,'NA') multival_attribute2,
nvl(l.multival_attr2_type,'NA') multival_attr2_type,
nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
(q.priced_quantity * q.unit_price) gross_revenue_wanted,
-(decode(q.line_type_code,
'ORDER', decode(r.operand_calculation_code,
'%', q.unit_price * r.operand_value/100,
r.operand_value),
r.adjustment_amount * q.priced_quantity)) cost_wanted,
/*decode(r.operand_calculation_code,
QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
q.priced_quantity quantity_wanted
FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
WHERE r.created_from_list_header_id = l.list_header_id
AND r.created_from_list_line_id = l.list_line_id
AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND r.applied_flag = 'Y' -- [5385851/5322832]
AND r.header_limit_exists = 'Y' --common flag for both header and line
AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
AND r.line_index = q.line_index
AND l.non_each_attr_count = 0
UNION
--Statement to select header-level limits for pure Non-each and mixed cases
SELECT r.line_index, r.created_from_list_header_id,
r.created_from_list_line_id, 'H' limit_level, l.limit_id,
l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
l.limit_level_code, r.adjustment_amount, r.benefit_qty,
r.created_from_list_line_type, r.pricing_group_sequence,
r.operand_calculation_code, q.price_request_code,
q.request_type_code, q.line_category,
r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
decode(l.organization_flag,
'Y','PARTY','NA') organization_attr_context,
decode(l.organization_flag,
'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
nvl(l.multival_attr1_context,'NA') multival_attr1_context,
nvl(l.multival_attribute1,'NA') multival_attribute1,
nvl(l.multival_attr1_type,'NA') multival_attr1_type,
nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
nvl(l.multival_attr2_context,'NA') multival_attr2_context,
nvl(l.multival_attribute2,'NA') multival_attribute2,
nvl(l.multival_attr2_type,'NA') multival_attr2_type,
nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
(q.priced_quantity * q.unit_price) gross_revenue_wanted,
-(decode(q.line_type_code,
'ORDER', decode(r.operand_calculation_code,
'%', q.unit_price * r.operand_value/100,
r.operand_value),
r.adjustment_amount * q.priced_quantity)) cost_wanted,
/* decode(r.operand_calculation_code,
QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
q.priced_quantity quantity_wanted
FROM qp_npreq_ldets_tmp r, qp_limits l,
qp_limit_attributes a, qp_npreq_line_attrs_tmp rl, qp_npreq_lines_tmp q
WHERE r.created_from_list_header_id = l.list_header_id
AND l.list_line_id = -1
AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND r.applied_flag = 'Y' -- [5385851/5322832]
AND r.header_limit_exists = 'Y' --common flag for both header and line
AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
AND l.limit_id = a.limit_id
AND a.limit_attribute_context = rl.context
AND a.limit_attribute = rl.attribute
AND a.limit_attr_value = rl.value_from
AND a.limit_attribute_type = rl.attribute_type
AND rl.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
AND r.line_index = rl.line_index
AND r.line_index = q.line_index
GROUP BY r.line_index, r.created_from_list_header_id,
r.created_from_list_line_id, 'H', l.limit_id,
l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
l.limit_level_code, r.adjustment_amount, r.benefit_qty,
r.created_from_list_line_type, r.pricing_group_sequence,
r.operand_calculation_code, q.price_request_code,
q.request_type_code, q.line_category, r.operand_value, q.unit_price,
l.each_attr_exists, r.pricing_phase_id, l.non_each_attr_count,
l.total_attr_count, r.line_detail_index, l.organization_flag,
l.multival_attr1_context, l.multival_attribute1,
l.multival_attr1_type, l.multival_attr1_datatype,
l.multival_attr2_context, l.multival_attribute2,
l.multival_attr2_type, l.multival_attr2_datatype,
q.priced_quantity, q.line_type_code
HAVING count(*) = (select count(*)
from qp_limit_attributes la
where la.limit_id = l.limit_id)
UNION
--Statement to select headerlevel limits for pure Each and no limit attrs cases
SELECT r.line_index, r.created_from_list_header_id,
r.created_from_list_line_id, 'H' limit_level, l.limit_id,
l.amount, l.limit_exceed_action_code, l.basis, l.limit_hold_flag,
l.limit_level_code, r.adjustment_amount, r.benefit_qty,
r.created_from_list_line_type, r.pricing_group_sequence,
r.operand_calculation_code, q.price_request_code,
q.request_type_code, q.line_category,
r.operand_value, q.unit_price, l.each_attr_exists, r.pricing_phase_id,
l.non_each_attr_count, l.total_attr_count, r.line_detail_index,
decode(l.organization_flag,
'Y','PARTY','NA') organization_attr_context,
decode(l.organization_flag,
'Y','QUALIFIER_ATTRIBUTE3','NA') organization_attribute,
nvl(l.multival_attr1_context,'NA') multival_attr1_context,
nvl(l.multival_attribute1,'NA') multival_attribute1,
nvl(l.multival_attr1_type,'NA') multival_attr1_type,
nvl(l.multival_attr1_datatype,'NA') multival_attr1_datatype,
nvl(l.multival_attr2_context,'NA') multival_attr2_context,
nvl(l.multival_attribute2,'NA') multival_attribute2,
nvl(l.multival_attr2_type,'NA') multival_attr2_type,
nvl(l.multival_attr2_datatype,'NA') multival_attr2_datatype,
(q.priced_quantity * q.unit_price) gross_revenue_wanted,
-(decode(q.line_type_code,
'ORDER', decode(r.operand_calculation_code,
'%', q.unit_price * r.operand_value/100,
r.operand_value),
r.adjustment_amount * q.priced_quantity)) cost_wanted,
/*decode(r.operand_calculation_code,
QP_PREQ_GRP.G_LUMPSUM_DISCOUNT, r.benefit_qty,
r.benefit_qty * q.priced_quantity) accrual_wanted, -- 3598337, see bug for explanation*/
r.benefit_qty accrual_wanted, --4328118, see bug for explanation.
q.priced_quantity quantity_wanted
FROM qp_npreq_ldets_tmp r, qp_limits l, qp_npreq_lines_tmp q
WHERE r.created_from_list_header_id = l.list_header_id
AND l.list_line_id = -1
AND r.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND r.applied_flag = 'Y' -- [5385851/5322832]
AND r.header_limit_exists = 'Y' --common flag for both header and line
AND r.CREATED_FROM_LIST_LINE_TYPE NOT IN ('OID','PRG','CIE','IUE','TSN') --Bug#4101675
AND r.line_index = q.line_index
AND l.non_each_attr_count = 0
ORDER BY 1,2,3,4,5;
g_limit_balance_line.DELETE;
select MODIFIER_LEVEL_CODE into l_modifier_level_code
from qp_list_lines where list_line_id = l_old_limit_rec.created_from_list_line_id;
g_limit_balance_line.DELETE;
'Update limit balance. Loop through ' ||
'Recheck_balance and Update_Balance, if ' ||
'necessary ');
l_return_status := Update_Balance(x_return_text);
ELSE --If Update_Balance successful
IF g_limit_balance_line.COUNT > 0 THEN
--Reset limit_code and limit_text when line_index or
--list_line_id changes.
IF (l_old_limit_rec.created_from_list_line_id <>
l_limit_rec.created_from_list_line_id) OR
(l_old_limit_rec.line_index <> l_limit_rec.line_index)
THEN
l_limit_code := '';
UPDATE qp_npreq_ldets_tmp
SET operand_value = g_limit_balance_line(j).operand_value,
benefit_qty = g_limit_balance_line(j).benefit_qty,
limit_code = l_limit_code,
limit_text = l_limit_text
WHERE line_index = g_limit_balance_line(j).line_index
AND created_from_list_line_id =
g_limit_balance_line(j).list_line_id
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
UPDATE qp_npreq_ldets_tmp a
SET a.operand_value = DECODE(
g_limit_balance_line(j).operand_calculation_code,
'%', (g_limit_balance_line(j).least_percent/100) *
g_limit_balance_line(j).operand_value,
'AMT', (g_limit_balance_line(j).least_percent/100) *
g_limit_balance_line(j).operand_value,
'LUMPSUM', (g_limit_balance_line(j).least_percent/100)
* g_limit_balance_line(j).operand_value,
'NEWPRICE', g_limit_balance_line(j).operand_value -
(100 - g_limit_balance_line(j).least_percent)/100
* g_limit_balance_line(j).adjustment_amount,
g_limit_balance_line(j).operand_value),
a.benefit_qty = DECODE(
g_limit_balance_line(j).basis,
'ACCRUAL', (g_limit_balance_line(j).least_percent/100)
* g_limit_balance_line(j).benefit_qty,
g_limit_balance_line(j).benefit_qty),
a.limit_code = l_limit_code,
a.limit_text = l_limit_text
WHERE a.line_index = g_limit_balance_line(j).line_index
AND a.created_from_list_line_id =
g_limit_balance_line(j).list_line_id
AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND a.line_detail_index IN
(SELECT b.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp b
WHERE b.line_index = a.line_index
AND b.relationship_type_code = 'PRICE_BREAK'
AND b.line_detail_index =
g_limit_balance_line(j).line_detail_index);
UPDATE qp_npreq_lines_tmp
SET hold_code = DECODE(hold_code, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, l_hold_code)
WHERE line_index = g_limit_balance_line(j).line_index;
INSERT INTO qp_limit_transactions
(
limit_balance_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
list_header_id,
list_line_id,
price_request_date,
price_request_type_code,
price_request_code,
pricing_phase_id,
amount
)
VALUES
(
g_limit_balance_line(j).limit_balance_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
g_limit_balance_line(j).list_header_id,
g_limit_balance_line(j).list_line_id,
sysdate,
g_limit_balance_line(j).request_type_code,
g_limit_balance_line(j).price_request_code,
g_limit_balance_line(j).pricing_phase_id,
g_limit_balance_line(j).given_amount
);
update qp_limit_transactions
set amount = g_limit_balance_line(j).given_amount,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
price_request_date = sysdate
where limit_balance_id = g_limit_balance_line(j).limit_balance_id and list_header_id = g_limit_balance_line(j).list_header_id
and list_line_id = g_limit_balance_line(j).list_line_id
and price_request_code =
g_limit_balance_line(j).price_request_code;
g_limit_balance_line.DELETE;
END IF; --If Update_Balance returns Error
UPDATE qp_npreq_ldets_tmp
SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
WHERE created_from_list_header_id =
l_limit_rec.created_from_list_header_id
AND line_index = l_limit_rec.line_index
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
UPDATE qp_npreq_ldets_tmp
SET pricing_status_code = QP_PREQ_GRP.G_STATUS_DELETED
WHERE created_from_list_line_id =
l_limit_rec.created_from_list_line_id
AND line_index = l_limit_rec.line_index
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
select MODIFIER_LEVEL_CODE into l_modifier_level_code
from qp_list_lines where list_line_id = l_old_limit_rec.created_from_list_line_id;
l_return_status := Update_Balance(x_return_text);
QP_PREQ_GRP.engine_debug('Update_Balance l_return_status ' ||l_return_status);
ELSE --If Update_Balance successful
IF g_limit_balance_line.COUNT > 0 THEN
--Reset limit_code and limit_text when line_index for boundary condition
--processing.
l_limit_code := '';
UPDATE qp_npreq_ldets_tmp
SET operand_value = g_limit_balance_line(j).operand_value,
benefit_qty = g_limit_balance_line(j).benefit_qty,
limit_code = l_limit_code,
limit_text = l_limit_text
WHERE line_index = g_limit_balance_line(j).line_index
AND created_from_list_line_id =
g_limit_balance_line(j).list_line_id
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
UPDATE qp_npreq_ldets_tmp a
SET a.operand_value = DECODE(
g_limit_balance_line(j).operand_calculation_code,
'%', (g_limit_balance_line(j).least_percent/100) *
g_limit_balance_line(j).operand_value,
'AMT', (g_limit_balance_line(j).least_percent/100) *
g_limit_balance_line(j).operand_value,
'LUMPSUM', (g_limit_balance_line(j).least_percent/100)
* g_limit_balance_line(j).operand_value,
'NEWPRICE', g_limit_balance_line(j).operand_value -
(100 - g_limit_balance_line(j).least_percent)/100
* g_limit_balance_line(j).adjustment_amount,
g_limit_balance_line(j).operand_value),
a.benefit_qty = DECODE(
g_limit_balance_line(j).basis,
'ACCRUAL', (g_limit_balance_line(j).least_percent/100) *
g_limit_balance_line(j).benefit_qty,
g_limit_balance_line(j).benefit_qty),
a.limit_code = l_limit_code,
a.limit_text = l_limit_text
WHERE a.line_index = g_limit_balance_line(j).line_index
AND a.created_from_list_line_id =
g_limit_balance_line(j).list_line_id
AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND a.line_detail_index IN
(SELECT b.related_line_detail_index
FROM qp_npreq_rltd_lines_tmp b
WHERE b.line_index = a.line_index
AND b.relationship_type_code = 'PRICE_BREAK'
AND b.line_detail_index =
g_limit_balance_line(j).line_detail_index);
UPDATE qp_npreq_rltd_lines_tmp a
SET a.operand = g_limit_balance_line(j).operand_value
WHERE a.line_index = g_limit_balance_line(j).line_index
AND a.list_line_id =
g_limit_balance_line(j).list_line_id
AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND a.line_detail_index =
g_limit_balance_line(j).line_detail_index;
QP_PREQ_GRP.engine_debug('label 123-rows updated-'||sql%rowcount);
UPDATE qp_npreq_lines_tmp
SET hold_code = DECODE(hold_code, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, l_hold_code)
WHERE line_index = g_limit_balance_line(j).line_index;
QP_PREQ_GRP.engine_debug('Inserting Into qp_limit_transactions');
INSERT INTO qp_limit_transactions
(
limit_balance_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
list_header_id,
list_line_id,
price_request_date,
price_request_type_code,
price_request_code,
pricing_phase_id,
amount
)
VALUES
(
g_limit_balance_line(j).limit_balance_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
g_limit_balance_line(j).list_header_id,
g_limit_balance_line(j).list_line_id,
sysdate,
g_limit_balance_line(j).request_type_code,
g_limit_balance_line(j).price_request_code,
g_limit_balance_line(j).pricing_phase_id,
g_limit_balance_line(j).given_amount
);
update qp_limit_transactions
set amount = g_limit_balance_line(j).given_amount,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
price_request_date = sysdate
where limit_balance_id = g_limit_balance_line(j).limit_balance_id
and list_header_id = g_limit_balance_line(j).list_header_id
and list_line_id = g_limit_balance_line(j).list_line_id
and price_request_code =
g_limit_balance_line(j).price_request_code;
g_limit_balance_line.DELETE;
END IF; --If Update_Balance returns Error