[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This API takes a list of selected options (p_options_tbl) for the
top level model(p_top_model_line_id ) in OM.
It performs certain validations based on the BOM setup.
1) if the ordered quantity of any option is not out side of
the Min - Max quantity settings in BOM.
2) if the ratio of ordered quantity of a class to model
and option to class is integer ratio i.e. exact multiple.
3) to see that a class does not exist w/o any options selected for it.
4) if a class that has mutually exclusive options, does not have
more than one options selected under it.
5) if at least one option is selected per mandatory class.
If any of the validation fails, it will populate error messages
and will return with a status of error.
Change Record:
ER2625376: changes made to BOM should be visible to Order unitl Booking.
OE_Config_Util.Get_Config_effective_Date should be used to decide
the date for effective/diabled filter on bom_explosions sqls.
p_creatione_date parameter will be renamed to p_effective_date.
------------------------------------------------------------------------*/
Procedure Bom_Based_Config_Validation
( p_top_model_line_id IN NUMBER
,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
,x_valid_config OUT NOCOPY VARCHAR2
,x_complete_config OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
SELECT component_sequence_id, ordered_quantity
INTO l_top_bill_sequence_id, l_model_qty
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
oe_debug_pub.add('SELECT FAILED '|| SQLERRM , 1 ) ;
SELECT ordered_quantity
INTO l_parent_qty
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
SELECT ordered_item, item_type_code
INTO l_ordered_item, l_item_type_code
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
To see if every item in the configuration has immediate parent selected.
------------------------------------------------------------------------*/
FUNCTION Check_Parent_Exists
( p_component_code IN VARCHAR2
,p_index IN NUMBER
,p_top_model_line_id IN NUMBER
,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE)
RETURN BOOLEAN
IS
I NUMBER;
SELECT open_flag
INTO l_open_flag
FROM oe_order_lines
where line_id =
(SELECT line_id
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND component_code = SUBSTR(p_component_code, 1,
INSTR(p_component_code, '-', -1) - 1));
SELECT ordered_item, line_number, shipment_number, option_number
INTO l_ordered_item, l_line_number, l_shipment_number, l_option_number
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND component_code = p_component_code;
it selects the min and max quantity from bom_explosions table. Some or all
of the options might not have the min max range set up in the BOM set up.
if the ordered quantity is not within the range, it popuplates a message
and it sets the return staus to error.
If all the options are within the min max range, we return success.
------------------------------------------------------------------------*/
Procedure Check_Min_Max
( p_top_bill_sequence_id IN NUMBER
,p_model_qty IN NUMBER
,p_effective_date IN DATE
,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
,x_valid_config OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
I NUMBER;
SELECT /*+ INDEX(BE1 BOM_EXPLOSIONS_N1) */
nvl(be1.high_quantity,0), nvl(be1.low_quantity,0)
INTO l_max_allowed_qty, l_min_allowed_qty
FROM bom_explosions be1
WHERE be1.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id
AND be1.component_code = p_options_tbl(I).component_code
AND be1.effectivity_date <=
p_effective_date
AND be1.disable_date >
p_effective_date
AND be1.explosion_type = 'OPTIONAL'
AND be1.plan_level >= 0;
one option selected.
-------------------------------------------------------------------------*/
PROCEDURE Check_Class_Has_Options
(p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
,x_complete_config OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
I NUMBER;
oe_debug_pub.add('NO NEED TO CHECK , EITHER DELETE OR CLOSE' , 1 ) ;
finds out if more than one option is selected. If so, it popuplates a error
message and sets the return status to error.
If all the classes with mutually exclusive options have only one option
selected, it returs success.
-------------------------------------------------------------------------*/
PROCEDURE Check_Mut_Excl_Options
( p_top_bill_sequence_id IN NUMBER
,p_effective_date IN DATE
,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
,x_valid_config OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
I NUMBER;
SELECT /*+ INDEX(BOMEXP BOM_EXPLOSIONS_N1) */
bomexp.description, bomexp.component_code
FROM BOM_EXPLOSIONS BOMEXP
WHERE bomexp.explosion_type = 'OPTIONAL'
AND bomexp.top_bill_sequence_id = p_top_bill_sequence_id
AND bomexp.plan_level >= 0
AND bomexp.effectivity_date <=
p_effective_date
AND bomexp.disable_date >
p_effective_date
AND bomexp.bom_item_type in ( 1, 2 ) /* Model, Class */
AND bomexp.mutually_exclusive_options = 1 /* Exclusive */
ORDER BY bomexp.sort_order;
p_options_tbl(I).ordered_quantity <> 0 -- if 0, either will be deleted or closed
THEN
l_count := l_count + 1;
check if at least one option is selectd. If not populte error messate and
set retruns status to error.
-------------------------------------------------------------------------*/
PROCEDURE Check_Mandatory_Classes
( p_top_bill_sequence_id IN NUMBER
,p_top_model_line_id IN NUMBER
,p_effective_date IN DATE
,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
,x_complete_config OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_top_bill_sequence_id NUMBER;
SELECT /*+ INDEX(BOMEXP BOM_EXPLOSIONS_N1) */
bomexp.description, bomexp.component_code
--,bomexp.sort_order???? perf
FROM bom_explosions bomexp
WHERE bomexp.explosion_type = 'OPTIONAL'
AND bomexp.top_bill_sequence_id = p_top_bill_sequence_id
AND bomexp.plan_level >= 0
AND bomexp.effectivity_date <=
p_effective_date
AND bomexp.disable_date >
p_effective_date
AND bomexp.bom_item_type IN ( 1, 2 ) -- Model, Class
AND bomexp.optional = 2; -- Mandatory
SELECT bomexp.description, bomexp.component_code
FROM OE_ORDER_LINES OECFG
, BOM_EXPLOSIONS BOMEXP
WHERE OECFG.TOP_MODEL_LINE_ID = p_top_model_line_id
AND OECFG.SERVICE_REFERENCE_LINE_ID IS NULL
AND OECFG.ITEM_TYPE_CODE IN ( 'MODEL', 'CLASS' )
AND BOMEXP.EXPLOSION_TYPE = 'OPTIONAL'
AND BOMEXP.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id ???????? + 0
AND BOMEXP.PLAN_LEVEL >= 0
AND BOMEXP.EFFECTIVITY_DATE <= sysdate
AND BOMEXP.DISABLE_DATE > sysdate
AND BOMEXP.BOM_ITEM_TYPE IN ( 1, 2 ) Model, Class
AND BOMEXP.OPTIONAL = 2 Mandatory
AND BOMEXP.Component_code like OECFG.component_code || '%'
AND SUBSTR( BOMEXP.COMPONENT_CODE, 1,
LENGTH( RTRIM( BOMEXP.COMPONENT_CODE,
'0123456789' ) ) - 1 ) = OECFG.COMPONENT_CODE
AND NOT EXISTS (
SELECT NULL
FROM OE_ORDER_LINES OEOPT
WHERE OEOPT.TOP_MODEL_LINE_ID = p_top_model_line_id
AND OEOPT.SERVICE_REFERENCE_LINE_ID IS NULL
AND OEOPT.COMPONENT_CODE = BOMEXP.COMPONENT_CODE
)
ORDER BY BOMEXP.SORT_ORDER;
SELECT open_flag, ordered_quantity
INTO l_open_flag, l_ordered_quantity
FROM oe_order_lines
where line_id =
(SELECT line_id
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND component_code = SUBSTR(p_component_code, 1,
INSTR(p_component_code, '-', -1) - 1));
SELECT ordered_item, line_number, shipment_number, option_number
INTO l_ordered_item, l_line_number, l_shipment_number, l_option_number
FROM oe_order_lines
WHERE top_model_line_id = p_top_model_line_id
AND component_code = p_component_code;