The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Op Res does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE (wdj.status_type <> 1
OR
NOT EXISTS(SELECT NULL
FROM WIP_OPERATION_RESOURCES wor
WHERE wor.resource_seq_num = p_resource_seq_num
AND wor.operation_seq_num = p_operation_seq_num
AND wor.wip_entity_id = wdj.wip_entity_id)
)
AND wdj.lot_number = p_lot_number
AND wdj.organization_id = p_organization_id
AND wdj.primary_item_id = p_rev_item_id
) ;
SELECT 'Op Res does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
, WIP_ENTITIES we
, WIP_ENTITIES we1
, WIP_ENTITIES we2
WHERE (wdj.status_type <> 1
OR
NOT EXISTS (SELECT NULL
FROM WIP_OPERATION_RESOURCES wor
WHERE resource_seq_num = p_resource_seq_num
AND operation_seq_num = p_operation_seq_num
AND wip_entity_id = wdj.wip_entity_id)
)
AND wdj.wip_entity_id = we.wip_entity_id
AND we.organization_Id = p_organization_id
AND we.wip_entity_name >= we1.wip_entity_name
AND we.wip_entity_name <= we2.wip_entity_name
AND we1.wip_entity_id = p_from_wip_entity_id
AND we2.wip_entity_id = NVL(p_to_wip_entity_id, p_from_wip_entity_id)
) ;
SELECT 'Op Res does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE (wdj.status_type <> 1
OR
NOT EXISTS(SELECT NULL
FROM WIP_OPERATION_RESOURCES wor
WHERE resource_seq_num = p_resource_seq_num
AND operation_seq_num = p_operation_seq_num
AND wip_entity_id = wdj.wip_entity_id)
)
AND wdj.wip_entity_id = p_from_wip_entity_id
) ;
SELECT acd_type
FROM BOM_OPERATION_SEQUENCES
WHERE operation_sequence_id = p_op_seq_id ;
SELECT 'Resource is invalid'
FROM DUAL
WHERE NOT EXISTS(
SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
, BOM_DEPARTMENT_RESOURCES bdr
, BOM_RESOURCES br
WHERE NVL(br.disable_date, bos.effectivity_date + 1)
> bos.effectivity_date
AND NVL(br.disable_date, sysdate + 1)
> trunc(sysdate)
AND bdr.department_id = bos.department_id
AND bos.operation_sequence_id = p_op_seq_id
AND bdr.resource_id = br.resource_id
AND br.resource_id = p_resource_id ) ;
SELECT 'Activity is invalid'
FROM DUAL
WHERE NOT EXISTS(
SELECT NULL
FROM bom_operational_routings bor
, BOM_OPERATION_SEQUENCES bos
, CST_ACTIVITIES ca
WHERE bor.organization_id =
NVL(ca.organization_id, bor.organization_id)
AND NVL(TRUNC(ca.disable_date), TRUNC(bos.effectivity_date) + 1)
> TRUNC(bos.effectivity_date)
AND bor.routing_sequence_id = bos.routing_sequence_id
AND bos.operation_sequence_id = p_op_seq_id
AND ca.activity_id = p_activity_id ) ;
SELECT 'Setup Id is invalid'
FROM DUAL
WHERE NOT EXISTS(
SELECT NULL
FROM BOM_RESOURCE_SETUPS brs
, BOM_SETUP_TYPES bst
WHERE brs.setup_id = bst.setup_id
AND brs.organization_id = bst.organization_id
AND brs.resource_id = p_resource_id
AND bst.organization_id = p_organization_id
AND bst.setup_id = p_setup_id
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES
WHERE schedule_flag = p_schedule_flag
AND resource_seq_num <> p_res_seq_num
AND operation_sequence_id = p_op_seq_id
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
WHERE bor.schedule_flag = p_schedule_flag
AND bor.resource_seq_num <> p_res_seq_num
AND bor.operation_sequence_id = old_operation_sequence_id
AND bos.acd_type = l_ACD_CHANGE
AND bos.operation_sequence_Id = p_op_seq_id
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES
WHERE schedule_flag NOT IN (p_schedule_flag, l_NO_SCHEDULE)
AND operation_sequence_id = p_op_seq_id
AND resource_seq_num <> p_res_seq_num
AND schedule_seq_num = p_sch_seq_num
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
WHERE bor.schedule_flag NOT IN (p_schedule_flag, l_NO_SCHEDULE)
AND bor.resource_seq_num <> p_res_seq_num
AND bor.operation_sequence_id = old_operation_sequence_id
AND bor.schedule_seq_num = p_sch_seq_num
AND bos.acd_type = l_ACD_CHANGE
AND bos.operation_sequence_Id = p_op_seq_id
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES
WHERE schedule_flag IN (L_PRIOR, L_NEXT)
AND operation_sequence_id = p_op_seq_id
AND resource_seq_num <> p_res_seq_num
AND schedule_seq_num = p_sch_seq_num
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
WHERE bor.schedule_flag IN (L_PRIOR, L_NEXT)
AND bor.resource_seq_num <> p_res_seq_num
AND bor.operation_sequence_id = old_operation_sequence_id
AND bor.schedule_seq_num = p_sch_seq_num
AND bos.acd_type = l_ACD_CHANGE
AND bos.operation_sequence_Id = p_op_seq_id
) ;
SELECT 'Not OSP Resource'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_RESOURCES
WHERE resource_id = p_resource_id
AND organization_id = p_organization_id
AND cost_code_type <> l_OSP -- 4 : Outside Processing
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES
WHERE autocharge_type = l_PO_MOVE
AND resource_seq_num <> p_res_seq_num
AND operation_sequence_id = p_op_seq_id
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
WHERE bor.autocharge_type = l_PO_MOVE
AND bor.resource_seq_num <> p_res_seq_num
AND bor.operation_sequence_id = old_operation_sequence_id
AND bos.acd_type = l_ACD_CHANGE
AND bos.operation_sequence_id = p_op_seq_id
) ;
SELECT 'No Dept Location'
FROM SYS.DUAL
WHERE NOT EXISTS( SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
, BOM_DEPARTMENTS bd
WHERE bd.location_id IS NOT NULL
AND bd.department_id = bos.department_id
AND bos.operation_sequence_id = p_op_seq_id
) ;
SELECT uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE uom_code = p_uom_code ;
SELECT unit_of_measure
FROM BOM_RESOURCES
WHERE resource_id = p_resource_id ;
SELECT 'Unable to convert'
FROM SYS.DUAL
WHERE NOT EXISTS(
SELECT NULL
FROM MTL_UOM_CONVERSIONS muc1,
MTL_UOM_CONVERSIONS muc2
WHERE muc1.uom_code = p_res_uom_code
AND muc1.uom_class = p_res_uom_class
AND muc1.inventory_item_id = 0
AND NVL(muc1.disable_date, SYSDATE + 1) > SYSDATE
AND muc2.uom_code = p_hour_uom_code
AND muc2.inventory_item_id = 0
AND muc2.uom_class = muc1.uom_class ) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES
WHERE principle_flag = 1 -- Yes
AND NVL(acd_type, l_ACD_ADD) <> l_ACD_DISABLE
AND nvl(substitute_group_num, resource_seq_num) = nvl(p_sub_group_num, p_res_seq_num)
AND resource_seq_num <> p_res_seq_num
AND operation_sequence_id = p_op_seq_id
) ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
WHERE bor.principle_flag = 1 -- Yes
AND bor.substitute_group_num = p_sub_group_num
AND bor.resource_seq_num <> p_res_seq_num
AND bor.operation_sequence_id = bos.old_operation_sequence_id
AND bos.acd_type = l_ACD_CHANGE
AND bos.operation_sequence_id = p_op_seq_id
) ;
select 1 from dual
where exists (select 1 --schedule_seq_num, count(distinct(substitute_group_num)) sgn_count
from bom_operation_resources
where operation_sequence_id = p_op_seq_id
and schedule_seq_num = p_sch_seq_num
and substitute_group_num <> p_sub_grp_num /* is not null*/
and resource_seq_num <> p_res_seq_num);
select substitute_group_num,
min(schedule_seq_num) mn_ssn1, min(resource_seq_num) mn_rsn1,
max(schedule_seq_num) mx_ssn1, max(resource_seq_num) mx_rsn1
from bom_operation_resources
where operation_sequence_id = p_op_seq_id
and substitute_group_num is not null
group by substitute_group_num
order by substitute_group_num;
select substitute_group_num,
min(schedule_seq_num) mn_ssn2, --min(resource_seq_num) mn_rsn2,
max(schedule_seq_num) mx_ssn2 --max(resource_seq_num) mx_rsn2
from bom_sub_operation_resources
where operation_sequence_id = p_op_seq_id
and substitute_group_num = l_sgn
group by substitute_group_num
order by substitute_group_num;
* return an error if the operation resource is UPDATE
* and record DOES NOT EXIST.
* In case of UPDATE if record exists, then the procedure
* will return old record in the old entity parameters
* with a success status.
*********************************************************************/
PROCEDURE Check_Existence
( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
, p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
, x_old_rev_op_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
, x_old_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
, x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_Token_Tbl Error_Handler.Token_Tbl_Type;
( BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_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_RES_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
) ;
SELECT reference_flag
FROM BOM_OPERATION_SEQUENCES
WHERE operation_sequence_id = p_op_seq_id ;
* Procedure : Check_Attribute (Validation) for CREATE and UPDATE
* by ECO BO and internally called by RTG BO
* Parameters IN : Revised Operation Resource exposed column record
* Revised Operation Resource unexposed column record
* Parameters out: Return Status
* Message Token Table
* Purpose : Attribute validation procedure will validate each
* attribute of Revised operation resource in its entirety.
* If the validation of a column requires looking at some
* other columns value then the validation is done at
* the Entity level instead.
* All errors in the attribute validation are accumulated
* before the procedure returns with a Return_Status
* of 'E'.
*********************************************************************/
PROCEDURE Check_Attributes
( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
, p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
, x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1) ;
IF p_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Operation Resource Attr Validation: Missing Value. . . ' || l_return_status) ;
SELECT count(setup_id)
FROM bom_resource_setups
WHERE resource_id = p_resource_id
AND organization_id = p_org_id;
SELECT 'Rev Op Resource Not Exists'
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
, BOM_OPERATION_RESOURCES bor
WHERE bor.resource_seq_num = p_res_seq_num
AND bor.operation_sequence_id = bos.old_operation_sequence_id
AND bos.operation_sequence_id = p_op_seq_id
) ;
SELECT 'Sub Res Exists'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES
WHERE operation_sequence_id = p_op_seq_id
AND substitute_group_num = p_sub_group_num )
AND NOT EXISTS ( SELECT NULL
FROM BOM_OPERATION_RESOURCES
WHERE substitute_group_num = p_sub_group_num
AND resource_seq_num <> p_res_seq_num
AND operation_sequence_id = p_op_seq_id ) ;
SELECT 'Sub Res Exists'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES bsor
, BOM_OPERATION_SEQUENCES bos
WHERE bsor.substitute_group_num = p_sub_group_num
AND bsor.operation_sequence_id = bos.old_operation_sequence_id
AND bos.operation_sequence_id = p_op_seq_id )
AND NOT EXISTS (SELECT NULL
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
WHERE bor.substitute_group_num = p_sub_group_num
AND resource_seq_num <> p_res_seq_num
AND bor.operation_sequence_id = bos.old_operation_sequence_id
AND bos.operation_sequence_id = p_op_seq_id ) ;
SELECT 'Sub PO Move Exists'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES
WHERE autocharge_type = l_PO_MOVE
AND substitute_group_num = p_sub_group_num
AND operation_sequence_id = p_op_seq_id ) ;
SELECT 'Sub PO Move Exists'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES bsor
, BOM_OPERATION_SEQUENCES bos
WHERE autocharge_type = l_PO_MOVE
AND bsor.substitute_group_num = p_sub_group_num
AND bsor.operation_sequence_id = bos.old_operation_sequence_id
AND bos.operation_sequence_id = p_op_seq_id ) ;
SELECT 'Sub PO Move Exists'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES
WHERE schedule_flag = p_schedule_flag
AND substitute_group_num = p_sub_group_num
AND operation_sequence_id = p_op_seq_id ) ;
SELECT 'Sub PO Move Exists'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES bsor
, BOM_OPERATION_SEQUENCES bos
WHERE bsor.schedule_flag = p_schedule_flag
AND bsor.substitute_group_num = p_sub_group_num
AND bsor.operation_sequence_id = bos.old_operation_sequence_id
AND bos.operation_sequence_id = p_op_seq_id ) ;
/* User is allowed to update res attributes
IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
THEN
IF NOT Check_Res_Attr_changed
( p_rev_op_resource_rec => l_rev_op_resource_rec
, p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
, p_old_rev_op_resource_rec => p_old_rev_op_resource_rec
, p_old_rev_op_res_unexp_rec => p_old_rev_op_res_unexp_rec )
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_RES_NOT_UPDATE_IN_CHANGE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => l_token_tbl
) ;
IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
AND l_rev_op_resource_rec.acd_type <> p_old_rev_op_resource_rec.acd_type
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_RES_ACD_TYPENOT_UPDATEABLE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => l_token_tbl
) ;
SELECT nvl(batchable,2) INTO l_batchable
FROM bom_resources
WHERE resource_id = p_rev_op_res_unexp_rec.resource_id;
(BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE)
THEN
--
-- APS Enhancement for Routings.
-- Verify that if a resource has setups defined, or is Batchable then
-- the Assigned Units for that Resource have to be 1.
--
IF p_rev_op_resource_rec.assigned_units <> FND_API.G_MISS_NUM THEN
OPEN get_setups (p_rev_op_res_unexp_rec.resource_id, p_rev_op_res_unexp_rec.organization_id);
SELECT nvl(batchable,2) INTO l_batchable
FROM bom_resources
WHERE resource_id = p_rev_op_res_unexp_rec.resource_id;
IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
OR
(l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
)
THEN
/**** This validation is not required ****
--
-- Scheduled Resource
-- Check if there are associated sub Next or Prior resources on
-- this resource
--
IF ( l_rev_op_resource_rec.schedule_flag <> l_NEXT
OR ( l_rev_op_res_unexp_rec.substitute_group_number
<> p_old_rev_op_res_unexp_rec.substitute_group_number ))
AND p_old_rev_op_resource_rec.schedule_flag = l_NEXT
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Check if you can update Next or Prior Schedule Res to others. . . . ' || l_return_status) ;
( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
) ;
( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
) ;
( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
) ;
( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
) ;
('Check if you can update PO Move to others. . . . ' || l_return_status) ;
( p_message_name => 'BOM_RES_POMOVE_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
) ;
( p_message_name => 'BOM_RES_POMOVE_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
) ;
END IF ; -- Transation: UPDATE
('End of Validation specific to the Transaction Type of Update : ' || l_return_status) ;
(BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE)
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Common Validateion for Transaction Type : Create and Update . . . . ' || l_return_status) ;
l_token_tbl.delete(2) ;
l_token_tbl.delete(3) ;
SELECT RESOURCE_CODE
INTO l_res_code
FROM BOM_RESOURCES_V
WHERE RESOURCE_ID = l_rev_op_res_unexp_rec.resource_id;
Select resource_code into l_res_code
from bom_resources_v
where resource_id=l_rev_op_res_unexp_rec.resource_id;
Select resource_code into l_res_code_2
from bom_resources_v
where resource_id=l_res_id;
END IF ; -- Transaction Type : Create and Update
SELECT COUNT(1)
INTO l_same_rsc_ssn
FROM BOM_OPERATION_RESOURCES
WHERE
SCHEDULE_SEQ_NUM = p_sch_seq_num
AND p_sch_seq_num IS NOT NULL
AND SCHEDULE_SEQ_NUM IS NOT NULL
AND RESOURCE_SEQ_NUM <> p_res_seq_num
AND RESOURCE_ID = p_resource_id
AND OPERATION_SEQUENCE_ID = p_op_seq_id ;
SELECT resource_id
FROM bom_operation_resources
WHERE operation_sequence_id = p_op_seq_id
AND resource_seq_num <> p_res_seq_num
AND nvl(schedule_seq_num,resource_seq_num) = nvl(p_sch_seq_num,p_res_seq_num)
AND schedule_flag not in (p_sch_flag,l_NO_SCHEDULE)
AND rownum=1;
SELECT resource_id
FROM bom_sub_operation_resources
WHERE operation_sequence_id = p_op_seq_id
AND schedule_seq_num = nvl(p_sch_seq_num,p_res_seq_num)
AND schedule_flag not in (p_sch_flag,l_NO_SCHEDULE)
AND rownum=1;