[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.last_updated_by,p_old_flow_schedule_rec.last_updated_by)
THEN
NULL;
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.last_update_date,p_old_flow_schedule_rec.last_update_date)
THEN
NULL;
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.last_update_login,p_old_flow_schedule_rec.last_update_login)
THEN
NULL;
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.program_update_date,p_old_flow_schedule_rec.program_update_date)
THEN
NULL;
ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
NULL;
ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
NULL;
ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
NULL;
ELSIF p_attr_id = G_PROGRAM_UPDATE_DATE THEN
NULL;
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.last_updated_by,p_old_flow_schedule_rec.last_updated_by)
THEN
NULL;
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.last_update_date,p_old_flow_schedule_rec.last_update_date)
THEN
NULL;
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.last_update_login,p_old_flow_schedule_rec.last_update_login)
THEN
NULL;
/* Fix for bug 3661250. Added the following SQL to update primary_item_id in WIP_ENTITIES if it has been changed.
*/
UPDATE WIP_ENTITIES
SET primary_item_id = p_flow_schedule_rec.primary_item_id
WHERE wip_entity_id = p_flow_schedule_rec.wip_entity_id
AND organization_id = p_flow_schedule_rec.organization_id;
IF NOT MRP_GLOBALS.Equal(p_flow_schedule_rec.program_update_date,p_old_flow_schedule_rec.program_update_date)
THEN
NULL;
UPDATE wip_entities
SET wip_entity_name = p_flow_schedule_rec.schedule_number
WHERE wip_entity_id = p_flow_schedule_rec.wip_entity_id;
IF l_flow_schedule_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_flow_schedule_rec.last_updated_by := NULL ;
ELSIF l_flow_schedule_rec.last_updated_by IS NULL THEN
l_flow_schedule_rec.last_updated_by := p_old_flow_schedule_rec.last_updated_by;
IF l_flow_schedule_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_flow_schedule_rec.last_update_date := NULL ;
ELSIF l_flow_schedule_rec.last_update_date IS NULL THEN
l_flow_schedule_rec.last_update_date := p_old_flow_schedule_rec.last_update_date;
IF l_flow_schedule_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_flow_schedule_rec.last_update_login := NULL ;
ELSIF l_flow_schedule_rec.last_update_login IS NULL THEN
l_flow_schedule_rec.last_update_login := p_old_flow_schedule_rec.last_update_login;
IF l_flow_schedule_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_flow_schedule_rec.program_update_date := NULL ;
ELSIF l_flow_schedule_rec.program_update_date IS NULL THEN
l_flow_schedule_rec.program_update_date := p_old_flow_schedule_rec.program_update_date;
IF l_flow_schedule_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_flow_schedule_rec.last_updated_by := NULL;
IF l_flow_schedule_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_flow_schedule_rec.last_update_date := NULL;
IF l_flow_schedule_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_flow_schedule_rec.last_update_login := NULL;
IF l_flow_schedule_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_flow_schedule_rec.program_update_date := NULL;
PROCEDURE Update_Row
( p_flow_schedule_rec IN MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type
)
IS
--bug 3906891: also changed the p_flow_schedules_rec.XXX to l_flow_schedule_rec.XXX
--in the UPDATE statement below
l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type := Convert_Miss_To_Null (p_flow_schedule_rec);
UPDATE WIP_FLOW_SCHEDULES
SET ALTERNATE_BOM_DESIGNATOR = l_flow_schedule_rec.alternate_bom_designator
, ALTERNATE_ROUTING_DESIGNATOR = l_flow_schedule_rec.alternate_routing_desig
, ATTRIBUTE1 = l_flow_schedule_rec.attribute1
, ATTRIBUTE10 = l_flow_schedule_rec.attribute10
, ATTRIBUTE11 = l_flow_schedule_rec.attribute11
, ATTRIBUTE12 = l_flow_schedule_rec.attribute12
, ATTRIBUTE13 = l_flow_schedule_rec.attribute13
, ATTRIBUTE14 = l_flow_schedule_rec.attribute14
, ATTRIBUTE15 = l_flow_schedule_rec.attribute15
, ATTRIBUTE2 = l_flow_schedule_rec.attribute2
, ATTRIBUTE3 = l_flow_schedule_rec.attribute3
, ATTRIBUTE4 = l_flow_schedule_rec.attribute4
, ATTRIBUTE5 = l_flow_schedule_rec.attribute5
, ATTRIBUTE6 = l_flow_schedule_rec.attribute6
, ATTRIBUTE7 = l_flow_schedule_rec.attribute7
, ATTRIBUTE8 = l_flow_schedule_rec.attribute8
, ATTRIBUTE9 = l_flow_schedule_rec.attribute9
, ATTRIBUTE_CATEGORY = l_flow_schedule_rec.attribute_category
, BOM_REVISION = l_flow_schedule_rec.bom_revision
, BOM_REVISION_DATE = l_flow_schedule_rec.bom_revision_date
, BUILD_SEQUENCE = l_flow_schedule_rec.build_sequence
, CLASS_CODE = l_flow_schedule_rec.class_code
, COMPLETION_LOCATOR_ID = l_flow_schedule_rec.completion_locator_id
, COMPLETION_SUBINVENTORY = l_flow_schedule_rec.completion_subinventory
, CREATED_BY = l_flow_schedule_rec.created_by
, CREATION_DATE = l_flow_schedule_rec.creation_date
, DATE_CLOSED = l_flow_schedule_rec.date_closed
, DEMAND_CLASS = l_flow_schedule_rec.demand_class
, DEMAND_SOURCE_DELIVERY = l_flow_schedule_rec.demand_source_delivery
, DEMAND_SOURCE_HEADER_ID = l_flow_schedule_rec.demand_source_header_id
, DEMAND_SOURCE_LINE = l_flow_schedule_rec.demand_source_line
, DEMAND_SOURCE_TYPE = l_flow_schedule_rec.demand_source_type
, LAST_UPDATED_BY = l_flow_schedule_rec.last_updated_by
, LAST_UPDATE_DATE = l_flow_schedule_rec.last_update_date
, LAST_UPDATE_LOGIN = l_flow_schedule_rec.last_update_login
, LINE_ID = l_flow_schedule_rec.line_id
, MATERIAL_ACCOUNT = l_flow_schedule_rec.material_account
, MATERIAL_OVERHEAD_ACCOUNT = l_flow_schedule_rec.material_overhead_account
, MATERIAL_VARIANCE_ACCOUNT = l_flow_schedule_rec.material_variance_account
, MPS_NET_QUANTITY = l_flow_schedule_rec.mps_net_quantity
, MPS_SCHEDULED_COMPLETION_DATE = l_flow_schedule_rec.mps_scheduled_comp_date
, ORGANIZATION_ID = l_flow_schedule_rec.organization_id
, OUTSIDE_PROCESSING_ACCOUNT = l_flow_schedule_rec.outside_processing_acct
, OUTSIDE_PROC_VARIANCE_ACCOUNT = l_flow_schedule_rec.outside_proc_var_acct
, OVERHEAD_ACCOUNT = l_flow_schedule_rec.overhead_account
, OVERHEAD_VARIANCE_ACCOUNT = l_flow_schedule_rec.overhead_variance_account
, PLANNED_QUANTITY = l_flow_schedule_rec.planned_quantity
, PRIMARY_ITEM_ID = l_flow_schedule_rec.primary_item_id
, PROGRAM_APPLICATION_ID = l_flow_schedule_rec.program_application_id
, PROGRAM_ID = l_flow_schedule_rec.program_id
, PROGRAM_UPDATE_DATE = l_flow_schedule_rec.program_update_date
, PROJECT_ID = l_flow_schedule_rec.project_id
, QUANTITY_COMPLETED = l_flow_schedule_rec.quantity_completed
, REQUEST_ID = l_flow_schedule_rec.request_id
, RESOURCE_ACCOUNT = l_flow_schedule_rec.resource_account
, RESOURCE_VARIANCE_ACCOUNT = l_flow_schedule_rec.resource_variance_account
, ROUTING_REVISION = l_flow_schedule_rec.routing_revision
, ROUTING_REVISION_DATE = l_flow_schedule_rec.routing_revision_date
, SCHEDULED_COMPLETION_DATE = l_flow_schedule_rec.scheduled_completion_date
, SCHEDULED_FLAG = l_flow_schedule_rec.scheduled_flag
, SCHEDULED_START_DATE = l_flow_schedule_rec.scheduled_start_date
, SCHEDULE_GROUP_ID = l_flow_schedule_rec.schedule_group_id
, SCHEDULE_NUMBER = l_flow_schedule_rec.schedule_number
, STATUS = l_flow_schedule_rec.status
, STD_COST_ADJUSTMENT_ACCOUNT = l_flow_schedule_rec.std_cost_adjustment_acct
, TASK_ID = l_flow_schedule_rec.task_id
, END_ITEM_UNIT_NUMBER = l_flow_schedule_rec.end_item_unit_number
, QUANTITY_SCRAPPED = l_flow_schedule_rec.quantity_scrapped
, WIP_ENTITY_ID = l_flow_schedule_rec.wip_entity_id
WHERE ORGANIZATION_ID = l_flow_schedule_rec.organization_id
AND WIP_ENTITY_ID = l_flow_schedule_rec.wip_entity_id
;
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_flow_schedule_rec IN MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type
)
IS
--bug 3906891: also changed the p_flow_schedules_rec.XXX to l_flow_schedule_rec.XXX
--in the INSERT statement below
l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type := Convert_Miss_To_Null (p_flow_schedule_rec);
INSERT INTO WIP_FLOW_SCHEDULES
( ALTERNATE_BOM_DESIGNATOR
, ALTERNATE_ROUTING_DESIGNATOR
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, BOM_REVISION
, BOM_REVISION_DATE
, BUILD_SEQUENCE
, CLASS_CODE
, COMPLETION_LOCATOR_ID
, COMPLETION_SUBINVENTORY
, CREATED_BY
, CREATION_DATE
, DATE_CLOSED
, DEMAND_CLASS
, DEMAND_SOURCE_DELIVERY
, DEMAND_SOURCE_HEADER_ID
, DEMAND_SOURCE_LINE
, DEMAND_SOURCE_TYPE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, MATERIAL_ACCOUNT
, MATERIAL_OVERHEAD_ACCOUNT
, MATERIAL_VARIANCE_ACCOUNT
, MPS_NET_QUANTITY
, MPS_SCHEDULED_COMPLETION_DATE
, ORGANIZATION_ID
, OUTSIDE_PROCESSING_ACCOUNT
, OUTSIDE_PROC_VARIANCE_ACCOUNT
, OVERHEAD_ACCOUNT
, OVERHEAD_VARIANCE_ACCOUNT
, PLANNED_QUANTITY
, PRIMARY_ITEM_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PROJECT_ID
, QUANTITY_COMPLETED
, REQUEST_ID
, RESOURCE_ACCOUNT
, RESOURCE_VARIANCE_ACCOUNT
, ROUTING_REVISION
, ROUTING_REVISION_DATE
, SCHEDULED_COMPLETION_DATE
, SCHEDULED_FLAG
, SCHEDULED_START_DATE
, SCHEDULE_GROUP_ID
, SCHEDULE_NUMBER
, STATUS
, STD_COST_ADJUSTMENT_ACCOUNT
, TASK_ID
, END_ITEM_UNIT_NUMBER
, QUANTITY_SCRAPPED
, WIP_ENTITY_ID
, KANBAN_CARD_ID
, SYNCH_SCHEDULE_NUM
, SYNCH_OPERATION_SEQ_NUM
, ROLL_FORWARDED_FLAG
, CURRENT_LINE_OPERATION
)
VALUES
( l_flow_schedule_rec.alternate_bom_designator
, l_flow_schedule_rec.alternate_routing_desig
, l_flow_schedule_rec.attribute1
, l_flow_schedule_rec.attribute10
, l_flow_schedule_rec.attribute11
, l_flow_schedule_rec.attribute12
, l_flow_schedule_rec.attribute13
, l_flow_schedule_rec.attribute14
, l_flow_schedule_rec.attribute15
, l_flow_schedule_rec.attribute2
, l_flow_schedule_rec.attribute3
, l_flow_schedule_rec.attribute4
, l_flow_schedule_rec.attribute5
, l_flow_schedule_rec.attribute6
, l_flow_schedule_rec.attribute7
, l_flow_schedule_rec.attribute8
, l_flow_schedule_rec.attribute9
, l_flow_schedule_rec.attribute_category
, l_flow_schedule_rec.bom_revision
, l_flow_schedule_rec.bom_revision_date
, l_flow_schedule_rec.build_sequence
, l_flow_schedule_rec.class_code
, l_flow_schedule_rec.completion_locator_id
, l_flow_schedule_rec.completion_subinventory
, l_flow_schedule_rec.created_by
, l_flow_schedule_rec.creation_date
, l_flow_schedule_rec.date_closed
, l_flow_schedule_rec.demand_class
, l_flow_schedule_rec.demand_source_delivery
, l_flow_schedule_rec.demand_source_header_id
, l_flow_schedule_rec.demand_source_line
, l_flow_schedule_rec.demand_source_type
, l_flow_schedule_rec.last_updated_by
, l_flow_schedule_rec.last_update_date
, l_flow_schedule_rec.last_update_login
, l_flow_schedule_rec.line_id
, l_flow_schedule_rec.material_account
, l_flow_schedule_rec.material_overhead_account
, l_flow_schedule_rec.material_variance_account
, l_flow_schedule_rec.mps_net_quantity
, l_flow_schedule_rec.mps_scheduled_comp_date
, l_flow_schedule_rec.organization_id
, l_flow_schedule_rec.outside_processing_acct
, l_flow_schedule_rec.outside_proc_var_acct
, l_flow_schedule_rec.overhead_account
, l_flow_schedule_rec.overhead_variance_account
, l_flow_schedule_rec.planned_quantity
, l_flow_schedule_rec.primary_item_id
, l_flow_schedule_rec.program_application_id
, l_flow_schedule_rec.program_id
, l_flow_schedule_rec.program_update_date
, l_flow_schedule_rec.project_id
, l_flow_schedule_rec.quantity_completed
, l_flow_schedule_rec.request_id
, l_flow_schedule_rec.resource_account
, l_flow_schedule_rec.resource_variance_account
, l_flow_schedule_rec.routing_revision
, l_flow_schedule_rec.routing_revision_date
, l_flow_schedule_rec.scheduled_completion_date
, l_flow_schedule_rec.scheduled_flag
, l_flow_schedule_rec.scheduled_start_date
, l_flow_schedule_rec.schedule_group_id
, l_flow_schedule_rec.schedule_number
, l_flow_schedule_rec.status
, l_flow_schedule_rec.std_cost_adjustment_acct
, l_flow_schedule_rec.task_id
, l_flow_schedule_rec.end_item_unit_number
, l_flow_schedule_rec.quantity_scrapped
, l_flow_schedule_rec.wip_entity_id
, l_flow_schedule_rec.kanban_card_id
, l_flow_schedule_rec.synch_schedule_num
, l_flow_schedule_rec.synch_operation_seq_num
, l_flow_schedule_rec.roll_forwarded_flag
, l_flow_schedule_rec.current_line_operation
);
, 'Insert_Row'
);
END Insert_Row;
PROCEDURE Delete_Row
( p_wip_entity_id IN NUMBER
)
IS
BEGIN
DELETE FROM WIP_FLOW_SCHEDULES
WHERE WIP_ENTITY_ID = p_wip_entity_id
;
, 'Delete_Row'
);
END Delete_Row;
SELECT ALTERNATE_BOM_DESIGNATOR
, ALTERNATE_ROUTING_DESIGNATOR
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, BOM_REVISION
, BOM_REVISION_DATE
, BUILD_SEQUENCE
, CLASS_CODE
, COMPLETION_LOCATOR_ID
, COMPLETION_SUBINVENTORY
, CREATED_BY
, CREATION_DATE
, DATE_CLOSED
, DEMAND_CLASS
, DEMAND_SOURCE_DELIVERY
, DEMAND_SOURCE_HEADER_ID
, DEMAND_SOURCE_LINE
, DEMAND_SOURCE_TYPE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, MATERIAL_ACCOUNT
, MATERIAL_OVERHEAD_ACCOUNT
, MATERIAL_VARIANCE_ACCOUNT
, MPS_NET_QUANTITY
, MPS_SCHEDULED_COMPLETION_DATE
, ORGANIZATION_ID
, OUTSIDE_PROCESSING_ACCOUNT
, OUTSIDE_PROC_VARIANCE_ACCOUNT
, OVERHEAD_ACCOUNT
, OVERHEAD_VARIANCE_ACCOUNT
, PLANNED_QUANTITY
, PRIMARY_ITEM_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PROJECT_ID
, QUANTITY_COMPLETED
, REQUEST_ID
, RESOURCE_ACCOUNT
, RESOURCE_VARIANCE_ACCOUNT
, ROUTING_REVISION
, ROUTING_REVISION_DATE
, SCHEDULED_COMPLETION_DATE
, SCHEDULED_FLAG
, SCHEDULED_START_DATE
, SCHEDULE_GROUP_ID
, SCHEDULE_NUMBER
, STATUS
, STD_COST_ADJUSTMENT_ACCOUNT
, TASK_ID
, WIP_ENTITY_ID
, END_ITEM_UNIT_NUMBER
, QUANTITY_SCRAPPED
INTO l_flow_schedule_rec.alternate_bom_designator
, l_flow_schedule_rec.alternate_routing_desig
, l_flow_schedule_rec.attribute1
, l_flow_schedule_rec.attribute10
, l_flow_schedule_rec.attribute11
, l_flow_schedule_rec.attribute12
, l_flow_schedule_rec.attribute13
, l_flow_schedule_rec.attribute14
, l_flow_schedule_rec.attribute15
, l_flow_schedule_rec.attribute2
, l_flow_schedule_rec.attribute3
, l_flow_schedule_rec.attribute4
, l_flow_schedule_rec.attribute5
, l_flow_schedule_rec.attribute6
, l_flow_schedule_rec.attribute7
, l_flow_schedule_rec.attribute8
, l_flow_schedule_rec.attribute9
, l_flow_schedule_rec.attribute_category
, l_flow_schedule_rec.bom_revision
, l_flow_schedule_rec.bom_revision_date
, l_flow_schedule_rec.build_sequence
, l_flow_schedule_rec.class_code
, l_flow_schedule_rec.completion_locator_id
, l_flow_schedule_rec.completion_subinventory
, l_flow_schedule_rec.created_by
, l_flow_schedule_rec.creation_date
, l_flow_schedule_rec.date_closed
, l_flow_schedule_rec.demand_class
, l_flow_schedule_rec.demand_source_delivery
, l_flow_schedule_rec.demand_source_header_id
, l_flow_schedule_rec.demand_source_line
, l_flow_schedule_rec.demand_source_type
, l_flow_schedule_rec.last_updated_by
, l_flow_schedule_rec.last_update_date
, l_flow_schedule_rec.last_update_login
, l_flow_schedule_rec.line_id
, l_flow_schedule_rec.material_account
, l_flow_schedule_rec.material_overhead_account
, l_flow_schedule_rec.material_variance_account
, l_flow_schedule_rec.mps_net_quantity
, l_flow_schedule_rec.mps_scheduled_comp_date
, l_flow_schedule_rec.organization_id
, l_flow_schedule_rec.outside_processing_acct
, l_flow_schedule_rec.outside_proc_var_acct
, l_flow_schedule_rec.overhead_account
, l_flow_schedule_rec.overhead_variance_account
, l_flow_schedule_rec.planned_quantity
, l_flow_schedule_rec.primary_item_id
, l_flow_schedule_rec.program_application_id
, l_flow_schedule_rec.program_id
, l_flow_schedule_rec.program_update_date
, l_flow_schedule_rec.project_id
, l_flow_schedule_rec.quantity_completed
, l_flow_schedule_rec.request_id
, l_flow_schedule_rec.resource_account
, l_flow_schedule_rec.resource_variance_account
, l_flow_schedule_rec.routing_revision
, l_flow_schedule_rec.routing_revision_date
, l_flow_schedule_rec.scheduled_completion_date
, l_flow_schedule_rec.scheduled_flag
, l_flow_schedule_rec.scheduled_start_date
, l_flow_schedule_rec.schedule_group_id
, l_flow_schedule_rec.schedule_number
, l_flow_schedule_rec.status
, l_flow_schedule_rec.std_cost_adjustment_acct
, l_flow_schedule_rec.task_id
, l_flow_schedule_rec.wip_entity_id
, l_flow_schedule_rec.end_item_unit_number
, l_flow_schedule_rec.quantity_scrapped
FROM WIP_FLOW_SCHEDULES
WHERE WIP_ENTITY_ID = p_wip_entity_id
;
SELECT ALTERNATE_BOM_DESIGNATOR
, ALTERNATE_ROUTING_DESIGNATOR
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, BOM_REVISION
, BOM_REVISION_DATE
, BUILD_SEQUENCE
, CLASS_CODE
, COMPLETION_LOCATOR_ID
, COMPLETION_SUBINVENTORY
, CREATED_BY
, CREATION_DATE
, DATE_CLOSED
, DEMAND_CLASS
, DEMAND_SOURCE_DELIVERY
, DEMAND_SOURCE_HEADER_ID
, DEMAND_SOURCE_LINE
, DEMAND_SOURCE_TYPE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, MATERIAL_ACCOUNT
, MATERIAL_OVERHEAD_ACCOUNT
, MATERIAL_VARIANCE_ACCOUNT
, MPS_NET_QUANTITY
, MPS_SCHEDULED_COMPLETION_DATE
, ORGANIZATION_ID
, OUTSIDE_PROCESSING_ACCOUNT
, OUTSIDE_PROC_VARIANCE_ACCOUNT
, OVERHEAD_ACCOUNT
, OVERHEAD_VARIANCE_ACCOUNT
, PLANNED_QUANTITY
, PRIMARY_ITEM_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PROJECT_ID
, QUANTITY_COMPLETED
, REQUEST_ID
, RESOURCE_ACCOUNT
, RESOURCE_VARIANCE_ACCOUNT
, ROUTING_REVISION
, ROUTING_REVISION_DATE
, SCHEDULED_COMPLETION_DATE
, SCHEDULED_FLAG
, SCHEDULED_START_DATE
, SCHEDULE_GROUP_ID
, SCHEDULE_NUMBER
, STATUS
, STD_COST_ADJUSTMENT_ACCOUNT
, TASK_ID
, WIP_ENTITY_ID
, END_ITEM_UNIT_NUMBER
, QUANTITY_SCRAPPED
INTO l_flow_schedule_rec.alternate_bom_designator
, l_flow_schedule_rec.alternate_routing_desig
, l_flow_schedule_rec.attribute1
, l_flow_schedule_rec.attribute10
, l_flow_schedule_rec.attribute11
, l_flow_schedule_rec.attribute12
, l_flow_schedule_rec.attribute13
, l_flow_schedule_rec.attribute14
, l_flow_schedule_rec.attribute15
, l_flow_schedule_rec.attribute2
, l_flow_schedule_rec.attribute3
, l_flow_schedule_rec.attribute4
, l_flow_schedule_rec.attribute5
, l_flow_schedule_rec.attribute6
, l_flow_schedule_rec.attribute7
, l_flow_schedule_rec.attribute8
, l_flow_schedule_rec.attribute9
, l_flow_schedule_rec.attribute_category
, l_flow_schedule_rec.bom_revision
, l_flow_schedule_rec.bom_revision_date
, l_flow_schedule_rec.build_sequence
, l_flow_schedule_rec.class_code
, l_flow_schedule_rec.completion_locator_id
, l_flow_schedule_rec.completion_subinventory
, l_flow_schedule_rec.created_by
, l_flow_schedule_rec.creation_date
, l_flow_schedule_rec.date_closed
, l_flow_schedule_rec.demand_class
, l_flow_schedule_rec.demand_source_delivery
, l_flow_schedule_rec.demand_source_header_id
, l_flow_schedule_rec.demand_source_line
, l_flow_schedule_rec.demand_source_type
, l_flow_schedule_rec.last_updated_by
, l_flow_schedule_rec.last_update_date
, l_flow_schedule_rec.last_update_login
, l_flow_schedule_rec.line_id
, l_flow_schedule_rec.material_account
, l_flow_schedule_rec.material_overhead_account
, l_flow_schedule_rec.material_variance_account
, l_flow_schedule_rec.mps_net_quantity
, l_flow_schedule_rec.mps_scheduled_comp_date
, l_flow_schedule_rec.organization_id
, l_flow_schedule_rec.outside_processing_acct
, l_flow_schedule_rec.outside_proc_var_acct
, l_flow_schedule_rec.overhead_account
, l_flow_schedule_rec.overhead_variance_account
, l_flow_schedule_rec.planned_quantity
, l_flow_schedule_rec.primary_item_id
, l_flow_schedule_rec.program_application_id
, l_flow_schedule_rec.program_id
, l_flow_schedule_rec.program_update_date
, l_flow_schedule_rec.project_id
, l_flow_schedule_rec.quantity_completed
, l_flow_schedule_rec.request_id
, l_flow_schedule_rec.resource_account
, l_flow_schedule_rec.resource_variance_account
, l_flow_schedule_rec.routing_revision
, l_flow_schedule_rec.routing_revision_date
, l_flow_schedule_rec.scheduled_completion_date
, l_flow_schedule_rec.scheduled_flag
, l_flow_schedule_rec.scheduled_start_date
, l_flow_schedule_rec.schedule_group_id
, l_flow_schedule_rec.schedule_number
, l_flow_schedule_rec.status
, l_flow_schedule_rec.std_cost_adjustment_acct
, l_flow_schedule_rec.task_id
, l_flow_schedule_rec.wip_entity_id
, l_flow_schedule_rec.end_item_unit_number
, l_flow_schedule_rec.quantity_scrapped
FROM WIP_FLOW_SCHEDULES
WHERE WIP_ENTITY_ID = p_flow_schedule_rec.wip_entity_id
FOR UPDATE NOWAIT;
/* AND ( (l_flow_schedule_rec.last_updated_by =
p_flow_schedule_rec.last_updated_by) OR
((p_flow_schedule_rec.last_updated_by = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.last_updated_by IS NULL) AND
(p_flow_schedule_rec.last_updated_by IS NULL))))
AND ( (l_flow_schedule_rec.last_update_date =
p_flow_schedule_rec.last_update_date) OR
((p_flow_schedule_rec.last_update_date = FND_API.G_MISS_DATE) OR
( (l_flow_schedule_rec.last_update_date IS NULL) AND
(p_flow_schedule_rec.last_update_date IS NULL))))
AND ( (l_flow_schedule_rec.last_update_login =
p_flow_schedule_rec.last_update_login) OR
((p_flow_schedule_rec.last_update_login = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.last_update_login IS NULL) AND
(p_flow_schedule_rec.last_update_login IS NULL)))) */
AND ( (l_flow_schedule_rec.line_id =
p_flow_schedule_rec.line_id) OR
((p_flow_schedule_rec.line_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.line_id IS NULL) AND
(p_flow_schedule_rec.line_id IS NULL))))
AND ( (l_flow_schedule_rec.material_account =
p_flow_schedule_rec.material_account) OR
((p_flow_schedule_rec.material_account = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.material_account IS NULL) AND
(p_flow_schedule_rec.material_account IS NULL))))
AND ( (l_flow_schedule_rec.material_overhead_account =
p_flow_schedule_rec.material_overhead_account) OR
((p_flow_schedule_rec.material_overhead_account = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.material_overhead_account IS NULL) AND
(p_flow_schedule_rec.material_overhead_account IS NULL))))
AND ( (l_flow_schedule_rec.material_variance_account =
p_flow_schedule_rec.material_variance_account) OR
((p_flow_schedule_rec.material_variance_account = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.material_variance_account IS NULL) AND
(p_flow_schedule_rec.material_variance_account IS NULL))))
AND ( (l_flow_schedule_rec.mps_net_quantity =
p_flow_schedule_rec.mps_net_quantity) OR
((p_flow_schedule_rec.mps_net_quantity = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.mps_net_quantity IS NULL) AND
(p_flow_schedule_rec.mps_net_quantity IS NULL))))
AND ( (l_flow_schedule_rec.mps_scheduled_comp_date =
p_flow_schedule_rec.mps_scheduled_comp_date) OR
((p_flow_schedule_rec.mps_scheduled_comp_date = FND_API.G_MISS_DATE) OR
( (l_flow_schedule_rec.mps_scheduled_comp_date IS NULL) AND
(p_flow_schedule_rec.mps_scheduled_comp_date IS NULL))))
AND ( (l_flow_schedule_rec.organization_id =
p_flow_schedule_rec.organization_id) OR
((p_flow_schedule_rec.organization_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.organization_id IS NULL) AND
(p_flow_schedule_rec.organization_id IS NULL))))
AND ( (l_flow_schedule_rec.outside_processing_acct =
p_flow_schedule_rec.outside_processing_acct) OR
((p_flow_schedule_rec.outside_processing_acct = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.outside_processing_acct IS NULL) AND
(p_flow_schedule_rec.outside_processing_acct IS NULL))))
AND ( (l_flow_schedule_rec.outside_proc_var_acct =
p_flow_schedule_rec.outside_proc_var_acct) OR
((p_flow_schedule_rec.outside_proc_var_acct = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.outside_proc_var_acct IS NULL) AND
(p_flow_schedule_rec.outside_proc_var_acct IS NULL))))
AND ( (l_flow_schedule_rec.overhead_account =
p_flow_schedule_rec.overhead_account) OR
((p_flow_schedule_rec.overhead_account = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.overhead_account IS NULL) AND
(p_flow_schedule_rec.overhead_account IS NULL))))
AND ( (l_flow_schedule_rec.overhead_variance_account =
p_flow_schedule_rec.overhead_variance_account) OR
((p_flow_schedule_rec.overhead_variance_account = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.overhead_variance_account IS NULL) AND
(p_flow_schedule_rec.overhead_variance_account IS NULL))))
AND ( (l_flow_schedule_rec.planned_quantity =
p_flow_schedule_rec.planned_quantity) OR
((p_flow_schedule_rec.planned_quantity = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.planned_quantity IS NULL) AND
(p_flow_schedule_rec.planned_quantity IS NULL))))
AND ( (l_flow_schedule_rec.primary_item_id =
p_flow_schedule_rec.primary_item_id) OR
((p_flow_schedule_rec.primary_item_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.primary_item_id IS NULL) AND
(p_flow_schedule_rec.primary_item_id IS NULL))))
AND ( (l_flow_schedule_rec.program_application_id =
p_flow_schedule_rec.program_application_id) OR
((p_flow_schedule_rec.program_application_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.program_application_id IS NULL) AND
(p_flow_schedule_rec.program_application_id IS NULL))))
AND ( (l_flow_schedule_rec.program_id =
p_flow_schedule_rec.program_id) OR
((p_flow_schedule_rec.program_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.program_id IS NULL) AND
(p_flow_schedule_rec.program_id IS NULL))))
AND ( (l_flow_schedule_rec.program_update_date =
p_flow_schedule_rec.program_update_date) OR
((p_flow_schedule_rec.program_update_date = FND_API.G_MISS_DATE) OR
( (l_flow_schedule_rec.program_update_date IS NULL) AND
(p_flow_schedule_rec.program_update_date IS NULL))))
AND ( (l_flow_schedule_rec.project_id =
p_flow_schedule_rec.project_id) OR
((p_flow_schedule_rec.project_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.project_id IS NULL) AND
(p_flow_schedule_rec.project_id IS NULL))))
AND ( (l_flow_schedule_rec.quantity_completed =
p_flow_schedule_rec.quantity_completed) OR
((p_flow_schedule_rec.quantity_completed = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.quantity_completed IS NULL) AND
(p_flow_schedule_rec.quantity_completed IS NULL))))
AND ( (l_flow_schedule_rec.request_id =
p_flow_schedule_rec.request_id) OR
((p_flow_schedule_rec.request_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.request_id IS NULL) AND
(p_flow_schedule_rec.request_id IS NULL))))
AND ( (l_flow_schedule_rec.resource_account =
p_flow_schedule_rec.resource_account) OR
((p_flow_schedule_rec.resource_account = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.resource_account IS NULL) AND
(p_flow_schedule_rec.resource_account IS NULL))))
AND ( (l_flow_schedule_rec.resource_variance_account =
p_flow_schedule_rec.resource_variance_account) OR
((p_flow_schedule_rec.resource_variance_account = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.resource_variance_account IS NULL) AND
(p_flow_schedule_rec.resource_variance_account IS NULL))))
AND ( (l_flow_schedule_rec.routing_revision =
p_flow_schedule_rec.routing_revision) OR
((p_flow_schedule_rec.routing_revision = FND_API.G_MISS_CHAR) OR
( (l_flow_schedule_rec.routing_revision IS NULL) AND
(p_flow_schedule_rec.routing_revision IS NULL))))
AND ( (l_flow_schedule_rec.routing_revision_date =
p_flow_schedule_rec.routing_revision_date) OR
((p_flow_schedule_rec.routing_revision_date = FND_API.G_MISS_DATE) OR
( (l_flow_schedule_rec.routing_revision_date IS NULL) AND
(p_flow_schedule_rec.routing_revision_date IS NULL))))
AND ( (l_flow_schedule_rec.scheduled_completion_date =
p_flow_schedule_rec.scheduled_completion_date) OR
((p_flow_schedule_rec.scheduled_completion_date = FND_API.G_MISS_DATE) OR
( (l_flow_schedule_rec.scheduled_completion_date IS NULL) AND
(p_flow_schedule_rec.scheduled_completion_date IS NULL))))
AND ( (l_flow_schedule_rec.scheduled_flag =
p_flow_schedule_rec.scheduled_flag) OR
((p_flow_schedule_rec.scheduled_flag = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.scheduled_flag IS NULL) AND
(p_flow_schedule_rec.scheduled_flag IS NULL))))
AND ( (l_flow_schedule_rec.scheduled_start_date =
p_flow_schedule_rec.scheduled_start_date) OR
((p_flow_schedule_rec.scheduled_start_date = FND_API.G_MISS_DATE) OR
( (l_flow_schedule_rec.scheduled_start_date IS NULL) AND
(p_flow_schedule_rec.scheduled_start_date IS NULL))))
AND ( (l_flow_schedule_rec.schedule_group_id =
p_flow_schedule_rec.schedule_group_id) OR
((p_flow_schedule_rec.schedule_group_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.schedule_group_id IS NULL) AND
(p_flow_schedule_rec.schedule_group_id IS NULL))))
AND ( (l_flow_schedule_rec.schedule_number =
p_flow_schedule_rec.schedule_number) OR
((p_flow_schedule_rec.schedule_number = FND_API.G_MISS_CHAR) OR
( (l_flow_schedule_rec.schedule_number IS NULL) AND
(p_flow_schedule_rec.schedule_number IS NULL))))
AND ( (l_flow_schedule_rec.status =
p_flow_schedule_rec.status) OR
((p_flow_schedule_rec.status = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.status IS NULL) AND
(p_flow_schedule_rec.status IS NULL))))
AND ( (l_flow_schedule_rec.std_cost_adjustment_acct =
p_flow_schedule_rec.std_cost_adjustment_acct) OR
((p_flow_schedule_rec.std_cost_adjustment_acct = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.std_cost_adjustment_acct IS NULL) AND
(p_flow_schedule_rec.std_cost_adjustment_acct IS NULL))))
AND ( (l_flow_schedule_rec.task_id =
p_flow_schedule_rec.task_id) OR
((p_flow_schedule_rec.task_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.task_id IS NULL) AND
(p_flow_schedule_rec.task_id IS NULL))))
AND ( (l_flow_schedule_rec.end_item_unit_number =
p_flow_schedule_rec.end_item_unit_number) OR
((p_flow_schedule_rec.end_item_unit_number = FND_API.G_MISS_CHAR) OR
( (l_flow_schedule_rec.end_item_unit_number IS NULL) AND
(p_flow_schedule_rec.end_item_unit_number IS NULL))))
AND ( (l_flow_schedule_rec.quantity_scrapped =
p_flow_schedule_rec.quantity_scrapped) OR
((p_flow_schedule_rec.quantity_scrapped = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.quantity_scrapped IS NULL) AND
(p_flow_schedule_rec.quantity_scrapped IS NULL))))
AND ( (l_flow_schedule_rec.wip_entity_id =
p_flow_schedule_rec.wip_entity_id) OR
((p_flow_schedule_rec.wip_entity_id = FND_API.G_MISS_NUM) OR
( (l_flow_schedule_rec.wip_entity_id IS NULL) AND
(p_flow_schedule_rec.wip_entity_id IS NULL))))
THEN
-- Row has not changed. Set out parameter.
x_flow_schedule_rec := l_flow_schedule_rec;
FND_MESSAGE.SET_NAME('MRP','MRP_LOCK_ROW_DELETED');
SELECT mls.primary_item_id,
-- bucket_counter
floor(mls.scheduled_completion_date-start_date)+1,
-- bucket_date
floor(mls.scheduled_completion_date-start_date)+client_start_date,--TZ BOM Calendar bug 3832684
decode(x,1,sum(nvl(mls.planned_quantity,0)),
2,sum(nvl(mls.quantity_completed,0)),
sum((nvl(mls.planned_quantity,0)-nvl(mls.quantity_completed,0))))
FROM mrp_line_sch_avail_v mls
WHERE mls.line_id = p_line_id
AND mls.organization_id = p_org_id
AND mls.scheduled_completion_date
BETWEEN start_date and start_date+(7-1/(24*60*60))
GROUP BY mls.primary_item_id, floor(mls.scheduled_completion_date-start_date)
order by mls.primary_item_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
INSERT INTO mrp_form_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number15,
char1,
number14,
date1,
date2,
date3,
date4,
date5,
date6,
date7,
number1,
number2,
number3,
number4,
number5,
number6,
number7,
number10 )
VALUES (
p_query_id,
sysdate,
1,
sysdate,
1,
p_item_id,
substr(l_item_segments,1,80),
x,
bucket_dates(1),
bucket_dates(2),
bucket_dates(3),
bucket_dates(4),
bucket_dates(5),
bucket_dates(6),
bucket_dates(7),
bucket_quantity(1),
bucket_quantity(2),
bucket_quantity(3),
bucket_quantity(4),
bucket_quantity(5),
bucket_quantity(6),
bucket_quantity(7),
bucket_quantity(1)+bucket_quantity(2)+bucket_quantity(3)+bucket_quantity(4)+bucket_quantity(5)+bucket_quantity(6)+bucket_quantity(7)
);
INSERT INTO mrp_form_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number14,
number1,
number2,
number3,
number4,
number5,
number6,
number7,
number10 )
SELECT
p_query_id,
sysdate,
1,
sysdate,
1,
number14 + 10,
sum(number1),
sum(number2),
sum(number3),
sum(number4),
sum(number5),
sum(number6),
sum(number7),
sum(number1) + sum(number2) + sum(number3) + sum(number4) +
sum(number5) + sum(number6) + sum(number7)
FROM mrp_form_query
WHERE query_id = p_query_id
AND number14 in (1,2,3)
GROUP BY number14;
SELECT mrp_form_query_s.nextval
INTO l_query_id1
FROM dual;
INSERT INTO mrp_form_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number13,
number14,
number1,
date1 )
VALUES (
l_query_id1,
sysdate,
1,
sysdate,
1,
p_line_id,
p_org_id,
loop,
l_first_bucket_client + (loop - 1) --fix bug#3170105
);
PROCEDURE Update_Quantity(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_wip_entity_id IN NUMBER,
p_quantity IN NUMBER ) IS
BEGIN
FND_MSG_PUB.Initialize;
UPDATE wip_flow_schedules
SET planned_quantity = planned_quantity + p_quantity
WHERE wip_entity_id = p_wip_entity_id;
, 'update_quantity'
);
END Update_Quantity;
PROCEDURE Update_Quantity(
p_wip_entity_id IN NUMBER,
p_quantity IN NUMBER ) IS
BEGIN
UPDATE wip_flow_schedules
SET planned_quantity = planned_quantity + p_quantity
WHERE wip_entity_id = p_wip_entity_id;
, 'update_quantity'
);
END Update_Quantity;
PROCEDURE Delete_Flow_Row
( x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_wip_entity_id IN NUMBER
)
IS
BEGIN
FND_MSG_PUB.Initialize;
DELETE FROM WIP_FLOW_SCHEDULES
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND status = 1
AND nvl(transacted_flag, 'N') = 'N'
AND quantity_completed = 0
AND quantity_scrapped = 0 ;
, 'Delete_Flow_Row'
);
END Delete_Flow_Row;
PROCEDURE Delete_Flow_Schedules( ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_from_line IN VARCHAR2,
p_to_line IN VARCHAR2,
p_from_item IN VARCHAR2,
p_to_item IN VARCHAR2,
p_category_set_id IN NUMBER,
p_category_structure_id IN NUMBER,
p_from_category IN VARCHAR2,
p_to_category IN VARCHAR2) IS
-- Local variables
l_log_message VARCHAR2(2000);
l_where_clause := ' and wfs.line_id in (select line_id from wip_lines '||
' where line_code between :from_line and :to_line )';
'(select inventory_item_id from mtl_system_items msi ' ||
' where ' || l_item_where_clause || ')';
l_cat_where_clause := ' AND wfs.primary_item_id in (select ' ||
' inventory_item_id from mtl_item_categories mic, '||
' mtl_categories cat where ' ||
' cat.category_id = mic.category_id ' ||
' and mic.organization_id = :cat_organization_id ' ||
' and mic.category_set_id = :cat_category_set_id ' ||
' and ' || l_cat_where_clause || ')';
l_cat_where_clause := ' AND wfs.primary_item_id in (select ' ||
' inventory_item_id from mtl_item_categories mic '||
' where mic.organization_id = :cat_organization_id '||
' and mic.category_set_id = :cat_category_set_id ) ';
'SELECT wfs.wip_entity_id, wfs.demand_source_line, msi.replenish_to_order_flag, msi.build_in_wip_flag ' ||
' FROM wip_flow_schedules wfs, mtl_system_items msi ' ||
' WHERE wfs.primary_item_id = msi.inventory_item_id '||
' AND wfs.organization_id = msi.organization_id ' ||
' AND wfs.organization_id = to_char(:organization_id) ' ||
l_where_clause ||
' AND wfs.status = 1 AND wfs.quantity_completed = 0 ' ||
' AND wfs.scheduled_completion_date between ' ||
' :start_date ' ||
' and :end_date '||
' and not exists ( select 1 from mtl_transactions_interface ' ||
' where transaction_source_id = wfs.wip_entity_id ' ||
' and organization_id = wfs.organization_id ' ||
' and transaction_source_type_id = 5 ' || -- perf bug 4911894
' and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) ) ';
DELETE FROM wip_flow_schedules
WHERE wip_entity_id = l_wip_entity_id AND organization_id = p_organization_id;
delete from flm_exe_operations
where organization_id = p_organization_id and wip_entity_id = l_wip_entity_id;
delete from flm_exe_req_operations
where organization_id = p_organization_id and wip_entity_id = l_wip_entity_id;
delete from flm_exe_lot_numbers
where organization_id = p_organization_id and wip_entity_id = l_wip_entity_id;
delete from flm_exe_serial_numbers
where organization_id = p_organization_id and wip_entity_id = l_wip_entity_id;
FND_MESSAGE.set_name('MRP','MRP_ROWS_DELETED');
FND_MESSAGE.set_name('MRP','MRP_FLOW_DELETE');
FND_MESSAGE.set_name('MRP','MRP_NO_FLOW_DELETED');
FND_MESSAGE.set_name('MRP','MRP_DELETE_FLOW_ERROR');
FND_MESSAGE.set_name('MRP','MRP_DELETE_FLOW_ERROR');
FND_MESSAGE.set_name('MRP','MRP_DELETE_FLOW_ERROR');
END Delete_Flow_Schedules;
l_update_variance NUMBER := 0;
SELECT Kanban_Activity_Id
FROM mtl_kanban_card_activity
WHERE source_wip_entity_id = p_wip_entity_id;
SELECT nvl(sum(planned_quantity),0)
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND primary_item_id = p_item_id
AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
AND build_sequence IS NULL
AND demand_source_header_id IS NULL
AND scheduled_completion_date --fix bug#3170105
BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60);
SELECT wip_entity_id,
planned_quantity
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND primary_item_id = p_item_id
AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
AND build_sequence IS NULL
AND demand_source_header_id IS NULL
AND scheduled_completion_date --fix bug#3170105
BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60);
SELECT wip_entity_id,
scheduled_completion_date,
planned_quantity,
quantity_completed
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND primary_item_id = p_item_id
AND demand_source_header_id IS NULL
AND planned_quantity > quantity_completed
AND scheduled_completion_date --fix bug#3170105
BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60)
UNION
SELECT 0,
flm_timezone.calendar_to_server(calendar_date), --TZ Bug 3832684
0,
0
FROM mtl_parameters mp,
bom_calendar_dates bom
WHERE mp.organization_id = p_organization_id
AND mp.calendar_exception_set_id = bom.exception_set_id
AND mp.calendar_code = bom.calendar_code
AND bom.seq_num IS NOT NULL
AND bom.calendar_date between flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
AND flm_timezone.server_to_calendar(p_to_end_date)
AND bom.calendar_date NOT IN (select flm_timezone.server_to_calendar(scheduled_completion_date)
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND primary_item_id = p_item_id
AND demand_source_header_id IS NULL
AND scheduled_completion_date --fix bug#3170105
BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60))
ORDER BY 2 desc;
SELECT count(bom.calendar_date)
FROM mtl_parameters mp,
bom_calendar_dates bom
WHERE mp.calendar_exception_set_id = bom.exception_set_id
AND mp.calendar_code = bom.calendar_code
AND mp.organization_id = p_organization_id
AND bom.calendar_date BETWEEN flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
AND flm_timezone.server_to_calendar(p_to_end_date)
AND ((bom.seq_num IS NOT NULL) OR
(bom.calendar_date IN (SELECT flm_timezone.server_to_calendar(scheduled_completion_date)
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND primary_item_id = p_item_id
AND scheduled_completion_date BETWEEN
flm_timezone.calendar_to_server(bom.calendar_date)
AND flm_timezone.calendar_to_server(bom.calendar_date)+1-1/(24*60*60)
AND demand_source_header_id IS NULL)));
SELECT wip_entity_id,
scheduled_completion_date, --fix bug#3170105
planned_quantity
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND primary_item_id = p_item_id
AND build_sequence IS NULL
AND demand_source_header_id IS NULL
AND scheduled_completion_date --fix bug#3170105
BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60)
ORDER BY scheduled_completion_date desc
FOR UPDATE OF wip_entity_id NOWAIT;
SELECT wip_entity_id,
scheduled_completion_date, --fix bug#3170105
planned_quantity
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND primary_item_id = p_item_id
AND planned_quantity > 0
AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
AND build_sequence IS NULL
AND demand_source_header_id IS NULL
AND scheduled_completion_date BETWEEN
p_to_start_date + (p_completion_date - p_from_start_date)
AND p_to_start_date + (p_completion_date - p_from_start_date) +1-1/(24*60*60)
AND scheduled_completion_date --fix bug#3170105
BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60)
ORDER BY scheduled_completion_date desc
FOR UPDATE OF wip_entity_id NOWAIT;
SELECT rpad(substr(wl.line_code,1,10),10),
number1,
rpad(substr(kfv.concatenated_segments,1,14),14),
number2,
rpad(substr(nvl(sg.schedule_group_name,' '),1,8),8),
number3,
date1,
number4,
number5,
number4 - number5,
date2,
nvl(number6,0),
number7,
number7 - nvl(number6,0)
FROM wip_lines wl,
mtl_system_items_kfv kfv,
wip_schedule_groups sg,
mrp_form_query
WHERE wl.line_id = number1
AND wl.organization_id = number10
AND kfv.inventory_item_id = number2
AND kfv.organization_id = number10
AND sg.schedule_group_id(+) = number3
AND sg.organization_id(+) = number10
AND query_id = l_query_id
ORDER BY wl.line_code, kfv.concatenated_segments,schedule_group_name,
date1, date2, number4, number5, number6, number7;
SELECT sum(number4),
sum(number5),
sum(number4 - number5),
sum(nvl(number6,0)),
sum(number7),
sum(number7 - nvl(number6,0))
FROM mrp_form_query
WHERE query_id = l_query_id
AND number1 = l_line_id
AND number2 = l_item_id
AND nvl(number3,-1) = nvl(l_schedule_group_id,-1);
SELECT nvl(max(build_sequence),0)
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND build_sequence is NOT NULL
AND (ROLL_FORWARDED_FLAG <> G_INTERMEDIATE_ROLL_FORWARDED OR
ROLL_FORWARDED_FLAG IS NULL) /*Bug 3019639*/
/** Forward ported bug 3055939 */
AND scheduled_completion_date >= flm_timezone.client00_in_server(p_target_date) --fix bug#3170105
AND scheduled_completion_date < flm_timezone.client00_in_server(p_target_date+1); --fix bug#3170105
SELECT count(*)
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
AND nvl(planned_quantity,0) > nvl(quantity_completed,0)
/** Forward ported bug 3055939 */
AND scheduled_completion_date >= p_from_start_date --fix bug#3170105
AND scheduled_completion_date < p_from_end_date+1; --fix bug#3170105
SELECT count(distinct(nvl(roll_forwarded_flag,-1)))
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
/** Forward ported bug 3055939 */
AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
SELECT distinct(nvl(roll_forwarded_flag,-1))
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
/** Forward ported bug 3055939 */
AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
IS SELECT START_TIME,MAXIMUM_RATE FROM wip_lines
WHERE organization_id = p_organization_id
AND LINE_ID = p_line_id;
SELECT MAX(scheduled_completion_date)
FROM wip_flow_schedules
WHERE organization_id = p_organization_id
AND line_id = p_line_id
/** Forward ported bug 3055939 */
AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
l_cat_where_clause := ' AND fs.primary_item_id in (select ' ||
' inventory_item_id from mtl_item_categories mic, '||
' mtl_categories cat where ' ||
' cat.category_id = mic.category_id ' ||
' and mic.organization_id = :cat_organization_id ' ||
' and mic.category_set_id = :cat_category_set_id ' ||
' and ' || l_cat_where_clause || ')';
'SELECT fs.wip_entity_id ' ||
' FROM wip_lines line, '||
l_cat_table_sql ||
' mtl_system_items_kfv msi, '||
' wip_flow_schedules fs ' ||
' WHERE fs.organization_id = to_char( :organization_id) ' ||
l_where_clause ||
' AND fs.scheduled_completion_date < flm_timezone.sysdate00_in_server' ||
' and line.line_id = fs.line_id ' ||
' and line.organization_id = fs.organization_id '||
' and nvl(fs.status ,0) = 1 '||
l_cat_sql ||
' and msi.inventory_item_id = fs.primary_item_id ' ||
' and msi.organization_id = fs.organization_id ' ||
' and not exists ( select 1 from mtl_transactions_interface ' ||
' where transaction_source_id = fs.wip_entity_id ' ||
' and organization_id = fs.organization_id ' ||
' and transaction_source_type_id = 5 ' || -- perf bug 4911894
' and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) ) ';
l_cat_where_clause := ' AND fs.primary_item_id in (select ' ||
' inventory_item_id from mtl_item_categories mic, '||
' mtl_categories cat where ' ||
' cat.category_id = mic.category_id ' ||
' and mic.organization_id = :cat_organization_id ' ||
' and mic.category_set_id = :cat_category_set_id ' ||
' and ' || l_cat_where_clause || ')';
' SELECT fs.line_id, line.line_code, fs.primary_item_id, ' ||
' msi.concatenated_segments,null' ||
' ,sum(nvl(fs.planned_quantity,0)), ' ||
' sum(nvl(fs.quantity_completed,0)) ' ||
' FROM wip_lines line, '||
l_cat_table_sql ||
' mtl_system_items_kfv msi, '||
' wip_flow_schedules fs ' ||
' WHERE fs.organization_id = to_char( :organization_id ) ' ||
l_where_clause ||
' AND (nvl(fs.planned_quantity,0) - nvl(fs.quantity_completed,0)) <> 0 ' ||
' AND fs.scheduled_completion_date between ' ||
' :from_start_date ' ||
' and :from_end_date ' ||
' and line.line_id = fs.line_id ' ||
' and fs.demand_source_header_id IS NULL '||
' and line.organization_id = fs.organization_id '||
-- ' and nvl(fs.status ,0) = 1 '||
l_cat_sql ||
' and msi.inventory_item_id = fs.primary_item_id ' ||
' and msi.organization_id = fs.organization_id '||
' and not exists ( select 1 from mtl_transactions_interface ' ||
' where transaction_source_id = fs.wip_entity_id ' ||
' and organization_id = fs.organization_id '||
' and transaction_source_type_id = 5 ' || -- perf bug 4911894
' and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) ) ' ||
' group by fs.line_id, line.line_code, fs.primary_item_id, ' ||
' msi.concatenated_segments ' ||
' order by line.line_code, msi.concatenated_segments ' ;
l_select VARCHAR2(100);
l_cat_where_clause := ' AND fs.primary_item_id in (select ' ||
' inventory_item_id from mtl_item_categories mic, '||
' mtl_categories cat where ' ||
' cat.category_id = mic.category_id ' ||
' and mic.organization_id = :cat_organization_id ' ||
' and mic.category_set_id = :cat_category_set_id ' ||
' and ' || l_cat_where_clause || ')';
' SELECT fs.line_id, line.line_code, fs.primary_item_id, ' ||
' fs.schedule_group_id,fs.schedule_number,'||
' fs.build_sequence,fs.demand_source_header_id,fs.demand_source_line, ' ||
' fs.demand_source_delivery,fs.demand_source_type,fs.demand_class, ' ||
' msi.concatenated_segments ' ||
' ,nvl(fs.planned_quantity,0), ' ||
' nvl(fs.quantity_completed,0), ' ||
' fs.MPS_SCHEDULED_COMPLETION_DATE, nvl(fs.MPS_NET_QUANTITY,0), '||
' fs.BOM_REVISION, fs.ROUTING_REVISION,fs.BOM_REVISION_DATE, '||
' fs.ROUTING_REVISION_DATE, fs.ALTERNATE_BOM_DESIGNATOR, '||
' fs.ALTERNATE_ROUTING_DESIGNATOR, fs.COMPLETION_SUBINVENTORY, '||
' fs.COMPLETION_LOCATOR_ID, fs.MATERIAL_ACCOUNT, '||
' fs.MATERIAL_OVERHEAD_ACCOUNT, fs.RESOURCE_ACCOUNT, '||
' fs.OUTSIDE_PROCESSING_ACCOUNT, fs.MATERIAL_VARIANCE_ACCOUNT, '||
' fs.RESOURCE_VARIANCE_ACCOUNT, fs.OUTSIDE_PROC_VARIANCE_ACCOUNT, '||
' fs.STD_COST_ADJUSTMENT_ACCOUNT, fs.OVERHEAD_ACCOUNT, '||
' fs.OVERHEAD_VARIANCE_ACCOUNT, fs.PROJECT_ID,fs.TASK_ID, '||
' fs.ATTRIBUTE_CATEGORY, fs.ATTRIBUTE1,fs.ATTRIBUTE2, '||
' fs.ATTRIBUTE3, fs.ATTRIBUTE4,fs.ATTRIBUTE5, '||
' fs.ATTRIBUTE6, fs.ATTRIBUTE7,fs.ATTRIBUTE8, '||
' fs.ATTRIBUTE9, fs.ATTRIBUTE10,fs.ATTRIBUTE11, '||
' fs.ATTRIBUTE12, fs.ATTRIBUTE13,fs.ATTRIBUTE14, '||
' fs.ATTRIBUTE15, fs.KANBAN_CARD_ID,fs.END_ITEM_UNIT_NUMBER, '||
' fs.CURRENT_LINE_OPERATION, '||
' fs.WIP_ENTITY_ID '||
' FROM wip_lines line, '||
l_cat_table_sql ||
' mtl_system_items_kfv msi, '||
' wip_flow_schedules fs ' ||
' WHERE fs.organization_id = to_char(:organization_id) '||
l_where_clause ||
' AND (nvl(fs.planned_quantity,0) - nvl(fs.quantity_completed,0)) > 0 ' ||
' AND fs.scheduled_completion_date between ' ||
' :from_start_date ' ||
' and :from_end_date ' ||
' and line.line_id = fs.line_id ' ||
' and line.organization_id = fs.organization_id '||
' and nvl(fs.status,0) = 1 '||
l_cat_sql ||
' and msi.inventory_item_id = fs.primary_item_id ' ||
' and msi.organization_id = fs.organization_id '||
' and not exists ( select 1 from mtl_transactions_interface ' ||
' where transaction_source_id = fs.wip_entity_id ' ||
' and organization_id = fs.organization_id ' ||
' and transaction_source_type_id = 5 ' || -- perf bug 4911894
' and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) ) ' ||
' order by line.line_code, trunc(fs.scheduled_completion_date), fs.build_sequence ';
UPDATE WIP_FLOW_SCHEDULES SET
status = 2 , date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
l_select VARCHAR2(100);
UPDATE WIP_FLOW_SCHEDULES SET
status = 2 , date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
WHERE schedule_number = p_schedule_number
AND organization_id = p_organization_id;
l_select VARCHAR2(2000);
l_cat_where_clause := ' AND fs.primary_item_id in (select ' ||
' inventory_item_id from mtl_item_categories mic, '||
' mtl_categories cat where ' ||
' cat.category_id = mic.category_id ' ||
' and mic.organization_id = :cat_organization_id ' ||
' and mic.category_set_id = :cat_category_set_id ' ||
' and ' || l_cat_where_clause || ')';
l_select := ' AND (nvl(fs.planned_quantity,0) - nvl(fs.quantity_completed,0)) > 0 AND nvl(fs.status,0) = 1 ';
l_select := ' AND (nvl(fs.planned_quantity,0) - nvl(fs.quantity_completed,0)) <> 0 AND fs.demand_source_header_id IS NULL ';
' SELECT fs.wip_entity_id ' ||
' FROM wip_lines line, '||
l_cat_table_sql ||
' mtl_system_items_kfv msi, '||
' wip_flow_schedules fs ' ||
' WHERE fs.organization_id = to_char(:organization_id) ' ||
l_where_clause ||
l_select ||
' AND fs.scheduled_completion_date between ' ||
' :from_start_date ' ||
' and :from_end_date ' ||
' and line.line_id = fs.line_id ' ||
' and line.organization_id = fs.organization_id '||
l_cat_sql ||
' and msi.inventory_item_id = fs.primary_item_id ' ||
' and msi.organization_id = fs.organization_id ' ||
' and not exists ( select 1 from mtl_transactions_interface ' ||
' where transaction_source_id = fs.wip_entity_id ' ||
' and organization_id = fs.organization_id ' ||
' and transaction_source_type_id = 5 ' || -- perf bug 4911894
' and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) ) ';
SELECT mrp_form_query_s.nextval
INTO p_query_id
FROM DUAL;
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
fs1.line_id,
fs1.primary_item_id,
null,
--fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
sum(fs1.planned_quantity),
sum(fs1.quantity_completed),
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
+floor(p_to_start_date-p_from_start_date),
NULL,
0,
fs1.organization_id
FROM wip_lines wl,
mtl_system_items_kfv kfv,
mtl_categories_kfv cat,
mtl_item_categories mic,
wip_flow_schedules fs1
WHERE wl.line_id = fs1.line_id
AND fs1.demand_source_header_id IS NULL
-- AND nvl(fs1.closed,0) = 0
AND wl.organization_id = fs1.organization_id
AND wl.line_code
BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
AND kfv.inventory_item_id = fs1.primary_item_id
AND kfv.organization_id = fs1.organization_id
AND kfv.concatenated_segments
BETWEEN NVL(p_from_item,kfv.concatenated_segments) AND
NVL(p_to_item,kfv.concatenated_segments)
AND mic.inventory_item_id = fs1.primary_item_id
AND mic.organization_id = fs1.organization_id
AND mic.category_set_id = p_category_set_id
AND cat.category_id = mic.category_id
AND cat.concatenated_segments BETWEEN
NVL(p_from_category,cat.concatenated_segments)
AND NVL(p_to_category,cat.concatenated_segments)
AND fs1.scheduled_completion_date --fix bug#3170105
BETWEEN p_from_start_date AND
p_from_end_date+1-1/(24*60*60)
AND fs1.organization_id = p_organization_id
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs1.wip_entity_id
and organization_id = fs1.organization_id
and transaction_source_type_id = 5 -- perf bug 4911894
and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
GROUP BY fs1.line_id,
fs1.primary_item_id,
--fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
--floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
fs1.organization_id;
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
fs1.line_id,
fs1.primary_item_id,
null,
--fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
sum(fs1.planned_quantity),
sum(fs1.quantity_completed),
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
+floor(p_to_start_date-p_from_start_date),
NULL,
0,
fs1.organization_id
FROM wip_lines wl,
mtl_system_items_kfv kfv,
mtl_item_categories mic,
wip_flow_schedules fs1
WHERE wl.line_id = fs1.line_id
AND fs1.demand_source_header_id IS NULL
-- AND nvl(fs1.closed,0) = 0
AND wl.organization_id = fs1.organization_id
AND wl.line_code
BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
AND kfv.inventory_item_id = fs1.primary_item_id
AND kfv.organization_id = fs1.organization_id
AND kfv.concatenated_segments
BETWEEN NVL(p_from_item,kfv.concatenated_segments) AND
NVL(p_to_item,kfv.concatenated_segments)
AND mic.inventory_item_id = fs1.primary_item_id
AND mic.organization_id = fs1.organization_id
AND mic.category_set_id = p_category_set_id
AND fs1.scheduled_completion_date --fix bug#3170105
BETWEEN p_from_start_date AND
p_from_end_date+1-1/(24*60*60)
AND fs1.organization_id = p_organization_id
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs1.wip_entity_id
and organization_id = fs1.organization_id
and transaction_source_type_id = 5 -- perf bug 4911894
and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
GROUP BY fs1.line_id,
fs1.primary_item_id,
--fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
--floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
fs1.organization_id;
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
fs1.line_id,
fs1.primary_item_id,
null,
--fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
sum(fs1.planned_quantity),
sum(fs1.quantity_completed),
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
+floor(p_to_start_date-p_from_start_date),
NULL,
0,
fs1.organization_id
FROM wip_lines wl,
mtl_system_items_kfv kfv,
wip_flow_schedules fs1
WHERE wl.line_id = fs1.line_id
AND fs1.demand_source_header_id IS NULL
-- AND nvl(fs1.closed,0) = 0
AND wl.organization_id = fs1.organization_id
AND wl.line_code
BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
AND kfv.inventory_item_id = fs1.primary_item_id
AND kfv.organization_id = fs1.organization_id
AND kfv.concatenated_segments
BETWEEN NVL(p_from_item,kfv.concatenated_segments) AND
NVL(p_to_item,kfv.concatenated_segments)
AND fs1.scheduled_completion_date --fix bug#3170105
BETWEEN p_from_start_date AND
p_from_end_date+1-1/(24*60*60)
AND fs1.organization_id = p_organization_id
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs1.wip_entity_id
and organization_id = fs1.organization_id
and transaction_source_type_id = 5 -- perf bug 4911894
and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
GROUP BY fs1.line_id,
fs1.primary_item_id,
--fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
--floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
fs1.organization_id;
SELECT mrp_form_query_s.nextval
INTO p_query_id
FROM DUAL;
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10,
number11)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
fs1.line_id,
fs1.primary_item_id,
fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
fs1.planned_quantity,
fs1.quantity_completed,
flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
NULL,
0,
fs1.organization_id,
NVL(fs1.build_sequence,0)
FROM wip_lines wl,
mtl_system_items_kfv kfv,
mtl_categories_kfv cat,
mtl_item_categories mic,
wip_flow_schedules fs1
WHERE wl.line_id = fs1.line_id
AND wl.organization_id = fs1.organization_id
AND wl.line_code
BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
AND kfv.inventory_item_id = fs1.primary_item_id
AND (nvl(fs1.planned_quantity,0) - nvl(fs1.quantity_completed,0)) > 0
AND nvl(fs1.status,0) = 1
AND kfv.organization_id = fs1.organization_id
AND kfv.concatenated_segments
BETWEEN NVL(p_from_item,kfv.concatenated_segments) AND
NVL(p_to_item,kfv.concatenated_segments)
AND mic.inventory_item_id = fs1.primary_item_id
AND mic.organization_id = fs1.organization_id
AND mic.category_set_id = p_category_set_id
AND cat.category_id = mic.category_id
AND cat.concatenated_segments BETWEEN
NVL(p_from_category,cat.concatenated_segments)
AND NVL(p_to_category,cat.concatenated_segments)
AND fs1.scheduled_completion_date --fix bug#3170105
BETWEEN p_from_start_date AND
p_from_end_date+1-1/(24*60*60)
AND fs1.organization_id = p_organization_id
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs1.wip_entity_id
and organization_id = fs1.organization_id
and transaction_source_type_id = 5 -- perf bug 4911894
and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) );
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10,
number11)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
fs1.line_id,
fs1.primary_item_id,
fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
fs1.planned_quantity,
fs1.quantity_completed,
flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
NULL,
0,
fs1.organization_id,
NVL(fs1.build_sequence,0)
FROM wip_lines wl,
mtl_system_items_kfv kfv,
mtl_item_categories mic,
wip_flow_schedules fs1
WHERE wl.line_id = fs1.line_id
AND wl.organization_id = fs1.organization_id
AND wl.line_code
BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
AND kfv.inventory_item_id = fs1.primary_item_id
AND (nvl(fs1.planned_quantity,0) - nvl(fs1.quantity_completed,0)) > 0
AND nvl(fs1.status,0) = 1
AND kfv.organization_id = fs1.organization_id
AND kfv.concatenated_segments
BETWEEN NVL(p_from_item,kfv.concatenated_segments) AND
NVL(p_to_item,kfv.concatenated_segments)
AND mic.inventory_item_id = fs1.primary_item_id
AND mic.organization_id = fs1.organization_id
AND mic.category_set_id = p_category_set_id
AND fs1.scheduled_completion_date --fix bug#3170105
BETWEEN p_from_start_date AND
p_from_end_date+1-1/(24*60*60)
AND fs1.organization_id = p_organization_id
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs1.wip_entity_id
and organization_id = fs1.organization_id
and transaction_source_type_id = 5
and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) );
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10,
number11)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
fs1.line_id,
fs1.primary_item_id,
fs1.schedule_group_id,
trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
fs1.planned_quantity,
fs1.quantity_completed,
flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
NULL,
0,
fs1.organization_id,
NVL(fs1.build_sequence,0)
FROM wip_lines wl,
mtl_system_items_kfv kfv,
wip_flow_schedules fs1
WHERE wl.line_id = fs1.line_id
AND wl.organization_id = fs1.organization_id
AND wl.line_code
BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
AND kfv.inventory_item_id = fs1.primary_item_id
AND (nvl(fs1.planned_quantity,0) - nvl(fs1.quantity_completed,0)) > 0
AND nvl(fs1.status,0) = 1
AND kfv.organization_id = fs1.organization_id
AND kfv.concatenated_segments
BETWEEN NVL(p_from_item,kfv.concatenated_segments) AND
NVL(p_to_item,kfv.concatenated_segments)
AND fs1.scheduled_completion_date --fix bug#3170105
BETWEEN p_from_start_date AND
p_from_end_date+1-1/(24*60*60)
AND fs1.organization_id = p_organization_id
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs1.wip_entity_id
and organization_id = fs1.organization_id
and transaction_source_type_id = 5
and transaction_action_id in (1, 27, 30, 31, 32, 33,34) );
PROCEDURE report_update_WD(p_query_id IN NUMBER,
p_line_id IN NUMBER,
p_item_id IN NUMBER,
p_schedule_group_id IN NUMBER,
p_completion_date IN DATE,
p_build_sequence IN NUMBER,
p_quantity IN NUMBER,
p_from_start_date IN DATE,
p_from_end_date IN DATE,
p_to_start_date IN DATE,
p_to_end_date IN DATE) IS --fix bug#3170105
l_rowid VARCHAR2(80);
SELECT rowid
FROM mrp_form_query
WHERE query_id = p_query_id
AND number1 = p_line_id
AND number2 = p_item_id
AND NVL(number3,-1) = NVL(p_schedule_group_id,-1)
AND trunc(date2) = trunc(flm_timezone.server_to_client(p_completion_date)) --fix bug#3170105
AND NVL(number11,0) = NVL(p_build_sequence,0);
SELECT planned_quantity
FROM wip_flow_schedules
WHERE line_id = p_line_id
AND primary_item_id = p_item_id
AND organization_id = p_organization_id
AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
AND NVL(build_sequence,0) = NVL(p_build_sequence,0)
AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10,
number11)
SELECT
p_query_id,
sysdate,
1,
sysdate,
1,
p_line_id,
p_item_id,
p_schedule_group_id,
flm_timezone.server_to_client(p_completion_date)
-floor(p_to_start_date-p_from_start_date), --fix bug#3170105
0,
0,
flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
0,
p_quantity,
p_organization_id,
NVL(p_build_sequence,0)
FROM wip_flow_schedules fs
WHERE fs.line_id = p_line_id
AND fs.primary_item_id = p_item_id
AND fs.organization_id = p_organization_id
AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
AND scheduled_completion_date
BETWEEN flm_timezone.client00_in_server(p_completion_date)
AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#3170105
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs.wip_entity_id
and organization_id = fs.organization_id
and transaction_source_type_id = 5
and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) );
UPDATE mrp_form_query
SET number6 = decode(number6,NULL,nvl(l_to_upd_qty,0),number6),
number7 = decode(nvl(number7,0),0,nvl(l_to_upd_qty,0),number7) +
p_quantity
WHERE rowid = l_rowid;
l_log_message := 'Problem in report_update_WD procedure';
END report_update_WD;
PROCEDURE report_update_WOD(p_query_id IN NUMBER,
p_line_id IN NUMBER,
p_item_id IN NUMBER,
p_schedule_group_id IN NUMBER,
p_completion_date IN DATE,
p_quantity IN NUMBER,
p_from_start_date IN DATE,
p_from_end_date IN DATE,
p_to_start_date IN DATE,
p_to_end_date IN DATE) IS --fix bug#3170105
l_rowid VARCHAR2(80);
SELECT rowid
FROM mrp_form_query
WHERE query_id = p_query_id
AND number1 = p_line_id
AND number2 = p_item_id
--2 AND NVL(number3,-1) = NVL(p_schedule_group_id,-1)
AND trunc(date2) = trunc(flm_timezone.server_to_client(p_completion_date)); --fix bug#3170105
SELECT sum(planned_quantity)
FROM wip_flow_schedules
WHERE line_id = p_line_id
AND primary_item_id = p_item_id
AND organization_id = p_organization_id
--3 AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
INSERT INTO mrp_form_query
(query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number1,
number2,
number3,
date1,
number4,
number5,
date2,
number6,
number7,
number10)
SELECT
p_query_id,
sysdate,
1,
sysdate,
1,
p_line_id,
p_item_id,
null,
--p_schedule_group_id,
flm_timezone.server_to_client(p_completion_date)
-floor(p_to_start_date-p_from_start_date), --fix bug#3170105
0,
0,
flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
nvl(sum(planned_quantity),0),
nvl(sum(planned_quantity),0) + p_quantity,
p_organization_id
FROM wip_flow_schedules fs
WHERE fs.line_id = p_line_id
AND fs.primary_item_id = p_item_id
AND fs.organization_id = p_organization_id
--4 AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
AND scheduled_completion_date
BETWEEN flm_timezone.client00_in_server(p_completion_date)
AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#317010
AND not exists ( select 1 from mtl_transactions_interface
where transaction_source_id = fs.wip_entity_id
and organization_id = fs.organization_id
and transaction_source_type_id = 5
and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) );
UPDATE mrp_form_query
SET number6 = decode(number6,NULL,nvl(l_to_upd_qty,0),number6),
number7 =
decode(nvl(number7,0),0,nvl(l_to_upd_qty,0),number7) + p_quantity
WHERE rowid = l_rowid;
l_log_message := 'Problem in report_update_WOD procedure';
END report_update_WOD;
INSERT INTO mrp_relief_interface
(inventory_item_id, -- NN
organization_id, -- NN
last_update_date, -- NN sysdate
last_updated_by, -- NN :new.last_updated_by
creation_date, -- NN sysdate
created_by, -- NN :new.created_by
last_update_login, -- N -1
new_order_quantity, -- NN
old_order_quantity, -- N
new_order_date, -- NN
old_order_date, -- N
disposition_id, -- NN :new.wip_entity_id
planned_order_id, -- N
relief_type, -- NN 2
disposition_type, -- NN 9
demand_class, -- N
old_demand_class, -- N
line_num, -- N null
request_id, -- N null
program_application_id, -- N null
program_id, -- N null
program_update_date, -- N null
process_status, -- NN 2
source_code, -- N 'WIP'
source_line_id, -- N null
error_message, -- N null
transaction_id, -- NN
project_id,
old_project_id,
task_id,
old_task_id
)
SELECT primary_item_id,
organization_id,
sysdate,
1,
sysdate,
1,
-1,
greatest(planned_quantity, quantity_completed)
- (planned_quantity - quantity_completed),
greatest(planned_quantity, quantity_completed),
scheduled_completion_date,
scheduled_completion_date,
wip_entity_id,
DECODE(demand_source_type,100,
to_number(demand_source_line), NULL),
2,
9,
demand_class,
demand_class,
null,
null,
null,
null,
null,
2,
'WIP',
null,
null,
mrp_relief_interface_s.nextval,
project_id,
project_id,
task_id,
task_id
FROM wip_flow_schedules
WHERE wip_entity_id = p_wip_entity_id
AND planned_quantity > quantity_completed;
SELECT to_char(sysdate)
INTO l_trans_var2
FROM dual;
| Do setup for report by inserting existing records |
| into mrp_form_query |
+-----------------------------------------------------*/
/* this part of code is moved from the buttom of the procedure to here,
because after the schedules got rolled, there would be no flow schedules
to be queried to insert into mrp_relief_interface */
IF p_output = 2 THEN
l_cursor := Create_Relieve_Cursor(l_from_start_date,l_from_end_date); --fix bug#317105
(l_update_variance <> 0) THEN
/*-------------------------------------------------------+
| Add one to each until there is no remainder left. |
| l_update_variance will equal zero unless variance was |
| negative and there is still some left after reducing |
| the last schedule |
| If the schedule is still on the same date, we want to |
| carry over the variance that is left over from the |
| previous schedules (which is still on the same day). |
+-------------------------------------------------------*/
IF l_completion_date <> l_old_completion_date THEN
IF l_remainder > 0 THEN
IF l_variance < 0 THEN
l_update_variance := l_update_variance + l_daily_variance - 1;
l_update_variance := l_update_variance + l_daily_variance + 1;
l_update_variance := l_update_variance + l_daily_variance;
IF l_update_variance > 0 THEN
l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_CREATE;
l_flow_schedule_rec.planned_quantity := l_update_variance;
l_completion_date,l_update_variance);
report_update_WOD(l_report_query_id,
fs_report_rec.line_id,
fs_report_rec.primary_item_id,
fs_report_rec.schedule_group_id,
l_completion_date,
l_update_variance,
l_from_start_date,
l_from_end_date,
l_to_start_date,
l_to_end_date);
l_update_variance := 0;
| - delete schedule and carry remainder variance |
| to next schedule |
+------------------------------------------------------------*/
ELSIF l_update_variance < 0 THEN
IF l_planned_quantity = 0 THEN
/*---------------------------------------------+
| Store variance to be handled the next day |
| Just don't zero out l_update_variance, |
| nothing else needs to be done |
+---------------------------------------------*/
NULL;
IF -(l_update_variance) < (l_planned_quantity -
l_quantity_completed) THEN
-- Call procedure to update mrp_form_query for the report
report_update_WOD(l_report_query_id,
fs_report_rec.line_id,
fs_report_rec.primary_item_id,
fs_report_rec.schedule_group_id,
l_completion_date,
l_update_variance,
l_from_start_date,
l_from_end_date,
l_to_start_date,
l_to_end_date);
| Update existing schedule |
+-----------------------------*/
Update_Quantity(l_wip_entity_id,
l_update_variance);
FND_MESSAGE.set_name('MRP','MRP_ROLL_UPDATE');
(l_planned_quantity + l_update_variance));
l_update_variance := 0;
ELSIF -(l_update_variance) >= (l_planned_quantity -
l_quantity_completed) THEN
-- Call procedure to update mrp_form_query for the report
report_update_WOD(l_report_query_id,
fs_report_rec.line_id,
fs_report_rec.primary_item_id,
fs_report_rec.schedule_group_id,
l_completion_date,
-(l_planned_quantity - l_quantity_completed),
l_from_start_date,
l_from_end_date,
l_to_start_date,
l_to_end_date);
| Delete existing schedule and save remaining update |
| variance for next schedule |
+------------------------------------------------------*/
Delete_Row(l_wip_entity_id);
| Update existing schedule |
+-----------------------------*/
Update_Quantity(l_wip_entity_id,
- (l_planned_quantity-l_quantity_completed));
FND_MESSAGE.set_name('MRP','MRP_ROLL_DELETE');
l_update_variance := l_update_variance +
(l_planned_quantity-l_quantity_completed);
| quantity in l_update_variance (from a negative variance |
| that didn't have enough quantities to delete on the |
| right days). This is a corner case but it is possible |
| that there will be other schedules in the timeframe |
| that we can delete from so we loop again. |
| e.g. Day 1 Qty = 5 |
| Day 2 Qty = 5 |
| Day 3 Qty = 2 |
| If the daily update variance was -3, then after the |
| first loop we have: |
| Day 1 Qty = 2 |
| Day 2 Qty = 2 |
| Day 3 Qty = 0 |
| and a remainder variance of -1. The second loop will |
| reduce the Day 1 Qty to 1. |
| If we still have a remainder qty after the second loop |
| then generate an error message to the log |
+---------------------------------------------------------*/
IF l_update_variance < 0 THEN
-- Log what's going on
FND_MESSAGE.set_name('MRP','MRP_ROLL_SECOND_LOOP');
IF -(l_update_variance) < (l_planned_quantity -
l_quantity_completed) THEN
-- Call procedure to update mrp_form_query for the report
report_update_WOD(l_report_query_id,
fs_report_rec.line_id,
fs_report_rec.primary_item_id,
fs_report_rec.schedule_group_id,
l_completion_date,
l_update_variance,
l_from_start_date,
l_from_end_date,
l_to_start_date,
l_to_end_date);
| Update existing schedule |
+-----------------------------*/
Update_Quantity(l_wip_entity_id,
l_update_variance);
FND_MESSAGE.set_name('MRP','MRP_ROLL_UPDATE');
(l_planned_quantity + l_update_variance));
l_update_variance := 0;
ELSIF -(l_update_variance) >= (l_planned_quantity -
l_quantity_completed) THEN
-- Call procedure to update mrp_form_query for the report
report_update_WOD(l_report_query_id,
fs_report_rec.line_id,
fs_report_rec.primary_item_id,
fs_report_rec.schedule_group_id,
l_completion_date,
-(l_planned_quantity- l_quantity_completed),
l_from_start_date,
l_from_end_date,
l_to_start_date,
l_to_end_date);
| Delete existing schedule and save remaining update |
| variance for next schedule |
+------------------------------------------------------*/
Delete_Row(l_wip_entity_id);
| Update existing schedule |
+-----------------------------*/
Update_Quantity(l_wip_entity_id,
- (l_planned_quantity-l_quantity_completed));
FND_MESSAGE.set_name('MRP','MRP_ROLL_DELETE');
l_update_variance := l_update_variance +
(l_planned_quantity-l_quantity_completed);
EXIT WHEN l_update_variance = 0;
| If l_update_variance is still less than zero, print a |
| message to the log. |
+-------------------------------------------------------*/
IF l_update_variance < 0 THEN
FND_MESSAGE.set_name('MRP','MRP_ROLL_QTY_REMAINDER');
FND_MESSAGE.set_token('QTY',l_update_variance);
l_update_variance := 0;
UPDATE wip_flow_schedules set roll_forwarded_flag =
G_ROLL_FORWARDED
where roll_forwarded_flag =
G_INTERMEDIATE_ROLL_FORWARDED;
l_update_variance := fs_report_rec.planned_quantity
- fs_report_rec.quantity_completed;
l_flow_schedule_rec.planned_quantity := l_update_variance;
l_update_variance);
report_update_WD(l_report_query_id,
fs_report_rec.line_id,
fs_report_rec.primary_item_id,
fs_report_rec.schedule_group_id,
l_flow_schedule_rec.scheduled_completion_date,
fs_report_rec.build_sequence, /* Bug 2558664 */
l_update_variance,
l_from_start_date,
l_from_end_date,
l_to_start_date,
l_to_end_date);
SELECT auto_replenish,
wip_entity_id
INTO l_auto_replenish,
l_wip_entity_id
FROM wip_flow_schedules
WHERE schedule_number = fs_report_rec.schedule_number
AND organization_id = p_organization_id;
/* Update the auto_replenish flag of new schedules with the old value,
if auto_replenish is not null for old flow schedule */
IF l_auto_replenish IS NOT NULL THEN
UPDATE wip_flow_schedules
SET auto_replenish = l_auto_replenish
WHERE schedule_number = l_x_flow_schedule_rec.schedule_number
AND organization_id = p_organization_id;
/* Update the Kanban Cards to reference the new flow schedule */
IF (nvl(l_auto_replenish, 'N') = 'Y') THEN
FOR l_kanban_card_activity_csr IN kanban_card_activity_csr(l_wip_entity_id)
LOOP
l_kanban_activity_id := l_kanban_card_activity_csr.kanban_activity_id;
UPDATE mtl_kanban_card_activity
SET source_wip_entity_id = l_x_flow_schedule_rec.wip_entity_id
WHERE kanban_activity_id = l_kanban_activity_id;
l_update_variance := 0;
UPDATE wip_flow_schedules set roll_forwarded_flag =
G_ROLL_FORWARDED
where roll_forwarded_flag =
G_INTERMEDIATE_ROLL_FORWARDED;
UPDATE wip_flow_schedules
set BUILD_SEQUENCE = BUILD_SEQUENCE + l_total_count
WHERE organization_id = p_organization_id
AND line_id = l_index
AND nvl(planned_quantity,0) > nvl(quantity_completed,0)
AND scheduled_completion_date --fix bug#3170105
BETWEEN l_to_start_date AND l_to_end_date+1-1/(24*60*60)
AND (ROLL_FORWARDED_FLAG <> G_INTERMEDIATE_ROLL_FORWARDED OR
ROLL_FORWARDED_FLAG IS NULL); /*Bug 3019639*/
| Update the newly forwarded schedules as ROLL FORWARDED; |
UPDATE wip_flow_schedules set
roll_forwarded_flag = G_ROLL_FORWARDED,
build_sequence = build_sequence - l_base_number
where roll_forwarded_flag = G_INTERMEDIATE_ROLL_FORWARDED
and line_id = l_index
--bug 3749052:
and scheduled_completion_date
BETWEEN l_to_start_date AND l_to_end_date+1-1/(24*60*60);
UPDATE wip_flow_schedules
SET schedule_number=('?*?'||oldFSSchNum(i))
WHERE wip_entity_id=oldFSWipId(i)
AND organization_id=p_organization_id;
UPDATE wip_flow_schedules
SET schedule_number=oldFSSchNum(i)
WHERE wip_entity_id=newFSWipId(i)
AND organization_id=p_organization_id;
UPDATE wip_flow_schedules
SET schedule_number=newFSSchNum(i)
WHERE wip_entity_id=oldFSWipId(i)
AND organization_id=p_organization_id;
UPDATE flm_exe_operations
SET wip_entity_id=newFSWipId(i)
WHERE wip_entity_id=oldFSWipId(i)
AND organization_id=p_organization_id;
UPDATE flm_exe_req_operations
SET wip_entity_id=newFSWipId(i)
WHERE wip_entity_id=oldFSWipId(i)
AND organization_id=p_organization_id;
UPDATE flm_exe_lot_numbers
SET wip_entity_id=newFSWipId(i)
WHERE wip_entity_id=oldFSWipId(i)
AND organization_id=p_organization_id;
UPDATE flm_exe_serial_numbers
SET wip_entity_id=newFSWipId(i)
WHERE wip_entity_id=oldFSWipId(i)
AND organization_id=p_organization_id;
UPDATE wip_flow_schedules SET status = 2,
date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
WHERE wip_entity_id = l_flow_schedule_rec.wip_entity_id;
p_update IN NUMBER,
p_flex_tolerance IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR RULE_CURSOR IS
SELECT distinct user_defined
FROM mrp_scheduling_rules
WHERE rule_id = p_rule_id;
IF p_update = 1 THEN
Post_Schedule_Update (p_org_id);
PROCEDURE Post_Schedule_Update (p_org_id IN NUMBER)
IS
CURSOR C1 IS
SELECT wip_entity_id, schedule_number
FROM wip_flow_schedules
WHERE request_id = USERENV( 'SESSIONID' );
SELECT sum(planned_quantity), demand_source_line
FROM wip_flow_schedules
WHERE organization_id = p_org_id
AND demand_source_header_id IS NULL
AND request_id = USERENV('SESSIONID')
GROUP BY demand_source_line;
UPDATE wip_flow_schedules
SET schedule_number = l_schedule_number_out
WHERE wip_entity_id = l_wip_entity_id;
UPDATE wip_entities
SET wip_entity_name = l_schedule_number_out
WHERE wip_entity_id = l_wip_entity_id;
UPDATE mrp_recommendations
SET quantity_in_process = nvl(quantity_in_process,0) + l_quantity
WHERE transaction_id = l_trans_id;
UPDATE wip_flow_schedules
SET request_id = NULL,
scheduled_flag = 1
WHERE request_id = USERENV('SESSIONID');
END Post_Schedule_Update;
SELECT distinct primary_item_id, organization_id, alternate_bom_designator
FROM wip_flow_schedules
WHERE request_id = USERENV('SESSIONID');
SELECT line_id,
--fix bug#3417588
decode(nvl(p_use_open_quantity, 'N'), 'Y',
decode(SL.SHIPPED_QUANTITY, NULL,
greatest(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SL.SHIP_FROM_ORG_ID,
SL.INVENTORY_ITEM_ID,
SL.ORDER_QUANTITY_UOM,
SL.ORDERED_QUANTITY)
- MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY(
SL.SHIP_FROM_ORG_ID,
SL.INVENTORY_ITEM_ID,
SL.LINE_ID,
p_use_open_quantity), 0),
0),
greatest(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SL.SHIP_FROM_ORG_ID,
SL.INVENTORY_ITEM_ID,
SL.ORDER_QUANTITY_UOM,
SL.ORDERED_QUANTITY)
- MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY(
SL.SHIP_FROM_ORG_ID,
SL.INVENTORY_ITEM_ID,
SL.LINE_ID,
p_use_open_quantity), 0))
order_quantity,
--end of fix bug#3417588
split_from_line_id parent_line_id
FROM oe_order_lines_all sl
WHERE header_id = i_header_id
AND inventory_item_id = i_item_id
ORDER BY sl.line_id;
SELECT header_id, inventory_item_id
INTO l_header_id, l_item_id
FROM oe_order_lines_all
WHERE line_id = to_number(p_demand_source_line);
l_lines.DELETE;
l_lines.DELETE(l_index_temp);
l_lines.DELETE(l_index_temp);
/** Bug 2536351 - update previous_brother of each child node **/
for i in l_lines.FIRST..l_lines.LAST
LOOP
if (l_lines.exists(i)) then
if (l_lines(i).next_brother is NOT NULL) THEN
l_lines(l_lines(i).next_brother).previous_brother := i;
/** Bug 2536351 - update previous_brother ends ****************/
-- query flow quantity
l_index := l_lines.FIRST;
SELECT nvl(sum(decode(nvl(p_use_open_quantity,'N'),'Y',decode(status,2,0,
(planned_quantity-quantity_completed)),
decode(status,2,quantity_completed,
planned_quantity))),0)
INTO l_lines(l_index).fs_quantity
FROM wip_flow_schedules
WHERE primary_item_id = l_item_id
AND demand_source_line = to_char(l_lines(l_index).line_id)
AND demand_source_type = p_demand_source_type
AND ((demand_source_delivery IS NULL) or
(demand_source_delivery = p_demand_source_delivery));
SELECT nvl(sum(decode(nvl(p_use_open_quantity,'N'),'Y',decode(status,2,0,
(planned_quantity-quantity_completed)),
decode(status,2,quantity_completed,
planned_quantity))),0)
FROM wip_flow_schedules
WHERE demand_source_line = p_demand_source_line
AND demand_source_type = p_demand_source_type
AND ((demand_source_delivery IS NULL)or(demand_source_delivery = p_demand_source_delivery));
SELECT split_from_line_id
INTO l_split_from_line
FROM oe_order_lines_all
WHERE line_id = to_number(p_demand_source_line);
SELECT project_id, task_id
FROM pjm_project_demand_v
WHERE demand_id = p_demand_id;
SELECT nvl(sum(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(P_ORG_ID,
P_ITEM_ID,
RESERVATION_UOM_CODE,
RESERVATION_QUANTITY)), 0)
INTO l_reserved_qty
FROM mtl_reservations
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND demand_source_line_id = p_line_id
AND ( nvl(p_use_open_quantity,'N') = 'N'
and (supply_source_header_id is NULL
or not exists (select wip_entity_id
from wip_flow_schedules
where wip_entity_id = supply_source_header_id
and demand_source_line = nvl(p_line_id,-1))
)
or nvl(p_use_open_quantity,'N') = 'Y') ; /*Bug 3042045 - do not check supply source for ATO items*/
SELECT nvl(SUM(mtrl.primary_quantity),0)
INTO l_wip_state_qty
FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn, wip_lpn_completions wlc
WHERE mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND NVL(mtrl.quantity_delivered, 0) = 0
AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context = 2 -- WIP
AND wlc.header_id = mtrl.reference_id
AND wlc.wip_entity_id = mtrl.txn_source_id
AND wlc.lpn_id = mtrl.lpn_id
AND wlc.inventory_item_id = mtrl.inventory_item_id
AND wlc.organization_id = mtrl.organization_id
AND wlc.demand_source_line = p_line_id
AND wlc.wip_entity_type = 4;
SELECT count(*)
FROM oe_hold_sources_all hs,
oe_order_holds_all oh,
oe_hold_definitions hd
WHERE oh.hold_source_id = hs.hold_source_id
AND oh.line_id = p_line_id
AND oh.hold_release_id IS NULL
AND hd.item_type = 'OEOL'
AND hd.activity_name = 'LINE_SCHEDULING'
AND hd.hold_id = hs.hold_id;
SELECT alternate_routing_designator
FROM bom_operational_routings
WHERE line_id = p_line_id
AND assembly_item_id = p_item_id
AND organization_id = p_organization_id
AND cfm_routing_flag = 1
ORDER BY alternate_routing_designator desc;