[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_model_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
,p_ui_flag IN VARCHAR2
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
,p_x_options_tbl IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
,p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,x_result OUT NOCOPY /* file.sql.39 change */ BOOLEAN);
( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
);
( p_options_tbl IN OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
,x_bom_validation_tbl OUT NOCOPY OE_CONFIG_VALIDATION_PVT.VALIDATE_OPTIONS_TBL_TYPE);
( p_x_option_rec IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_REC
,p_top_model_line_id IN NUMBER);
This procedure works on the selected options from options window.
1) It first completes the configuration, which means some of the classes
that are not selected by the user are filled in the table of options.
2) After that the configuration is validated using BOM rules.
3) Then we call process_order API to create the option / class lines in
oe tables.
There can not be a duplicate class(llid caode will fail),
however I do not know about a duplicate options. May be we should
have a handled exception for this.
Exception block:
options window UI populates process_messages window to display errors.
If the return status is unexp error, the continue button is disabled
if it is execution error, continue button on msg window is enabled.
we want user to continue only in case of bom based validation failure.
other cases no matter what is the ret status(ex: process_order returned
execution error, we can not commit user changes, so no point in keeping
continue enabled.)
So I am manipulating the return status to always return unexp error
in exception handling block here, in case of a UI call.
In case of delayed request call, we will return what ever
is the error.
The return status is not used in any other way by Options window UI.
Change record:
3687870 : check the fulfilled_flag, open flag etc. for UI.
---------------------------------------------------------------------- */
Procedure Process_Config_Options
( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_header_id IN NUMBER
,p_top_model_line_id IN NUMBER
,p_ui_flag IN VARCHAR2 := 'Y'
,p_caller IN VARCHAR2 := '' -- bug 4636208
,x_valid_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,x_complete_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,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
l_model_line_rec OE_Order_Pub.Line_Rec_Type;
l_options_tbl OE_Process_Options_Pvt.Selected_Options_Tbl_Type;
l_db_options_tbl OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE;
l_deleted_options_tbl OE_Order_PUB.request_tbl_type;
l_updated_options_tbl OE_Order_PUB.request_tbl_type;
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_order_source_id => l_model_line_rec.order_source_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_source_document_type_id => l_model_line_rec.source_document_type_id);
,p_x_updated_options_tbl => l_updated_options_tbl
,p_x_deleted_options_tbl => l_deleted_options_tbl);
IF l_updated_options_tbl.COUNT > 0 OR
l_deleted_options_tbl.COUNT > 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CALLING CASCADE_UPDATES_DELETES' , 1 ) ;
OE_Config_Util.Cascade_Updates_Deletes
( p_model_line_id => p_top_model_line_id
,p_model_component => l_model_line_rec.component_code
,p_x_options_tbl => l_options_tbl
,p_deleted_options_tbl => l_deleted_options_tbl
,p_updated_options_tbl => l_updated_options_tbl
,p_ui_flag => p_ui_flag
,x_return_status => l_return_status);
used to prepare i/p tables for cascade_update_deletes API.
handles diabled options also.
3563690 => pass ordered_item in param10
----------------------------------------------------------*/
PROCEDURE Prepare_Cascade_Tables
( p_options_tbl IN OUT NOCOPY
OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_top_model_line_id IN NUMBER
,p_x_updated_options_tbl IN OUT NOCOPY OE_Order_PUB.request_tbl_type
,p_x_deleted_options_tbl IN OUT NOCOPY OE_Order_PUB.request_tbl_type)
IS
l_count NUMBER;
l_count := p_x_updated_options_tbl.COUNT;
l_index := p_x_deleted_options_tbl.COUNT;
IF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('GET OLD QTY FOR '|| p_OPTIONS_TBL(I).LINE_ID,1);
SELECT ordered_quantity
INTO p_options_tbl(I).old_ordered_quantity
FROM oe_order_lines
WHERE line_id = p_options_tbl(I).line_id;
('UPDATE: '||L_REQ_REC.PARAM2||' '
||L_REQ_REC.PARAM3||L_REQ_REC.PARAM5,1);
p_x_updated_options_tbl(l_count) := l_req_rec;
ELSIF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_index := l_index + 1;
oe_debug_pub.add( 'DELETE: '|| L_REQ_REC.PARAM2 || ' '
|| L_REQ_REC.PARAM3 , 1 ) ;
p_x_deleted_options_tbl(l_index) := l_req_rec;
( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_comp_code IN VARCHAR2)
RETURN NUMBER
IS
I NUMBER;
p_options_tbl(I).operation <> OE_GLOBALS.G_OPR_INSERT
THEN
RETURN I;
For any updates/deletes user will use sales order form.
To aid performance, in this procedure we first default one option
and use the defaulted record as the base record for all other
options that need to be created. This saves us from defaulting
all n options for 300 or so attributes in oe_order_lines.
We set the item dependent attributes (once that are set dependent
on inventory_item_id in OEXUDEPB.pls) as missing on all the
options so that they will get defaulted individually. Please
note that any future additions to OEXUDEPB.pls should be
added in this API also.
We call process_order and then the change columns procedure
which sets the configuration related links(link to line id etc)
on all the options.
Change Record:
bug fix 1894020,2184255 to support dff.
the operation on disabled options should always be none.
bug fix 3095496, change reason for updates and a call to
Is_Cancel_Or_Delete for Delete operation.
Bug 3611416
Send reason for CREATE operation also, will be required if there is
a require reason constraint for versioning during create operation.
------------------------------------------------------------*/
Procedure Handle_DML
( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,p_model_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
,p_ui_flag IN VARCHAR2
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
-- process_order in params
l_control_rec OE_GLOBALS.Control_Rec_Type;
l_line_upd_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_del_rec.operation := OE_GLOBALS.G_OPR_DELETE;
IF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_INSERT
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('INSERT: '|| P_OPTIONS_TBL(I).COMPONENT_CODE ,1);
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_class_line_rec.line_id
FROM DUAL;
ELSIF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
l_line_upd_rec.line_id := p_options_tbl(I).line_id;
oe_debug_pub.add('UPDATE LINE_ID: '|| P_OPTIONS_TBL(I).LINE_ID ,1);
ELSIF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_DELETE THEN
l_line_del_rec.line_id := p_options_tbl(I).line_id;
oe_debug_pub.add('calling Is_Cancel_OR_Delete '
|| l_line_del_rec.line_id, 3);
OE_Config_Pvt.Is_Cancel_OR_Delete
( p_line_id => l_line_del_rec.line_id
,p_change_reason => 'CONFIGURATOR'
,p_change_comments => 'Changes in Options Window'
,x_cancellation => l_cancellation
,x_line_rec => l_line_del_rec);
oe_debug_pub.add('DELETE LINE_ID: '|| P_OPTIONS_TBL(I).LINE_ID , 1);
,p_update_columns => TRUE
,x_return_status => l_return_status);
system can eihter delete or cancel them also populates
message back to user to indicate the same.
-----------------------------------------------------------------------*/
PROCEDURE Handle_Disabled_Options
( p_x_option_rec IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_REC
,p_top_model_line_id IN NUMBER)
IS
l_line_rec OE_ORDER_PUB.Line_Rec_Type;
SELECT ordered_item
INTO l_dummy
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
SELECT line_number || '.' || shipment_number || '.' ||
option_number || '.' || component_number || '.' ||
service_number
INTO l_dummy
FROM oe_order_lines
WHERE line_id = p_x_option_rec.line_id;
OE_Config_Pvt.Is_Cancel_OR_Delete
( p_line_id => p_x_option_rec.line_id
,p_change_reason => 'SYSTEM'
,p_change_comments => 'DISABLED'
,x_cancellation => l_cancellation
,x_line_rec => l_line_rec);
oe_debug_pub.add('do cancellation hence update with 0', 3 );
p_x_option_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
p_x_option_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add('no cancellation, delete ok ', 3 ) ;
operation of INSERT indicates that the record is to created in DB.
operation of CREATE means the record is created however the
transaction is yet not commited(mainly in case of delyed requests).
------------------------------------------------------------------------*/
Procedure Fill_In_Classes
( p_top_model_line_id IN NUMBER
,p_model_component IN VARCHAR2
,p_model_quantity IN NUMBER
,p_top_bill_sequence_id IN NUMBER
,p_effective_date IN DATE
,p_ui_flag IN VARCHAR2
,p_x_options_tbl IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
I NUMBER;
= OE_GLOBALS.G_OPR_INSERT OR
nvl(p_x_options_tbl(I).operation, OE_GLOBALS.G_OPR_NONE)
= OE_GLOBALS.G_OPR_CREATE
THEN
J := 2;
p_x_options_tbl(l_options_tbl_index).operation := OE_GLOBALS.G_OPR_INSERT;
SELECT component_sequence_id, component_item_id, sort_order,
primary_uom_code, EXTENDED_QUANTITY * p_model_quantity,
DECODE(bom_item_type, 1, 2, 2, 2, 4, 4)
INTO p_x_options_tbl(I).component_sequence_id,
p_x_options_tbl(I).inventory_item_id,
p_x_options_tbl(I).sort_order,
p_x_options_tbl(I).order_quantity_uom,
p_x_options_tbl(I).ordered_quantity,
p_x_options_tbl(I).bom_item_type
FROM bom_explosions be
WHERE be.explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS
AND be.top_bill_sequence_id = p_top_bill_sequence_id
AND be.plan_level > 0
AND be.effectivity_date <= p_effective_date
AND be.disable_date > p_effective_date
AND be.component_code = p_x_options_tbl(I).component_code
AND rownum = 1;
SELECT concatenated_segments
INTO p_x_options_tbl(I).ordered_item
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_x_options_tbl(I).inventory_item_id
AND organization_id = l_validation_org;
If the operation on the matching component record is DELETE, this function
will return a value of false.
------------------------------------------------------------------------*/
PROCEDURE component_exists
( p_component IN VARCHAR2
,p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,x_result OUT NOCOPY /* file.sql.39 change */ BOOLEAN)
IS
I NUMBER;
OE_GLOBALS.G_OPR_DELETE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'COMPONENT FOUND' , 1 ) ;
( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_outer_index NUMBER;
OE_GLOBALS.G_OPR_INSERT
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( L_OUTER_INDEX || ' CHECK DUPL: '
||P_OPTIONS_TBL ( L_OUTER_INDEX ) .COMPONENT_CODE , 1 ) ;
OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE)
IS
CURSOR Get_Options
IS
SELECT component_code , ordered_quantity, inventory_item_id,
component_sequence_id, sort_order, order_quantity_uom,
DECODE(item_type_code, 'MODEL', 1, 'CLASS', 2, 4) bom_item_type,
ordered_item, configuration_id, config_header_id, line_id,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20,
context
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND open_flag = 'Y'
AND nvl(config_header_id, -1) = -1
AND (item_type_code = OE_GLOBALS.G_ITEM_MODEL
OR item_type_code = OE_GLOBALS.G_ITEM_OPTION
OR item_type_code = OE_GLOBALS.G_ITEM_CLASS
OR item_type_code = OE_GLOBALS.G_ITEM_KIT);
SELECT inventory_item_id, component_sequence_id
INTO l_top_item_id, l_top_bill_sequence_id
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
SELECT 1
INTO l_disable_code
FROM bom_explosions
WHERE component_item_id = opt_rec.inventory_item_id
AND explosion_type = Oe_Config_Util.OE_BMX_OPTION_COMPS
AND top_bill_sequence_id = l_top_bill_sequence_id
AND effectivity_date <= l_config_effective_date
AND disable_date > l_config_effective_date
AND organization_id = OE_SYS_PARAMETERS.VALUE
('MASTER_ORGANIZATION_ID')
AND component_code = opt_rec.component_code;
oe_debug_pub.add('bom item select error '|| sqlerrm, 1);
( p_options_tbl IN OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
,x_bom_validation_tbl OUT NOCOPY OE_CONFIG_VALIDATION_PVT.VALIDATE_OPTIONS_TBL_TYPE)
IS
I NUMBER;
<> OE_GLOBALS.G_OPR_DELETE AND
nvl(p_options_tbl(I).disabled_flag, 'N') = 'N' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add
( 'COMPONENT: '|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 2 ) ;