[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select sum(Percent) Per_total
From oe_sales_credits sc,
oe_sales_credit_types sct
Where line_id = p_line_id
And sct.sales_credit_type_id = sc.sales_credit_type_id
And sct.quota_flag = 'Y';
SELECT Line_id, item_type_code,line_set_id,
Service_reference_line_id,
inventory_item_id,
service_reference_type_code --Bug 4946843
FROM OE_ORDER_LINES_ALL
WHERE Line_id = l_line_id;
SELECT /* MOAC_SQL_CHANGE */ line_id, header_id
FROM OE_ORDER_LINES
WHERE service_reference_line_id
in (SELECT line_id
FROM oe_order_lines_all
WHERE top_model_line_id = l_line_id1)
AND line_id <> l_line_id and
inventory_item_id = l_temp_inv_item_id;
Select '1', sales_credit_id, nvl(percent,0)
INTO l_temp, l_sales_credit_id, l_q_percent
from oe_sales_credits
Where header_id = i.header_id
AND line_id = i.line_id
And sales_credit_type_id = l_sales_credit_type_id
AND salesrep_id = l_salesrep_id;
Select quota_flag
Into l_new_quota_flag
From oe_sales_credit_types
Where sales_credit_type_id = l_new_sales_credit_type_id;
Select quota_flag
Into l_old_quota_flag
From oe_sales_credit_types
Where sales_credit_type_id = l_old_sales_credit_type_id;
Select nvl(sum(Percent),0) Per_total
Into l_per_total
From oe_sales_credits sc,
oe_sales_credit_types sct
Where header_id = i.header_id
AND line_id = i.line_id
And sct.sales_credit_type_id = sc.sales_credit_type_id
And sct.quota_flag = 'Y';
IF l_operation = OE_GLOBALS.G_OPR_UPDATE THEN
oe_msg_pub.add('In Update');
Select nvl(sum(Percent),0) Per_total
Into l_per_total
From oe_sales_credits sc,
oe_sales_credit_types sct
Where header_id = i.header_id
AND line_id = i.line_id
And sct.sales_credit_type_id = sc.sales_credit_type_id
And sct.quota_flag = 'Y';
l_Line_Scredit_tbl(l_count).Operation := OE_GLOBALS.G_OPR_UPDATE;
ELSIF l_operation = OE_GLOBALS.G_OPR_DELETE
AND l_percent = l_q_percent THEN
l_count := l_count + 1;
l_Line_Scredit_tbl(l_count).Operation := OE_GLOBALS.G_OPR_DELETE;
l_Line_Scredit_tbl.DELETE;
PROCEDURE UPDATE_LINK_TO_LINE_ID
( x_return_status OUT NOCOPY Varchar2
,p_top_model_line_id IN NUMBER
)
IS
BEGIN
null;
END UPDATE_LINK_TO_LINE_ID;
SELECT 'DUPLICATE_DISCOUNT'
INTO l_duplicate
FROM oe_price_adjustments
WHERE header_id = l_header_id
AND discount_id = l_discount_id
AND price_adjustment_id <> Nvl(l_entity_id, -1)
AND line_id IS NULL;
SELECT 'DUPLICATE_DISCOUNT'
INTO l_duplicate
FROM oe_price_adjustments
WHERE header_id = l_header_id
AND discount_id = l_discount_id
AND line_id = l_line_id
AND price_adjustment_id <> Nvl(l_entity_id, -1);
SELECT count(p.price_adjustment_id)
FROM oe_price_adjustments p
WHERE p.header_id = l_header_id
AND (p.line_id = l_line_id
OR p.line_id IS NULL) ;
SELECT p.line_id
FROM oe_price_adjustments p
WHERE p.header_id = l_header_id
AND p.line_id IS NOT NULL
ORDER by p.line_id;
SELECT count(d.name)
INTO l_count
FROM oe_price_adjustments adj,
oe_discount_lines dln,
oe_discounts d
WHERE adj.header_id = l_header_id
AND Nvl(adj.line_id, l_line_id) = l_line_id
AND d.discount_id = adj.discount_id
AND dln.discount_line_id = adj.discount_line_id
AND dln.price IS NOT NULL
AND ROWNUM = 1;
SELECT count(adj.price_adjustment_id)
INTO l_count
FROM oe_price_adjustments adj
WHERE adj.header_id = l_header_id
AND Nvl(adj.line_id, l_line_id) = l_line_id
AND exists
(SELECT 'fixed_price'
FROM oe_discount_lines dln
WHERE dln.discount_line_id = l_discount_line_id
AND dln.price IS NOT NULL)
AND ROWNUM = 1;
SELECT d.name
INTO l_fixed_price
FROM oe_price_adjustments adj,
oe_discount_lines dln,
oe_discounts d
WHERE adj.header_id = l_header_id
AND Nvl(adj.line_id, l_line_id) = l_line_id
AND d.discount_id = adj.discount_id
AND dln.discount_line_id = adj.discount_line_id
AND dln.price IS NOT NULL
AND ROWNUM = 1;
SELECT p.price_adjustment_id, Nvl(p.line_id, -1) line_id
FROM oe_price_adjustments p,
oe_order_lines o
WHERE p.header_id = l_header_id
AND o.header_id = l_header_id
AND (p.line_id = o.line_id
OR p.line_id IS NULL)
ORDER BY p.line_id;
SELECT Nvl(SUM(percent), 0)
FROM oe_price_adjustments p
WHERE header_id = l_header_id
AND line_id IS NULL;
SELECT Nvl(SUM(percent), 0) + l_percent_total
FROM oe_price_adjustments
WHERE header_id = l_header_id
AND line_id = l_line_id;
SELECT NVL( MAX(SUM(PERCENT)), 0) + l_percent_total
FROM OE_PRICE_ADJUSTMENTS P, OE_ORDER_LINES L
WHERE P.HEADER_ID = l_header_id
AND P.LINE_ID IS NOT NULL
AND P.LINE_ID = L.LINE_ID
GROUP BY P.line_id;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Order';
l_select_flag VARCHAR2(1);
OE_DEBUG_PUB.ADD('Inserting Included Items', 2);
, p_calling_action => 'UPDATE'
, p_delayed_request=> FND_API.G_TRUE
, p_msg_count => l_msg_count
, p_msg_data => l_msg_data
, p_return_status => l_return_status
);
/* procedure insert_rma_scredit_adjustment
To insert sales credit of corresponding RMA lines.
if sales credit exists on the existing line, delete them first,
then insert new ones taken from the referenced line.
Price adjustments has been moved to apply change attributes
*/
Procedure INSERT_RMA_SCREDIT_ADJUSTMENT
(p_line_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
l_api_name CONSTANT VARCHAR(30) := 'INSERT_RMA_SCREDIT_ADJUSTMENT';
OE_DEBUG_PUB.ADD('RMA: In INSERT_RMA_SCREDIT_ADJUSTMENT',1);
SELECT header_id, reference_header_id, reference_line_id,split_by
,SOURCE_DOCUMENT_TYPE_ID --added source document id as a part of 6778016
INTO l_header_id,l_ref_header_id, l_ref_line_id, l_split_by
,l_src_doc_type_id --added l_src_doc_type_id as a part of 6778016
FROM oe_order_lines
WHERE line_id = p_line_id;
OE_DEBUG_PUB.ADD('There are no existing sales credits to delete',1);
l_x_Line_Scredit_tbl(I).operation := OE_GLOBALS.G_OPR_DELETE;
l_Line_Scredit_tbl.DELETE;
END IF; /* end delete existing sales credit */
SELECT header_id, reference_header_id, reference_line_id
INTO l_header_id,l_ref_header_id, l_ref_line_id
FROM oe_order_lines
WHERE line_id = p_line_id;
END IF; /* end inserting sales credit */
l_Line_Scredit_tbl.DELETE;
END IF; /* end inserting sales credit */
oe_debug_pub.add('Exit INSERT_RMA_SCREDIT_ADJUSTMENT',1);
, 'INSERT_RMA_SCREDIT_ADJUSTMENT'
);
END INSERT_RMA_SCREDIT_ADJUSTMENT;
l_Line_Adj_tbl(l_counter).operation := OE_GLOBALS.G_OPR_DELETE;
l_Line_Adj_tbl(l_counter+I).updated_flag := NULL;
l_Line_Adj_tbl(l_counter+I).update_allowed := NULL;
l_Line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_tax_rec_out_tbl.delete;
SELECT header_id
INTO l_header_id
FROM oe_order_lines
WHERE line_id = l_request_rec.entity_id;
SELECT header_id
INTO l_header_id
FROM oe_order_lines
WHERE line_id = l_request_rec.entity_id;
PROCEDURE Insert_Set
(p_request_rec IN oe_order_pub.request_rec_type,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(30);
OE_SET_UTIL.Insert_Into_Set
(p_set_request_tbl => p_set_request,
p_Push_Set_Date => 'N',
X_Return_Status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
, 'Insert_Set'
);
End Insert_Set;
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id = p_entity_id;
l_selelect_line_tbl OE_GLOBALS.Selected_Record_Tbl;
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id = p_entity_id;
( p_selected_line_tbl => l_selelect_line_tbl,
p_record_count => 1,
p_set_name => p_fulfillment_set_name,
p_set_type => 'FULFILLMENT',
p_operation => l_action,
p_header_id => l_header_id,
x_Set_Id => l_set_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data);
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND service_bill_option_code = 'FPOM'
AND service_bill_profile_id IS NOT NULL
AND service_start_date is NOT NULL --16621149
AND service_end_date is NOT NULL --16621149
AND NVL(FULFILLED_FLAG,'N') = 'N';
oe_debug_pub.ADD( ' Start Date,End Date are NULL, so Update FirstPeriod Amt and EndDate to NULL');
UPDATE oe_order_lines_all
SET service_first_period_amount = l_first_period_amt,
service_first_period_enddate = l_first_period_enddate
WHERE line_id = p_line_id;
Oe_Order_Util.Update_Global_Picture ( p_Upd_New_Rec_If_Exists => FALSE,
p_header_id => l_line_rec.header_id,
p_old_line_rec => l_old_line_tbl(1),
p_line_rec => l_line_tbl(1),
p_line_id => l_line_rec.line_id,
x_index => l_global_index,
x_return_status => l_return_status );
oe_debug_pub.add('S.F.P.A: after update global picture, status: ' || l_return_status, 1 ) ;
-- Update the current line being processed
UPDATE oe_order_lines_all
SET service_first_period_amount = l_first_period_amt,
service_first_period_enddate = l_first_period_enddate
WHERE line_id = l_rec.line_id;
Oe_Order_Util.Update_Global_Picture ( p_Upd_New_Rec_If_Exists => FALSE,
p_header_id => l_line_rec.header_id,
p_old_line_rec => l_old_line_tbl(1),
p_line_rec => l_line_tbl(1),
p_line_id => l_line_rec.line_id,
x_index => l_global_index,
x_return_status => l_return_status );
oe_debug_pub.add('S.F.P.A: after update global picture, status: ' || l_return_status, 1 ) ;
PROCEDURE Update_shipping
-----------------------------------------------------------------------*/
PROCEDURE Update_shipping
( p_update_shipping_tbl IN OE_ORDER_PUB.request_tbl_type
, p_line_id IN NUMBER
, p_operation IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_update_shipping_index NUMBER := 0;
l_update_lines_tbl OE_ORDER_PUB.request_tbl_type;
l_update_lines_index NUMBER := 0;
oe_debug_pub.add('Entering UTIL.Update_Shipping'||p_line_id, 1);
l_update_shipping_index := p_update_shipping_tbl.FIRST;
WHILE l_update_shipping_index IS NOT NULL
LOOP
IF p_update_shipping_tbl(l_update_shipping_index).request_type
= OE_GLOBALS.G_UPDATE_SHIPPING
THEN
l_update_lines_index := l_update_lines_index + 1;
l_update_lines_tbl(l_update_lines_index)
:= p_update_shipping_tbl(l_update_shipping_index);
l_update_shipping_index := p_update_shipping_tbl.NEXT(l_update_shipping_index);
OE_Shipping_Integration_PVT.Update_Shipping_From_OE
( p_update_lines_tbl => l_update_lines_tbl,
x_return_status => x_return_status);
l_update_shipping_index := p_update_shipping_tbl.FIRST;
WHILE l_update_shipping_index IS NOT NULL
LOOP
IF p_update_shipping_tbl(l_update_shipping_index).request_type
= OE_GLOBALS.G_UPDATE_SHIPPING
THEN
IF NOT(p_line_id = p_update_shipping_tbl(l_update_shipping_index).entity_id AND
p_operation
= p_update_shipping_tbl(l_update_shipping_index).request_unique_key1)
THEN
oe_debug_pub.add
('deleting req '|| p_update_shipping_tbl(l_update_shipping_index).entity_id);
OE_Delayed_Requests_PVT.Delete_Request
(p_entity_code => p_update_shipping_tbl(l_update_shipping_index).entity_code
,p_entity_id => p_update_shipping_tbl(l_update_shipping_index).entity_id
,p_request_Type => p_update_shipping_tbl(l_update_shipping_index).request_type
,p_request_unique_key1
=> p_update_shipping_tbl(l_update_shipping_index).request_unique_key1
,x_return_status => x_return_status);
l_update_shipping_index := p_update_shipping_tbl.NEXT(l_update_shipping_index);
OE_Debug_PUB.Add('Exiting OE_Delayed_Requests_Util.Update_Shipping',1);
, 'Update_Shipping'
);
END Update_Shipping;
OE_Delayed_Requests_PVT.Delete_Request
(p_entity_code => p_ship_confirmation_tbl(l_shipping_index).entity_code
,p_entity_id => p_ship_confirmation_tbl(l_shipping_index).entity_id
,p_request_Type => p_ship_confirmation_tbl(l_shipping_index).request_type
,p_request_unique_key1 => p_ship_confirmation_tbl(l_shipping_index).request_unique_key1
,x_return_status => x_return_status);
, p_deleted_options_tbl IN OE_Order_PUB.request_tbl_type
, p_updated_options_tbl IN OE_Order_PUB.request_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1);
select booked_flag
into l_complete_flag
from oe_order_lines
where line_id = p_top_model_line_id;
p_deleted_options_tbl => p_deleted_options_tbl,
p_updated_options_tbl => p_updated_options_tbl,
p_validate_flag => 'Y',
p_complete_flag => l_complete_flag,
x_valid_config => l_valid_config,
x_complete_config => l_complete_config,
x_return_status => l_return_status);
/* procedure insert_rma_options_included
to insert options and included items
for the corresponding RMA lines.
*/
Procedure INSERT_RMA_OPTIONS_INCLUDED
(p_line_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_number NUMBER := 0;
l_api_name CONSTANT VARCHAR(30) := 'INSERT_RMA_OPTIONS_INCLUDED';
SELECT l.header_id, l.line_id, l.ordered_quantity
FROM oe_order_lines l,mtl_system_items m
WHERE l.top_model_line_id = l_top_model_line_id
AND nvl(l.ato_line_id,1) = nvl(l_ato_line_id,nvl(l.ato_line_id,1))
AND l.link_to_line_id = nvl(l_link_to_line_id,l.link_to_line_id)
AND line_id <> l_reference_line_id
AND l.header_id = l_header_id
AND l.inventory_item_id = m.inventory_item_id
AND nvl(m.returnable_flag,'Y') = 'Y'
AND nvl(l.cancelled_flag,'N') = 'N'
AND m.organization_id =
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')
ORDER BY LINE_NUMBER , SHIPMENT_NUMBER ,NVL(OPTION_NUMBER, -1),
NVL(COMPONENT_NUMBER,-1),NVL(SERVICE_NUMBER,-1);
OE_DEBUG_PUB.ADD('Entering INSERT_RMA_OPTIONS_INCLUDED',1);
select oe_sets_s.nextval into l_fulfillment_set_id from dual;
insert into oe_sets
( SET_ID, SET_NAME, SET_TYPE, HEADER_ID, SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,SCHEDULE_SHIP_DATE, SCHEDULE_ARRIVAL_DATE,
FREIGHT_CARRIER_CODE, SHIPPING_METHOD_CODE,
SHIPMENT_PRIORITY_CODE, SET_STATUS,
CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATE_DATE,
UPDATE_LOGIN, INVENTORY_ITEM_ID,ORDERED_QUANTITY_UOM,
LINE_TYPE_ID,SHIP_TOLERANCE_ABOVE, SHIP_TOLERANCE_BELOW)
values
( l_fulfillment_set_id, to_char(l_fulfillment_set_id),
'FULFILLMENT_SET',l_orig_line_rec.header_id,
null,null, null,null,null,
null,null,null, 0,sysdate,0, sysdate,
0,null,null,null,null,null
);
Insert into oe_line_sets(Line_id, Set_id, SYSTEM_REQUIRED_FLAG)
Values (p_line_id, l_fulfillment_set_id, 'Y');
-- Insert into Fulfillment set
Insert into oe_line_sets(Line_id, Set_id, SYSTEM_REQUIRED_FLAG)
Values (l_line_tbl(I).line_id, l_fulfillment_set_id, 'Y');
-- Insert RMA Sales Credits and Adjustments
INSERT_RMA_SCREDIT_ADJUSTMENT
( x_return_status => l_return_status
,p_line_id => l_line_tbl(I).line_id
);
-- Insert RMA Lot and Serial Numbers
INSERT_RMA_LOT_SERIAL
( x_return_status => l_return_status
,p_line_id => l_line_tbl(I).line_id
);
END IF; /* end inserting lines */
OE_DEBUG_PUB.ADD(' Exiting INSERT_RMA_OPTIONS_INCLUDED',1);
, 'INSERT_RMA_OPTIONS_INCLUDED'
);
END INSERT_RMA_OPTIONS_INCLUDED;
/* procedure insert_rma_lot_serial
to insert lot and serial numbers
for the corresponding RMA lines.
* Bug7195205 : All serial numbers from referenced header are inserted in case
* of partial return RMA
*/
Procedure INSERT_RMA_LOT_SERIAL
(p_line_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_number NUMBER := 0;
l_api_name CONSTANT VARCHAR(30) := 'INSERT_RMA_LOT_SERIAL';
SELECT decode(msi.lot_control_code,2,'Y','N'),
decode(msi.serial_number_control_code,2,'Y',5,'Y',6,'Y','N'),
primary_uom_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id = p_ref_org_id;
SELECT decode(msi.lot_control_code,2,'Y','N'),
decode(msi.serial_number_control_code,2,'Y',5,'Y',6,'Y','N'),
primary_uom_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id =
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID');
SELECT u.lot_number,
ABS(SUM(u.transaction_quantity)) transaction_quantity,
ABS(SUM(u.secondary_transaction_quantity)) secondary_transaction_quantity -- INVCONV
FROM mtl_transaction_lot_numbers u,
mtl_lot_numbers l,
mtl_material_transactions m
WHERE m.transaction_id = u.transaction_id
AND u.inventory_item_id = l_inventory_item_id
AND m.transaction_source_type_id = 2
AND m.trx_source_line_id = l_reference_line_id
AND m.ORGANIZATION_ID = l_ship_from_org_id
AND m.INVENTORY_ITEM_ID = l_inventory_item_id
and u.organization_id=l.organization_id
and u.inventory_item_id=l.inventory_item_id
and u.lot_number = l.lot_number
GROUP BY u.lot_number;
SELECT DISTINCT u.serial_number
FROM mtl_unit_transactions_all_v u,
mtl_material_transactions m
WHERE m.transaction_id = u.transaction_id
AND m.INVENTORY_ITEM_ID = l_inventory_item_id
AND u.serial_number = NVL(l_serial_number,u.serial_number)
AND u.inventory_item_id = l_inventory_item_id
AND m.transaction_source_type_id = 2
AND m.trx_source_line_id = l_reference_line_id
AND m.organization_id = l_ship_from_org_id
AND m.transaction_action_id = 1
AND m.transaction_type_id = 33;
SELECT DISTINCT t.lot_number,
u.serial_number
FROM mtl_unit_transactions_all_v u,
mtl_material_transactions m,
mtl_transaction_lot_val_v t
WHERE u.serial_number = NVL(l_serial_number,u.serial_number)
AND u.INVENTORY_ITEM_ID = t.inventory_item_id
AND t.serial_transaction_id = u.transaction_id
AND t.INVENTORY_ITEM_ID = m.INVENTORY_ITEM_ID
AND m.transaction_id = t.transaction_id
AND m.transaction_source_type_id = 2
AND m.trx_source_line_id = l_reference_line_id
AND m.ORGANIZATION_ID = l_ship_from_org_id
AND m.INVENTORY_ITEM_ID = l_inventory_item_id;
OE_DEBUG_PUB.ADD(' Entering INSERT_RMA_LOT_SERIAL',1);
oe_debug_pub.add('RMA: rma_lot_serial.DELETE_ROW', 2);
l_x_Lot_Serial_tbl(I).operation := OE_GLOBALS.G_OPR_DELETE;
l_Lot_serial_tbl.DELETE;
END IF; /* end delete existing lot serial numbers */
END IF; /* end inserting lot serial numbers */
l_Lot_serial_tbl.DELETE;
OE_DEBUG_PUB.ADD(' Exiting INSERT_RMA_LOT_SERIAL',1);
, 'INSERT_RMA_LOT_SERIAL'
);
END INSERT_RMA_LOT_SERIAL;
l_line_tbl.DELETE(I);
Select list_line_type_code
Into l_list_line_type_code
From qp_list_lines
Where list_line_id = to_number(p_adjust_tbl(I).param3);
PROCEDURE INSERT_SERVICE_FOR_OPTIONS
(p_serviced_line_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_number NUMBER := 0;
l_api_name CONSTANT VARCHAR(30) := 'INSERT_SERVICE_FOR_OPTIONS';
SELECT max(l.service_number) service_number
FROM oe_order_lines l
WHERE l.header_id = l_header_id
AND l.line_number = l_line_number
AND l.shipment_number = l_shipment_number
AND nvl(l.option_number,0) = nvl(l_option_number,0)
AND nvl(l.component_number,0) = nvl(l_component_number,0);
OE_DEBUG_PUB.ADD('Entering INSERT_SERVICE_FOR_OPTIONS',1);
SELECT /* MOAC_SQL_CHANGE */ h.order_number
INTO l_order_number
FROM oe_order_headers_all h, oe_order_lines l
WHERE h.header_id = l.header_id
AND h.header_id = l_orig_line_rec.header_id
AND rownum = 1;
SELECT 'Y'
INTO l_prg
FROM oe_price_adjustments prg, oe_price_adj_assocs a,
oe_price_adjustments p
WHERE prg.line_id = l_reference_line_rec.line_id
AND prg.list_line_type_code = 'PRG'
AND prg.price_adjustment_id = a.price_adjustment_id
AND p.price_adjustment_id = a.rltd_price_adj_id
AND p.line_id = l_orig_line_rec.line_id
AND l_orig_line_rec.item_type_code = 'SERVICE' AND l_orig_line_rec.SUBSCRIPTION_ENABLE_FLAG <> 'Y'
AND ROWNUM <2;
SELECT l.header_id,
l.line_id,
l.shipment_number,
l.line_number,
l.option_number,
l.component_number,
l.service_txn_reason_code,
l.service_txn_comments,
l.service_duration,
l.service_period,
l.service_start_date,
l.service_end_date,
l.service_coterminate_flag,
l.ordered_quantity
FROM oe_order_lines l
WHERE l.top_model_line_id = l_service_reference_line_id
AND l.item_type_code = 'INCLUDED'
AND exists (select null from mtl_system_items mtl where
mtl.inventory_item_id = l.inventory_item_id and
mtl.serviceable_product_flag = 'Y' and
mtl.organization_id=OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID') );
SELECT l.header_id,
l.line_id,
l.shipment_number,
l.line_number,
l.option_number,
l.component_number,
l.service_txn_reason_code,
l.service_txn_comments,
l.service_duration,
l.service_period,
l.service_start_date,
l.service_end_date,
l.service_coterminate_flag,
l.ordered_quantity
FROM oe_order_lines l
WHERE l.top_model_line_id = l_service_reference_line_id
AND l.top_model_line_id <> l.line_id -- For bug 2938790
AND l.item_type_code in ('INCLUDED','CLASS','OPTION', 'KIT') -- For bug 2447402
AND exists (select null from mtl_system_items mtl where
mtl.inventory_item_id = l.inventory_item_id and
mtl.serviceable_product_flag = 'Y' and
mtl.organization_id=OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID') );
OE_Delayed_Requests_PVT.Delete_Request
(p_entity_code => OE_GLOBALS.G_ENTITY_LINE
,p_entity_id => l_child.line_id
,p_request_Type => OE_GLOBALS.G_CASCADE_OPTIONS_SERVICE
,x_return_status => x_del_req_ret_status);
l_line_tbl.DELETE;
END IF; /* End inserting lines */
OE_DEBUG_PUB.ADD('Exiting INSERT_SERVICE_FOR_OPTIONS',1);
, 'INSERT_SERVICE_FOR_OPTIONS'
);
END INSERT_SERVICE_FOR_OPTIONS;
SELECT l.header_id,
l.line_id,
l.service_txn_reason_code,
l.service_txn_comments,
l.service_duration,
l.service_period,
l.service_start_date,
l.service_end_date,
l.service_coterminate_flag,
l.order_quantity_uom,
l.inventory_item_id,
l.subscription_enable_flag
FROM oe_order_lines l
where l.service_reference_line_id=l_top_model_line_id
and l.item_type_code = 'SERVICE'
and l.service_reference_type_code = 'ORDER';
SELECT max(l.service_number) service_number
FROM oe_order_lines l
WHERE l.header_id = l_header_id
AND l.line_number = l_line_number
AND l.shipment_number = l_shipment_number
AND nvl(l.option_number,0) = nvl(l_option_number,0)
AND nvl(l.component_number,0) = nvl(l_component_number,0); --bug 2447402
SELECT /* MOAC_SQL_CHANGE */ h.order_number
INTO l_order_number
FROM oe_order_headers_all h, oe_order_lines l
WHERE h.header_id = l.header_id
AND h.header_id = l_orig_line_rec.header_id
AND rownum = 1;
SELECT 'Y'
INTO l_prg_exist
FROM oe_price_adjustments prg, oe_price_adj_assocs a,
oe_price_adjustments p
WHERE prg.line_id = l_top_model_line_id
AND prg.list_line_type_code = 'PRG'
AND prg.price_adjustment_id = a.price_adjustment_id
AND p.price_adjustment_id = a.rltd_price_adj_id
AND p.line_id = l_child.line_id
AND l_child.SUBSCRIPTION_ENABLE_FLAG <> 'Y'
AND ROWNUM <2;
l_line_tbl.DELETE;
END IF; /* End inserting lines */
when new lines are inserted to a SCHEDULE SET. A set being
a user defined ship or arrival set, or a system defined
ATO or SMC PTO model. When multiple lines are inserted
to the same set, this procedure is called once for all the
lines of the set.
-------------------------------------------------------------------*/
Procedure Schedule_Line
( p_sch_set_tbl IN OE_ORDER_PUB.request_tbl_type
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_ship_set_id NUMBER := null;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT ship_set_id,arrival_set_id
INTO l_ship_set_id,l_arrival_set_id
FROM oe_order_lines_all
WHERE line_id = l_line_rec.ato_line_id;
UPDATE OE_ORDER_LINES_ALL
SET
(SCHEDULE_SHIP_DATE,
SCHEDULE_ARRIVAL_DATE,
SHIP_FROM_ORG_ID,
SHIP_SET_ID,
ARRIVAL_SET_ID) =
(SELECT null,
null,
decode(re_source_flag,'Y',ship_from_org_id,null),
null,
null
FROM OE_ORDER_LINES_ALL
WHERE line_id=l_line_rec.line_id)
WHERE line_id = l_line_rec.line_id;
UPDATE OE_ORDER_LINES_ALL
SET
SCHEDULE_SHIP_DATE = l_old_line_rec.schedule_ship_date,
SCHEDULE_ARRIVAL_DATE = l_old_line_rec.schedule_arrival_date,
SHIP_FROM_ORG_ID = l_old_line_rec.ship_from_org_id,
SHIP_SET_ID = null,
ARRIVAL_SET_ID = null
WHERE line_id = l_line_rec.line_id;
l_tax_rec_out_tbl.delete;
SELECT TAX_VALUE
INTO l_old_total_tax
FROM oe_order_lines_all
WHERE line_id = p_entity_id_tbl(i).entity_id;
oe_debug_pub.add('Updateing tax value from '||TO_CHAR(l_old_total_tax)|| ' To ' ||TO_CHAR(l_total_tax) ,2);
UPDATE OE_ORDER_LINES_ALL
SET TAX_VALUE = l_total_tax,
TAX_LINE_VALUE = l_total_tax - l_tax_on_charge --TaxER
WHERE line_id = p_entity_id_tbl(i).entity_id;
SELECT payment_type_code
INTO l_payment_type_code
FROM oe_order_lines_all
WHERE line_id = l_line_rec.line_id;
Oe_Order_Util.Update_Global_Picture ( p_Upd_New_Rec_If_Exists => FALSE,
p_header_id => l_line_rec.header_id,
p_old_line_rec => l_old_line_tbl(1),
p_line_rec => l_line_tbl(1),
p_line_id => l_line_rec.line_id,
x_index => l_global_index,
x_return_status => l_return_status );
oe_debug_pub.add('8825061: after update global picture, status: ' || l_return_status, 1 ) ;
l_l_line_adj_tbl.delete;
l_l_line_adj_tbl.delete;
AND l_l_line_adj_tbl(J).OPERATION <> OE_GLOBALS.G_OPR_UPDATE
THEN
l_match_flag := 'Y';
l_l_line_adj_tbl(J).OPERATION :=OE_GLOBALS.G_OPR_UPDATE;
UPDATE OE_PRICE_ADJUSTMENTS
SET ADJUSTED_AMOUNT = l_tax_rec_out_tbl(I).tax_amount,
OPERAND = l_tax_rec_out_tbl(I).tax_rate,
tax_Rate_id = l_tax_rec_out_tbl(I).tax_rate_id,
LAST_UPDATE_DATE = sysdate,
APPLIED_FLAG = Nvl(l_tax_rec_out_tbl(I).amount_includes_tax_flag,'N'), --for bug#12895421
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE price_adjustment_id = l_l_line_adj_tbl(J).price_adjustment_id;
l_l_line_adj_tbl(J).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
AND l_l_line_adj_tbl(J).OPERATION <> OE_GLOBALS.G_OPR_UPDATE
THEN
l_match_flag := 'Y';
l_l_line_adj_tbl(J).OPERATION :=OE_GLOBALS.G_OPR_UPDATE;
UPDATE OE_PRICE_ADJUSTMENTS
SET ADJUSTED_AMOUNT = l_tax_rec_out_tbl(I).tax_amount,
OPERAND = l_tax_rec_out_tbl(I).tax_rate,
tax_Rate_id = l_tax_rec_out_tbl(I).tax_rate_id,
LAST_UPDATE_DATE = sysdate,
applied_flag = Nvl(l_tax_rec_out_tbl(I).amount_includes_tax_flag,'N'),--for bug#12895421
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE price_adjustment_id = l_l_line_adj_tbl(J).price_adjustment_id;
l_l_line_adj_tbl(J).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
SELECT OE_PRICE_ADJUSTMENTS_S.nextval INTO l_price_adjustment_id FROM DUAL;
l_adj_rec.last_update_date := SYSDATE;
l_adj_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_adj_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
oe_debug_pub.add('Inserting the Adj record '|| TO_CHAR(l_adj_rec.price_adjustment_id),2);
OE_LINE_ADJ_UTIL.INSERT_ROW(p_Line_Adj_rec => l_adj_rec);
DELETE
FROM OE_PRICE_ADJUSTMENTS
WHERE PRICE_ADJUSTMENT_ID = l_l_line_adj_tbl(J).price_adjustment_id;
SELECT SUM(ADJUSTED_AMOUNT)
INTO l_old_total_tax
FROM oe_price_adjustments
WHERE header_id = p_entity_id_tbl(i).entity_id
AND line_id IS NULL
AND list_line_type_code = 'TAX' ;
oe_debug_pub.add('Updateing tax value from '||TO_CHAR(l_old_total_tax)|| ' To ' ||TO_CHAR(l_total_tax) ,2);
SELECT payment_type_code
INTO l_payment_type_code
FROM oe_order_headers_all
WHERE header_id = l_header_rec.header_id;
SELECT COUNT(1)
INTO l_return
FROM oe_order_lines_all
WHERE 1 =1
AND header_id = l_header_rec.header_id
AND line_category_code = 'RETURN';
l_l_header_adj_tbl.delete;
l_l_header_adj_tbl.delete;
AND l_l_header_adj_tbl(J).OPERATION <> OE_GLOBALS.G_OPR_UPDATE
THEN
l_match_flag := 'Y';
l_l_header_adj_tbl(J).OPERATION :=OE_GLOBALS.G_OPR_UPDATE;
UPDATE OE_PRICE_ADJUSTMENTS
SET ADJUSTED_AMOUNT = l_tax_rec_out_tbl(I).tax_amount,
OPERAND = l_tax_rec_out_tbl(I).tax_rate,
tax_Rate_id = l_tax_rec_out_tbl(I).tax_rate_id,
LAST_UPDATE_DATE = sysdate,
applied_flag = Nvl(l_tax_rec_out_tbl(I).amount_includes_tax_flag,'N') , ----for bug#12895421
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE price_adjustment_id = l_l_header_adj_tbl(J).price_adjustment_id;
l_l_header_adj_tbl(J).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
SELECT OE_PRICE_ADJUSTMENTS_S.nextval INTO l_price_adjustment_id FROM DUAL;
l_h_adj_rec.last_update_date := SYSDATE;
l_h_adj_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_h_adj_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
oe_debug_pub.add('Inserting the Adj record '|| TO_CHAR(l_adj_rec.price_adjustment_id),2);
OE_Header_Adj_Util.INSERT_ROW(p_Header_Adj_rec => l_h_adj_rec);
DELETE
FROM OE_PRICE_ADJUSTMENTS
WHERE PRICE_ADJUSTMENT_ID = l_l_header_adj_tbl(J).price_adjustment_id;
l_tax_rec_out_tbl.delete;
l_tax_rec_out_tbl.delete;
l_l_line_adj_tbl.delete;
l_l_header_adj_tbl.DELETE ;
l_tax_rec_out_tbl.delete;
l_l_line_adj_tbl.delete;
l_l_header_adj_tbl.DELETE;
l_tax_rec_out_tbl.delete;
l_l_line_adj_tbl.delete;
l_l_header_adj_tbl.DELETE;
l_tax_rec_out_tbl.delete;
l_l_line_adj_tbl.delete;
l_l_header_adj_tbl.DELETE;
**--Update the Internal Sales Order with the Req header id, Req line Ids, Req number and line numbers.
**--Check for return status
**--Handle Exceptions
*/
l_int_req_Ret_sts varchar2(1);
SELECT created_by
,org_id
FROM OE_ORDER_HEADERS
WHERE header_id = p_header_id;
SELECT line_id
,order_quantity_uom
,ordered_quantity
,sold_to_org_id
,inventory_item_id
,schedule_ship_date
,org_id
,ship_from_org_id
,subinventory
,source_document_id
,source_document_line_id
,item_type_code
FROM OE_ORDER_LINES
WHERE header_id = p_header_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT b.location_id,
b.organization_id
FROM hz_party_sites_V a
,PO_LOCATION_ASSOCIATIONS b
WHERE a.party_site_use_id = b.SITE_USE_ID
and b.customer_id = p_cust_id
and primary_per_type = 'Y'
and site_use_type = 'SHIP_TO';
Update OE_Order_Headers
set source_document_Id = l_req_header_rec.requisition_header_id
,orig_sys_document_ref = l_req_header_rec.segment1
,source_document_type_id = OE_GLOBALS.G_ORDER_SOURCE_INTERNAL -- i.e 10 for internal
,order_source_id = OE_GLOBALS.G_ORDER_SOURCE_INTERNAL --i.e 10 for internal
Where header_id = p_ord_header_id;
oe_debug_pub.add('auto_create_internal_req after hdr update ',2);
Update Oe_Order_lines
Set source_document_id = l_req_header_rec.requisition_header_id
,source_document_line_id = l_req_line_tbl(k).requisition_line_id
,source_document_type_id = OE_GLOBALS.G_ORDER_SOURCE_INTERNAL -- i.e 10 for internal
,orig_sys_document_ref= l_req_header_rec.segment1
,orig_sys_line_ref = l_req_line_tbl(k).line_num
where oe_order_lines.line_id = l_req_line_tbl(k).source_doc_line_reference;
oe_debug_pub.add('auto_create_internal_req after line update ',2);
SELECT return.price_request_code,
referenced.pricing_quantity,
referenced.price_request_code
FROM OE_ORDER_LINES_ALL return , OE_ORDER_LINES_ALL referenced
WHERE return.line_id = p_line_id
and referenced.line_id = return.reference_line_id;
SELECT parent.header_id,
parent.pricing_quantity,
parent.line_id
FROM OE_ORDER_LINES_ALL child, OE_ORDER_LINES_ALL parent
WHERE child.line_id = p_line_id
and parent.line_id = child.split_from_line_id;
update oe_order_lines_All
set price_request_code = l_ret_price_request_code
where line_id = p_line_id;
SELECT order_source_id, orig_sys_document_ref, sold_to_org_id, change_sequence
INTO l_order_source_id, l_orig_sys_document_ref, l_sold_to_org_id, l_change_sequence
FROM oe_order_headers
WHERE header_id=l_header_id;
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
Insert Into OE_HEADER_ACKS (header_id, acknowledgment_type, last_ack_code, request_id, sold_to_org_id, change_sequence)
Values (l_header_id, l_acknowledgment_type, l_flow_status_code, l_itemkey,
l_sold_to_org_id, l_change_sequence);
oe_debug_pub.add( 'AFTER HEADER INSERT') ;
OE_Delayed_Requests_PVT.G_Delayed_Requests.Delete(l_count_old);
oe_debug_pub.add( 'BEFORE BULK INSERT OF LINES') ;
INSERT INTO OE_LINE_ACKS
(header_id
,line_id
,acknowledgment_type
,last_ack_code
,request_id
,sold_to_org_id
,change_sequence)
VALUES
(l_bulk_line_rec.header_id(j)
,l_bulk_line_rec.line_id(j)
,l_acknowledgment_type
,l_bulk_line_rec.last_ack_code(j)
,l_bulk_line_rec.request_id(j)
,l_bulk_line_rec.sold_to_org_id(j)
,l_bulk_line_rec.change_sequence(j)
);
oe_debug_pub.add( 'AFTER BULK INSERT OF LINES') ;
Procedure Name : Update_Requisition_Info
Input Params : P_Header_id - Primary key of the order header
P_Line_id - Primary key of the order line. This parameter
will be null for order header cancellation
P_Requisition_Header_id - Primary key of the requisition
header
P_Requisition_Line_id - Primary key of the requisition line
p_Line_ids - String variable containing line_ids delimited
by comma ?Q,?R. Will be populated only if it is
a partial order cancellation
p_num_records - Number of total order line records cancelled
while processing partial order cancellation
P_Quantity_Change - It will denote net change in order quantity
with respective single requisition line.
If it is greater than 0 then it is an
increment in the quantity, while if it is
less than 0 then it is a decrement in the
ordered quantity. If it is 0 then it
indicates there is no change in ordered
quantity value
P_New_Schedule_Ship_Date - It will denote the change in
Schedule Ship Date
P_Cancel_Order - It will denote whether internal sales order
is cancelled or not. If it is cancelled then
respective Purchasing api will be called to
trigger the requisition header cancellation.
Output Params : X_Return_Status - The return status of the API
(Expected/Unexpected/Success)
Brief Description : This program unit is added for IR ISO Change
management project, so as to trigger the new
program unit OE_Process_Requisition_Pvt.Updat
e_Internal_Requisition introduced as part of
this project, and responsible for calling several
Purchasing APIs based on the action performed
on the internal sales order header/line.
Possible actions can be:
Header Level FULL Cancellation
Header Level PARTIAL Cancellation
Line Level Cancellation
Line Ordered Quantity update
Line Schedule Ship/Arrival Date update
Line Ordered Quantity and Schedule Ship/Arrival
Date update
-- For details on IR ISO CMS project, please refer to FOL >
-- OM Development > OM GM > 12.1.1 > TDD > IR_ISO_CMS_TDD.doc
*/
Procedure Update_Requisition_Info -- Package Body
( p_header_id IN NUMBER -- Param5 or Entity id
, p_line_id IN NUMBER -- Entity id
, P_Line_ids IN VARCHAR2 -- Long_Param1
, P_num_records IN NUMBER -- Param6
, P_Requisition_Header_id IN NUMBER -- Param3
, P_Requisition_Line_id IN NUMBER DEFAULT NULL -- Param4
, P_Quantity_Change IN NUMBER DEFAULT NULL -- Param1
, P_Quantity2_Change IN NUMBER DEFAULT NULL --Bug 14211120
, P_New_Schedule_Ship_Date IN DATE -- Date_Param1
, P_Cancel_order IN BOOLEAN -- Param2
, x_return_status OUT NOCOPY varchar2
)
IS
--
l_return_status VARCHAR2(30);
oe_debug_pub.ADD('Entering delayed request utility for Update_Requisition_Info',1);
oe_debug_pub.ADD(' Number of shipment Lines updated '||p_num_records,5);
select nvl(ordered_quantity,0)
into l_new_ord_quantity
from oe_order_lines_all
where line_id = p_line_id;
OE_Process_Requisition_Pvt.Update_Internal_Requisition
( P_Header_id => p_header_id
, P_Line_id => p_line_id
, p_line_ids => p_line_ids
, p_num_records => p_num_records
, P_Req_Header_id => P_Requisition_Header_id
, P_Req_Line_id => P_Requisition_Line_id
, P_Quantity_Change => P_Quantity_Change
, P_Quantity2_Change => P_Quantity2_Change --Bug 14211120
, P_New_Schedule_Ship_Date => l_New_Schedule_Ship_Date
, P_Cancel_Order => P_Cancel_order
, P_Cancel_line => l_cancel_line
, X_msg_count => l_msg_count
, X_msg_data => l_msg_data
, X_return_status => l_return_status
);
oe_debug_pub.ADD('Exiting delayed request utility for Update_Requisition_Info',1);
, 'Update_Requisition_Info'
);
END Update_Requisition_Info;