[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X' date_available FROM sys.dual
WHERE EXISTS (
SELECT 1 from BOM_Component_All_Operations_V
WHERE Component_Item_Id = p_Component_Item_Id
AND Bill_Sequence_Id = p_Bill_Sequence_Id
AND Operation_Seq_Num = p_Operation_Seq_Num
/* AND
(
( p_entity = 'COPS'
AND
(p_comp_operation_seq_id IS NULL
OR
p_comp_operation_seq_id = FND_API.G_MISS_NUM
OR
comp_operation_seq_id <> p_comp_operation_seq_id)
)
OR
( p_entity = 'RC'
AND
(p_component_sequence_id IS NULL
OR
p_component_sequence_id = FND_API.G_MISS_NUM
OR
comp_operation_seq_id <> 0 -- row belongs to comp ops
OR
component_sequence_id <> p_component_sequence_id)
)
)
*/
/* AND
(
p_RowId IS NULL
or
p_Rowid = FND_API.G_MISS_CHAR
or
( decode(p_entity,'COPS',bco_rowid,
'RC',bic_RowId,' ') <> p_RowID )
)
*/
AND
(
p_RowId IS NULL
or
p_Rowid = FND_API.G_MISS_CHAR
or
row_id <> p_Rowid)
AND ( p_Disable_Date IS NULL
OR ( to_char(p_Disable_Date,'YYYY/MM/DD HH24:MI:SS') > to_char(Effectivity_Date,'YYYY/MM/DD HH24:MI:SS'))) -- 5954279
AND ( to_char(p_Effectivity_Date,'YYYY/MM/DD HH24:MI:SS') < to_char(Disable_Date,'YYYY/MM/DD HH24:MI:SS') -- 5954279
OR Disable_Date IS NULL
)
AND implementation_date IS NOT NULL -- Bug 3182080
);
SELECT 'X' unit_available FROM sys.dual
WHERE EXISTS (
SELECT 1 from BOM_INVENTORY_COMPONENTS
WHERE Component_Item_Id = p_Component_Item_Id
AND Bill_Sequence_Id = p_Bill_Sequence_Id
AND Operation_Seq_Num = p_Operation_Seq_Num
/* AND
(
( p_entity = 'COPS'
AND
(p_comp_operation_seq_id IS NULL
OR
p_comp_operation_seq_id = FND_API.G_MISS_NUM
OR
comp_operation_seq_id <> p_comp_operation_seq_id)
)
OR
( p_entity = 'RC'
AND
(p_component_sequence_id IS NULL
OR
p_component_sequence_id = FND_API.G_MISS_NUM
OR
comp_operation_seq_id <> 0
OR
component_sequence_id <> p_component_sequence_id)
)
) */
/*
AND
(
p_RowId IS NULL
or
p_Rowid = FND_API.G_MISS_CHAR
or
( decode(p_entity,'COPS',bco_rowid,
'RC',bic_RowId,' ') <> p_RowID )
)
*/
AND
(
p_RowId IS NULL
or
p_Rowid = FND_API.G_MISS_CHAR
or
rowid <> p_Rowid)
AND (p_To_End_Item_Number IS NULL
OR p_To_End_Item_Number >=
From_End_Item_Unit_Number)
AND (p_From_End_Item_Number <=
To_End_Item_Unit_Number
OR To_End_Item_Unit_Number IS NULL
)
AND ( IMPLEMENTATION_DATE IS NOT NULL )
AND ( DISABLE_DATE IS NULL ) --bug:5347036 Consider enabled components only
);
SELECT 'X' unit_available FROM sys.dual
WHERE EXISTS (
SELECT 1 from BOM_COMPONENT_OPERATIONS BCO,
BOM_INVENTORY_COMPONENTS BIC
WHERE BCO.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
AND BIC.Component_Item_Id = p_Component_Item_Id
AND BIC.Bill_Sequence_Id = p_Bill_Sequence_Id
AND BCO.Operation_Seq_Num = p_Operation_Seq_Num
/* AND
(
( p_entity = 'COPS'
AND
(p_comp_operation_seq_id IS NULL
OR
p_comp_operation_seq_id = FND_API.G_MISS_NUM
OR
comp_operation_seq_id <> p_comp_operation_seq_id)
)
OR
( p_entity = 'RC'
AND
(p_component_sequence_id IS NULL
OR
p_component_sequence_id = FND_API.G_MISS_NUM
OR
comp_operation_seq_id <> 0
OR
component_sequence_id <> p_component_sequence_id)
)
) */
/*
AND
(
p_RowId IS NULL
or
p_Rowid = FND_API.G_MISS_CHAR
or
( decode(p_entity,'COPS',bco_rowid,
'RC',bic_RowId,' ') <> p_RowID )
)
*/
AND
(
p_RowId IS NULL
or
p_Rowid = FND_API.G_MISS_CHAR
or
bco.rowid <> p_Rowid)
AND (p_To_End_Item_Number IS NULL
OR p_To_End_Item_Number >=
BIC.From_End_Item_Unit_Number)
AND (p_From_End_Item_Number <=
BIC.To_End_Item_Unit_Number
OR BIC.To_End_Item_Unit_Number IS NULL
)
AND ( bic.IMPLEMENTATION_DATE IS NOT NULL )
AND ( bic.DISABLE_DATE IS NULL ) --bug:5347036 Consider enabled components only
);
SELECT 1
INTO p_dummy
FROM bom_bill_of_materials
WHERE bill_sequence_id = source_bill_sequence_id
AND bill_sequence_id = p_bom_comp_ops_unexp_rec.bill_Sequence_id;
SELECT msi.bom_enabled_flag
INTO l_assy_bom_enabled
FROM mtl_system_items_b msi,
bom_bill_of_materials bbom
WHERE bbom.bill_sequence_id = p_bom_comp_ops_Unexp_Rec.bill_sequence_id
AND bbom.assembly_item_id = msi.inventory_item_id
AND bbom.organization_id = msi.organization_id;
/* Select the didsable date which is one of the key parameters */
SELECT disable_date INTO l_disable_date FROM bom_inventory_components WHERE
component_sequence_id = p_bom_comp_ops_unexp_rec.component_sequence_id;
If( p_bom_comp_ops_rec.transaction_type = BOM_globals.G_OPR_UPDATE and
p_bom_comp_ops_rec.new_additional_op_seq_num is not null
and p_bom_comp_ops_rec.new_additional_op_seq_num <> FND_API.G_MISS_NUM) then
l_Additional_Op_Seq_Number := p_bom_comp_ops_rec.new_additional_op_seq_num;
/* When the component operation is updated with new Component operation, It should be checked that
the New Component operation does not exists already */
IF ( p_bom_comp_ops_rec.new_additional_op_seq_num is not null
and p_bom_comp_ops_rec.new_additional_op_seq_num <> FND_API.G_MISS_NUM
and p_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_UPDATE) THEN
select count(*) into l_temp_var
FROM BOM_COMPONENT_OPERATIONS
WHERE OPERATION_SEQ_NUM = p_bom_comp_ops_rec.new_additional_op_seq_num
AND COMPONENT_SEQUENCE_ID = p_bom_Comp_ops_Unexp_Rec.component_sequence_id;
SELECT operation_seq_num
FROM bom_operation_sequences bos
WHERE routing_sequence_id =
(SELECT common_routing_sequence_id
FROM bom_operational_routings bor
WHERE assembly_item_id = p_bom_comp_ops_unexp_rec.assembly_item_id
and organization_id = p_bom_comp_ops_unexp_rec.organization_id
and nvl(alternate_routing_designator,'NONE') =
nvl(p_bom_comp_ops_rec.alternate_bom_code, 'NONE')
)
and nvl(trunc(disable_date), trunc(sysdate)+1) > trunc(sysdate) and nvl(operation_type,1) = 1;
SELECT operation_seq_num
FROM bom_operation_sequences bos
WHERE routing_sequence_id =
(SELECT common_routing_sequence_id
FROM bom_operational_routings bor
WHERE assembly_item_id = p_bom_comp_ops_unexp_rec.assembly_item_id
and organization_id = p_bom_comp_ops_unexp_rec.organization_id
and alternate_routing_designator IS NULL
)
and nvl(trunc(disable_date), trunc(sysdate)+1) > trunc(sysdate) and nvl(operation_type,1) = 1;
* error if the operation is UPDATE and the record DOES NOT
* EXIST.
* In case of UPDATE if the record exists then the procedure
* will return the old record in the old entity parameters
* with a success status.
****************************************************************************/
PROCEDURE Check_Existence
( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
, p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
, x_old_bom_comp_ops_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
, x_old_bom_comp_ops_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
(Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
THEN
Error_Handler.Add_Error_Token
( x_Mesg_token_tbl => l_Mesg_Token_Tbl
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_message_name => 'BOM_COMP_OPS_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
);
Bom_Globals.G_OPR_UPDATE;
SELECT component_sequence_id
FROM bom_inventory_components
WHERE component_item_id= p_bom_comp_ops_unexp_rec.component_item_id
AND operation_seq_num=p_bom_comp_ops_rec.operation_sequence_number
AND effectivity_date = p_bom_comp_ops_rec.start_effective_date
AND bill_sequence_id = p_bom_comp_ops_unexp_rec.bill_sequence_id;