[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT list_type_code
INTO l_list_type
FROM QP_LIST_HEADERS
WHERE LIST_HEADER_ID = p_price_list_id;
SELECT enforce_price_list_flag
INTO l_flag
FROM OE_BLANKET_LINES_EXT E
WHERE E.ORDER_NUMBER = p_blanket_number
AND E.LINE_NUMBER = p_blanket_line_number;
Select Unit_Price,
Adjusted_Unit_Price,
priced_quantity,
priced_uom_code
From QP_PREQ_LINES_TMP
Where Line_Id = p_line_id;
Select line_id,
blanket_number,
blanket_line_number,
unit_selling_price,
ordered_quantity,
order_quantity_uom,
inventory_item_id,
line_set_id,
fulfilled_flag,
header_id,
line_category_code,
commitment_id,
transaction_phase_code --for bug 3108881
From OE_ORDER_LINES
Where Line_Id = p_line_id;
FOR update_line IN c_line(p_line_rec.line_id) LOOP
IF update_line.line_category_code = 'ORDER'
AND update_line.blanket_number IS NOT NULL
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('OEXVADJB log blanket request');
oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
OE_Order_Cache.Load_Order_Header(update_line.header_id);
IF nvl(update_line.transaction_phase_code,'F') = 'F' THEN
OE_Delayed_Requests_Pvt.Log_Request
(p_entity_code => OE_GLOBALS.G_ENTITY_ALL
,p_entity_id => update_line.line_id
,p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE
,p_requesting_entity_id => update_line.line_id
,p_request_type => OE_GLOBALS.G_PROCESS_RELEASE
-- Old values
,p_param1 => update_line.blanket_number
,p_param2 => update_line.blanket_line_number
,p_param3 => update_line.ordered_quantity
,p_param4 => update_line.order_quantity_uom
,p_param5 => update_line.unit_selling_price
,p_param6 => update_line.inventory_item_id
-- New values
,p_param11 => update_line.blanket_number
,p_param12 => update_line.blanket_line_number
,p_param13 => update_line.ordered_quantity
,p_param14 => update_line.order_quantity_uom
,p_param15 => l_selling_price
,p_param16 => update_line.inventory_item_id
-- Other parameters
,p_param8 => update_line.fulfilled_flag
,p_param9 => update_line.line_set_id
,p_request_unique_key1 =>
OE_Order_Cache.g_header_rec.transactional_curr_code
,x_return_status => l_return_status
);
IF update_line.line_set_id IS NOT NULL THEN
OE_Delayed_Requests_Pvt.Log_Request
(p_entity_code => OE_GLOBALS.G_ENTITY_ALL
,p_entity_id => update_line.line_set_id
,p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE
,p_requesting_entity_id => update_line.line_id
,p_request_type => 'VALIDATE_RELEASE_SHIPMENTS'
,p_request_unique_key1 => update_line.blanket_number
,p_request_unique_key2 => update_line.blanket_line_number
,p_param1 =>
OE_Order_Cache.g_header_rec.transactional_curr_code
,x_return_status => l_return_status
);
IF update_line.commitment_id IS NOT NULL
AND OE_Commitment_Pvt.Do_Commitment_Sequencing THEN
oe_debug_pub.add('Logging delayed request for Commitment.', 2);
p_entity_id => update_line.line_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
p_requesting_entity_id => update_line.line_id,
p_request_type => OE_GLOBALS.G_CALCULATE_COMMITMENT,
x_return_status => l_return_status);
Update Oe_Order_Lines
Set Unit_Selling_Price= l_selling_price,
Unit_List_Price = l_unit_price,
unit_selling_price_per_pqty = l_qp_adjusted_unit_price,
unit_list_price_per_pqty = l_qp_unit_price,
pricing_quantity = l_pricing_quantity,
pricing_quantity_uom = l_pricing_quantity_uom,
tax_value = l_tax_value,
lock_control = lock_control +1
Where Line_id = p_line_rec.line_id;
oe_line_adj_util.delete_row(p_line_id => p_line_rec.line_id);
Select c.name,
a.list_line_no
From qp_preq_ldets_tmp a,
qp_preq_lines_tmp b,
qp_list_headers_vl c
Where b.line_id = p_line_id
And b.line_index = a.line_index
And a.created_from_list_header_id = c.list_header_id
And a.automatic_flag = 'Y'
And a.pricing_status_code = 'N'
And a.created_from_list_line_type <> 'PLL';
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = l_header_adj_rec.header_Id;
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = l_old_header_adj_rec.header_id;
ELSIF l_Header_Adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Header_Adj_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Header_Adj_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'HEADER_ADJ'
,p_entity_id => l_header_adj_rec.price_adjustment_id
,p_header_id => l_header_adj_rec.header_Id);
OR l_header_adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE)
THEN
adj_debug('Check Attributes Security');
IF l_Header_Adj_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Validate_Header_Adj.Entity_Delete
( x_return_status => l_return_status
, p_Header_Adj_rec => l_Header_Adj_rec
);
IF NOT (l_Header_Adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND OE_Header_Adj_Security.g_check_all_cols_constraint = 'Y')
AND l_control_rec.check_security THEN
adj_debug('Check Entity Security');
OE_DEBUG_PUB.add('OEXVADJB:calling to insert audit history reason for header adj in pre_write_process', 2);
oe_debug_pub.add('OEXVADJB:After calling to insert audit history');
IF l_Header_Adj_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Header_Adj_Util.Delete_Row
( p_price_adjustment_id => l_Header_Adj_rec.price_adjustment_id
);
-- Log a delayed request to cause repricing due to deleted
-- record
-- NOTE: Requesting entity is header, not header adj. as the adj. itself
-- has been deleted and this request should be deleted when the order is
-- deleted. Should be revisited if the entity logged against is changed
-- to header.
IF OE_Globals.G_RECURSION_MODE <> 'Y' AND
l_header_adj_rec.list_line_type_code NOT IN ('COST','TAX')
then
adj_debug('Log header level PRICE_ADJ');
l_Header_Adj_rec.last_update_date := SYSDATE;
l_Header_Adj_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Header_Adj_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Header_Adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
OE_Header_Adj_Util.Update_Row (l_Header_Adj_rec);
OE_Header_Adj_Util.Insert_Row (l_Header_Adj_rec);
OE_DBI_UTIL.Update_DBI_Log( x_return_status => l_return_status);
oe_delayed_requests_pvt.delete_request(p_entity_code =>OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => l_header_adj_rec.Price_adjustment_id,
p_request_type => NULL,
x_return_status => l_return_status);
oe_delayed_requests_pvt.delete_request(p_entity_code =>OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => l_header_adj_rec.Price_adjustment_id,
p_request_type => NULL,
x_return_status => l_return_status);
oe_delayed_requests_pvt.delete_request(p_entity_code =>OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => l_header_adj_rec.Price_adjustment_id,
p_request_type => NULL,
x_return_status => l_return_status);
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id, orig_sys_line_ref,
source_document_line_id, orig_sys_shipment_ref
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id, l_orig_sys_line_ref,
l_source_document_line_id, l_orig_sys_shipment_ref
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_line_adj_rec.line_id;
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id, orig_sys_line_ref,
source_document_line_id, orig_sys_shipment_ref
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id, l_orig_sys_line_ref,
l_source_document_line_id, l_orig_sys_shipment_ref
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_old_line_adj_rec.line_id;
ELSIF l_Line_Adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Line_Adj_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Line_Adj_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'LINE_ADJ'
,p_entity_id => l_line_adj_rec.price_adjustment_id
,p_header_id => l_line_adj_rec.header_id
,p_line_id => l_line_adj_rec.line_id);
OR l_line_adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE)
THEN
adj_debug('Check Attributes Security',2);
IF l_Line_Adj_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Validate_Line_Adj.Entity_Delete
( x_return_status => l_return_status
, p_Line_Adj_rec => l_Line_Adj_rec
);
adj_debug('After OE_Validate_Line_Adj.delete ',2);
OE_DEBUG_PUB.add('OEXVADJB:calling to insert audit history for line adj from pre_write_process', 2);
OE_DEBUG_PUB.add('OEXVADJB:After calling to insert audit history for line adj from pre_write_process', 2);
IF l_Line_Adj_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Line_Adj_Util.Delete_Row
( p_price_adjustment_id => l_Line_Adj_rec.price_adjustment_id
);
-- Log a delayed request to cause repricing due to deleted
-- record
-- NOTE: Requesting entity is line, not line adj. as the adj. itself
-- has been deleted and this request should be deleted when the line gets
-- deleted. Should be revisited if the entity logged against is changed
-- to line.
IF OE_Globals.G_RECURSION_MODE <> 'Y' and
l_Line_adj_rec.list_line_type_code NOT IN ('COST','TAX')
THEN
/* 1905650 - G_PRICE_ADJ request should be logged against LINE entity,
not against LINE_ADJ entity
1503357 - Minor change to handle header level adjustments
*/
if (l_line_adj_rec.line_id is NULL) Then
oe_delayed_requests_pvt.log_request(
p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_Line_adj_rec.header_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
p_requesting_entity_id => l_Line_adj_rec.header_id,
p_request_type => OE_GLOBALS.G_PRICE_ADJ,
x_return_status => l_return_status);
l_Line_Adj_rec.last_update_date := SYSDATE;
l_Line_Adj_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Line_Adj_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Line_Adj_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
oe_debug_pub.add(' updating list line id:'||l_Line_Adj_rec.list_line_id);
OE_Line_Adj_Util.Update_Row (l_Line_Adj_rec);
adj_debug('Before insert row',2);
OE_Line_Adj_Util.Insert_Row (l_Line_Adj_rec);
adj_debug('After insert row',2);
OE_DBI_UTIL.Update_DBI_Log( x_return_status => l_return_status);
oe_delayed_requests_pvt.delete_request(p_entity_code =>OE_GLOBALS.G_ENTITY_LINE_ADJ,
p_entity_id => l_line_adj_rec.Price_adjustment_id,
p_request_type => NULL,
x_return_status => l_return_status);
oe_delayed_requests_pvt.delete_request(p_entity_code =>OE_GLOBALS.G_ENTITY_LINE_ADJ,
p_entity_id => l_line_adj_rec.Price_adjustment_id,
p_request_type => NULL,
x_return_status => l_return_status);
oe_delayed_requests_pvt.delete_request(p_entity_code =>OE_GLOBALS.G_ENTITY_LINE_ADJ,
p_entity_id => l_line_adj_rec.Price_adjustment_id,
p_request_type => NULL,
x_return_status => l_return_status);
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = l_header_Price_Att_rec.header_Id;
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = l_old_header_Price_Att_rec.header_Id;
ELSIF l_Header_Price_Att_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Header_Price_Att_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Header_Price_Att_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'HEADER_ADJ'
,p_entity_id => l_header_Price_Att_rec.order_price_attrib_id
,p_header_id => l_header_Price_Att_rec.header_Id);
IF l_Header_Price_Att_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Validate_Header_Pattr.Entity_Delete
( x_return_status => l_return_status
, p_Header_Price_Attr_rec => l_Header_Price_Att_rec
);
IF l_Header_Price_Att_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Header_PAttr_Util.Delete_Row
( p_order_price_attrib_id => l_Header_Price_Att_rec.order_price_attrib_id
);
-- Log a delayed request to cause repricing due to deleted
-- record
--2442012
/* OE_delayed_requests_Pvt.log_request(
p_entity_code => OE_GLOBALS.G_ENTITY_HEader_Price_Att,
p_entity_id => l_Header_Price_Att_Rec.Header_Id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEader_Price_Att,
p_requesting_entity_id => l_Header_Price_Att_Rec.Header_Id,
p_param1 => l_Header_Price_Att_Rec.Header_Id,
p_param2 => 'LINE',
p_request_type => OE_GLOBALS.G_PRICE_ORDER,
x_return_status => l_return_status);
/*Select booked_flag Into
l_booked_flag
From OE_Order_Headers where
Header_id = l_Header_Price_Att_Rec.Header_Id;
l_Header_Price_Att_rec.last_update_date := SYSDATE;
l_Header_Price_Att_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Header_Price_Att_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Header_Price_Att_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
OE_Header_PAttr_Util.Update_Row (l_Header_Price_Att_rec);
oe_debug_pub.add(' Before calling pattr_util.insert_row');
select OE_ORDER_PRICE_ATTRIBS_S.nextval
into l_header_price_att_rec.order_price_attrib_id
from dual;
OE_Header_PAttr_Util.Insert_Row (l_Header_Price_Att_rec);
oe_delayed_requests_pvt.delete_request
(p_entity_code =>OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => l_header_Price_Att_rec.order_price_attrib_id,
p_request_type => NULL,
x_return_status => l_return_status);
oe_delayed_requests_pvt.delete_request
(p_entity_code =>OE_GLOBALS.G_ENTITY_HEADER_ADJ,
p_entity_id => l_header_Price_Att_rec.order_price_attrib_id,
p_request_type => NULL,
x_return_status => l_return_status);
ELSIF l_Header_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Header_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Header_Adj_Att_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'ADJ_ATTS'
,p_entity_id => l_Header_Adj_Att_rec.Price_adj_attrib_id);
IF l_Header_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
Oe_Header_Price_Aattr_util.Delete_Row
( p_Price_Adj_Attrib_id => l_Header_Adj_Att_rec.Price_adj_attrib_id
);
l_Header_Adj_Att_rec.last_update_date := SYSDATE;
l_Header_Adj_Att_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Header_Adj_Att_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Header_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
Oe_Header_Price_Aattr_util.Update_Row (l_Header_Adj_Att_rec);
Oe_Header_Price_Aattr_util.Insert_Row (l_Header_Adj_Att_rec);
ELSIF l_Header_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Header_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Header_Adj_Assoc_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'ADJ_ATTS'
,p_entity_id => l_Header_Adj_Assoc_rec.Price_Adj_assoc_id);
IF l_Header_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
Oe_Header_Adj_Assocs_util.Delete_Row
( p_Price_Adj_assoc_id => l_Header_Adj_Assoc_rec.Price_Adj_assoc_id
);
l_Header_Adj_Assoc_rec.last_update_date := SYSDATE;
l_Header_Adj_Assoc_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Header_Adj_Assoc_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Header_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
Oe_Header_Adj_Assocs_util.Update_Row (l_Header_Adj_Assoc_rec);
Oe_Header_Adj_Assocs_util.Insert_Row (l_Header_Adj_Assoc_rec);
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id, orig_sys_line_ref,
source_document_line_id, orig_sys_shipment_ref
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id, l_orig_sys_line_ref,
l_source_document_line_id, l_orig_sys_shipment_ref
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_Line_price_att_rec.Line_Id;
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id, orig_sys_line_ref,
source_document_line_id, orig_sys_shipment_ref
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id, l_orig_sys_line_ref,
l_source_document_line_id, l_orig_sys_shipment_ref
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_old_Line_price_att_rec.Line_Id;
ELSIF l_Line_price_att_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Line_price_att_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Line_price_att_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'LINE_PATTS'
,p_entity_id => l_Line_price_att_rec.order_price_attrib_id
,p_header_id => l_Line_price_att_rec.header_Id
,p_line_id => l_Line_price_att_rec.line_id);
OR l_Line_price_att_rec.operation = OE_GLOBALS.G_OPR_UPDATE)
THEN
adj_debug('Check Attributes Security');
IF l_Line_price_att_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Validate_Line_Pattr.Entity_Delete
( x_return_status => l_return_status
, p_Line_price_attr_rec => l_Line_Price_Att_rec
);
IF l_Line_price_att_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Line_Pattr_Util.Delete_Row
( p_order_price_attrib_id => l_Line_price_att_rec.order_price_attrib_id
);
Select booked_flag,Shipped_quantity into
l_booked_flag,l_Shipped_quantity
From OE_Order_lines where
Line_id = l_Line_Price_Att_rec.Line_Id;
l_Line_price_att_rec.last_update_date := SYSDATE;
l_Line_price_att_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Line_price_att_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Line_price_att_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
OE_Line_Pattr_Util.Update_Row (l_Line_price_att_rec);
select OE_ORDER_PRICE_ATTRIBS_S.nextval
into l_Line_price_att_rec.order_price_attrib_id
from dual;
OE_Line_Pattr_Util.Insert_Row (l_Line_price_att_rec);
PROCEDURE Insert_Adj_Atts
(p_Line_Adj_attribs_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type
)
is
I PLS_INTEGER;
Select OE_PRICE_ADJ_ATTRIBS_S.nextval
Into l_line_adj_att_tbl(i).price_adj_attrib_id
From dual;
End Insert_Adj_Atts;
ELSIF l_Line_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Line_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Line_Adj_Att_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'ADJ_ATTS'
,p_entity_id => l_Line_Adj_Att_rec.Price_adj_attrib_id);
IF l_Line_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
Oe_Line_Price_Aattr_util.Delete_Row
( p_Price_Adj_Attrib_id => l_Line_Adj_Att_rec.Price_adj_attrib_id
);
l_Line_Adj_Att_rec.last_update_date := SYSDATE;
l_Line_Adj_Att_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Line_Adj_Att_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Line_Adj_Att_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
Oe_Line_Price_Aattr_util.Update_Row (l_Line_Adj_Att_rec);
Oe_Line_Price_Aattr_util.Insert_Row (l_Line_Adj_Att_rec);
PROCEDURE Insert_Adj_Assocs
(p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
)
is
I PLS_INTEGER;
Select oe_price_adj_assocs_s.nextval
Into l_line_adj_assoc_tbl(i).price_adj_assoc_id
From dual;
ELSIF l_Line_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_Line_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_Line_Adj_Assoc_rec.db_flag := FND_API.G_TRUE;
OE_MSG_PUB.update_msg_context(
p_entity_code => 'ADJ_ATTS'
,p_entity_id => l_Line_Adj_Assoc_rec.Price_Adj_assoc_id);
IF l_Line_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
adj_debug('deleting line adj assocs',2);
Oe_Line_Adj_Assocs_util.Delete_Row
( p_Price_Adj_assoc_id => l_Line_Adj_Assoc_rec.Price_Adj_assoc_id
);
l_Line_Adj_Assoc_rec.last_update_date := SYSDATE;
l_Line_Adj_Assoc_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Line_Adj_Assoc_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Line_Adj_Assoc_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
adj_debug('updating line adj assocs',2);
Oe_Line_Adj_Assocs_util.Update_Row (l_Line_Adj_Assoc_rec);
adj_debug('inserting into line adj assocs',2);
Oe_Line_Adj_Assocs_util.Insert_Row (l_Line_Adj_Assoc_rec);
select nvl(enforce_line_prices_flag,'N') into l_enforce_price_flag
from oe_line_types_v where line_type_id=OE_Order_PUB.G_Line.Line_Type_id;
select nvl(enforce_line_prices_flag,'N') into l_enforce_price_flag
from oe_Order_types_v where Order_type_id=OE_Order_PUB.g_hdr.Order_Type_Id;
select /* MOAC_SQL_CHANGE */ nvl(enforce_line_prices_flag,'N') into px_order_enforce_list_price
from oe_transaction_types_all o,oe_order_headers h
where h.header_id=p_header_id and h.order_type_id=o.transaction_type_id
and o.transaction_type_code=l_literal_order;
select nvl(enforce_line_prices_flag,'N') into x_line_enforce_list_price
from oe_line_types_v where line_type_id=p_line_type_id;
select rounding_factor into g_rounding_factor_rec.rounding_factor from
qp_list_headers_b where list_header_id=p_list_header_id;
Select list_type_code into
px_Req_LINE_DETAIL_tbl(l_RLD_Index).created_from_list_type_code
from qp_list_headers_b where
list_header_id= p_Line_Adj_rec.List_Header_Id;
px_Req_LINE_DETAIL_tbl(l_RLD_Index).override_flag := p_Line_Adj_rec.update_allowed;
select flex_title, pricing_context, pricing_attribute1,
pricing_attribute2 , pricing_attribute3 , pricing_attribute4 , pricing_attribute5 ,
pricing_attribute6 , pricing_attribute7 , pricing_attribute8 , pricing_attribute9 ,
pricing_attribute10 , pricing_attribute11 , pricing_attribute12 , pricing_attribute13 ,
pricing_attribute14 , pricing_attribute15 , pricing_attribute16 , pricing_attribute17 ,
pricing_attribute18 , pricing_attribute19 , pricing_attribute20 , pricing_attribute21 ,
pricing_attribute22 , pricing_attribute23 , pricing_attribute24 , pricing_attribute25 ,
pricing_attribute26 , pricing_attribute27 , pricing_attribute28 , pricing_attribute29 ,
pricing_attribute30 , pricing_attribute31 , pricing_attribute32 , pricing_attribute33 ,
pricing_attribute34 , pricing_attribute35 , pricing_attribute36 , pricing_attribute37 ,
pricing_attribute38 , pricing_attribute39 , pricing_attribute40 , pricing_attribute41 ,
pricing_attribute42 , pricing_attribute43 , pricing_attribute44 , pricing_attribute45 ,
pricing_attribute46 , pricing_attribute47 , pricing_attribute48 , pricing_attribute49 ,
pricing_attribute50 , pricing_attribute51 , pricing_attribute52 , pricing_attribute53 ,
pricing_attribute54 , pricing_attribute55 , pricing_attribute56 , pricing_attribute57 ,
pricing_attribute58 , pricing_attribute59 , pricing_attribute60 , pricing_attribute61 ,
pricing_attribute62 , pricing_attribute63 , pricing_attribute64 , pricing_attribute65 ,
pricing_attribute66 , pricing_attribute67 , pricing_attribute68 , pricing_attribute69 ,
pricing_attribute70 , pricing_attribute71 , pricing_attribute72 , pricing_attribute73 ,
pricing_attribute74 , pricing_attribute75 , pricing_attribute76 , pricing_attribute77 ,
pricing_attribute78 , pricing_attribute79 , pricing_attribute80 , pricing_attribute81 ,
pricing_attribute82 , pricing_attribute83 , pricing_attribute84 , pricing_attribute85 ,
pricing_attribute86 , pricing_attribute87 , pricing_attribute88 , pricing_attribute89 ,
pricing_attribute90 , pricing_attribute91 , pricing_attribute92 , pricing_attribute93 ,
pricing_attribute94 , pricing_attribute95 , pricing_attribute96 , pricing_attribute97 ,
pricing_attribute98 , pricing_attribute99 , pricing_attribute100
,Override_Flag
from oe_order_price_attribs a
where (a.line_id is null and a.header_id = p_header_id )
union all
select flex_title, pricing_context, pricing_attribute1,
pricing_attribute2 , pricing_attribute3 , pricing_attribute4 , pricing_attribute5 ,
pricing_attribute6 , pricing_attribute7 , pricing_attribute8 , pricing_attribute9 ,
pricing_attribute10 , pricing_attribute11 , pricing_attribute12 , pricing_attribute13 ,
pricing_attribute14 , pricing_attribute15 , pricing_attribute16 , pricing_attribute17 ,
pricing_attribute18 , pricing_attribute19 , pricing_attribute20 , pricing_attribute21 ,
pricing_attribute22 , pricing_attribute23 , pricing_attribute24 , pricing_attribute25 ,
pricing_attribute26 , pricing_attribute27 , pricing_attribute28 , pricing_attribute29 ,
pricing_attribute30 , pricing_attribute31 , pricing_attribute32 , pricing_attribute33 ,
pricing_attribute34 , pricing_attribute35 , pricing_attribute36 , pricing_attribute37 ,
pricing_attribute38 , pricing_attribute39 , pricing_attribute40 , pricing_attribute41 ,
pricing_attribute42 , pricing_attribute43 , pricing_attribute44 , pricing_attribute45 ,
pricing_attribute46 , pricing_attribute47 , pricing_attribute48 , pricing_attribute49 ,
pricing_attribute50 , pricing_attribute51 , pricing_attribute52 , pricing_attribute53 ,
pricing_attribute54 , pricing_attribute55 , pricing_attribute56 , pricing_attribute57 ,
pricing_attribute58 , pricing_attribute59 , pricing_attribute60 , pricing_attribute61 ,
pricing_attribute62 , pricing_attribute63 , pricing_attribute64 , pricing_attribute65 ,
pricing_attribute66 , pricing_attribute67 , pricing_attribute68 , pricing_attribute69 ,
pricing_attribute70 , pricing_attribute71 , pricing_attribute72 , pricing_attribute73 ,
pricing_attribute74 , pricing_attribute75 , pricing_attribute76 , pricing_attribute77 ,
pricing_attribute78 , pricing_attribute79 , pricing_attribute80 , pricing_attribute81 ,
pricing_attribute82 , pricing_attribute83 , pricing_attribute84 , pricing_attribute85 ,
pricing_attribute86 , pricing_attribute87 , pricing_attribute88 , pricing_attribute89 ,
pricing_attribute90 , pricing_attribute91 , pricing_attribute92 , pricing_attribute93 ,
pricing_attribute94 , pricing_attribute95 , pricing_attribute96 , pricing_attribute97 ,
pricing_attribute98 , pricing_attribute99 , pricing_attribute100
,Override_Flag
from oe_order_price_attribs a
where (p_line_id is not null and a.line_id = p_line_id )
;
SELECT concatenated_segments
INTO px_line_rec.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = px_line_rec.inventory_item_id
AND organization_id = l_org_id;
select modified_from from oe_price_adjustments
where line_id=px_line_rec.line_id
and list_line_type_code='IUE';
SELECT concatenated_segments
INTO px_line_rec.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = px_line_rec.inventory_item_id
AND organization_id = l_org_id;
Select 'x' into l_dummy from dual
where exists (select 'x' from oe_agreements_vl where
agreement_id = l_line_tbl(line_Tbl_Index).agreement_id and
( trunc(nvl(l_line_tbl(line_Tbl_Index).PRICING_DATE,sysdate))
between
trunc(nvl(start_date_active, nvl(l_line_tbl(line_Tbl_Index).PRICING_DATE,sysdate)))
and
trunc(nvl(end_date_active, nvl(l_line_tbl(line_Tbl_Index).PRICING_DATE, sysdate)))));
select name, revision into l_agreement_name, l_revision
from oe_agreements_vl where agreement_id =
l_line_tbl(line_Tbl_Index).agreement_id;
Select 'x' into l_dummy from dual
Where exists
(select 'x' from
oe_order_price_attribs oopa
where
nvl(oopa.line_id,l_Line_Tbl(line_Tbl_Index).line_id) = l_Line_Tbl(line_Tbl_Index).line_id
and oopa.header_id = l_header_id2);
Select 'x' into l_dummy
from dual where
exists(select 'x' from oe_order_lines
Where header_id = l_header_id
and calculate_price_flag in ('Y','P')
and item_type_code not in ('CONFIG', 'INCLUDED'));
Select 'p' into l_dummy
from dual where
exists (select 'x' from oe_order_lines
where header_id = l_header_id
and calculate_price_flag in ('N', 'P')
and cancelled_flag = 'N'
and item_type_code not in ('CONFIG', 'INCLUDED') );
Select 'x' into l_dummy from dual
where exists(
Select 'X' from oe_order_price_attribs oopa
where oopa.header_id = l_header_id2 and oopa.line_id is null);
Function Update_Adj_Line_rec(
p_Line_Adj_Rec in out nocopy OE_Order_Pub.Line_Adj_Rec_Type
,p_req_line_detail_Rec qp_preq_grp.line_detail_rec_type
)
Return Boolean
is
l_updated_Flag Boolean := False;
p_Line_Adj_Rec.operation := OE_GLOBALS.G_OPR_UPDATE;
adj_debug('Entering oe_order_Adj_pvt.Update_Adj_Line_rec',1);
G_STMT_NO := 'Update_Adj_Line_rec#10';
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
if (not l_updated_flag) and p_Line_Adj_Rec.list_line_type_code = 'CIE' Then
adj_debug('CIE:to delete coupon'||p_req_line_detail_rec.list_line_no);
QP_COUPON_PVT.Delete_Coupon(p_req_line_detail_Rec.list_line_no,
x_return_status,
x_return_status_txt
);
l_updated_Flag := True;
l_updated_Flag := True;
adj_debug('Value error , The term is not updated '||p_req_line_detail_Rec.substitution_From);
l_updated_Flag := True;
adj_debug('Value error , The term is not updated '||p_req_line_detail_Rec.substitution_to);
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
if not OE_GLOBALS.Equal(p_Line_Adj_Rec.update_allowed,
p_req_line_detail_Rec.Override_flag) Then
p_Line_Adj_Rec.update_allowed := p_req_line_detail_Rec.Override_flag;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
l_updated_Flag := True;
adj_debug('Exiting oe_order_Adj_pvt.Update_Adj_Line_rec',1);
Return l_updated_Flag;
End Update_Adj_Line_rec;
px_line_adj_Tbl(x_Adj_Index).Updated_Flag := 'N';
px_line_adj_Tbl(x_Adj_Index).Updated_Flag := 'N';
/* Taking out nocopy this part to be set in update_adj_line_rec: Manish */
-- Else
-- px_Line_Adj_Tbl(x_Adj_Index).operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('updated_flag:'||px_Line_Adj_Tbl(x_Adj_Index).updated_Flag);
if px_Line_Adj_Tbl(x_Adj_Index).updated_Flag = 'Y' Then
-- User has updated this record Do not touch this
px_Line_Adj_Tbl(x_Adj_Index).Operation := FND_API.G_MISS_CHAR;
OE_GLOBALS.G_OPR_DELETE
AND
nvl(px_Line_Adj_Tbl(x_Adj_Index).adjusted_amount_per_pqty,0) >=
nvl(p_req_line_detail_Rec.adjustment_amount,0)
Then
-- Retain the freight charge of higher amount.
Null;
elsif Not Update_Adj_Line_rec(
p_Line_Adj_Rec => px_Line_Adj_Tbl(x_Adj_Index)
,p_req_line_detail_Rec => p_req_line_detail_Rec
)
Then
-- There is no change in the adjustment record
px_Line_Adj_Tbl(x_Adj_Index).Operation := FND_API.G_MISS_CHAR;
px_line_adj_tbl(x_adj_index).operation := OE_GLOBALS.G_OPR_UPDATE;
End If; -- Check Updated_Flag
p_delete_flag in Varchar2 default 'Y'
) Is
L_Line_Adj_Assoc_Tbl OE_Order_Pub.line_adj_Assoc_tbl_Type;
-- Append to the tbl with Delete flag set.
-- The row would be removed from this structure if there is a match.
G_STMT_NO := 'Query_Adj_Assocs#20';
If p_delete_flag = 'Y' Then
p_Line_Adj_Assoc_Tbl(l_index).Operation := OE_GLOBALS.G_OPR_DELETE;
x_updated_flag out nocopy varchar2,
p_multiple_events in VARCHAR2 Default 'N'
)
is
l_Pricing_Phase_id Index_Tbl_Type;
l_mark_for_delete Varchar2(1);
x_updated_flag := 'N';
IF l_Line_Adj_Tbl(i).updated_flag = 'Y' and x_updated_flag = 'N' THEN
x_updated_flag := 'Y';
l_mark_for_delete:='Y';
l_mark_for_delete := 'Y';
l_mark_for_delete := 'N';
l_mark_for_delete := 'N';
adj_debug('Delete ' || l_Line_adj_tbl(i).price_adjustment_id || '? ' || l_mark_for_delete);
If l_mark_for_delete = 'Y' Then
G_STMT_NO := 'Append_Adjustment_Lines#30';
elsif nvl(l_Line_Adj_Tbl(i).Updated_Flag,'N') = 'N' or
l_Line_Adj_Tbl(i).Updated_Flag = fnd_api.g_miss_char then
adj_debug('BCT'||l_Line_Adj_Tbl(i).price_adjustment_id);
L_Line_Adj_Tbl(i).operation := OE_GLOBALS.G_OPR_DELETE;
If px_line_adj_tbl(px_line_adj_tbl.count).operation = OE_GLOBALS.G_OPR_DELETE
Then
Query_Adj_Assocs(p_price_adjustment_id=>px_line_adj_tbl(px_line_adj_tbl.count).price_adjustment_id,
p_Adj_Index => px_line_adj_tbl.count,
p_Line_Adj_Assoc_Tbl => px_line_adj_Assoc_tbl,
p_delete_flag => 'Y');
p_delete_flag => 'N');
-- Append to the tbl with Delete flag set.
-- The row would be removed from this structure if there is a match.
G_STMT_NO := 'Query_Adj_Attribs#20';
p_Line_Adj_Att_Tbl(l_index).Operation := OE_GLOBALS.G_OPR_DELETE;
-- Do not delete the record from oe_order_Price_adj_Attribs
If p_Line_Adj_Att_Tbl(i).Operation = OE_GLobals.g_opr_delete then
p_Line_Adj_Att_Tbl(i).Operation := FND_API.G_MISS_CHAR;
-- Do not delete the record from oe_order_Price_adj_Attribs
If p_Line_Adj_Att_Tbl(i).Operation = oe_globals.g_opr_delete Then
p_Line_Adj_Att_Tbl(i).Operation := FND_API.G_MISS_CHAR;
select PRICING_ATTR_VALUE_FROM into l_inventory_item_id
from oe_price_adj_attribs opaa
where price_adjustment_id =p_price_Adjustment_Id and
Flex_Title ='QP_ATTR_DEFNS_PRICING' and
Pricing_Context='ITEM' and
Pricing_attribute='PRICING_ATTRIBUTE1' and
Rownum < 2;
Select inventory_item_id into l_inventory_item_id
from oe_price_adjustments opa,oe_order_lines_all ola
where opa.line_id=ola.line_id and
opa.price_adjustment_id=p_price_Adjustment_Id;
-- Find the "Other Item" Line and update the attributes
adj_debug('The PRG already exists '||p_Price_Adjustment_Id);
-- Update The Adjustment Record of the "Other Item"
-- bug 1843872, find whether the discount line is in memory
j := p_line_adj_tbl.first;
p_Line_Adj_Assoc_Tbl.delete(i);
If Update_Adj_Line_rec(
p_Line_Adj_Rec => l_line_Adj_Rec
,p_req_line_detail_Rec => p_req_line_detail_Tbl(p_Line_Detail_Index)
)
Then
-- There is a Change is the Adjsutment Record
l_Line_Adj_Rec.operation := OE_GLOBALS.G_OPR_UPDATE;
adj_debug('delete line'||p_rltd_lines_prcd(l_req_line_index));
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
select nvl(sum(ordered_quantity),0),nvl(sum(pricing_quantity),0)
into l_tot_qty,l_tot_price_qty
from oe_order_lines
where split_from_line_id = l_line_rec.line_id
and header_id = l_line_rec.header_id;
oe_debug_pub.add('bug 2412868 in update1:');
oe_debug_pub.add('bug 2412868 in update:');
SELECT concatenated_segments
INTO l_line_rec.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_org_id;
-- Insert Adjustment Records
-- Check If this Related Adjustment Line Has Already been processed in an earlier loop
G_STMT_NO := 'Process_Other_Item_Line#40';
If Update_Adj_Line_rec(
p_Line_Adj_Rec => p_Line_Adj_Tbl(l_Adj_Index)
,p_req_line_detail_Rec => p_req_line_detail_Tbl(p_Line_Detail_Index)
)
Then
p_line_adj_Tbl(l_Adj_Index).Updated_Flag := 'N';
Function find_updated_adjustments(
p_header_id number default null
,p_Line_id number default null
,p_Line_Adj_Tbl oe_order_pub.line_adj_tbl_type
)
Return boolean
is
i pls_integer;
p_Line_Adj_Tbl(i).updated_flag = 'Y' and
p_Line_Adj_Tbl(i).Applied_Flag = 'Y' then
Return True; -- Atleast one adjustment has been updated
end find_updated_adjustments;
Select/*+ ordered use_nl(qpq qppa qpll qplh) */ min(qpll.operand)
From
qp_qualifiers qpq
, qp_pricing_attributes qppa
, qp_list_lines qpll
, qp_list_headers_b qplh
, qp_price_req_sources 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 concatenated_segments
INTO l_msg_text
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_org_id;
Select hold_id into l_hold_source_rec.hold_id
from oe_hold_definitions where type_code='GSA';
select 'Y' into l_gsa_released from
oe_order_holds ooh,oe_hold_sources ohs,oe_hold_releases ohr
where ooh.line_id = p_line_id
and ooh.hold_source_id = ohs.hold_source_id
and ohr.hold_release_id = ooh.hold_release_id
and ohs.hold_id = l_hold_source_rec.hold_id
and ohr.created_by <> 1
and ohr.release_reason_code <> 'PASS_GSA';
Select hold_id into l_hold_source_rec.hold_id
from oe_hold_definitions where type_code='GSA';
p_line_tbl(i).operation = oe_globals.G_OPR_UPDATE)
OR p_honor_price_flag = 'N' --bug 2503186
OR -- OPM 2547940 start - for copy of order OR split from shipping - need to re-price as these lines
-- as may have freeze price for calulate price flag when pricing by quantity2
(
( l_process)
and (l_item_rec.ont_pricing_qty_source = 1 ) -- price by quantity 2
and ( p_line_tbl(i).calculate_price_flag In ('N','P') )
and ( p_line_tbl(i).split_by is not null )
and ( p_line_tbl(i).split_from_line_id is not null and p_line_tbl(i).split_from_line_id <> FND_API.G_MISS_NUM)
) -- OPM 2547940 end
Then
adj_debug('Processing Line id '||p_line_tbl(i).line_id,2);
If p_Line_Tbl(i).operation in (oe_globals.g_opr_delete, oe_globals.g_opr_lock) or
(p_Line_Tbl(i).Unit_list_price is null
and p_Line_Tbl(i).Unit_list_price_per_pqty is null)
Then
adj_debug('Line '||p_line_tbl(i).line_id||' price is not calculated');
p_Line_Adj_Tbl(j).Operation <> OE_Globals.G_OPR_DELETE and
nvl(p_Line_Adj_Tbl(j).applied_flag,'N')='Y' and
/* Modified the nvl to 'N' in the above statement to fix the bug 2164508 */
p_Line_Adj_Tbl(j).list_line_type_code in ('DIS','SUR','FREIGHT_CHARGE','PBH')
then
--adj_debug('BCT+fullfilled calculate');
p_Line_Adj_Tbl(j).Price_Adjustment_id <> fnd_api.g_miss_num )) and (nvl(p_line_adj_assoc_Tbl(j1).operation,'xxyz') <> OE_GLOBALS.G_OPR_DELETE)
) Then
l_assoc_exist := TRUE;
p_Line_Adj_Tbl(j).Price_Adjustment_id <> fnd_api.g_miss_num )) And (nvl(p_line_adj_assoc_Tbl(j1).operation,'xxyz') <> OE_GLOBALS.G_OPR_DELETE)
) Then
adj_debug('Got an association Rltd_index '||p_line_adj_assoc_Tbl(j1).Rltd_Adj_index,2);
IF p_Line_Adj_Tbl(j2).updated_flag = 'Y' and p_Line_Adj_Tbl(j).updated_flag <> 'Y' THEN
p_Line_Adj_Tbl(j).updated_flag := 'Y';
If p_Line_Adj_Tbl(j).updated_flag = 'N' Then
p_Line_Adj_Tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
End If ; -- Of lines.operation in update or create
select nvl(transactional_curr_code,'USD') into l_currency_code from oe_order_headers
where header_id=p_header_id;
Order no longer qualifies for PRG modifier. Either delete (or) update
the free goods lines, depending on whether they are shipped or not
*/
PROCEDURE change_prg_lines(p_price_adjustment_id IN NUMBER,
p_line_tbl IN OUT NoCopy OE_Order_PUB.Line_Tbl_Type,
p_line_adj_tbl IN OUT NoCopy OE_Order_PUB.Line_Adj_Tbl_Type,
p_delete_prg_lines IN OUT NoCopy index_tbl_type) IS
Cursor prg_lines is
Select radj.line_id
from oe_price_adjustments radj,
oe_price_adj_assocs assoc
where radj.price_adjustment_id =assoc.rltd_price_adj_id and
assoc.price_adjustment_id = p_price_adjustment_id;
Select pricing_phase_id
from qp_event_phases
where pricing_event_code like 'BOOK';
select pricing_phase_id
into pricing_ph_id
from oe_price_adjustments
where price_adjustment_id = p_price_adjustment_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
p_delete_prg_lines(l_line_rec.line_id) := l_line_rec.line_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
p_delete_prg_lines(l_line_rec.line_id) := l_line_rec.line_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_updated_flag varchar2(1);
l_preinsert_manual_adj VARCHAR2(1):= Nvl(Fnd_Profile.Value('ONT_PREINSERT_MANUAL_ADJ'),'N');
l_delete_prg_lines index_tbl_type;
Select name into l_price_list
from qp_list_headers_vl where
list_header_id = l_line_rec.price_list_id;
Update Oe_Order_Lines
set Unit_Selling_Price = Null,Unit_list_price = Null
where line_id = l_line_rec.line_id;
oe_line_adj_util.delete_row(p_line_id => l_line_rec.line_id);
Oe_Debug_Pub.add('Oe_Order_Adj_Pvt:Failed to update price:'||SQLERRM);
Select name into l_price_list
from qp_list_headers_vl a,qp_list_lines b where
b.list_line_id = to_number(substr(p_req_line_tbl(i).status_text,1,
instr(p_req_line_tbl(i).status_text,',')-1))
and a.list_header_id=b.list_header_id
;
Select name into l_price_list
from qp_list_headers_vl a,qp_list_lines b where
b.list_line_id = to_number(substr(p_req_line_tbl(i).status_text,
instr(p_req_line_tbl(i).status_text,',')+1))
and a.list_header_id=b.list_header_id ;
( QP_PREQ_GRP.G_STATUS_UPDATED,
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
QP_PREQ_GRP.G_STATUS_UNCHANGED) and
nvl(p_req_line_tbl(i).processed_code,'0') <> QP_PREQ_GRP.G_BY_ENGINE
and p_req_line_tbl(i).price_flag IN ('Y','P') )
or -- OPM 2547940 start
-- pricing by quantity2 - this is for case when order line is split from shipping and new line gets generated
-- and this line has to be re-priced for catchweight pricing for OPM if pricing in secondary quantity
( ( oe_line_util.dual_uom_control -- INVCONV
(l_line_rec.inventory_item_id
,l_line_rec.ship_from_org_id
,l_item_rec) )
and p_req_line_tbl(i).line_Type_code ='LINE'
and (l_item_rec.ont_pricing_qty_source = 'S' ) -- price by quantity 2 -- INVCONV
and ( p_req_line_tbl(i).price_flag In ('N','P') )
and ( l_line_rec.split_by is not null )
and ( l_line_rec.split_from_line_id is not null and l_line_rec.split_from_line_id <> FND_API.G_MISS_NUM) )
-- OPM 2547940 end
--we do not want to go in this loop if price_flag is set up 'N' because
--engine doesn't look at the line and will not return adjustments. In this
--case we DON't want to remove the adjustments that engine doesn't return.
then
G_STMT_NO := 'process_adjustments11.5';
x_updated_flag => l_updated_flag,
p_multiple_events => p_multiple_events);
(p_req_line_tbl(i).status_code in ( QP_PREQ_GRP.G_STATUS_UPDATED ,
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
--btea
--In this case even engine doesn't update the order (status = UNCHANGED)
--because of one of the lined is frozen,
--there can be some order level adjustments in database which
--need to be pulled out by append_adjustment_lines routine
or (p_req_line_tbl(i).status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
-- and p_any_frozen_line = TRUE -- bug 1675449
))
Then
G_STMT_NO := 'Process_Adjustments#35';
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
x_updated_flag => l_updated_flag,
p_multiple_events => p_multiple_events);
select oe_msg_request_id_s.nextval into l_request_id from dual;
OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, l_request_id,'U');
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id, orig_sys_line_ref,
source_document_line_id, orig_sys_shipment_ref
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id, l_orig_sys_line_ref,
l_source_document_line_id, l_orig_sys_shipment_ref
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_req_line_tbl(i).line_id;
QP_PREQ_GRP.G_STATUS_UPDATED ,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION )
and nvl(p_req_line_tbl( p_req_line_detail_Tbl(j).line_index).processed_code,'0')
<> QP_PREQ_GRP.G_BY_ENGINE
--only process/insert automatic adjustments, with freight charge as exception
and (p_req_line_detail_Tbl(j).automatic_flag = 'Y' or
(p_req_line_detail_Tbl(j).automatic_flag = 'N' and (p_req_line_detail_Tbl(j).list_line_type_code = 'FREIGHT_CHARGE' or l_preinsert_manual_adj = 'Y' )))
and (
((
( l_line_tbl.exists( p_req_line_detail_Tbl(j).line_index)
and
oe_line_util.Get_Return_Item_Type_Code
(l_line_tbl( p_req_line_detail_Tbl(j).line_index))<> 'INCLUDED'
)
Or not l_line_tbl.exists (p_req_line_detail_Tbl(j).line_index )
)
And
G_CHARGES_FOR_INCLUDED_ITEM = 'N'
)
Or
G_CHARGES_FOR_INCLUDED_ITEM = 'Y'
)
then
--adj_debug('BCT+Engine returned status fullfilled');
-- Updated the Terms at Line Level
If p_req_line_detail_Tbl(j).Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
adj_debug('Payment_term updated to '||p_req_line_detail_Tbl(j).Substitution_to);
adj_debug('shipping_method_code updated to '||p_req_line_detail_Tbl(j).Substitution_to);
adj_debug('freight_terms_code updated to '||p_req_line_detail_Tbl(j).Substitution_to);
-- Updated the Terms at Line Level
If p_req_line_detail_Tbl(j).Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
adj_debug('Payment_term updated to '||p_req_line_detail_Tbl(j).Substitution_to);
adj_debug('shipping_method_code updated to '||p_req_line_detail_Tbl(j).Substitution_to);
adj_debug('freight_terms_code updated to '||p_req_line_detail_Tbl(j).Substitution_to);
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
-- Delete
-- Subsequently these rows would be reinstated
-- Delete is cascaded to children when adjustment is deleted.
G_STMT_NO := 'process_adjustments#60';
OE_GLOBALS.G_OPR_UPDATE ) Then
-- This Record Needs to be processed
if p_req_line_tbl( p_req_line_detail_Tbl(j).line_index).line_type_code = 'LINE'
Then
if p_req_line_detail_Tbl(j).list_line_type_code ='IUE' then
-- Do not give item upgrades to config items.
If l_line_tbl( p_req_line_detail_Tbl(j).line_index).top_model_line_id is null
or l_line_tbl( p_req_line_detail_Tbl(j).line_index).top_model_line_id= fnd_api.g_miss_num
then
If -- Allow Item upgrade only if the order is not booked.
nvl(l_line_tbl( p_req_line_detail_Tbl(j).line_index).booked_flag,'N')
= 'N'
then
-- Set the Line Item to the new item
Set_item_for_iue(
px_line_rec => l_line_tbl( p_req_line_detail_Tbl(j).line_index)
,p_req_line_detail_rec => p_req_line_detail_Tbl(j)
);
-- Inserts Rows into Line_Tbl and Adjustments
G_STMT_NO := 'process_adjustments#90';
end if; -- Status_code = Updated.
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id, orig_sys_line_ref,
source_document_line_id, orig_sys_shipment_ref
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id, l_orig_sys_line_ref,
l_source_document_line_id, l_orig_sys_shipment_ref
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_req_line_tbl(p_req_line_detail_tbl(j).line_index).line_id;
select oe_msg_request_id_s.nextval into g_request_id from dual;
OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
OE_MSG_PUB.DELETE_MSG(OE_MSG_PUB.COUNT_MSG);
and l_line_adj_tbl(l_line_details_prcd(p_Req_Related_Lines_Tbl(i).Line_Detail_Index)).updated_flag = 'N'
Then
If G_DEBUG Then
adj_debug('RLTD: line detail '||p_Req_Related_Lines_Tbl(i).Line_Detail_Index,2);
IF (l_line_adj_tbl(i).operation = oe_globals.g_opr_delete
and l_line_adj_tbl(i).list_line_type_code='PRG') THEN
adj_debug('Need to delete/update free goods line');
p_delete_prg_lines => l_delete_prg_lines);
l_line_tbl_final(l).operation := OE_GLOBALS.G_OPR_UPDATE;
AND l_Line_Adj_Tbl(i).operation IN (OE_GLOBALS.G_OPR_CREATE, OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_DELETE)
AND l_Line_Adj_Tbl(i).list_line_type_code NOT IN ('FREIGHT_CHARGE','TAX','COST')
AND nvl(g_pass_all_lines,'N') <> 'Y' THEN
-- This is a header level adjustments, and not a freight charge.
-- Need to log a delayed request to apply this header level adjustment
-- to all lines on this order if not all lines were passed to pricing engine.
adj_debug('Logging request to update all lines for header level adjustment.', 1);
-- Update the Header Level TERM Substitution on all Lines.
If l_header_rec.operation = oe_globals.g_opr_update then
If l_line_adj_tbl(i).list_line_type_code = 'TSN' and
l_line_adj_tbl(i).modifier_level_code ='ORDER'
then
adj_debug('In Header TSN ');
-- Loop through all the lines and update
j:= l_Line_Tbl_Final.first;
l_Line_Tbl_Final(j).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_adj_tbl.delete(i);
Elsif l_line_adj_tbl(i).operation = oe_globals.g_opr_update and
( l_line_adj_tbl(i).price_adjustment_id is null or
l_line_adj_tbl(i).price_adjustment_id = fnd_api.g_miss_num ) then
l_line_adj_tbl(i).operation := oe_globals.g_opr_Create;
l_delete_prg_lines.exists(l_line_adj_tbl(i).line_id) THEN
l_line_adj_tbl.delete(i);
l_line_adj_Att_tbl.delete(i);
l_line_adj_Att_tbl.delete(i);
l_line_adj_Att_tbl.delete(i);
l_Line_Adj_Assoc_tbl.delete(i);
l_Line_Adj_Assoc_tbl.delete(i);
l_Line_Adj_Assoc_tbl.delete(i);
l_Line_Adj_Assoc_tbl.delete(i);
l_Line_Adj_Assoc_tbl.delete(i);
l_line_tbl_Final.delete(j);
,p_delete => FND_API.G_TRUE
,x_return_status => x_return_status
);
Select e.Pricing_Phase_Id,nvl(p.user_freeze_override_flag,p.freeze_override_flag) freeze_override_flag
from qp_event_Phases e, qp_pricing_phases p
where e.pricing_phase_id = p.pricing_phase_id and
trunc(sysdate) between Trunc(nvl(start_date_active,sysdate)) and
trunc(nvl(End_Date_Active,sysdate))
and e.pricing_event_code IN
(SELECT decode(rownum
,1 ,substr(p_pricing_event,1,instr(l_event_code1,',',1,1)-1)
,2 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,3 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,4 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,5 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1))
,6 ,substr(p_pricing_event , instr(l_event_code1,',',1,rownum-1) + 1,
instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1)))
FROM qp_event_phases
WHERE rownum < 7);
G_Pricing_Phase_Id_Tbl.delete;
SELECT 1 into l_tmp_no from dual where exists (
select 1 from qp_list_headers_b qh
, oe_order_headers_all oh
where qh.currency_code = oh.transactional_curr_code
and oh.header_id = l_header_id
and qh.source_system_code='AMS'
and qh.active_flag = 'Y');
select 'x' from qp_event_phases ep
where ep.pricing_event_code = p_control_rec.pricing_event
and trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
trunc(nvl(end_date_active, sysdate));
select 'x' from qp_event_phases ep
where ep.pricing_event_code IN ('BATCH','BOOK','SHIP')
and trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
trunc(nvl(end_date_active, sysdate));*/
select 'x' from qp_event_phases ep
where instr(p_control_rec.pricing_event||',',
ep.pricing_event_code||',') > 0
and trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
trunc(nvl(end_date_active, sysdate));
select 'x' from dual where
exists (select 'x' from oe_order_lines where header_id = p_header_id
and calculate_price_flag in ('Y','P'));
OE_BULK_PRICE_PVT.Update_Pricing_Attributes(p_line_tbl => x_line_tbl);
l_updated_flag varchar2(1);
l_line_tbl(l_line_index).operation := OE_GLOBALS.G_OPR_UPDATE;
, x_updated_flag =>l_updated_flag);
If (l_updated_flag = 'Y' and l_line_tbl(l_line_index).calculate_price_flag = 'Y') THEN
If l_set_price_flag_on_manual = 'Y' Then
l_line_tbl(l_line_index).calculate_price_flag := 'P';
,x_updated_flag => l_updated_flag);
p_delete_flag => 'N');
l_Line_Adj_Tbl.delete(i);
L_Line_Adj_Tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl.delete(i);
p_selected_records Oe_Globals.Selected_Record_Tbl
,P_price_level varchar2
,p_header_id Number default null
,x_Return_Status out nocopy varchar2
,x_msg_count out nocopy number
,x_msg_data out nocopy varchar2
)
is
l_Price_Control_rec QP_PREQ_GRP.control_record_type;
Select 'Y'
From OE_PRICE_ADJUSTMENTS a, OE_ORDER_LINES_ALL b
Where b.line_id = p_line_id
AND b.header_id = a.header_id
And a.list_line_type_code = 'PRG';
last_record_line_id Number := p_selected_records(p_selected_records.count).id1;
i := p_selected_records.first;
L_line_id := p_selected_records(i).id1;
i := p_selected_records.next(i);
i := p_selected_records.first;
oe_debug_pub.add('id1 : ' || p_selected_records(i).id1);
oe_debug_pub.add('org_id : ' || p_selected_records(i).org_id);
oe_debug_pub.add('id3 : ' || p_selected_records(i).id3);
l_line_id := p_selected_records(i).id1;
l_org_id := p_selected_records(i).org_id;
L_header_id := p_selected_records(i).id3;
L_x_line_tbl.delete;
i := p_selected_records.next(i);
L_x_line_tbl.delete;
IF p_selected_records(1).id3 IS NULL THEN
L_id3 := FALSE;
i := p_selected_records.first;
l_header_id := p_selected_records(i).id1;
l_org_id := p_selected_records(i).org_id;
/*Select booked_flag into l_booked_flag from oe_order_headers_all
where header_id=l_header_id; */
i := p_selected_records.next(i);
i := p_selected_records.first;
l_header_id := p_selected_records(i).id3;
l_org_id := p_selected_records(i).org_id;
Select booked_flag into l_booked_flag from oe_order_headers_all
where header_id=l_header_id;
i := p_selected_records.next(i);
Select 'Y'
From OE_PRICE_ADJUSTMENTS a, OE_ORDER_LINES_ALL b
Where b.line_id = p_line_id
AND b.header_id = a.header_id
And a.list_line_type_code = 'PRG';
Select booked_flag into l_booked_flag from oe_order_headers_all
where header_id=l_header_id;
Purpose : Insert manual overriable adjustment into Oe_Price_Adjustments
Called by : Mass Change pld
Known Issues: Doesn't handle order level manual overriable adjustment
****************************************************************************************************/
Procedure Create_Manual_Adjustments(p_line_id In Number)
As
l_return_stauts Varchar2(15);
Select 'Y' Into l_found
From Dual
Where exists (Select 'X' From Oe_Price_Adjustments
Where line_id = p_line_id
and list_line_id = l_manual_adj_tbl(i).list_line_id);
l_line_adj_tbl(1).update_allowed := l_manual_adj_tbl(i).override_flag;
l_line_adj_tbl(1).updated_flag := 'N';
Select Oe_Price_Adjustments_S.Nextval
Into l_line_adj_tbl(1).price_adjustment_id
From dual;
select ld.automatic_flag, ld.line_index, ld.modifier_level_code,
ld.line_quantity, ld.line_detail_type_code,
ld.CREATED_FROM_LIST_LINE_TYPE list_line_type_code,
ld.list_line_no, ld.operand_value,
ld.CREATED_FROM_LIST_LINE_ID list_line_id,
ld.CREATED_FROM_LIST_HEADER_ID list_header_id,
ld.pricing_phase_id, ll.override_flag, ld.operand_calculation_code,
ld.ADJUSTMENT_AMOUNT, ll.CHARGE_TYPE_CODE, ll.CHARGE_SUBTYPE_CODE,
l.line_type_code, l.line_id, ld.PRICE_BREAK_TYPE_CODE,
ld.pricing_group_sequence
from QP_PREQ_LINES_TMP l, QP_PREQ_LDETS_TMP ld, QP_LIST_LINES ll
where l.line_index = ld.line_index
and ld.CREATED_FROM_LIST_LINE_ID = ll.LIST_LINE_ID
and ld.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
ORDER BY ld.list_line_no; -- bug 6323362
/* --5598523 No need for this code as change done by this logic is not reflected while inserting data
--into qp tables because direct insert is done using global str and hence changes made here are not used.
--In the case of Order level manual adjustments we will need to set calculate price
--flag for all the lines to 'N'and summary line to 'Y'
If p_level = 'ORDER' or l_profile_value = 'Y' Then
l_req_line_tbl(l_req_line_tbl.count).price_flag := 'N';
Select 'x' into l_dummy from dual
Where exists
(select 'x' from
oe_order_price_attribs oopa
where
nvl(oopa.line_id,l_Line_Tbl(line_Tbl_Index).line_id) = l_Line_Tbl(line_Tbl_Index).line_id
and oopa.header_id = l_header_id2);
Select 'x' into l_dummy from dual
where exists(
Select 'X' from oe_order_price_attribs oopa
where oopa.header_id = l_header_id2 and oopa.line_id is null);
l_control_rec.temp_table_insert_flag := 'N';
x_manual_adj_tbl.delete(j);
x_manual_adj_tbl.delete(j);
SELECT 'X'
FROM
qp_list_headers_b qh,
qp_list_lines ql,
qp_pricing_attributes qppr
WHERE ql.list_line_id = qppr.list_line_id
AND ql.automatic_flag = 'N'
AND qh.list_header_id = ql.list_header_id
AND qh.active_flag = 'Y'
AND qh.list_type_code not in ('AGR','PRL')
AND ql.list_line_type_code <> 'PLL'
AND (ql.list_line_type_code IN ('PRG','OID') OR ql.modifier_level_code =
'LINEGROUP')
AND qppr.product_attribute_context='ITEM' -- bug#7488440
AND qppr.product_attribute='PRICING_ATTRIBUTE3' -- bug#7488440
AND qppr.product_attr_value='ALL' -- bug#7488440
AND qppr.pricing_phase_id
IN ( SELECT distinct a.pricing_phase_id
FROM qp_pricing_phases a , qp_event_phases b
WHERE
a.pricing_phase_id = b.pricing_phase_id
-- AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y' OR a.rltd_exists = 'Y') -- bug#7488440
AND b.pricing_event_code in (SELECT decode(rownum
,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 -
instr(p_event_code1,',',1,rownum-1))
,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 -
instr(p_event_code1,',',1,rownum-1))
,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 -
instr(p_event_code1,',',1,rownum-1))
,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 -
instr(p_event_code1,',',1,rownum-1))
,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 -
instr(p_event_code1,',',1,rownum-1)))
FROM qp_event_phases
WHERE rownum < 7))
AND ROWNUM = 1;