[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(
x_OPERATION_SEQUENCE_ID NUMBER,
x_SUBSTITUTE_GROUP_NUM NUMBER,
x_RESOURCE_ID NUMBER,
x_SCHEDULE_SEQ_NUM NUMBER,
x_REPLACEMENT_GROUP_NUM NUMBER,
x_ACTIVITY_ID NUMBER,
x_STANDARD_RATE_FLAG NUMBER,
x_ASSIGNED_UNITS NUMBER,
x_USAGE_RATE_OR_AMOUNT NUMBER,
x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
x_BASIS_TYPE NUMBER,
x_SCHEDULE_FLAG NUMBER,
x_LAST_UPDATE_DATE DATE,
x_LAST_UPDATED_BY NUMBER,
x_CREATION_DATE DATE,
x_CREATED_BY NUMBER,
x_LAST_UPDATE_LOGIN NUMBER,
x_RESOURCE_OFFSET_PERCENT NUMBER,
x_AUTOCHARGE_TYPE NUMBER,
x_PRINCIPLE_FLAG NUMBER,
x_ATTRIBUTE_CATEGORY VARCHAR2,
x_ATTRIBUTE1 VARCHAR2,
x_ATTRIBUTE2 VARCHAR2,
x_ATTRIBUTE3 VARCHAR2,
x_ATTRIBUTE4 VARCHAR2,
x_ATTRIBUTE5 VARCHAR2,
x_ATTRIBUTE6 VARCHAR2,
x_ATTRIBUTE7 VARCHAR2,
x_ATTRIBUTE8 VARCHAR2,
x_ATTRIBUTE9 VARCHAR2,
x_ATTRIBUTE10 VARCHAR2,
x_ATTRIBUTE11 VARCHAR2,
x_ATTRIBUTE12 VARCHAR2,
x_ATTRIBUTE13 VARCHAR2,
x_ATTRIBUTE14 VARCHAR2,
x_ATTRIBUTE15 VARCHAR2)
IS
BEGIN
INSERT INTO BOM_SUB_OPERATION_RESOURCES(
OPERATION_SEQUENCE_ID,
SUBSTITUTE_GROUP_NUM,
RESOURCE_ID,
SCHEDULE_SEQ_NUM,
REPLACEMENT_GROUP_NUM,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
BASIS_TYPE,
SCHEDULE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE,
PRINCIPLE_FLAG,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
values (
x_OPERATION_SEQUENCE_ID,
x_SUBSTITUTE_GROUP_NUM,
x_RESOURCE_ID,
x_SCHEDULE_SEQ_NUM,
x_REPLACEMENT_GROUP_NUM,
x_ACTIVITY_ID,
x_STANDARD_RATE_FLAG,
x_ASSIGNED_UNITS,
x_USAGE_RATE_OR_AMOUNT,
x_USAGE_RATE_OR_AMOUNT_INVERSE,
x_BASIS_TYPE,
x_SCHEDULE_FLAG,
x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY,
x_CREATION_DATE,
x_CREATED_BY,
x_LAST_UPDATE_LOGIN,
x_RESOURCE_OFFSET_PERCENT,
x_AUTOCHARGE_TYPE,
x_PRINCIPLE_FLAG,
x_ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1,
x_ATTRIBUTE2,
x_ATTRIBUTE3,
x_ATTRIBUTE4,
x_ATTRIBUTE5,
x_ATTRIBUTE6,
x_ATTRIBUTE7,
x_ATTRIBUTE8,
x_ATTRIBUTE9,
x_ATTRIBUTE10,
x_ATTRIBUTE11,
x_ATTRIBUTE12,
x_ATTRIBUTE13,
x_ATTRIBUTE14,
x_ATTRIBUTE15
);
END INSERT_ROW;
PROCEDURE Insert_Row(
x_OPERATION_SEQUENCE_ID NUMBER,
x_SUBSTITUTE_GROUP_NUM NUMBER,
x_RESOURCE_ID NUMBER,
x_SCHEDULE_SEQ_NUM NUMBER,
x_REPLACEMENT_GROUP_NUM NUMBER,
x_ACTIVITY_ID NUMBER,
x_STANDARD_RATE_FLAG NUMBER,
x_ASSIGNED_UNITS NUMBER,
x_USAGE_RATE_OR_AMOUNT NUMBER,
x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
x_BASIS_TYPE NUMBER,
x_SCHEDULE_FLAG NUMBER,
x_LAST_UPDATE_DATE DATE,
x_LAST_UPDATED_BY NUMBER,
x_CREATION_DATE DATE,
x_CREATED_BY NUMBER,
x_LAST_UPDATE_LOGIN NUMBER,
x_RESOURCE_OFFSET_PERCENT NUMBER,
x_AUTOCHARGE_TYPE NUMBER,
x_PRINCIPLE_FLAG NUMBER,
x_ATTRIBUTE_CATEGORY VARCHAR2,
x_ATTRIBUTE1 VARCHAR2,
x_ATTRIBUTE2 VARCHAR2,
x_ATTRIBUTE3 VARCHAR2,
x_ATTRIBUTE4 VARCHAR2,
x_ATTRIBUTE5 VARCHAR2,
x_ATTRIBUTE6 VARCHAR2,
x_ATTRIBUTE7 VARCHAR2,
x_ATTRIBUTE8 VARCHAR2,
x_ATTRIBUTE9 VARCHAR2,
x_ATTRIBUTE10 VARCHAR2,
x_ATTRIBUTE11 VARCHAR2,
x_ATTRIBUTE12 VARCHAR2,
x_ATTRIBUTE13 VARCHAR2,
x_ATTRIBUTE14 VARCHAR2,
x_ATTRIBUTE15 VARCHAR2,
x_SETUP_ID NUMBER)
IS
BEGIN
INSERT INTO BOM_SUB_OPERATION_RESOURCES(
OPERATION_SEQUENCE_ID,
SUBSTITUTE_GROUP_NUM,
RESOURCE_ID,
SCHEDULE_SEQ_NUM,
REPLACEMENT_GROUP_NUM,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
BASIS_TYPE,
SCHEDULE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE,
PRINCIPLE_FLAG,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
setup_id)
values (
x_OPERATION_SEQUENCE_ID,
x_SUBSTITUTE_GROUP_NUM,
x_RESOURCE_ID,
x_SCHEDULE_SEQ_NUM,
x_REPLACEMENT_GROUP_NUM,
x_ACTIVITY_ID,
x_STANDARD_RATE_FLAG,
x_ASSIGNED_UNITS,
x_USAGE_RATE_OR_AMOUNT,
x_USAGE_RATE_OR_AMOUNT_INVERSE,
x_BASIS_TYPE,
x_SCHEDULE_FLAG,
x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY,
x_CREATION_DATE,
x_CREATED_BY,
x_LAST_UPDATE_LOGIN,
x_RESOURCE_OFFSET_PERCENT,
x_AUTOCHARGE_TYPE,
x_PRINCIPLE_FLAG,
x_ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1,
x_ATTRIBUTE2,
x_ATTRIBUTE3,
x_ATTRIBUTE4,
x_ATTRIBUTE5,
x_ATTRIBUTE6,
x_ATTRIBUTE7,
x_ATTRIBUTE8,
x_ATTRIBUTE9,
x_ATTRIBUTE10,
x_ATTRIBUTE11,
x_ATTRIBUTE12,
x_ATTRIBUTE13,
x_ATTRIBUTE14,
x_ATTRIBUTE15,
x_SETUP_ID
);
END INSERT_ROW;
CURSOR C IS SELECT
operation_sequence_id,
substitute_group_num,
resource_id,
schedule_seq_num,
replacement_group_num,
activity_id,
standard_rate_flag,
assigned_units,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
basis_type,
schedule_flag,
resource_offset_percent,
NVL(principle_flag, 2) principle_flag,
autocharge_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM BOM_SUB_OPERATION_RESOURCES
WHERE rowid = x_row_id
FOR UPDATE of operation_sequence_id, substitute_group_num, resource_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
CURSOR C IS SELECT
operation_sequence_id,
substitute_group_num,
resource_id,
schedule_seq_num,
replacement_group_num,
activity_id,
standard_rate_flag,
assigned_units,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
basis_type,
schedule_flag,
resource_offset_percent,
NVL(principle_flag, 2) principle_flag,
autocharge_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
setup_id
FROM BOM_SUB_OPERATION_RESOURCES
WHERE rowid = x_row_id
FOR UPDATE of operation_sequence_id, substitute_group_num, resource_id, replacement_group_num NOWAIT; --for bug 3287004
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(x_ROW_ID VARCHAR2,
x_OPERATION_SEQUENCE_ID NUMBER,
x_SUBSTITUTE_GROUP_NUM NUMBER,
x_RESOURCE_ID NUMBER,
x_SCHEDULE_SEQ_NUM NUMBER,
x_REPLACEMENT_GROUP_NUM NUMBER,
x_ACTIVITY_ID NUMBER,
x_STANDARD_RATE_FLAG NUMBER,
x_ASSIGNED_UNITS NUMBER,
x_USAGE_RATE_OR_AMOUNT NUMBER,
x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
x_BASIS_TYPE NUMBER,
x_SCHEDULE_FLAG NUMBER,
x_LAST_UPDATE_DATE DATE,
x_LAST_UPDATED_BY NUMBER,
x_CREATION_DATE DATE,
x_CREATED_BY NUMBER,
x_LAST_UPDATE_LOGIN NUMBER,
x_RESOURCE_OFFSET_PERCENT NUMBER,
x_AUTOCHARGE_TYPE NUMBER,
x_PRINCIPLE_FLAG NUMBER,
x_ATTRIBUTE_CATEGORY VARCHAR2,
x_ATTRIBUTE1 VARCHAR2,
x_ATTRIBUTE2 VARCHAR2,
x_ATTRIBUTE3 VARCHAR2,
x_ATTRIBUTE4 VARCHAR2,
x_ATTRIBUTE5 VARCHAR2,
x_ATTRIBUTE6 VARCHAR2,
x_ATTRIBUTE7 VARCHAR2,
x_ATTRIBUTE8 VARCHAR2,
x_ATTRIBUTE9 VARCHAR2,
x_ATTRIBUTE10 VARCHAR2,
x_ATTRIBUTE11 VARCHAR2,
x_ATTRIBUTE12 VARCHAR2,
x_ATTRIBUTE13 VARCHAR2,
x_ATTRIBUTE14 VARCHAR2,
x_ATTRIBUTE15 VARCHAR2)
IS
BEGIN
UPDATE BOM_SUB_OPERATION_RESOURCES SET
OPERATION_SEQUENCE_ID = x_OPERATION_SEQUENCE_ID,
SUBSTITUTE_GROUP_NUM = x_SUBSTITUTE_GROUP_NUM,
RESOURCE_ID = x_RESOURCE_ID,
SCHEDULE_SEQ_NUM = x_SCHEDULE_SEQ_NUM,
REPLACEMENT_GROUP_NUM = x_REPLACEMENT_GROUP_NUM ,
ACTIVITY_ID = x_ACTIVITY_ID,
STANDARD_RATE_FLAG = x_STANDARD_RATE_FLAG,
ASSIGNED_UNITS = x_ASSIGNED_UNITS,
USAGE_RATE_OR_AMOUNT = x_USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE = x_USAGE_RATE_OR_AMOUNT_INVERSE,
BASIS_TYPE = x_BASIS_TYPE,
SCHEDULE_FLAG = x_SCHEDULE_FLAG,
LAST_UPDATE_DATE = x_LAST_UPDATE_DATE,
LAST_UPDATED_BY = x_LAST_UPDATED_BY,
CREATION_DATE = x_CREATION_DATE,
CREATED_BY = x_CREATED_BY,
LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN,
RESOURCE_OFFSET_PERCENT = x_RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE = x_AUTOCHARGE_TYPE,
PRINCIPLE_FLAG = x_PRINCIPLE_FLAG,
attribute_category = x_attribute_category,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15
WHERE rowid = x_row_id;
PROCEDURE Update_Row(x_ROW_ID VARCHAR2,
x_OPERATION_SEQUENCE_ID NUMBER,
x_SUBSTITUTE_GROUP_NUM NUMBER,
x_RESOURCE_ID NUMBER,
x_SCHEDULE_SEQ_NUM NUMBER,
x_REPLACEMENT_GROUP_NUM NUMBER,
x_ACTIVITY_ID NUMBER,
x_STANDARD_RATE_FLAG NUMBER,
x_ASSIGNED_UNITS NUMBER,
x_USAGE_RATE_OR_AMOUNT NUMBER,
x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
x_BASIS_TYPE NUMBER,
x_SCHEDULE_FLAG NUMBER,
x_LAST_UPDATE_DATE DATE,
x_LAST_UPDATED_BY NUMBER,
x_CREATION_DATE DATE,
x_CREATED_BY NUMBER,
x_LAST_UPDATE_LOGIN NUMBER,
x_RESOURCE_OFFSET_PERCENT NUMBER,
x_AUTOCHARGE_TYPE NUMBER,
x_PRINCIPLE_FLAG NUMBER,
x_ATTRIBUTE_CATEGORY VARCHAR2,
x_ATTRIBUTE1 VARCHAR2,
x_ATTRIBUTE2 VARCHAR2,
x_ATTRIBUTE3 VARCHAR2,
x_ATTRIBUTE4 VARCHAR2,
x_ATTRIBUTE5 VARCHAR2,
x_ATTRIBUTE6 VARCHAR2,
x_ATTRIBUTE7 VARCHAR2,
x_ATTRIBUTE8 VARCHAR2,
x_ATTRIBUTE9 VARCHAR2,
x_ATTRIBUTE10 VARCHAR2,
x_ATTRIBUTE11 VARCHAR2,
x_ATTRIBUTE12 VARCHAR2,
x_ATTRIBUTE13 VARCHAR2,
x_ATTRIBUTE14 VARCHAR2,
x_ATTRIBUTE15 VARCHAR2,
x_SETUP_ID NUMBER)
IS
BEGIN
UPDATE BOM_SUB_OPERATION_RESOURCES SET
OPERATION_SEQUENCE_ID = x_OPERATION_SEQUENCE_ID,
SUBSTITUTE_GROUP_NUM = x_SUBSTITUTE_GROUP_NUM,
RESOURCE_ID = x_RESOURCE_ID,
SCHEDULE_SEQ_NUM = x_SCHEDULE_SEQ_NUM,
REPLACEMENT_GROUP_NUM = x_REPLACEMENT_GROUP_NUM ,
ACTIVITY_ID = x_ACTIVITY_ID,
STANDARD_RATE_FLAG = x_STANDARD_RATE_FLAG,
ASSIGNED_UNITS = x_ASSIGNED_UNITS,
USAGE_RATE_OR_AMOUNT = x_USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE = x_USAGE_RATE_OR_AMOUNT_INVERSE,
BASIS_TYPE = x_BASIS_TYPE,
SCHEDULE_FLAG = x_SCHEDULE_FLAG,
LAST_UPDATE_DATE = x_LAST_UPDATE_DATE,
LAST_UPDATED_BY = x_LAST_UPDATED_BY,
CREATION_DATE = x_CREATION_DATE,
CREATED_BY = x_CREATED_BY,
LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN,
RESOURCE_OFFSET_PERCENT = x_RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE = x_AUTOCHARGE_TYPE,
PRINCIPLE_FLAG = x_PRINCIPLE_FLAG,
attribute_category = x_attribute_category,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15,
setup_id = x_setup_id
WHERE rowid = x_row_id;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM BOM_SUB_OPERATION_RESOURCES
WHERE rowid=x_rowid;
END Delete_Row;
SELECT operation_seq_num
INTO from_op_seq_num
FROM bom_operation_sequences
WHERE operation_sequence_id = x_from_op_seq_id;
SELECT operation_seq_num
INTO to_op_seq_num
FROM bom_operation_sequences
WHERE operation_sequence_id = x_to_op_seq_id;
SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM BOM_OPERATION_NETWORKS
WHERE from_op_seq_id = X_From_Op_Seq_Id
AND To_Op_Seq_Id = X_To_Op_Seq_Id
AND ((X_Rowid IS NULL) OR (ROWID <> X_Rowid))
);
SELECT br.resource_code
FROM bom_resources br
WHERE br.resource_id = p_res_id;
SELECT operation_seq_num, operation_sequence_id
FROM bom_operation_sequences
WHERE routing_sequence_id = p_rtg_seq_id;
SELECT * FROM
(
(
SELECT bor.resource_id,
bor.resource_seq_num,
bor.schedule_seq_num,
bor.substitute_group_num,
bor.schedule_flag
FROM bom_operation_resources bor ,
bom_operation_sequences bos ,
bom_resources br ,
bom_department_resources bdr ,
cst_activities ca ,
bom_setup_types bst
WHERE br.resource_id = bor.resource_id
AND bdr.department_id = bos.department_id
AND bdr.resource_id = bor.resource_id
AND bor.operation_sequence_id = bos.operation_sequence_id
AND NVL ( bos.operation_type , 1 ) = 1
AND bor.activity_id = ca.activity_id ( + )
AND bor.setup_id = bst.setup_id ( + )
AND bor.operation_sequence_id = p_opn_seq_num
AND bor.schedule_flag <> 2
UNION
SELECT bor.resource_id,
bor.resource_seq_num,
bor.schedule_seq_num,
bor.substitute_group_num,
bor.schedule_flag
FROM bom_operation_resources bor ,
eng_revised_operations ero ,
bom_resources br ,
bom_department_resources bdr ,
cst_activities ca ,
bom_setup_types bst
WHERE br.resource_id = bor.resource_id
AND bdr.department_id = ero.department_id
AND bdr.resource_id = bor.resource_id
AND bor.operation_sequence_id = ero.operation_sequence_id
AND bor.acd_type IS NOT NULL
AND NVL ( ero.operation_type , 1 ) = 1
AND bor.activity_id = ca.activity_id ( + )
AND bor.setup_id = bst.setup_id ( + )
AND bor.operation_sequence_id = p_opn_seq_num
AND bor.schedule_flag <> 2
)
UNION ALL
SELECT bor.resource_id,
to_number(NULL) resource_seq_num,
bor.schedule_seq_num,
bor.substitute_group_num,
bor.schedule_flag
FROM bom_sub_operation_resources bor ,
bom_operation_sequences bos ,
bom_resources br ,
bom_department_resources bdr ,
cst_activities ca ,
bom_setup_types bst
WHERE br.resource_id = bor.resource_id
AND bdr.department_id = bos.department_id
AND bdr.resource_id = bor.resource_id
AND bor.operation_sequence_id = bos.operation_sequence_id
AND NVL ( bos.operation_type , 1 ) = 1
AND bor.activity_id = ca.activity_id ( + )
AND bor.setup_id = bst.setup_id ( + )
AND bor.operation_sequence_id = p_opn_seq_num
AND bor.schedule_flag <> 2
) ORDER BY nvl(schedule_seq_num, resource_seq_num);