[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure update_link_to_line_id
( p_top_model_line_id IN NUMBER
,p_remnant_flag IN VARCHAR2
,p_config_hdr_id IN NUMBER);
Procedure update_ato_line_attributes
( p_top_model_line_id IN NUMBER
,p_ui_flag IN VARCHAR2
,p_config_hdr_id IN NUMBER);
PROCEDURE Handle_Inserts
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_config_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2 := 'Y');
PROCEDURE Handle_Inserts_Old
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE);
PROCEDURE Handle_Updates
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2);
PROCEDURE Handle_Updates_old
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2);
PROCEDURE Handle_Deletes
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2);
PROCEDURE Handle_Deletes_Old
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2);
insert,update,or deletethem from order_lines.
We will open three cursors :
the first to pass an operation of INSERT
for records which do not exist
=> handle_inserts proc
The second to pass an operation of UPDATE
for records which already exist.
=> handle_updates prc
The third to pass an operation of DELETE
for records which exist but are no longer selected.
=> handle_deletes proc
get values from cz_config_details_v
viz. comp_code, qty, cfg_hdr_id, rev_no
and bom_explosions viz.
component_sequence_id, sort_order, bom_item_type,
bill_sequence_id, top_bill_sequence_id
call process_order and update the links.
If the p_ui_flag 'Y', we set the control_rec.process to TRUE
This means when options are created using configurator,
we want all the delayed requests to be processed.
when order_import calls process_order to create options,
control_rec.process is set to false so that delayed requests
do not get processed in the recursive call to process_order
after batch validation.
Change Record:
Bug 2181376: explode bill is not required since in handle_inserts
procedure we will selecet all required data from cz_config_details_v
and do not need to join with bom_explosions anymore. This change is
also useful for multiple instance project.
--------------------------------------------------------------------*/
Procedure Process_Config(p_header_id IN NUMBER
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_top_model_line_id IN NUMBER
,p_ui_flag IN VARCHAR2 :='Y'
,p_config_instance_tbl IN
csi_datastructures_pub.instance_cz_tbl := G_CONFIG_INSTANCE_TBL
,x_change_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
-- general, column_changes and and cz's delete api stuff
l_direct_save BOOLEAN;
OE_MSG_PUB.update_msg_context
( p_entity_code => 'LINE'
,p_entity_id => l_model_line_rec.line_id
,p_header_id => l_model_line_rec.header_id
,p_line_id => l_model_line_rec.line_id
,p_orig_sys_document_ref => l_model_line_rec.orig_sys_document_ref
,p_orig_sys_document_line_ref => l_model_line_rec.orig_sys_line_ref
,p_orig_sys_shipment_ref => l_model_line_rec.orig_sys_shipment_ref
,p_change_sequence => l_model_line_rec.change_sequence
,p_source_document_id => l_model_line_rec.source_document_id
,p_source_document_line_id => l_model_line_rec.source_document_line_id
,p_order_source_id => l_model_line_rec.order_source_id
,p_source_document_type_id => l_model_line_rec.source_document_type_id);
SELECT quantity, config_item_id
INTO l_model_new_qty, l_model_line_rec.configuration_id
FROM cz_config_details_v
WHERE config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr
AND inventory_item_id = l_model_line_rec.inventory_item_id;
UPDATE OE_ORDER_LINES_ALL
SET CONFIGURATION_ID = l_model_line_rec.configuration_id
WHERE inventory_item_id = l_model_line_rec.inventory_item_id
AND LINE_ID = l_model_line_rec.line_id;
oe_debug_pub.add( 'QTY SELECT: '|| SQLERRM , 1 ) ;
oe_debug_pub.add('UPDATE MODEL WITH NEW QTY '|| L_MODEL_NEW_QTY,1);
l_model_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Handle_Inserts(p_model_line_rec => l_model_line_rec
,p_config_hdr_id => p_config_hdr_id
,p_config_rev_nbr => p_config_rev_nbr
,p_x_line_tbl => l_line_tbl
,p_config_instance_tbl => p_config_instance_tbl
,p_x_class_line_tbl => l_class_line_tbl
,p_direct_save => l_direct_save
,p_ui_flag => p_ui_flag);
Handle_Updates(p_model_line_rec => l_model_line_rec
,p_config_hdr_id => p_config_hdr_id
,p_config_rev_nbr => p_config_rev_nbr
,p_x_line_tbl => l_line_tbl
,p_x_class_line_tbl => l_class_line_tbl
,p_direct_save => FALSE
,p_ui_flag => p_ui_flag);
Handle_Deletes(p_model_line_rec => l_model_line_rec
,p_config_hdr_id => p_config_hdr_id
,p_config_rev_nbr => p_config_rev_nbr
,p_x_line_tbl => l_line_tbl
,p_x_class_line_tbl => l_class_line_tbl
,p_direct_save => FALSE
,p_ui_flag => p_ui_flag);
,p_update_columns => TRUE
,x_return_status => l_return_status);
oe_debug_pub.add('do not delete, special', 1);
Delete_Config( p_config_hdr_id => p_config_hdr_id
,p_config_rev_nbr => p_config_rev_nbr
,x_return_status => l_return_status_del);
Delete_Config( p_config_hdr_id => l_model_line_rec.config_header_id
,p_config_rev_nbr => l_model_line_rec.config_rev_nbr
,x_return_status => l_return_status_del);
END IF; -- do no delete
PROCEDURE Handle_Inserts
if a component is present in cz_config_details_v but not present
in oe_order_lines, we need to insert it.
Change Record:
Bug 2181376: explode bill is not required since in this
procedure we will selecet all required data from cz_config_details_v
and do not need to join with bom_explosions anymore. This change is
also useful for multiple instance project
Bug 2869052 :
Default_Child_Line procedure would be called only if there are
any new class lines to be created and direct save shoule be true.
If the call returns an error an exception would be raised. New
variable l_default_child_line has been created.
MACD: Line Type Support - modified the cursor to select the line_type
also and then if it null and not missing, assigning the child line
record's line_type_id to the retreived line_type
Bug 3611416
Send reason for CREATE operation also, will be required if there is
a require reason constraint for versioning during create operation.
bug3578056
for a new configuration, the IB fields should be NULL for the
top model and child lines
-------------------------------------------------------------------*/
PROCEDURE Handle_Inserts
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_config_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2 := 'Y')
IS
CURSOR config_ins_cursor IS
SELECT c.component_code, c.quantity
,c.uom_code, c.inventory_item_id
,c.instance_hdr_id, c.instance_rev_nbr
,c.component_sequence_id, c.bom_sort_order
,c.bom_item_type, c.config_item_id
,c.line_type
FROM CZ_CONFIG_DETAILS_V c
WHERE c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr
AND NOT EXISTS
( SELECT 'X'
FROM oe_order_lines l
WHERE l.top_model_line_id = p_model_line_rec.line_id
AND l.component_code = c.component_code
AND l.configuration_id = c.config_item_id
AND l.open_flag = 'Y')
ORDER BY c.component_code;
Print_Time('Handle_Inserts start time');
UPDATE oe_order_lines oe
SET ( configuration_id , sort_order ) =
(SELECT config_item_id , bom_sort_order --bug6628691
FROM cz_config_details_v
WHERE config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr
AND component_code = oe.component_code
)
WHERE top_model_line_id = p_model_line_rec.line_id
AND config_header_id is NULL
AND configuration_id is NULL
AND item_type_code in ('MODEL', 'CLASS', 'OPTION', 'KIT')
AND open_flag = 'Y';
oe_debug_pub.add( 'CONFIGURATION_ID UPDATED '|| SQL%ROWCOUNT ,3);
oe_debug_pub.add( 'CRM 1:CONFIGURATION_ID NOT UPDATED ' , 3 ) ;
UPDATE oe_order_lines oe
SET ( configuration_id , sort_order ) =
(SELECT config_item_id , bom_sort_order --bug6628691
FROM cz_config_details_v
WHERE config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr
AND component_code = oe.component_code
)
WHERE top_model_line_id = p_model_line_rec.line_id
AND configuration_id is NULL
AND item_type_code in ('MODEL', 'CLASS', 'OPTION', 'KIT')
AND open_flag = 'Y';
oe_debug_pub.add( 'CRM 2:CONFIGURATION_ID NOT UPDATED ' , 3 ) ;
Handle_Inserts_Old
(p_model_line_rec => p_model_line_rec
,p_config_hdr_id => p_config_hdr_id
,p_config_rev_nbr => p_config_rev_nbr
,p_x_line_tbl => p_x_line_tbl
,p_x_class_line_tbl => p_x_class_line_tbl
,p_direct_save => p_direct_save);
SELECT concatenated_segments
INTO l_concatenated_segments
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = config_rec.inventory_item_id
AND organization_id = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
oe_debug_pub.add('INSERTING COMP CODE: '||CONFIG_REC.COMPONENT_CODE,1);
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_class_line_rec.line_id
FROM DUAL;
Print_Time('Handle_Inserts end time');
oe_debug_pub.add( 'EXCEPTION IN HANDLE_INSERTS'|| SQLERRM , 1 ) ;
END Handle_Inserts;
PROCEDURE Handle_Inserts_Old
if a component is present in cz_config_details_v but not present
in oe_order_lines, we need to insert it.
Change Record:
Bug 2181376: explode bill is not required since in this
procedure we will selecet all required data from cz_config_details_v
and do not need to join with bom_explosions anymore. This change is
also useful for multiple instance project.
Bug 2869052 :
Default_Child_Line procedure would be called only if there are
any new class lines to be created and direct save shoule be true.
If the call returns an error an exception would be raised. New
variable l_default_child_line has been created.
-------------------------------------------------------------*/
PROCEDURE Handle_Inserts_Old
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE)
IS
CURSOR config_ins_cursor IS
SELECT c.component_code, c.quantity
,c.uom_code, c.inventory_item_id
,c.component_sequence_id, c.bom_sort_order
,c.bom_item_type
FROM CZ_CONFIG_DETAILS_V c
WHERE c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr
AND NOT EXISTS
( SELECT 'X'
FROM oe_order_lines l
WHERE l.top_model_line_id = p_model_line_rec.line_id
AND l.component_code = c.component_code
AND l.open_flag = 'Y')
ORDER BY c.component_code;
Print_Time('Handle_Inserts start time');
SELECT concatenated_segments
INTO l_concatenated_segments
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = config_rec.inventory_item_id
AND organization_id = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
oe_debug_pub.add('INSERTING COMP CODE: ' || CONFIG_REC.COMPONENT_CODE,1);
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_class_line_rec.line_id
FROM DUAL;
Print_Time('Handle_Inserts_Old end time');
oe_debug_pub.add( 'EXCEPTION IN HANDLE_INSERTS_OLD'|| SQLERRM , 1 ) ;
END Handle_Inserts_Old;
PROCEDURE Handle_Updates
If quantity of a component is different in oe_order_lines and
cz_config_details_v, we need to update that component.
for config UI only: if there is a constraint on qty change,
should we pass a hardcoded reason/comment, or should we fail?
we should fail.
MACD: Compare the line type in OM and CZ and if different
select into cursor. Also, set the child line record's line_type to
the selected value from cz
------------------------------------------------------------*/
PROCEDURE Handle_Updates
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2)
IS
CURSOR config_upd_cursor IS
SELECT distinct
l.line_id
,c.component_code
,c.quantity
,l.ordered_quantity
,l.cancelled_quantity -- 12695580
,l.item_type_code
,c.line_type
,c.bom_sort_order
FROM CZ_CONFIG_DETAILS_V c, oe_order_lines l
WHERE c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr
AND (c.quantity <> l.ordered_quantity OR
c.line_type <> l.line_type_id OR
c.bom_sort_order <> l.sort_order)
AND l.top_model_line_id = p_model_line_rec.line_id
AND l.component_code = c.component_code
AND l.configuration_id = c.config_item_id
AND l.open_flag = 'Y';
Print_Time('Handle_Updates start time');
Handle_Updates_Old
(p_model_line_rec => p_model_line_rec
,p_config_hdr_id => p_config_hdr_id
,p_config_rev_nbr => p_config_rev_nbr
,p_x_line_tbl => p_x_line_tbl
,p_x_class_line_tbl => p_x_class_line_tbl
,p_direct_save => FALSE
,p_ui_flag => p_ui_flag);
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Print_Time('Handle_Updates end time');
oe_debug_pub.add( 'EXCEPTION IN HANDLE_UPDATES'|| SQLERRM , 1 ) ;
END Handle_Updates;
PROCEDURE Handle_Updates_Old
If quantity of a component is different in oe_order_lines and
cz_config_details_v, we need to update that component.
for config UI only: if there is a constraint on qty change,
should we pass a hardcoded reason/comment, or should we fail?
we should fail.
------------------------------------------------------------*/
PROCEDURE Handle_Updates_Old
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2)
IS
CURSOR config_upd_cursor IS
SELECT distinct
l.line_id
,c.component_code
,c.quantity
,l.ordered_quantity
,l.item_type_code
FROM CZ_CONFIG_DETAILS_V c, oe_order_lines l
WHERE c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr
AND c.quantity <> l.ordered_quantity
AND l.top_model_line_id = p_model_line_rec.line_id
AND l.component_code = c.component_code
AND l.open_flag = 'Y';
Print_Time('Handle_Updates_Old start time');
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Print_Time('Handle_Updates_Old end time');
oe_debug_pub.add( 'EXCEPTION IN HANDLE_UPDATES_OLD'|| SQLERRM , 1 ) ;
END Handle_Updates_Old;
PROCEDURE Handle_Deletes
If a component exists in oe_order_lines, but does not exist
in cz_config_details_v, we need to delete that component.
Change Record:
bug 1939531: to not call check_if_cancellation
if first configuration is yet getting saved for the first
time and link_to_line_id is not yet populated.
do not call the check_if_cancellation id p_ui_flag is 'Y'.
This is because,
1) you can not enter reason and comment in configurator, so
if cancellation constraint is on, delete will fail.
2) configuraor will take care of cascading change to
child and parent lines, so we do not have to check in
oe_order_lines.
----------------------------------------------------------*/
PROCEDURE Handle_Deletes
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2)
IS
CURSOR config_del_cursor IS
SELECT l.line_id, l.item_type_code, l.link_to_line_id,
l.component_code, nvl(l.cancelled_flag, 'N') cancelled_flag
FROM oe_order_lines l
WHERE l.top_model_line_id = p_model_line_rec.line_id
AND (l.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
l.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
l.item_type_code = OE_GLOBALS.G_ITEM_KIT)
AND l.open_flag = 'Y'
AND
(NOT EXISTS
( SELECT 'X'
FROM CZ_CONFIG_DETAILS_V c
WHERE c.component_code = l.component_code
AND c.config_item_id = l.configuration_id
AND c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr )
OR EXISTS
( SELECT 'X'
FROM CZ_CONFIG_DETAILS_V c
WHERE c.component_code = l.component_code
AND c.config_item_id = l.configuration_id
AND c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr
AND c.quantity = 0));
Print_Time('Handle_Deletes start time');
Handle_Deletes_Old
( p_model_line_rec => p_model_line_rec
,p_config_hdr_id => p_config_hdr_id
,p_config_rev_nbr => p_config_rev_nbr
,p_x_line_tbl => p_x_line_tbl
,p_x_class_line_tbl => p_x_class_line_tbl
,p_direct_save => FALSE
,p_ui_flag => p_ui_flag);
Is_Cancel_OR_Delete
( p_line_id => config_rec.line_id
,p_change_reason => l_change_reason
,p_change_comments => l_change_comments
,x_cancellation => l_cancellation
,x_line_rec => l_line_rec);
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add('LINE_ID TO BE DELETED: ' || CONFIG_REC.LINE_ID ,1);
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Print_Time('Handle_Deletes end time');
oe_debug_pub.add( 'EXCEPTION IN HANDLE_DELETES'|| SQLERRM , 1 ) ;
END Handle_Deletes;
PROCEDURE Handle_Deletes_Old
If a component exists in oe_order_lines, but does not exist
in cz_config_details_v, we need to delete that component.
Change Record:
bug 1939531: to not call check_if_cancellation
if first configuration is yet getting saved for the first
time and link_to_line_id is not yet populated.
do not call the check_if_cancellation id p_ui_flag is 'Y'.
This is because,
1) you can not enter reason and comment in configurator, so
if cancellation constraint is on, delete will fail.
2) configuraor will take care of cascading change to
child and parent lines, so we do not have to check in
oe_order_lines.
----------------------------------------------------------*/
PROCEDURE Handle_Deletes_Old
( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
,p_config_hdr_id IN NUMBER
,p_config_rev_nbr IN NUMBER
,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
,p_direct_save IN BOOLEAN := FALSE
,p_ui_flag IN VARCHAR2)
IS
CURSOR config_del_cursor IS
SELECT l.line_id, l.item_type_code, l.link_to_line_id,
l.component_code
FROM oe_order_lines l
WHERE l.top_model_line_id = p_model_line_rec.line_id
AND (l.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
l.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
l.item_type_code = OE_GLOBALS.G_ITEM_KIT)
AND l.open_flag = 'Y'
AND
(NOT EXISTS
( SELECT 'X'
FROM CZ_CONFIG_DETAILS_V c
WHERE c.component_code = l.component_code
AND c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr )
OR EXISTS
( SELECT 'X'
FROM CZ_CONFIG_DETAILS_V c
WHERE c.component_code = l.component_code
AND c.config_hdr_id = p_config_hdr_id
AND c.config_rev_nbr = p_config_rev_nbr
AND c.quantity = 0));
Print_Time('Handle_Deletes_Old start time');
Is_Cancel_OR_Delete
( p_line_id => config_rec.line_id
,p_change_reason => 'CONFIGURATOR'
,p_change_comments => 'Changes in Configurator Window'
,x_cancellation => l_cancellation
,x_line_rec => l_line_rec);
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add('LINE_ID TO BE DELETED: '||CONFIG_REC.LINE_ID,1);
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
Print_Time('Handle_Deletes_Old end time');
oe_debug_pub.add( 'EXCEPTION IN HANDLE_DELETES_OLD'|| SQLERRM , 1 ) ;
END Handle_Deletes_Old;
is actually a complete cancellation. If so, we will not delete
the lines from oe_order_lines and they will be closed instead.
Change Record:
bug 2191666: the sqls and logic modified when a class gets
cancelled as a result of cascading.
---------------------------------------------------------------------*/
PROCEDURE Check_If_cancellation
( p_line_id IN NUMBER
,p_top_model_line_id IN NUMBER
,p_item_type_code IN VARCHAR2
,x_cancellation OUT NOCOPY /* file.sql.39 change */ BOOLEAN
,x_current_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER)
IS
l_open_flag VARCHAR2(1);
SELECT ordered_quantity, open_flag, component_code
INTO l_ordered_quantity, l_open_flag, l_component_code
FROM oe_order_lines
WHERE line_id = l_line_id;
oe_debug_pub.add('NOT COMPLETE CANCEL, SO DELETE'|| L_LINE_ID ,3);
SELECT link_to_line_id
INTO l_parent_line_id
FROM oe_order_lines
WHERE line_id = l_line_id;
SELECT open_flag
INTO l_open_flag
FROM oe_order_lines
WHERE line_id = l_parent_line_id;
SELECT count(*)
INTO l_line_id
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND link_to_line_id = p_line_id
AND open_flag = 'Y'
AND item_type_code IN ('CLASS', 'OPTION', 'KIT');
SELECT count(*)
INTO l_parent_line_id
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND link_to_line_id = p_line_id
AND item_type_code IN ('CLASS', 'OPTION', 'KIT');
oe_debug_pub.add( 'IT IS A DELETE' , 1 ) ;
SELECT count(*)
INTO l_line_id
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND component_code like (l_component_code || '%')
AND open_flag = 'N'
AND cancelled_flag = 'Y'
AND item_type_code IN ('CLASS', 'OPTION', 'KIT');
The direct operation of update and delete should not
happen, no records should be in class_tbl for update
and delete, since a a value of FALSE is passes to
handle_updates and handle_deletes.
We set a bunch of globals before and after call to lines.
in a delayed request, process order calls batch validate.
after batch validation we might insert/update/delete option
&/ classes.
because of this change there should not be again a delayed request
for batch validation. hence a global OECFG_VALIDATE_CONFIG varchar2(1)
will be reset and set before and after a call to process_order resp.
also we do not wnat to cascade here,
set OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'Y' before the call
oe_config_ui_used: idicates, options window/configurator call.
There are some other flags also.
Change Record:
added Insert_into_Set call
If the parent is in fulfillment set then push the child
into same fulfillment set. This will handle cases that
are not calling Lines procedure and doing direct inserts
into the the table.
MACD: Modified the control record to pass security
---------------------------------------------------------------*/
PROCEDURE Call_Process_Order
( p_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
,p_class_line_tbl IN OE_Order_Pub.Line_Tbl_Type
:= OE_ORDER_PUB.G_MISS_LINE_TBL
,p_control_rec IN OUT NOCOPY OE_GLOBALS.Control_Rec_Type
,p_ui_flag IN VARCHAR2 := 'N'
,p_top_model_line_id IN NUMBER := null
,p_config_hdr_id IN NUMBER := null
,p_config_rev_nbr IN NUMBER := null
,p_update_columns IN BOOLEAN := FALSE
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
-- process_order in variables
I NUMBER;
oe_debug_pub.add('INSERT: ' || L_LINE_REC.COMPONENT_CODE , 1 ) ;
OE_Line_Util.Insert_Row( p_line_rec => l_line_rec);
OE_Default_Line.Insert_into_set
(p_line_id => l_line_rec.top_model_line_id,
p_child_line_id => l_line_rec.line_id,
x_return_status => l_return_status);
ELSIF l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('UPDATE: ' || L_LINE_REC.LINE_ID , 1 ) ;
UPDATE oe_order_lines
SET ordered_quantity = l_line_rec.ordered_quantity
WHERE line_id = l_line_rec.line_id;
ELSIF l_line_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('DELETE: ' || L_LINE_REC.LINE_ID , 1 ) ;
DELETE FROM oe_order_lines
WHERE line_id = l_line_rec.line_id;
IF p_update_columns = TRUE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('CALLING CHANGE COLUMNS' , 2 ) ;
oe_service_util.Update_Service_Option_Numbers
(p_top_model_line_id => p_top_model_line_id);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
Update link_to_line_id, ATO_line_id, option_number
config_header_id and config_rev_nbr of model/class/option
after updating ato_linr_id, for ato's under pto,
default stuff.
update lock_control, once is enough, however if
p_config_flag is 'N', that is why also update in
option_number update.
we can set config_hdr and rev in the line_rec passed
to process_order. But that will change the hdr and revb
of only those options that got updated/newly inserted
not all the options of this model.
change columns updates:
column when do we want to update
------------------------------ ----------------------------
link_to_line_id => creates
ato_line_id => creates
option_number => creates/deletes
config_header_id, config_rev_nbr => always
p_operation : C for create
D for delete
A for all (proportional split, copy config calls)
Change Record :
Bug-2405271 : Update Option numbers for Config Item
Bug-3318910 : Update config hdr/rev/id only if top level is MODEL
Bug-3082485 : validation of decimal ratio for options to classes
Bug-3700148 : do not do ratio check if model is remnant
-----------------------------------------------------------------*/
Procedure Change_Columns
(p_top_model_line_id IN NUMBER,
p_config_hdr_id IN NUMBER,
p_config_rev_nbr IN NUMBER,
p_ui_flag IN VARCHAR2 := 'N',
p_operation IN VARCHAR2 := 'A')
IS
l_line_id NUMBER;
SELECT line_id, link_to_line_id, item_type_code,ordered_quantity,
ato_line_id,inventory_item_id,ordered_item
,split_from_line_id ,sort_order--bug12758138
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND line_id <> p_top_model_line_id
AND service_reference_line_id is null
AND item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED
AND item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
order by sort_order;
SELECT item_type_code, ato_line_id, config_header_id, config_rev_nbr,
nvl(model_remnant_flag, 'N')
INTO l_model_item_type_code , l_model_ato_line_id,
l_prev_config_header_id, l_prev_config_rev_nbr,
l_remnant_flag
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
UPDATE oe_order_lines
SET configuration_id = nvl(configuration_id, 0) + 1,
lock_control = lock_control + 1
WHERE top_model_line_id = p_top_model_line_id
AND item_type_code IN ('MODEL', 'CLASS', 'OPTION', 'KIT');
UPDATE oe_order_lines
SET config_header_id = p_config_hdr_id,
config_rev_nbr = p_config_rev_nbr,
lock_control = lock_control + 1
WHERE top_model_line_id = p_top_model_line_id
AND item_type_code IN ('MODEL', 'CLASS', 'OPTION', 'KIT');
oe_debug_pub.add('UPDATE ATO ATTRIBS FOR SUBASSEMBLIES' , 1 ) ;
update_ato_line_attributes( p_top_model_line_id => p_top_model_line_id
,p_ui_flag => p_ui_flag
,p_config_hdr_id => p_config_hdr_id);
UPDATE oe_order_lines
SET shippable_flag = 'N'
WHERE top_model_line_id = p_top_model_line_id
AND ato_line_id is NOT NULL
AND item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
AND NOT (item_type_code = OE_GLOBALS.G_ITEM_OPTION AND
ato_line_id = line_id)
AND NOT (item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND --9775352
ato_line_id = line_id);
update_link_to_line_id
( p_top_model_line_id => p_top_model_line_id
,p_remnant_flag => l_remnant_flag
,p_config_hdr_id => p_config_hdr_id);
Select option_number
into l_option_number
from oe_order_lines
where split_from_line_id = l_split_from_line_id
and model_remnant_flag = 'Y'
and option_number is not null
-- and ato_line_id is not null
-- and ato_line_id <> top_model_line_id
and top_model_line_id = p_top_model_line_id
and shipped_quantity is null
and NVL(sort_order,-99) = NVL(l_sort_order, -99)
and line_id <> l_line_id;
oe_debug_pub.add('option_nbr update error: -' || SQLERRM, 1 ) ;
UPDATE oe_order_lines
SET option_number = l_option_number,
lock_control = lock_control + 1
WHERE line_id = l_line_id;
oe_debug_pub.add('option_nbr rows updated : -' || SQL%ROWCOUNT, 1 ) ;
UPDATE oe_order_lines
SET option_number = l_option_nbr,
lock_control = lock_control + 1
WHERE line_id = l_line_id;
SELECT INDIVISIBLE_FLAG
INTO l_indivisible_flag
FROM mtl_system_items
WHERE inventory_item_id = l_child_inv_item_id
AND organization_id =
OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
SELECT ordered_quantity,ordered_item,
item_type_code,inventory_item_id
INTO l_parent_ordered_quantity,l_parent_ordered_item,
l_parent_item_type_code, l_parent_inv_item_id
FROM OE_ORDER_LINES
WHERE line_id = l_link;
UPDATE oe_order_lines
SET option_number = l_option_nbr,
lock_control = lock_control + 1
WHERE top_model_line_id = p_top_model_line_id
AND link_to_line_id = l_line_id
AND item_type_code = 'INCLUDED';
UPDATE oe_order_lines
-- SET option_number = l_option_nbr, ---commented for bug12758138
SET option_number = NVL(l_option_number,l_option_nbr), ---bug12758138
lock_control = lock_control + 1
WHERE top_model_line_id = p_top_model_line_id
AND ato_line_id = l_line_id
AND item_type_code = 'CONFIG';
UPDATE oe_order_lines o
SET ordered_quantity =
(SELECT ordered_quantity
FROM oe_order_lines
WHERE line_id = o.link_to_line_id)
WHERE top_model_line_id = p_top_model_line_id
AND item_type_code = OE_GLOBALS.G_ITEM_CONFIG
AND nvl(model_remnant_flag, 'N') = 'N';
UPDATE oe_order_lines
SET cancelled_flag = 'Y'
WHERE top_model_line_id = p_top_model_line_id
AND item_type_code = 'CONFIG'
AND ordered_quantity = 0;
Procedure : update_link_to_line_id
OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' : Pack H
-----------------------------------------------------------------*/
Procedure update_link_to_line_id
( p_top_model_line_id IN NUMBER
,p_remnant_flag IN VARCHAR2
,p_config_hdr_id IN NUMBER)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT link_to_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y' )
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id is NULL
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.open_flag = 'Y';
oe_debug_pub.add('1 LLID UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('update_link_to_line_id: May be Due to bug 12758138',1 ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT link_to_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y'
AND oe1.shipped_quantity IS NULL
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id is NULL
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.shipped_quantity IS NULL
AND OEOPT.open_flag = 'Y';
oe_debug_pub.add('1aE for unshipped LLID UPDATED ' || SQL%ROWCOUNT,1 ) ;
oe_debug_pub.add('update_link_to_line_id: In errors block at 2',1 ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT link_to_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y'
AND oe1.shipped_quantity IS NOT NULL )
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id is NULL
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.shipped_quantity IS NOT NULL
AND OEOPT.open_flag = 'Y';
oe_debug_pub.add('1bE for shipped LLID UPDATED ' || SQL%ROWCOUNT,1 ) ;
oe_debug_pub.add('update_link_to_line_id: In errors block at 3 llid',1 ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT link_to_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y'
AND oe1.shipped_quantity IS NULL )
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id is NULL
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.shipped_quantity IS NOT NULL
AND OEOPT.open_flag = 'Y';
oe_debug_pub.add('1cE for shipped LLID UPDATED ' || SQL%ROWCOUNT,1 ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
( SELECT line_id
FROM oe_order_lines OELNK
WHERE OELNK.top_model_line_id = OEOPT.top_model_line_id
AND OELNK.configuration_id =
( SELECT parent_config_item_id
FROM cz_config_details_v
WHERE config_hdr_id = OELNK.config_header_id
AND config_rev_nbr = OELNK.config_rev_nbr
AND config_item_id = OEOPT.configuration_id
)
AND OELNK.open_flag = 'Y'
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id IS NULL;
oe_debug_pub.add('2 LLID UPDATED ' || SQL%ROWCOUNT ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
( SELECT OELNK.line_id
FROM oe_order_lines OELNK
WHERE (( OELNK.line_id = oeopt.top_model_line_id OR
OELNK.top_model_line_id = OEOPT.top_model_line_id ))
AND (OELNK.component_code = SUBSTR( OEOPT.component_code,1,
LENGTH( RTRIM( OEOPT.component_code,'0123456789' )) - 1)
OR (OELNK.component_code = OEOPT.component_code AND
OEOPT.item_type_code = OE_GLOBALS.G_ITEM_MODEL))
AND open_flag = 'Y'
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id IS NULL
AND OEOPT.item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED;
oe_debug_pub.add('3 LLID UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('update_link_to_line_id2 at error ' || SQLERRM ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
( SELECT OELNK.line_id
FROM oe_order_lines OELNK
WHERE (( OELNK.line_id = oeopt.top_model_line_id OR
OELNK.top_model_line_id = OEOPT.top_model_line_id ))
AND (OELNK.component_code = SUBSTR( OEOPT.component_code,1,
LENGTH( RTRIM( OEOPT.component_code,'0123456789' )) - 1)
OR (OELNK.component_code = OEOPT.component_code AND
OEOPT.item_type_code = OE_GLOBALS.G_ITEM_MODEL))
AND open_flag = 'Y'
AND OELNK.shipped_quantity IS NULL
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id IS NULL
AND OEOPT.shipped_quantity IS NULL
AND OEOPT.item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED;
oe_debug_pub.add('3 update_link_to_line_id2 UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('update_link_to_line_id2 at A' ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
( SELECT OELNK.line_id
FROM oe_order_lines OELNK
WHERE (( OELNK.line_id = oeopt.top_model_line_id OR
OELNK.top_model_line_id = OEOPT.top_model_line_id ))
AND (OELNK.component_code = SUBSTR( OEOPT.component_code,1,
LENGTH( RTRIM( OEOPT.component_code,'0123456789' )) - 1)
OR (OELNK.component_code = OEOPT.component_code AND
OEOPT.item_type_code = OE_GLOBALS.G_ITEM_MODEL))
AND open_flag = 'Y'
AND OELNK.shipped_quantity IS NOT NULL
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id IS NULL
AND OEOPT.shipped_quantity IS NOT NULL
AND OEOPT.item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED;
oe_debug_pub.add('3b update_link_to_line_id2 UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('update_link_to_line_id2 at B' ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
( SELECT OELNK.line_id
FROM oe_order_lines OELNK
WHERE (( OELNK.line_id = oeopt.top_model_line_id OR
OELNK.top_model_line_id = OEOPT.top_model_line_id ))
AND (OELNK.component_code = SUBSTR( OEOPT.component_code,1,
LENGTH( RTRIM( OEOPT.component_code,'0123456789' )) - 1)
OR (OELNK.component_code = OEOPT.component_code AND
OEOPT.item_type_code = OE_GLOBALS.G_ITEM_MODEL))
AND open_flag = 'Y'
AND OELNK.shipped_quantity IS NULL
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.link_to_line_id IS NULL
AND OEOPT.shipped_quantity IS NOT NULL
AND OEOPT.item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED;
oe_debug_pub.add('3c update_link_to_line_id2 UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('update_link_to_line_id2 at C' ) ;
UPDATE oe_order_lines OEOPT
SET link_to_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT link_to_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y' )
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
AND OEOPT.link_to_line_id is NULL
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.open_flag = 'Y';
oe_debug_pub.add('1 LLID UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('UPDATED LINK_TO_LINE_ID IN OE_ORDER_LINES' , 2 ) ;
oe_debug_pub.add('ERROR IN UPDATE_LINK_TO_LINE_ID' || SQLERRM , 1 ) ;
END update_link_to_line_id;
Procedure : update_ato_line_attributes
Change Record:
bug 1894331
the select statement for getting ato_line_id in case of
pto+ato case is modified. look at the bug for more details.
also made same change in OEXDLINB.pls:get_ato_line.
bug 2143052: added open_flag to cursors.
From mi project, we have decided that ato_line_id will not be
updated if the model is remnant here. The ato_line_id populated
by SPLIT defaulting remains.
-----------------------------------------------------------------*/
Procedure update_ato_line_attributes
( p_top_model_line_id IN NUMBER
,p_ui_flag IN VARCHAR2
,p_config_hdr_id IN NUMBER)
IS
l_component_code varchar2(1000);
SELECT unique(ato_line_id)
FROM oe_order_lines_all
WHERE top_model_line_id = p_top_model_line_id
AND ato_line_id is not null
AND item_type_code = OE_GLOBALS.G_ITEM_CLASS
AND open_flag = 'Y'; -- ato subconfigs
SELECT opt.line_id
FROM oe_order_lines_all opt, oe_order_lines_all ato_model
WHERE opt.top_model_line_id = p_top_model_line_id AND
ato_model.top_model_line_id = p_top_model_line_id AND
ato_model.line_id = p_ato_line_id AND
opt.open_flag = 'Y' AND
opt.ato_line_id = p_ato_line_id AND
(nvl(ato_model.project_id,-1) <>
nvl(opt.project_id,-1) OR
nvl(ato_model.task_id,-1) <>
nvl(opt.task_id,-1) OR
nvl(ato_model.ship_from_org_id,-1) <>
nvl(opt.ship_from_org_id,-1) OR
nvl(ato_model.ship_to_org_id,-1) <>
nvl(opt.ship_to_org_id,-1) OR
nvl(ato_model.schedule_ship_date,SYSDATE) <>
nvl(opt.schedule_ship_date,SYSDATE) OR
nvl(ato_model.schedule_arrival_date,SYSDATE) <>
nvl(opt.schedule_arrival_date,SYSDATE) OR
nvl(ato_model.request_date,SYSDATE) <>
nvl(opt.request_date,SYSDATE) OR
nvl(ato_model.shipping_method_code,'-') <>
nvl(opt.shipping_method_code,'-') OR
nvl(ato_model.freight_carrier_code,'-') <>
nvl(opt.freight_carrier_code,'-') );
oe_debug_pub.add('UPDATE_ATO: PACK H NEW LOGIC MI' , 1 ) ;
UPDATE oe_order_lines OEOPT
SET ato_line_id =
( SELECT line_id
FROM oe_order_lines OEATO
WHERE OEOPT.top_model_line_id = OEATO.top_model_line_id
AND OEATO.configuration_id =
(SELECT ato_config_item_id
FROM cz_config_details_v
WHERE config_hdr_id = OEOPT.config_header_id
AND config_rev_nbr = OEOPT.config_rev_nbr
AND config_item_id = OEOPT.configuration_id)
AND OEATO.open_flag = 'Y'
)
WHERE TOP_MODEL_LINE_ID = p_top_model_line_id
AND NOT (item_type_code = 'OPTION' AND
ato_line_id = line_id AND
ato_line_id is not null)
AND NOT (item_type_code = 'INCLUDED' AND --9775352
ato_line_id = line_id AND
ato_line_id is not null)
AND item_type_code <> 'CONFIG' -- not config line important.
AND nvl(model_remnant_flag, 'N') = 'N'
AND ordered_quantity > 0;
oe_debug_pub.add('NEW ATO_LINE UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('DID NOT UPDATE ANY LINE WITH ATO' ) ;
UPDATE oe_order_lines OEOPT
SET ato_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT ato_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y' )
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.open_flag = 'Y'
AND OEOPT.item_type_code = 'CONFIG'
AND OEOPT.ato_line_id is null;
UPDATE oe_order_lines OEOPT
SET ato_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT ato_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y' )
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.open_flag = 'Y'
AND OEOPT.model_remnant_flag = 'Y'
AND NOT (item_type_code = 'OPTION' AND
ato_line_id = line_id AND
ato_line_id is not null)
AND NOT (item_type_code = 'INCLUDED' AND --9775352
ato_line_id = line_id AND
ato_line_id is not null);
UPDATE oe_order_lines OEOPT
SET ato_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT ato_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y'
AND oe1.shipped_quantity IS NULL
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.open_flag = 'Y'
AND OEOPT.model_remnant_flag = 'Y'
AND OEOPT.shipped_quantity IS NULL
AND NOT (item_type_code = 'OPTION' AND
ato_line_id = line_id AND
ato_line_id is not null)
AND NOT (item_type_code = 'INCLUDED' AND --9775352
ato_line_id = line_id AND
ato_line_id is not null);
UPDATE oe_order_lines OEOPT
SET ato_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT ato_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y'
AND oe1.shipped_quantity IS NOT NULL
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.open_flag = 'Y'
AND OEOPT.model_remnant_flag = 'Y'
AND OEOPT.shipped_quantity IS NOT NULL
AND NOT (item_type_code = 'OPTION' AND
ato_line_id = line_id AND
ato_line_id is not null)
AND NOT (item_type_code = 'INCLUDED' AND --9775352
ato_line_id = line_id AND
ato_line_id is not null);
UPDATE oe_order_lines OEOPT
SET ato_line_id =
(SELECT line_id
FROM oe_order_lines oe1
WHERE split_from_line_id =
(SELECT ato_line_id
FROM oe_order_lines oe2
WHERE line_id = OEOPT.split_from_line_id
AND oe2.open_flag = 'Y')
AND oe1.top_model_line_id = p_top_model_line_id
AND oe1.open_flag = 'Y'
AND oe1.shipped_quantity IS NULL
)
WHERE OEOPT.top_model_line_id = p_top_model_line_id
AND OEOPT.line_id <> p_top_model_line_id
AND OEOPT.split_from_line_id is NOT NULL
AND OEOPT.open_flag = 'Y'
AND OEOPT.model_remnant_flag = 'Y'
AND OEOPT.shipped_quantity IS NOT NULL
AND OEOPT.item_type_code = 'CONFIG'
AND OEOPT.ato_line_id IS NULL
AND NOT (item_type_code = 'OPTION' AND
ato_line_id = line_id AND
ato_line_id is not null)
AND NOT (item_type_code = 'INCLUDED' AND --9775352
ato_line_id = line_id AND
ato_line_id is not null);
UPDATE OE_ORDER_LINES_ALL OEOPT
SET ATO_LINE_ID =
( SELECT OEATO.LINE_ID
FROM OE_ORDER_LINES_ALL OEATO
WHERE OEATO.TOP_MODEL_LINE_ID =
OEOPT.TOP_MODEL_LINE_ID
AND ITEM_TYPE_CODE = 'CLASS'
AND OEATO.COMPONENT_CODE =
SUBSTR( OEOPT.COMPONENT_CODE, 1,
LENGTH( OEATO.COMPONENT_CODE )
)
AND OEATO.inventory_item_id =
( SELECT inventory_item_id
FROM mtl_system_items
WHERE inventory_item_id =
OEATO.inventory_item_id
AND organization_id =
OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID')
AND replenish_to_order_flag = 'Y'
)
AND OEATO.COMPONENT_CODE =
( SELECT MIN( OEMIN.COMPONENT_CODE )
FROM OE_ORDER_LINES_ALL OEMIN
WHERE OEMIN.TOP_MODEL_LINE_ID
= OEOPT.TOP_MODEL_LINE_ID
AND OEMIN.COMPONENT_CODE =
SUBSTR( OEOPT.COMPONENT_CODE, 1,
LENGTH( OEMIN.COMPONENT_CODE ))
AND OEMIN.inventory_item_id =
( SELECT inventory_item_id
FROM mtl_system_items
WHERE inventory_item_id =
OEMIN.inventory_item_id
AND organization_id =
OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID')
AND replenish_to_order_flag = 'Y'
)
)
AND ((SUBSTR(OEOPT.component_code,
LENGTH(OEATO.component_code) + 1, 1) = '-' OR
SUBSTR(OEOPT.component_code,
LENGTH(OEATO.component_code) + 1, 1) is NULL)
)
)
WHERE TOP_MODEL_LINE_ID = p_top_model_line_id
AND NOT (item_type_code = 'OPTION' AND
ato_line_id = line_id AND
ato_line_id is not null)
AND NOT (item_type_code = 'INCLUDED' AND -- 9775352
ato_line_id = line_id AND
ato_line_id is not null);
oe_debug_pub.add('OLD ATO_LINE UPDATED ' || SQL%ROWCOUNT ) ;
oe_debug_pub.add('UNEXPECTED ERROR IN UPDATE ATO_LINE_ID' , 1 ) ;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('IN UPDATE_ATO_ATTIRBS , CALLING PROCESS_ORDER' , 1 ) ;
('IN UPDATE_ATO_ATTIRBS , AFTER PO: ' || L_RETURN_STATUS , 1 ) ;
oe_debug_pub.add('ERROR IN UPDATE_ATO_LINE_ATTRIBUTES' || SQLERRM , 1 ) ;
END update_ato_line_attributes;
Procedure Name : Delete_Config
Description : Deletes the configuration from SPC's tables
-------------------------------------------------------------------- */
Procedure Delete_Config
(p_config_hdr_id IN NUMBER ,
p_config_rev_nbr IN NUMBER ,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_usage_exists number;
oe_debug_pub.add('ENTERING DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
CZ_CF_API.Delete_Configuration
( config_hdr_id => p_config_hdr_id
,config_rev_nbr => p_config_rev_nbr
,usage_exists => l_usage_exists
,error_message => l_error_message
,return_value => l_return_value );
oe_debug_pub.add('ERROR IN DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
oe_debug_pub.add('LEAVING DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
oe_debug_pub.add('ERROR IN DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
END Delete_Config;
updates link_to_line_id, ATO_line_id, option_number
config_header_id and config_rev_nbr of model/class/option
Change Record:
2611771 : new cz copy config call
3144865 : skip call to change_columns if only model line is selected
3318910 : call change_columns for KIT/ options window, bug fix on top
of 3144865 to fix the issues in that bug fix.
-------------------------------------------------------------------- */
Procedure Copy_Config(p_top_model_line_id IN NUMBER ,
p_config_hdr_id IN NUMBER ,
p_config_rev_nbr IN NUMBER ,
p_configuration_id IN NUMBER ,
p_remnant_flag IN VARCHAR2 ,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
IS
l_return_value number;
oe_debug_pub.add('no need to update config ids ',1);
UPDATE oe_order_lines
SET configuration_id = l_new_item_id_tbl(I)
WHERE top_model_line_id = p_top_model_line_id
AND configuration_id = l_orig_item_id_tbl(I);
UPDATE oe_order_lines
SET configuration_id = null,
config_header_id = null,
config_rev_nbr = null
WHERE top_model_line_id = p_top_model_line_id;
oe_debug_pub.add('CONFIG IDS UPDATED TO NULL' , 2 ) ;
UPDATE oe_order_lines
SET link_to_line_id = NULL
WHERE top_model_line_id = p_top_model_line_id
AND split_from_line_id is not NULL;
UPDATE oe_order_lines
SET link_to_line_id = NULL
WHERE top_model_line_id = p_top_model_line_id;
Since Update_ato_line_attributes are updating only Class records
The ato_line_id populated by SPLIT code in case of PTO+ATO is
the line_id of the top PTO mode whcih is incorrect. Hence,
it is important to update the ATO with correct ato_line_id.
*/
SELECT ato_line_id ,booked_flag, item_type_code
INTO l_ato_line_id, l_booked_flag, l_error_message
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
UPDATE oe_order_lines
SET ato_line_id = NULL
WHERE top_model_line_id = p_top_model_line_id
AND NOT (item_type_code = OE_GLOBALS.G_ITEM_OPTION AND
ato_line_id = line_id)
AND NOT (item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND --9775352
ato_line_id = line_id);
update_link_to_line_id
( p_top_model_line_id => p_top_model_line_id --added for bug 7261021
,p_remnant_flag => p_remnant_flag
,p_config_hdr_id => p_config_hdr_id);
SELECT line_number || '.'|| shipment_number
INTO l_line_number
FROM oe_order_lines
WHERE line_id = p_line_id;
FND_MESSAGE.Set_Name('ONT', 'OE_VAL_CONFIG_HOLD_INSERT');
the model line rec so that it can be sent in for update.
we will call a direct update on the model line later.
change record:
new parameters for ER config date effectivity 2625376
p_check_effective_date : if need to check model date effectivity.
x_config_effective_date : null if p_check_effective_date is N
x_frozen_model_bill : null if p_check_effective_date is N
------------------------------------------------------------*/
Procedure Explode_Bill
( p_model_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
,p_do_update IN BOOLEAN := TRUE
,p_check_effective_date IN VARCHAR2 := 'Y'
,x_config_effective_date OUT NOCOPY DATE
,x_frozen_model_bill OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
/* variables for call to explode */
l_rev_date DATE;
SELECT bill_sequence_id
INTO p_model_line_rec.component_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id = p_model_line_rec.inventory_item_id
AND organization_id = l_validation_org
AND alternate_bom_designator is NULL;
oe_debug_pub.add('TOP_BILL_SEQ_ID SELECTED FROM BOM_bill_of_mat' ) ;
IF p_do_update THEN
UPDATE oe_order_lines
SET component_sequence_id = p_model_line_rec.component_sequence_id
,sort_order = p_model_line_rec.sort_order
,component_code = p_model_line_rec.component_code
,lock_control = lock_control + 1
WHERE line_id = p_model_line_rec.line_id;
SELECT line_id, ordered_quantity
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND link_to_line_id = p_link_to_line_id
AND item_type_code = OE_GLOBALS.G_ITEM_INCLUDED;
IF l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
l_line_rec.ordered_quantity = 0 AND
OE_MODIFY_INC_ITEMS_TBL(I).param11 = 'N'
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('DELETE SINCE NOT FULL CANCEL' , 3 ) ;
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
SELECT line_id, component_code, link_to_line_id, ordered_quantity
FROM oe_order_lines
WHERE item_type_code = 'INCLUDED'
AND link_to_line_id <> top_model_line_id
AND top_model_line_id = p_top_model_line_id;
SELECT component_sequence_id, creation_date
INTO l_component_sequence_id, l_creation_date
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
SELECT ordered_quantity
INTO l_ordered_qty
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND line_id = l_rec.link_to_line_id;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('PARENT WAS UPDATED' || L_ORDERED_QTY , 3 ) ;
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add('PARENT WAS DELETED' , 3 ) ;
oe_debug_pub.add('COULD NOT SELECT PARENT QTY' , 1 ) ;
PROCEDURE Is_Cancel_OR_Delete
--------------------------------------------------------*/
PROCEDURE Is_Cancel_OR_Delete
( p_line_id IN NUMBER
,p_change_reason IN VARCHAR2 := null
,p_change_comments IN VARCHAR2 := null
,x_cancellation OUT NOCOPY BOOLEAN
,x_line_rec IN OUT NOCOPY OE_Order_Pub.line_rec_type)
IS
l_return_status VARCHAR2(1);
x_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_Msg_Pub.Delete_Msg;
OE_Msg_Pub.Delete_Msg(l_msg_count2 - I);
oe_debug_pub.add('do cancellation hence update with 0', 3 );
x_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('no cancellation, delete ok ', 3 ) ;
oe_debug_pub.add('error in Is_Cancel_OR_Delete '|| sqlerrm, 3);
END Is_Cancel_OR_Delete;