[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
as a single update statement.
------------------------------------------------------------------------ */
Procedure Complete_Config
( p_top_model_line_id IN NUMBER
, x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
SELECT line_id, inventory_item_id, ordered_quantity, item_type_code,
shipped_quantity
FROM oe_order_lines
WHERE header_id = l_header_id
AND open_flag = 'Y'
AND ((top_model_line_id = p_parent_line_id
and line_id <> p_parent_line_id)
OR
(l_item_type_code = OE_GLOBALS.G_ITEM_CLASS
and ato_line_id = p_parent_line_id
and line_id <> p_parent_line_id ));
oe_debug_pub.add('NEW QTY 0 ,NOT CANCELLATION ,SO DELETE',1);
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_DELETE;
l_line_tbl(I).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
UPDATE oe_order_lines
SET config_header_id = null,
config_rev_nbr = null,
configuration_id = null,
lock_control = lock_control + 1
WHERE top_model_line_id = p_parent_line_id;
OE_Config_Pvt.Delete_Config
( p_config_hdr_id => l_parent_line_rec.config_header_id
,p_config_rev_nbr => l_parent_line_rec.config_rev_nbr
,x_return_status => l_return_status);
SELECT line_id
FROM oe_order_lines
WHERE((top_model_line_id = p_top_model_line_id
and line_id <> p_line_id)
OR (ato_line_id = p_ato_line_id and
line_id <> p_line_id ))
AND open_flag = 'Y';
l_line_rec.OPERATION := OE_GLOBALS.G_OPR_UPDATE;
SELECT po_header_id
INTO l_po_header_id
FROM oe_drop_ship_sources
WHERE line_id = l_line_rec.line_id
AND header_id = l_line_rec.header_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
SELECT line_id,config_header_id,config_rev_nbr,
configuration_id,booked_flag
FROM oe_order_lines
WHERE item_type_code = OE_GLOBALS.G_ITEM_MODEL
AND header_id = p_header_id
AND open_flag = 'Y';
Select config_rev_nbr
Into l_config_rev_nbr
From oe_order_lines_all
Where line_id = l_line_id;
SELECT line_id
FROM oe_order_lines
WHERE item_type_code in (OE_GLOBALS.G_ITEM_MODEL,
OE_GLOBALS.G_ITEM_CLASS,
OE_GLOBALS.G_ITEM_KIT)
AND open_flag = 'Y'
AND header_id = p_header_id;
( p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_ratio IN NUMBER
-- 4211654
,p_new_qty in number
,p_old_qty in number
,p_change_reason IN VARCHAR2
,p_change_comments IN VARCHAR2);
PROCEDURE Delete_Children
(p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_parent_item IN VARCHAR2);
PROCEDURE Delete_Parent
(p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_top_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_ui_flag IN VARCHAR2 := 'N');
(p_x_options_tbl IN Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_top_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_ui_flag IN VARCHAR2 := 'N')
RETURN BOOLEAN;
(p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_top_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_ui_flag IN VARCHAR2 := 'N');
p_deleted_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_updated_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_caller IN VARCHAR2 := '',
x_valid_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_complete_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_header_id NUMBER := NULL;
l_updated_options_tbl OE_Order_PUB.request_tbl_type
:= p_updated_options_tbl;
l_deleted_options_tbl OE_Order_PUB.request_tbl_type
:=p_deleted_options_tbl;
Oe_Process_Options_Pvt.Selected_Options_Tbl_Type;
l_any_insert NUMBER := 0;
SELECT header_id, ordered_quantity, booked_flag,
upgraded_flag, config_header_id, config_rev_nbr,
source_document_type_id,order_source_id
INTO l_header_id, l_model_qty, l_booked_flag,
l_upgraded_flag ,l_config_header_id, l_config_rev_nbr,
l_source_document_type_id, l_order_source_id
FROM OE_ORDER_LINES
WHERE line_id = p_model_line_id;
SELECT count (*)
INTO l_any_insert
FROM OE_ORDER_LINES
WHERE top_model_line_id = p_model_line_id
AND line_id <> p_model_line_id
AND config_header_id IS NULL
AND config_rev_nbr IS NULL;
OE_Debug_Pub.Add('lines w/o cfg hdr '|| l_any_insert, 3);
IF l_any_insert = 0 THEN
BEGIN
SELECT 1
INTO l_any_insert
FROM cz_config_details_v cz, oe_order_lines oe
WHERE oe.line_id = p_model_line_id
AND oe.ordered_quantity <> cz.quantity
AND oe.config_header_id = cz.config_hdr_id
AND oe.config_rev_nbr = cz.config_rev_nbr
AND oe.component_code = cz.component_code;
oe_debug_pub.add( 'QTY SELECT: '|| SQLERRM , 1 ) ;
END IF; -- booked flag and new inserts check.
l_updated_options_tbl.COUNT = 0 AND
l_deleted_options_tbl.COUNT = 0 AND
l_any_insert = 0 AND
OE_ORDER_COPY_UTIL.G_ORDER_LEVEL_COPY <> 1 AND
l_config_rev_nbr is not null AND
l_config_header_id is not null
THEN
IF l_debug_level > 0 THEN
OE_Debug_Pub.Add('Skip Batch Validation ');
p_deleted_options_tbl => l_deleted_options_tbl,
p_updated_options_tbl => l_updated_options_tbl,
p_model_qty => l_model_qty,
p_xml_hdr => l_xml_hdr,
x_out_xml_msg => l_xml_message,
x_return_status => l_return_status );
p_deleted_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_updated_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_caller IN VARCHAR2 := '',
x_valid_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_complete_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_options_tbl Oe_Process_Options_Pvt.Selected_Options_Tbl_Type;
l_updated_options_tbl OE_Order_PUB.request_tbl_type;
l_deleted_options_tbl OE_Order_PUB.request_tbl_type;
IF p_deleted_options_tbl.COUNT > 0 OR
p_updated_options_tbl.COUNT > 0 OR
l_msg_data = 'Y' THEN
SELECT component_code
INTO l_model_component
FROM oe_order_lines
WHERE line_id = p_model_line_id;
l_updated_options_tbl := p_updated_options_tbl;
l_deleted_options_tbl := p_deleted_options_tbl;
,p_x_updated_options_tbl => l_updated_options_tbl
,p_x_deleted_options_tbl => l_deleted_options_tbl);
oe_debug_pub.add('CALLING CASCADE_UPDATES_DELETES '||
l_deleted_options_tbl.COUNT || '-' ||
l_updated_options_tbl.COUNT, 3);
Cascade_Updates_Deletes
( p_model_line_id => p_model_line_id
,p_model_component => l_model_component
,p_x_options_tbl => l_options_tbl
,p_deleted_options_tbl => l_deleted_options_tbl
,p_updated_options_tbl => l_updated_options_tbl
,x_return_status => l_return_status);
Procedure Name : Cascade_Updates_Deletes
Description : This API will be used when there are updates and deletes
to option/class of a configuration and we want to cascade the operation to
either upwards or downwards.
-- handle updates
-- if any class is updated, modify the l_options-tbl so that, options
-- qty cascades, set operation of update on the updated once, and none
-- on the others
-- handle deletes
-- if any class deleted, delete all options underit.
-- if a option is deleted, i.e. the only option in a class
-- delete the class from l_options_tbl
Note:
If a kit under a model is updated/deleted, since its included items
are not present in l_options_tbl, so we can not use cascade_qty
or delete_options for it.
We handle this seperately, before call to process_order in
handle_dml in OEXVOPTB.pls. We also handle the case of included
items under a PTO class in handle_dml.
As part of pack J ato options decimal quantity project
decimal_ratio_check is moved to OE_VALIDATE_LINE
for Decimal quantities for ATO Options Project
the decimal ratio check will be part of line entity
validation
-----------------------------------------------------------------------*/
PROCEDURE Cascade_Updates_Deletes
( p_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_x_options_tbl IN OUT NOCOPY
Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_deleted_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL
,p_updated_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL
,p_ui_flag IN VARCHAR2 := 'N'
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
I NUMBER;
l_deleted_options_tbl OE_Order_PUB.request_tbl_type
:= p_deleted_options_tbl;
oe_debug_pub.add('ENTERING CASCADE_UPDATES_DELETES' , 1);
oe_debug_pub.add('FIRST UPDATES' , 2 );
IF p_updated_options_tbl.COUNT > 0 THEN
SELECT ordered_quantity
INTO l_qty
FROM oe_order_lines
WHERE line_id = p_model_line_id;
I :=p_updated_options_tbl.FIRST;
l_req_rec := p_updated_options_tbl(I);
oe_debug_pub.add('OPTIONS UPDATED ' , 4 );
oe_debug_pub.add('QTY = 0 AND NO CANCELLATION , SO DELETE' , 1);
p_x_options_tbl(l_index).operation := OE_GLOBALS.G_OPR_DELETE;
l_deleted_options_tbl(nvl(l_deleted_options_tbl.LAST, 0) + 1) :=
l_req_rec; -- ok, since params match
oe_debug_pub.add('REGULAR CODE OF UPDATES'|| L_REQ_REC.PARAM3 , 1);
I := p_updated_options_tbl.NEXT(I);
oe_debug_pub.add('NOW DELETES' , 1);
I :=l_deleted_options_tbl.FIRST;
oe_debug_pub.add('THERE ARE ITEMS DELETED'|| L_REQ_REC.PARAM2 , 1);
l_req_rec := l_deleted_options_tbl(I);
oe_debug_pub.add('CLASS IS DELETED'|| L_REQ_REC.PARAM2 , 1);
Delete_Children( p_x_options_tbl => p_x_options_tbl
,p_component_code => l_req_rec.param2
,p_parent_item => l_req_rec.param10 ); -- 3563690
oe_debug_pub.add('OPTION/CLASS/KIT IS DELETED' , 1);
Delete_Parent(p_x_options_tbl => p_x_options_tbl
,p_component_code => l_req_rec.param2
,p_top_model_line_id => p_model_line_id
,p_model_component => p_model_component
,p_ui_flag => p_ui_flag);
I := l_deleted_options_tbl.NEXT(I);
oe_debug_pub.add('LEAVING CASCADE_UPDATES_DELETES' , 1);
oe_debug_pub.add('EXCEPTION IN CASCADE_UPDATES_DELETES'|| SQLERRM , 1);
END Cascade_Updates_Deletes;
( p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_ratio IN NUMBER
-- 4211654
,p_new_qty IN number
,p_old_qty IN number
,p_change_reason IN VARCHAR2
,p_change_comments IN VARCHAR2)
IS
I NUMBER;
AND p_x_options_tbl(I).operation <> OE_GLOBALS.G_OPR_DELETE
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('OPTION TO BE UPDATED '
|| P_X_OPTIONS_TBL (I).COMPONENT_CODE , 3 );
IF p_x_options_tbl(I).operation <> OE_GLOBALS.G_OPR_INSERT THEN
p_x_options_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
PROCEDURE: Delete_Children
3563690 => If operation is insert for a child line, do not set the
operation to delete. Instead, raise an exception
Changed the signature of the procedure to accept the name
of the class.
----------------------------------------------------------------------*/
PROCEDURE Delete_Children
( p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_parent_item IN VARCHAR2)
IS
I NUMBER;
oe_debug_pub.add('ENTERING DELETE_CHILDREN' , 1);
oe_debug_pub.add('OPTION TO BE DELETED '
|| P_X_OPTIONS_TBL (I).COMPONENT_CODE , 3 );
IF p_x_options_tbl(I).operation <> OE_GLOBALS.G_OPR_INSERT
THEN
p_x_options_tbl(I).operation := OE_GLOBALS.G_OPR_DELETE;
FND_MESSAGE.SET_NAME('ONT','ONT_CONFIG_INSERT_DELETE');
oe_debug_pub.add('OPERATION IS INSERT IN PROCEDURE DELETE_CHILDREN');
oe_debug_pub.add('LEAVING DELETE_CHILDREN' , 1);
oe_debug_pub.add('ECXEPTION IN DELETE_CHILDREN'|| SQLERRM , 1);
END Delete_Children;
PROCEDURE Delete_Parent
delete the parent only if the only option under it is getting deleted.
Never delete the model line.
----------------------------------------------------------------------*/
PROCEDURE Delete_Parent
(p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_top_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_ui_flag IN VARCHAR2 := 'N')
IS
I NUMBER;
oe_debug_pub.add('ENTERING DELETE_PARENT' , 1);
oe_debug_pub.add('PARENT TO BE DELETED '
|| P_X_OPTIONS_TBL (I).COMPONENT_CODE , 3 );
p_x_options_tbl(I).operation := OE_GLOBALS.G_OPR_DELETE;
Delete_Parent(p_x_options_tbl => p_x_options_tbl
,p_component_code => l_parent
,p_top_model_line_id => p_top_model_line_id
,p_model_component => p_model_component
,p_ui_flag => p_ui_flag);
oe_debug_pub.add('LEAVING DELETE_PARENT'|| P_COMPONENT_CODE , 1);
oe_debug_pub.add('ECXEPTION IN DELETE_PARENT'|| SQLERRM , 1);
END Delete_Parent;
(p_x_options_tbl IN OUT NOCOPY Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_top_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_ui_flag IN VARCHAR2 := 'N')
IS
I NUMBER;
p_x_options_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
delete the parent only if the only option under it is getting deleted.
Never delete the model line.
p_component_code is the component_code of the child, for which
we are trying tofind out if this is the only child to its parent.
----------------------------------------------------------------------*/
FUNCTION No_More_Children_Left
(p_x_options_tbl IN Oe_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_component_code IN VARCHAR2
,p_top_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_ui_flag IN VARCHAR2 := 'N')
RETURN BOOLEAN
IS
l_parent VARCHAR2(1000);
AND p_x_options_tbl(I).operation <> OE_GLOBALS.G_OPR_DELETE
AND NOT(p_x_options_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE AND
p_x_options_tbl(I).ordered_quantity = 0) -- cancel
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('OPTION UNDER SAME PARENT EXISTS' , 3 );
SELECT /* MOAC_SQL_CHANGE */ count(*)
INTO l_count
FROM oe_order_lines_all
WHERE top_model_line_id = p_top_model_line_id
AND item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
AND open_flag = 'Y'
AND link_to_line_id =
(SELECT line_id
FROM oe_order_lines_all
WHERE top_model_line_id = p_top_model_line_id
AND component_code = l_parent
AND open_flag = 'Y' );
p_deleted_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_updated_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_validate_flag IN VARCHAR2 := 'Y',
p_complete_flag IN VARCHAR2 := 'Y',
p_caller IN VARCHAR2 := '',
x_valid_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_complete_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_header_id NUMBER;
SELECT header_id, config_header_id, config_rev_nbr, ordered_quantity,
configuration_id, order_source_id, orig_sys_document_ref,
orig_sys_line_ref, orig_sys_shipment_ref, change_sequence,
source_document_type_id, source_document_id, source_document_line_id
INTO l_header_id, l_config_hdr_id, l_config_rev_nbr, l_model_qty,
l_configuration_id, l_order_source_id, l_orig_sys_document_ref,
l_orig_sys_line_ref, l_orig_sys_shipment_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id, l_source_document_line_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_model_line_id;
p_deleted_options_tbl => p_deleted_options_tbl,
p_updated_options_tbl => p_updated_options_tbl,
p_caller => p_caller,
x_valid_config => x_valid_config,
x_complete_config => x_complete_config,
x_return_status => x_return_status);
p_deleted_options_tbl => p_deleted_options_tbl,
p_updated_options_tbl => p_updated_options_tbl,
p_caller => p_caller,
x_valid_config => x_valid_config,
x_complete_config => x_complete_config,
x_return_status => x_return_status);
options that are newly inserted/updated/deleted
from the model.
SPC validation_status :
CONFIG_PROCESSED constant NUMBER :=0;
p_deleted_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_updated_options_tbl IN OE_Order_PUB.request_tbl_type
:= OE_Order_Pub.G_MISS_REQUEST_TBL,
p_model_qty IN NUMBER,
p_xml_hdr IN VARCHAR2,
x_out_xml_msg OUT NOCOPY /* file.sql.39 change */ LONG ,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
l_html_pieces CZ_BATCH_VALIDATE.CFG_OUTPUT_PIECES;
l_option CZ_BATCH_VALIDATE.INPUT_SELECTION;
l_db_options_tbl OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE;
SELECT ol.component_code, ol.configuration_id
INTO l_component_code, l_configuration_id
FROM oe_order_lines ol, cz_config_details_v cz
WHERE ol.line_id = p_model_line_id
AND cz.component_code = ol.component_code
AND cz.config_hdr_id = ol.config_header_id
AND cz.config_rev_nbr = ol.config_rev_nbr
AND cz.quantity <> ol.ordered_quantity;
oe_debug_pub.add('cursor GET NEWLY INSERTED OPTIONS '|| l_flag , 2 );
oe_debug_pub.add('OUT OF NEWLY INSERTED OPTIONS LOOP' , 2 );
oe_debug_pub.add('NO. OF UPDATED OPTIONS: '
||P_UPDATED_OPTIONS_TBL.COUNT , 2 );
oe_debug_pub.add('ENTERING LOOP TO PASS UPDATED OPTIONS' , 2 );
FOR I IN 1..p_updated_options_tbl.COUNT
LOOP
IF l_debug_level > 0 THEN
oe_debug_pub.add('WITHIN THE LOOP OF P_UPDATED_OPTIONS_TBL' , 2 );
l_req_rec := p_updated_options_tbl(l_rec_index);
oe_debug_pub.add('UPDATE LINE_ID: ' || L_REQ_REC.ENTITY_ID , 2 );
|| ' OPTION UPDATED TO A QTY OF: ' || L_REQ_REC.PARAM5 , 3 );
oe_debug_pub.add('LEAVING LOOP TO PASS UPDATED OPTIONS' , 2 );
oe_debug_pub.add('NO. OF DELETED OPTIONS: '
||P_DELETED_OPTIONS_TBL.COUNT , 2 );
oe_debug_pub.add('ENTERING LOOP TO PASS DELETED OPTIONS' , 2 );
FOR I IN 1..p_deleted_options_tbl.COUNT
LOOP
IF l_debug_level > 0 THEN
oe_debug_pub.add('WITHIN THE LOOP OF P_DELETED_OPTIONS_TBL' , 2 );
l_req_rec := p_deleted_options_tbl(l_rec_index);
oe_debug_pub.add('OUT OF INSERT/UPDATE/DELETE OPTIONS LOOPS ' , 1);
l_html_pieces.DELETE;
l_selection_line_id NUMBER;
OE_Config_Pvt.Delete_Config
( p_config_hdr_id => l_config_header_id
,p_config_rev_nbr => l_config_rev_nbr
,x_return_status => l_return_status_del);
SELECT line_id
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = l_header_id AND
TOP_MODEL_LINE_ID = p_top_model_line_id
ORDER BY line_number,shipment_number,nvl(option_number,-1),nvl(component_number,-1);
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id = p_top_model_line_id;
SELECT line_id
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = l_header_id AND
TOP_MODEL_LINE_ID = l_top_model_line_id AND
ATO_LINE_ID = p_ato_line_id
ORDER BY line_number,shipment_number,nvl(option_number,-1);
SELECT top_model_line_id ,header_id
INTO l_top_model_line_id,l_header_id
FROM oe_order_lines_all
WHERE line_id = p_ato_line_id;
SELECT line_id
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = l_header_id AND
TOP_MODEL_LINE_ID = l_top_model_line_id AND
LINK_TO_LINE_ID = p_line_id AND
ITEM_TYPE_CODE = OE_GLOBALS.G_ITEM_INCLUDED
ORDER BY line_number,shipment_number,nvl(option_number,-1);
SELECT top_model_line_id ,header_id
INTO l_top_model_line_id,l_header_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
PROCEDURE update_component_number
(p_line_id IN NUMBER ,
p_top_model_line_id IN NUMBER ,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
Also, insert_into_set call removed, moved to generic
model_option_defaulting API.
2508632
Copy Calculate Price Flag of included items from Parent Line.
Bug 2869052 :
Default_Child_Line procedure would be called only if there are
any new included items to be created. If the call returns an
error an exception would be raised. New variable l_default_child_line
has been created.
-----------------------------------------------------------------------*/
FUNCTION Process_Included_Items
(p_line_rec IN OE_ORDER_PUB.line_rec_type
:= OE_ORDER_PUB.G_MISS_LINE_REC,
p_line_id IN NUMBER := FND_API.G_MISS_NUM,
p_freeze IN BOOLEAN,
p_process_requests IN BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2
IS
-- process_order in variables
l_control_rec OE_GLOBALS.Control_Rec_Type;
SELECT
component_item_id,
component_sequence_id,
extended_quantity,
component_code,
PRIMARY_UOM_CODE,
sort_order
FROM bom_explosions be
WHERE
be.explosion_type = 'INCLUDED'
AND be.plan_level >= 0
AND be.extended_quantity > 0
AND be.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id
AND be.EFFECTIVITY_DATE <= p_std_comp_freeze_date
AND be.DISABLE_DATE > p_std_comp_freeze_date
AND be.COMPONENT_ITEM_ID <> be.TOP_ITEM_ID
AND NOT EXISTS
( SELECT 'X'
FROM oe_order_lines l
WHERE l.top_model_line_id = p_top_model_line_id
AND l.link_to_line_id = l_parent_line_rec.line_id
AND l.component_code = be.component_code
AND l.open_flag = 'Y')
ORDER BY sort_order;
CURSOR update_included_items(p_top_bill_sequence_id IN NUMBER,
p_top_model_line_id IN NUMBER,
p_top_model_quantity IN NUMBER,
p_std_comp_freeze_date IN DATE)
IS
SELECT
oel.line_id, be.extended_quantity * p_top_model_quantity
FROM oe_order_lines oel, bom_explosions be
WHERE oel.top_model_line_id = p_top_model_line_id
AND oel.link_to_line_id = l_parent_line_rec.line_id
AND oel.item_type_code = 'INCLUDED'
AND be.explosion_type = 'INCLUDED'
AND be.plan_level >= 0
AND be.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id
AND be.EFFECTIVITY_DATE <= p_std_comp_freeze_date
AND be.DISABLE_DATE > p_std_comp_freeze_date
AND be.COMPONENT_ITEM_ID <> be.TOP_ITEM_ID
AND be.component_code = oel.component_code
AND oel.ordered_quantity/p_top_model_quantity <> be.extended_quantity
AND oel.open_flag = 'Y';
SELECT
l.line_id
FROM oe_order_lines l
WHERE l.link_to_line_id = l_parent_line_rec.line_id
AND l.top_model_line_id = p_top_model_line_id
AND l.item_type_code = 'INCLUDED'
AND l.open_flag = 'Y'
AND NOT EXISTS
( SELECT 'X'
FROM bom_explosions be
WHERE be.component_code = l.component_code
AND be.explosion_type = 'INCLUDED'
AND be.plan_level >= 0
AND be.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id
AND be.EFFECTIVITY_DATE <= p_std_comp_freeze_date
AND be.DISABLE_DATE > p_std_comp_freeze_date);
GOTO UPDATE_EXP_DATE;
SELECT bill_sequence_id
INTO l_parent_component_sequence_id
FROM bom_bill_of_materials
WHERE ASSEMBLY_ITEM_ID = l_parent_line_rec.inventory_item_id
AND ORGANIZATION_ID = l_validation_org
AND ALTERNATE_BOM_DESIGNATOR IS NULL;
SELECT max(component_number)
INTO l_component_number
FROM oe_order_lines
WHERE link_to_line_id = p_line_id
AND top_model_line_id = l_parent_line_rec.top_model_line_id;
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_validation_org;
oe_debug_pub.add('INSERTING....'|| L_LINE_REC.COMPONENT_CODE , 2 );
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_line_rec.line_id
FROM dual;
SELECT count(*)
INTO l_top_model_quantity
FROM oe_order_lines
WHERE item_type_code = 'INCLUDED'
AND top_model_line_id = l_parent_line_rec.top_model_line_id
AND link_to_line_id = l_parent_line_rec.line_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
OPEN update_included_items
(p_top_bill_sequence_id => l_parent_component_sequence_id,
p_top_model_line_id => l_parent_line_rec.top_model_line_id,
p_top_model_quantity => l_top_model_quantity,
p_std_comp_freeze_date => l_explosion_date);
FETCH update_included_items
INTO l_line_rec.line_id, l_line_rec.ordered_quantity;
EXIT WHEN update_included_items%NOTFOUND;
CLOSE update_included_items;
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add('INCLUDED ITEMS GOT DELETED' , 1);
update_component_number
(p_line_id => p_line_id,
p_top_model_line_id => l_parent_line_rec.top_model_line_id,
x_return_status => l_return_status);
<>
IF nvl(p_freeze, FALSE) = TRUE OR
l_freeze_macd_kit THEN
-- Update the explosion date on the model line.
BEGIN
UPDATE OE_ORDER_LINES_ALL
set explosion_date = l_explosion_date,
lock_control = lock_control + 1
WHERE line_id = l_parent_line_rec.line_id;
Procedure Name : update_component_number
Description : This procedure is written to update the
component_number on the included items,
if any of them is deleted.
-----------------------------------------------------------------------*/
PROCEDURE update_component_number(p_line_id IN NUMBER ,
p_top_model_line_id IN NUMBER ,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
CURSOR comp_number IS
SELECT line_id
FROM oe_order_lines
WHERE link_to_line_id = p_line_id
AND top_model_line_id = p_top_model_line_id;
oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_COMPONENT_NUMBER' , 1);
UPDATE oe_order_lines
SET component_number = l_component_number,
lock_control = lock_control + 1
WHERE line_id = l_line_id;
oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_COMPONENT_NUMBER' , 1);
END update_component_number;
SELECT line_id
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = l_header_id
AND (TOP_MODEL_LINE_ID = l_top_model_line_id OR
( LINK_TO_LINE_ID = p_link_to_line_id AND
ITEM_TYPE_CODE = OE_GLOBALS.G_ITEM_INCLUDED AND
TOP_MODEL_LINE_ID = p_top_model_line_id) OR
( ATO_LINE_ID = p_ato_line_id AND
TOP_MODEL_LINE_ID = p_top_model_line_id))
ORDER BY line_number,shipment_number,nvl(option_number,-1);
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id=ll_line_id;
SELECT line_id, inventory_item_id, ordered_item, component_code
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
AND open_flag = 'Y'
AND (component_code is null OR
component_sequence_id is null OR
sort_order is null OR
order_quantity_uom is null
);
SELECT creation_date, component_code, component_sequence_id,
inventory_item_id, ordered_quantity, ordered_item
INTO l_rev_date, l_component_code, l_model_seq_id,
l_top_item_id, l_exp_quantity, l_model_ordered_item
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
oe_debug_pub.add('EXCEPTION IN SELECT' , 1);
SELECT bill_sequence_id
into l_model_seq_id
FROM bom_explosions
WHERE COMPONENT_ITEM_ID = l_top_item_id
AND ORGANIZATION_ID = l_validation_org
AND PLAN_LEVEL = 0
AND effectivity_date <= l_rev_date
AND disable_date > l_rev_date
AND explosion_type = l_stdcompflag ;
SELECT component_code, component_sequence_id, sort_order,
primary_uom_code
INTO l_component_code, l_component_seq_id, l_sort_order,
l_uom_code
FROM bom_explosions
WHERE component_item_id = line_rec.inventory_item_id
AND explosion_type = Oe_Config_Util.OE_BMX_ALL_COMPS
AND top_bill_sequence_id = l_model_seq_id
AND effectivity_date <= l_rev_date
AND disable_date > l_rev_date
AND organization_id = l_validation_org
AND component_code = line_rec.component_code;
SELECT component_code, component_sequence_id, sort_order,
primary_uom_code
INTO l_component_code, l_component_seq_id, l_sort_order,
l_uom_code
FROM bom_explosions
WHERE component_item_id = line_rec.inventory_item_id
AND explosion_type = Oe_Config_Util.OE_BMX_ALL_COMPS
AND top_bill_sequence_id = l_model_seq_id
AND effectivity_date <= l_rev_date
AND disable_date > l_rev_date
AND organization_id = l_validation_org;
oe_debug_pub.add('SELECT COMP_CODE FAILED , NO DATA FOUND ' , 1);
oe_debug_pub.add('SELECT COMP_CODE FAILED , TOO_MANY ROWS ' , 1);
oe_debug_pub.add('SELECT COMP_CODE FAILED , OTHERS ' , 1);
UPDATE oe_order_lines
SET component_code = l_component_code,
component_sequence_id = l_component_seq_id,
sort_order = l_sort_order,
order_quantity_uom = l_uom_code,
lock_control = lock_control + 1
WHERE line_id = line_rec.line_id;
SELECT top_model_line_id, ordered_quantity, order_quantity_uom
INTO l_top_model_line_id, l_ordered_quantity, l_order_quantity_uom
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT item_type_code, ato_line_id, ordered_item,inventory_item_id
INTO l_item_type_code, l_ato_line_id, l_ordered_item,l_inv_item_id
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT inventory_item_id,line_id,header_id,
RTRIM(line_number||'.'||shipment_number||'.'||
option_number||'.'||component_number||'.'||
service_number,'.'),source_type_code
INTO l_config_id,l_config_line_id,l_config_header_id,
l_line_num,l_source_type
FROM oe_order_lines
-- Bug#5026787: Start:- ato_line_id should be used instead of top_model_line_id.
-- WHERE top_model_line_id = p_line_id
WHERE ato_line_id = p_line_id
-- Bug#5026787: End
AND item_type_code = 'CONFIG';
SELECT po_header_id
INTO l_po_header_id
FROM oe_drop_ship_sources
WHERE line_id = l_config_line_id
AND header_id = l_config_header_id;
oe_debug_pub.add('AFTER SELECT STMT.' , 2 );
SELECT item_type_code,header_id, top_model_line_id, line_id
INTO l_item_type_code,l_header_id, l_top_model_line_id, l_line_id
FROM oe_order_lines
WHERE line_id = oe_line_security.g_record.line_id;
SELECT count(*)
INTO l_count
FROM OE_ORDER_LINES
WHERE top_model_line_id = oe_line_security.g_record.line_id
AND line_id <> oe_line_security.g_record.line_id
AND header_id = l_header_id;
SELECT item_type_code, ato_line_id, ordered_item
INTO l_item_type_code, l_ato_line_id, l_ordered_item
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT distinct 1
INTO l_valid
FROM oe_order_lines_all oel,
mtl_system_items msi
WHERE oel.line_id = p_line_id
AND oel.inventory_item_id = msi.base_item_id
AND msi.inventory_item_id = p_config_item_id;
Procedure Name : Update_Comp_Seq_Id
Description : API for CTO to do a direct update on
oe_order_lines w/o calling process_order.
-----------------------------------------------------------------------*/
PROCEDURE Update_Comp_Seq_Id
( p_line_id IN NUMBER
,p_comp_seq_id IN NUMBER
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_COMP_SEQ_ID' , 1);
UPDATE oe_order_lines
SET component_sequence_id = p_comp_seq_id
,last_update_date = sysdate
,last_updated_by = FND_Global.User_Id
,last_update_login = FND_Global.Login_Id
,lock_control = lock_control + 1
where line_id = p_line_id;
oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_COMP_SEQ_ID' , 1);
oe_debug_pub.add('NO_DATA_FOUND IN UPDATE_COMP_SEQ_ID' , 1);
oe_debug_pub.add('OTHERS EXCEPTION IN UPDATE_COMP_SEQ_ID' , 1);
END Update_Comp_Seq_Id;
Procedure Name : Update_Visible_Demand_Flag
Description : API for CTO to do a direct update on
oe_order_lines w/o calling process_order.
-----------------------------------------------------------------------*/
PROCEDURE Update_Visible_Demand_Flag
( p_ato_line_id IN NUMBER
,p_visible_demand_flag IN VARCHAR2 := 'N'
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_VISIBLE_DEMAND_FLAG' , 1);
UPDATE oe_order_lines
SET visible_demand_flag = p_visible_demand_flag
,last_update_date = sysdate
,last_updated_by = FND_Global.User_Id
,last_update_login = FND_Global.Login_Id
,lock_control = lock_control + 1
where ato_line_id = p_ato_line_id;
oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_VISIBLE_DEMAND_FLAG' , 1);
oe_debug_pub.add('NO_DATA_FOUND IN UPDATE_VISIBLE_DEMAND_FLAG' , 1);
oe_debug_pub.add('OTHERS EXCEPTION IN UPDATE_VISIBLE_DEMAND_FLAG' , 1);
END Update_Visible_Demand_Flag;
Procedure Name : Update_Mfg_Comp_Seq_Id
Description : API for CTO to do a direct update on
oe_order_lines w/o calling process_order.
-----------------------------------------------------------------------*/
PROCEDURE Update_Mfg_Comp_Seq_Id
( p_ato_line_id IN NUMBER
,p_mfg_comp_seq_id IN NUMBER
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_MFG_COMP_SEQ_ID' , 1);
UPDATE oe_order_lines_all
SET mfg_component_sequence_id = p_mfg_comp_seq_id
,last_update_date = sysdate
,last_updated_by = FND_Global.User_Id
,last_update_login = FND_Global.Login_Id
,lock_control = lock_control + 1
where ato_line_id = p_ato_line_id;
oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_MFG_COMP_SEQ_ID' , 1);
oe_debug_pub.add('NO_DATA_FOUND IN UPDATE_MFG_COMP_SEQ_ID' , 1);
oe_debug_pub.add('OTHERS EXCEPTION IN UPDATE_MFG_COMP_SEQ_ID' , 1);
END Update_Mfg_Comp_Seq_Id;
Procedure Name : Update_Model_Group_Number
Description : API for CTO to do a direct update on
oe_order_lines w/o calling process_order.
-----------------------------------------------------------------------*/
PROCEDURE Update_Model_Group_Number
( p_ato_line_id IN NUMBER
,p_model_group_number IN NUMBER
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_MODEL_GROUP_NUMBER' , 1);
UPDATE oe_order_lines
SET model_group_number = p_model_group_number
,last_update_date = sysdate
,last_updated_by = FND_Global.User_Id
,last_update_login = FND_Global.Login_Id
,lock_control = lock_control + 1
where ato_line_id = p_ato_line_id;
oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_MODEL_GROUP_NUMBER' , 1);
oe_debug_pub.add('NO_DATA_FOUND IN UPDATE_MODEL_GROUP_NUMBER' , 1);
oe_debug_pub.add('OTHERS EXCEPTION IN UPDATE_MODEL_GROUP_NUMBER' , 1);
END Update_Model_Group_Number;
Procedure Name : Update_Cto_Columns
Description : API for CTO to do a direct update on
oe_order_lines w/o calling process_order.
-----------------------------------------------------------------------*/
PROCEDURE Update_Cto_Columns
( p_ato_line_id IN NUMBER
,p_request_id IN NUMBER
,p_program_id IN NUMBER
,p_prog_update_date IN DATE
,p_prog_appl_id IN NUMBER
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_CTO_COLUMNS' , 1);
UPDATE oe_order_lines
SET request_id = p_request_id
,program_id = p_program_id
,program_update_date = p_prog_update_date
,program_application_id = p_prog_appl_id
,last_update_date = sysdate
,last_updated_by = FND_Global.User_Id
,last_update_login = FND_Global.Login_Id
,lock_control = lock_control + 1
where ato_line_id = p_ato_line_id;
oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_CTO_COLUMNS' , 1);
oe_debug_pub.add('NO_DATA_FOUND IN UPDATE_CTO_COLUMNS' , 1);
oe_debug_pub.add('OTHERS EXCEPTION IN UPDATE_CTO_COLUMNS' , 1);
END Update_Cto_Columns;
SELECT ato_line_id
INTO l_ato_line_id
FROM oe_order_lines
WHERE line_id = p_request_tbl(I).param1;
SELECT ordered_item, item_type_code,inventory_item_id
INTO l_ordered_item, l_item_type_code,l_inv_item_id
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND component_code = p_component_code
AND rownum = 1;
SELECT ordered_item, item_type_code,inventory_item_id
INTO l_ordered_item, l_item_type_code,l_inv_item_id
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
additional columns from parent in case of direct insert of
class line.
bug 2015511:
added cancelled_flag := 'N' in direct_save defaulting.
2150536 : moved the ato/smc/set specific defaulting to
here in default_child_line.
bug 2208039: copy dff from parent to child.
Dropship for config: populate the source_type from parent if
ato_line_id not null.
bug 2311690: get all reqd. attributes for ato under pto
from the parent ato.
Bug 2454658: Raise Error if Top Model line id or
header id are NULL.
Bug 1282873: Assign override_atp_date_code from the parent to child
for ato model.
Bug 2511313: For flexfield defaulting.
The call to OE_Validate_Line.Validate_Flex is not for validation
but to default the flex field segments, this call should be made
after the ont_copy_model_dff logic.
Bug 2703023: Setting calculate price flag to Y when direct save
related profile is set to Yes
Bug 2869052: copy dff from model to child has been extended to all
callers and the validate_flex is called with validation level FULL
and we raise an exception if it returns an error.
Bug 3060043: Enabling the code to default blanket number,blanket
version number and blanket line number for Config Items.
Blanket Line number and Version Number for the Child lines will be
defaulted only when blanket number is defined on the parent line.
Otherwise it should not.
This code was added by Srini to support CONFIG ITEMS for PACK-J.
bug fix 3056512: ship to, bill to and request date not cascaded for
non SMC models if caller provides a value.
MACD: Different components of a container model should be allowed to
have different line types when order is received from upstream
sales application
-------------------------------------------------------------------*/
PROCEDURE Default_Child_Line
( p_parent_line_rec IN OE_Order_Pub.Line_Rec_Type
,p_x_child_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
,p_direct_save IN BOOLEAN := FALSE
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
p_x_child_line_rec.last_update_date := p_parent_line_rec.last_update_date;
p_x_child_line_rec.last_updated_by := p_parent_line_rec.last_updated_by;
SELECT user_item_description
INTO p_x_child_line_rec.user_item_description
FROM oe_order_lines
WHERE line_id = p_x_child_line_rec.ato_line_id;
SELECT source_type_code, project_id, task_id,
ship_from_org_id, ship_to_org_id,
schedule_ship_date, schedule_arrival_date,
request_date, shipping_method_code,
freight_carrier_code, invoice_to_org_id,
firm_demand_flag, override_atp_date_code,
ship_to_contact_id,intmed_ship_to_org_id --bug 7041018,7175458
INTO p_x_child_line_rec.source_type_code,
p_x_child_line_rec.project_id,
p_x_child_line_rec.task_id,
p_x_child_line_rec.ship_from_org_id,
p_x_child_line_rec.ship_to_org_id,
p_x_child_line_rec.schedule_ship_date,
p_x_child_line_rec.schedule_arrival_date,
p_x_child_line_rec.request_date,
p_x_child_line_rec.shipping_method_code,
p_x_child_line_rec.freight_carrier_code,
p_x_child_line_rec.invoice_to_org_id,
p_x_child_line_rec.firm_demand_flag,
p_x_child_line_rec.override_atp_date_code,
p_x_child_line_rec.ship_to_contact_id, --bug7041018,7175458
p_x_child_line_rec.intermed_ship_to_org_id --bug 7041018,7175458
FROM oe_order_lines
WHERE line_id = p_x_child_line_rec.ato_line_id;
This API will check if the delete and update quantity operation
performed on a Included item should be allowed or not.
We will not allow any user delete/update quantity.
We will allow system changes ex: cascading.
We have to write a pl/sql api because we want the system to be able to
do the operations.
result of 1 means constrained.
Process_Included_Items procedure will set the security_check to
false, before calling process_order.
--##1922440 bug fix.
----------------------------------------------------------------------*/
PROCEDURE Is_Included_Item_Constrained
( p_application_id IN NUMBER,
p_entity_short_name IN VARCHAR2,
p_validation_entity_short_name IN VARCHAR2,
p_validation_tmplt_short_name IN VARCHAR2,
p_record_set_short_name IN VARCHAR2,
p_scope IN VARCHAR2,
x_result OUT NOCOPY /* file.sql.39 change */ NUMBER )
IS
l_item_type_code VARCHAR2(30);
SELECT item_type_code, model_remnant_flag, pre_exploded_flag
INTO l_item_type_code, l_model_remnant_flag, l_pre_exploded_flag -- DOO Preexploded Kit ER 9339742
FROM oe_order_lines
WHERE line_id = oe_line_security.g_record.line_id;
p_line_tbl(I).operation = 'UPDATE' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('LOGGING REQ TO VERIFY_PAYMENT' , 3 );
select 'Y'
into l_serviceable_item
from mtl_system_items mtl
where mtl.inventory_item_id = p_line_tbl(I).inventory_item_id
and mtl.organization_id = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
and mtl.serviceable_product_flag='Y'
and rownum = 1;
select 'Y'
into l_serviced_model
from oe_order_lines
where item_type_code = 'SERVICE'
and service_reference_line_id = p_line_tbl(I).top_model_line_id
and service_reference_type_code = 'ORDER'
and rownum = 1;
SELECT count(*)
INTO l_count
FROM oe_order_lines
WHERE line_category_code <> 'RETURN'
-- the condition below is commented for bug 3441504
-- AND booked_flag = 'Y'
AND (header_id = p_header_id OR
(top_model_line_id = p_top_model_line_id AND
p_line_id = p_top_model_line_id) OR
(ato_line_id = p_ato_line_id AND
top_model_line_id = p_top_model_line_id AND
p_line_id = p_ato_line_id AND
p_item_type_code in ('MODEL', 'CLASS')) OR
line_id = p_line_id)
AND ((source_type_code = 'EXTERNAL' AND
shippable_flag = 'Y') OR
(ato_line_id = line_id AND
item_type_code in ('STANDARD', 'OPTION', 'INCLUDED')) OR --9775352
item_type_code = 'CONFIG')
AND OPEN_FLAG = 'Y';
SELECT constraint_type , message
FROM cz_config_messages
WHERE config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr;
SELECT nvl(ordered_item,inventory_item_id )
INTO l_message_text
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT creation_date
INTO l_creation_date
FROM oe_order_lines
WHERE header_id = l_header_id
AND line_set_id = p_model_line_rec.line_set_id
AND split_from_line_id is NULL;
SELECT creation_date
INTO l_creation_date
FROM oe_order_lines_history
WHERE header_id = l_header_id
AND line_set_id = p_model_line_rec.line_set_id
AND split_from_line_id is NULL
AND ROWNUM = 1;
SELECT header_id, creation_date,
line_set_id, split_from_line_id
INTO l_header_id, l_creation_date,
l_line_set_id, l_split_from_line_id
FROM oe_order_lines
WHERE line_id = p_model_line_id;
SELECT creation_date
INTO l_creation_date
FROM oe_order_lines
WHERE header_id = l_header_id
AND line_set_id = l_line_set_id
AND split_from_line_id is NULL;
SELECT creation_date
INTO l_creation_date
FROM oe_order_lines_history
WHERE header_id = l_header_id
AND line_set_id = l_line_set_id
AND split_from_line_id is NULL
AND ROWNUM = 1;
SELECT nvl(booked_flag, 'N'), booked_date
INTO x_frozen_model_bill, x_config_effective_date
FROM oe_order_headers
WHERE header_id = l_header_id;
SELECT nvl(booked_flag, 'N') INTO x_frozen_model_bill
FROM oe_order_headers
WHERE header_id = l_header_id;
SELECT line_id FROM oe_order_lines_all
WHERE item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
AND top_model_line_id = c_top_model_line_id
AND top_model_line_id <> line_id;
SELECT
component_item_id,
component_sequence_id,
extended_quantity,
component_code,
PRIMARY_UOM_CODE,
sort_order,
null OM_Parent_Line_id,
null OM_Parent_Inventory_Item_id
-- To OM fields, setting the NULL values so as to complete the record structure
FROM bom_explosions be
WHERE
be.explosion_type = OE_GLOBALS.G_ITEM_INCLUDED --'INCLUDED'
AND be.plan_level >= 0
AND be.extended_quantity > 0
AND be.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id
AND be.EFFECTIVITY_DATE <= p_std_comp_freeze_date
AND be.DISABLE_DATE > p_std_comp_freeze_date
AND be.COMPONENT_ITEM_ID <> be.TOP_ITEM_ID
ORDER BY sort_order; -- Here the sort_order is performing a very important
SELECT bom.bill_sequence_id, oel.line_id, oel.inventory_item_id
FROM bom_bill_of_materials bom, oe_order_lines_all oel
WHERE bom.ASSEMBLY_ITEM_ID = oel.inventory_item_id
AND oel.top_model_line_id = p_top_model_line_id
AND oel.header_id = p_header_id
AND oel.item_type_code in (OE_GLOBALS.G_ITEM_CLASS,OE_GLOBALS.G_ITEM_KIT,OE_GLOBALS.G_ITEM_MODEL)
AND bom.ORGANIZATION_ID = p_validation_org
AND bom.ALTERNATE_BOM_DESIGNATOR IS NULL;
SELECT line_id
FROM oe_order_lines_all
WHERE link_to_line_id = c_parent_line_id
AND top_model_line_id = c_top_model_line_id
AND header_id = c_header_id
AND item_type_code = OE_GLOBALS.G_ITEM_INCLUDED;
UPDATE OE_order_Lines_All
SET Component_Sequence_id = l_Bom_Explosion_Tbl(j).component_sequence_id
, Sort_Order = l_Bom_Explosion_Tbl(j).sort_order
, Component_Code = l_Bom_Explosion_Tbl(j).component_code
, Explosion_Date = l_explosion_date
, Link_To_Line_id = l_Bom_Explosion_Tbl(j).OM_Parent_Line_id
, lock_control = lock_control + 1
WHERE Line_id = l_included_item_tbl(i).line_id;
UPDATE oe_order_lines
SET component_number = l_component_number,
lock_control = lock_control + 1
WHERE line_id = l_comp_line_id;
UPDATE OE_order_Lines_All
SET Explosion_Date = l_explosion_date
, lock_control = lock_control + 1
WHERE Line_id = l_parent_line_rec.line_id;