[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Sub Res does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE wdj.lot_number = p_lot_number
AND (wdj.status_type <> 1
OR
NOT EXISTS(SELECT NULL
FROM WIP_SUB_OPERATION_RESOURCES wsor
WHERE substitute_group_num = p_sub_group_num
AND resource_id = p_resource_id
AND operation_seq_num = p_operation_seq_num
AND wip_entity_id = wdj.wip_entity_id)
)
AND wdj.lot_number = p_lot_number
) ;
SELECT 'Sub Res does not exist'
FROM DUAL
WHERE NOT EXISTS ( SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE wdj.lot_number = p_lot_number
AND wdj.status_type = 1
AND EXISTS ( SELECT NULL
FROM WIP_SUB_OPERATION_RESOURCES wsor
WHERE substitute_group_num = p_sub_group_num
AND resource_id = p_resource_id
AND operation_seq_num = p_operation_seq_num
AND wip_entity_id = wdj.wip_entity_id
)
);
SELECT 'Sub 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_SUB_OPERATION_RESOURCES wsor
WHERE substitute_group_num = p_sub_group_num
AND resource_id = p_resource_id
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.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 'Sub 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_SUB_OPERATION_RESOURCES wsor
WHERE substitute_group_num = p_sub_group_num
AND resource_id = p_resource_id
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 'Related Schedule Resource does not exist'
FROM SYS.DUAL
WHERE NOT EXISTS( SELECT NULL
FROM BOM_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 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES
WHERE schedule_flag = p_schedule_flag
AND resource_id <> p_resource_id
AND substitute_group_num = p_sub_group_num
AND operation_sequence_id = p_op_seq_id
) ;
SELECT 'Related PO Move Resource does not exist'
FROM SYS.DUAL
WHERE NOT EXISTS( SELECT NULL
FROM BOM_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 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES
WHERE autocharge_type = l_PO_MOVE
AND resource_id <> p_resource_id
AND substitute_group_num = p_sub_group_num
AND operation_sequence_id = p_op_seq_id
) ;
SELECT old_operation_sequence_id
INTO l_old_op_seq_id
FROM BOM_OPERATION_SEQUENCES
WHERE operation_sequence_id = p_op_seq_id ;
SELECT 'Already exists'
FROM SYS.DUAL
WHERE EXISTS( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES
WHERE principle_flag = 1 -- Yes
AND NVL(acd_type, l_ACD_ADD) <> l_ACD_DISABLE
AND (
( resource_id <> p_res_id )
OR ( ( resource_id = p_res_id )
AND ( basis_type <> p_basis_type
OR schedule_flag <> p_schedule_flag ) ) /* Added for bug 13005178 */
)
AND substitute_group_num = p_sub_group_num
AND replacement_group_num = p_rep_group_num
AND operation_sequence_id = p_op_seq_id
) ;
SELECT resource_id
FROM bom_operation_resources
WHERE operation_sequence_id = p_op_seq_id
AND nvl(schedule_seq_num,resource_seq_num) = p_sch_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 = p_sch_seq_num
AND schedule_flag not in (p_sch_flag,l_NO_SCHEDULE)
AND (
substitute_group_num <> p_sub_grp_num
OR replacement_group_num <> p_rep_grp_num
OR basis_type <> p_basis_type
OR resource_id <> p_in_res_id
)
AND rownum=1;
* 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_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
, p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
, x_old_rev_sub_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
, x_old_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_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;
SELECT br.DEFAULT_BASIS_TYPE
INTO l_default_basis_type
FROM BOM_RESOURCES br
WHERE br.RESOURCE_ID = p_rev_sub_res_unexp_rec.resource_id;
(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_SUB_RES_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
) ;
* Procedure : Check_Attribute (Validation) for CREATE and UPDATE
* by ECO BO and internally called by RTG BO
* Parameters IN : Revised Sub Operation Resource exposed column record
* Revised Sub Operation Resource unexposed column record
* Parameters out: Return Status
* Message Token Table
* Purpose : Attribute validation procedure will validate each
* attribute of Sub 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_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
, p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Sub Operation Resource Attr Validation: Missing Value. . . ' ) ;
( p_Message_Name => 'BOM_SUB_RES_CODE_NOTUPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
OR (p_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
AND p_rev_sub_resource_rec.schedule_sequence_number = FND_API.G_MISS_NUM)
OR p_rev_sub_resource_rec.schedule_sequence_number = 0
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'BOM_SSN_ZERO_VALUE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
--, p_Token_Tbl => l_Token_Tbl
);
SELECT count(setup_id)
FROM bom_resource_setups
WHERE resource_id = p_resource_id
AND organization_id = p_org_id;
SELECT 'Rev Sub Op Resource Not Exists'
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
, BOM_SUB_OPERATION_RESOURCES bsor
WHERE bsor.substitute_group_num = p_sub_group_num
AND bsor.resource_id = p_resource_id
AND bsor.operation_sequence_id = bos.operation_sequence_id
AND bos.operation_sequence_id = p_op_seq_id
) ;
SELECT 'Sub Res Duplicate'
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_SUB_OPERATION_RESOURCES
WHERE NVL(ACD_TYPE, 1) = NVL(p_acd_type, 1)
AND BASIS_TYPE = p_basis_type
AND RESOURCE_ID = p_resource_id
AND SUBSTITUTE_GROUP_NUM = p_substitute_group_number
AND REPLACEMENT_GROUP_NUM = p_replacement_group_number -- bug 3741570
AND OPERATION_SEQUENCE_ID = p_op_seq_id
AND SCHEDULE_FLAG = p_schedule_flag /* Added filter for bug 13005178 */
) ;
l_token_tbl.delete(3) ;
l_token_tbl.delete(3) ;
IF l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
AND l_rev_sub_resource_rec.acd_type <> p_old_rev_sub_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_SUB_RES_ACDTPNT_UPDATEABLE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => l_token_tbl
) ;
l_token_tbl.delete(3) ;
(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_sub_resource_rec.assigned_units <> FND_API.G_MISS_NUM THEN
OPEN get_setups (p_rev_sub_res_unexp_rec.resource_id, p_rev_sub_res_unexp_rec.organization_id);
SELECT nvl(batchable,2) INTO l_batchable
FROM bom_resources
WHERE resource_id = p_rev_sub_res_unexp_rec.resource_id;
IF l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
-- In this release, Acd type : Change is not allowed.
--
-- OR
-- (l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
-- AND l_rev_sub_resource_rec.acd_type = l_ACD_CHANGE
-- )
THEN
NULL ;
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) ;
AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
FOR l_duplicate_rec IN l_duplicate_csr
( p_resource_id => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
l_rev_sub_res_unexp_rec.resource_id )
, p_substitute_group_number => nvl(l_rev_sub_resource_rec.substitute_group_number,
l_rev_sub_res_unexp_rec.substitute_group_number)
, p_replacement_group_number => NVL(l_rev_sub_resource_rec.new_replacement_Group_number, -- bug 3741570
l_rev_sub_resource_rec.replacement_Group_number)
, p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
, p_acd_type => l_rev_sub_resource_rec.acd_type
, p_basis_type => NVL(l_rev_sub_resource_rec.new_basis_type,
l_rev_sub_resource_rec.basis_type)
, p_schedule_flag => NVL(l_rev_sub_resource_rec.new_schedule_flag,
l_rev_sub_resource_rec.schedule_flag) /* Added for bug 13005178 */
)
LOOP
l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
l_token_tbl.delete(3) ;
l_token_tbl.delete(3) ;
l_token_tbl.delete(3) ;
Select resource_code into l_res_code
from bom_resources_v
where resource_id=l_rev_sub_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