[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_JOB_STATUS_DELETED VARCHAR2(2) := '22'; --Deleted
SELECT value
INTO l_full_path
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT trim(substr(l_full_path, 1, decode(instr(l_full_path,',')-1,
-1, length(l_full_path),
instr(l_full_path, ',')-1
)
)
)
INTO l_file_dir
FROM dual;
SELECT *
FROM AHL_ALL_WORKORDERS_V
WHERE workorder_id=c_workorder_id;
SELECT quantity
FROM CSI_ITEM_INSTANCES
WHERE instance_id=c_instance_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ACTIVE_START_DATE,SYSDATE))
AND TRUNC(NVL(ACTIVE_END_DATE,SYSDATE));
SELECT department_id
FROM BOM_DEPARTMENTS
WHERE UPPER(description) LIKE UPPER(c_dept)
AND ORganization_id=c_org_id;
SELECT STATUS_CODE
FROM AHL_WORKORDERS
WHERE workorder_id = c_workorder_id;
SELECT
TO_CHAR(scheduled_start_date, 'ss') schedule_start_sec,
TO_CHAR(scheduled_completion_date, 'ss') schedule_end_sec
FROM
WIP_DISCRETE_JOBS
WHERE
WIP_ENTITY_ID = c_wip_entity_id;
SELECT
TO_CHAR(actual_start_date, 'ss') actual_start_sec,
TO_CHAR(actual_end_date, 'ss') actual_end_sec
FROM
AHL_WORKORDERS
WHERE
WORKORDER_ID = c_workorder_id;
SELECT Lookup_code
FROM FND_LOOKUPS
WHERE lookup_type = 'AHL_PRD_WO_HOLD_REASON'
AND MEANING = c_hold_reason;
SELECT COUNT(*) INTO l_ctr
FROM MFG_LOOKUPS
WHERE lookup_type='WIP_EAM_ACTIVITY_PRIORITY'
AND lookup_code=p_x_prd_workorder_rec.job_priority;
-- that is the workorder is not being updated from the prod UI
-- but is coming from VWP Push to Prod,
-- then the scheduled start hr and min fields are not populated
-- and are already part of the start and end dates
-- so these should not be converted to 00:00
OPEN get_wo_status(p_x_prd_workorder_rec.workorder_id);
FUNCTION is_wo_updated(p_prd_workorder_rec IN PRD_WORKORDER_REC)
RETURN BOOLEAN
AS
CURSOR get_old_wo_values(c_workorder_id NUMBER)
IS
SELECT AWOS.CONFIRM_FAILURE_FLAG,
AWOS.ACTUAL_START_DATE,
AWOS.ACTUAL_END_DATE,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE,
WIP.OWNING_DEPARTMENT
FROM AHL_WORKORDERS AWOS,
WIP_DISCRETE_JOBS WIP
WHERE AWOS.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
AND AWOS.workorder_id = c_workorder_id;
END is_wo_updated;
SELECT lookup_code
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_code = c_lookup_code
AND lookup_type = c_lookup_type
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
AND NVL(end_date_active,SYSDATE);
SELECT a.organization_id,
b.eam_enabled_flag
FROM ORG_ORGANIZATION_DEFINITIONS a,MTL_PARAMETERS b
WHERE a.organization_id=b.organization_id
AND a.organization_id=c_org_id;
SELECT a.organization_id,
b.eam_enabled_flag
FROM INV_ORGANIZATION_NAME_V a,MTL_PARAMETERS b
WHERE a.organization_id=b.organization_id
AND a.organization_id=c_org_id;
SELECT a.department_id
FROM BOM_DEPARTMENTS a
WHERE a.department_id=c_dept_id
AND a.organization_id=c_org_id;
SELECT a.instance_id
FROM CSI_ITEM_INSTANCES a
WHERE a.inventory_item_id=c_inv_item_id
AND a.instance_id=c_inst_id;
SELECT a.organization_id,
a.secondary_inventory,
b.eam_enabled_flag
FROM MTL_ITEM_SUB_INVENTORIES a,
MTL_PARAMETERS b
WHERE a.organization_id=b.organization_id
AND a.organization_id=c_org_id
AND a.secondary_inventory=c_sub_inv;
SELECT visit_task_name
FROM AHL_VISIT_TASKS_VL
WHERE visit_task_id=c_visit_task_id;
SELECT 1
FROM MTL_PARAMETERS mpr
WHERE mpr.organization_id=c_org_id
AND NVL(mpr.project_reference_enabled,2)=1;
SELECT WDJ.scheduled_start_date,
WDJ.scheduled_completion_date
FROM WIP_DISCRETE_JOBS WDJ,
AHL_WORKORDERS WO
WHERE WDJ.wip_entity_id = WO.wip_entity_id
AND WO.visit_task_id IS NULL
AND WO.master_workorder_flag = 'Y'
AND WO.visit_id = c_visit_id;
SELECT AWOS.status_code,
FNDL.meaning
FROM AHL_WORKORDERS AWOS,
FND_LOOKUP_VALUES_VL FNDL
WHERE AWOS.WORKORDER_ID = c_workorder_id
AND FNDL.lookup_type = 'AHL_JOB_STATUS'
AND FNDL.lookup_code(+) = AWOS.status_code;
SELECT MIN(ACTUAL_START_DATE),
MAX(ACTUAL_END_DATE)
FROM AHL_WORKORDER_OPERATIONS
WHERE WORKORDER_ID = c_workorder_id;
Select 1
FROM FND_LOOKUPS
WHERE lookup_type = 'AHL_PRD_WO_HOLD_REASON'
AND lookup_code = c_hold_reason;
IF is_wo_updated(p_prd_workorder_rec) = TRUE THEN
OPEN get_wo_status(p_prd_workorder_rec.workorder_id);
END IF; -- IF is_wo_updated = TRUE THEN
SELECT COUNT(*)
INTO l_dummy_ctr
FROM WIP_EAM_PARAMETERS
WHERE organization_id=p_prd_workorder_rec.ORGANIZATION_ID;
p_prd_workorder_rec.STATUS_CODE<> G_JOB_STATUS_DELETED AND
p_prd_workorder_rec.STATUS_CODE<> G_JOB_STATUS_CANCELLED THEN
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_STATUS_NOT_VALIDINMOD');
SELECT COUNT(*)
INTO l_dummy_ctr
FROM ORG_ACCT_PERIODS
WHERE organization_id=p_prd_workorder_rec.organization_id
AND TRUNC(p_prd_workorder_rec.scheduled_start_date) BETWEEN
TRUNC(period_start_date) AND TRUNC(NVL(schedule_CLOSE_date,SYSDATE+1));
SELECT COUNT(*) INTO l_dummy_ctr
--SELECT 1 INTO l_dummy_ctr
-- FROM MTL_PROJECT_V
FROM PJM_PROJECTS_ORG_OU_SECURE_V
WHERE project_id=p_prd_workorder_rec.PROJECT_ID
AND org_id = mo_global.get_current_org_id();
SELECT COUNT(*) INTO l_dummy_ctr
--SELECT 1 INTO l_dummy_ctr
FROM pa_tasks
WHERE project_id=p_prd_workorder_rec.project_id
AND task_id=p_prd_workorder_rec.project_task_id;
SELECT COUNT(*)
INTO l_dummy_ctr
FROM AHL_WORKORDERS
WHERE visit_task_id=NVL(p_prd_workorder_rec.visit_task_id,0)
AND LTRIM(RTRIM(status_code)) NOT IN ( G_JOB_STATUS_CANCELLED, G_JOB_STATUS_DELETED );
SELECT COUNT(*)
INTO l_dummy_ctr
FROM AHL_VISIT_TASKS_B
WHERE visit_task_id=p_prd_workorder_rec.visit_task_id;
SELECT COUNT(*)
INTO l_dummy_ctr
FROM MTL_ITEM_LOCATIONS
WHERE inventory_location_id=p_prd_workorder_rec.completion_locator_id;
SELECT COUNT(*)
INTO l_dummy_ctr
FROM AHL_MR_ROUTES_V -- Chnaged from AHL_MR_ROUTES to be Application Usage complaint.
WHERE mr_route_id=p_prd_workorder_rec.mr_route_id;
SELECT COUNT(a.instance_id)
INTO l_dummy_ctr
FROM CSI_ITEM_INSTANCES a,
MTL_SYSTEM_ITEMS b
WHERE a.instance_id=p_prd_workorder_rec.item_instance_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date,SYSDATE+1))
AND a.inventory_item_id=b.inventory_item_id;
SELECT DISTINCT MAP.department_id, DEPT.description
FROM BOM_DEPARTMENT_RESOURCES BD, AHL_RESOURCE_MAPPINGS MAP,
BOM_RESOURCES BR, BOM_DEPARTMENTS DEPT
WHERE BD.resource_id = MAP.bom_resource_id
AND BR.resource_id = BD.resource_id
AND BR.organization_id = p_org_id
AND MAP.aso_resource_id = p_aso_resource_id
AND MAP.BOM_org_id = p_org_id
AND MAP.department_id = BD.department_id
AND BD.department_id = dept.department_id;
SELECT AR.rt_oper_resource_id,
AR.aso_resource_id,
ART.name
FROM AHL_RT_OPER_RESOURCES AR, AHL_RESOURCES ART
WHERE AR.aso_resource_id = ART.resource_id
AND AR.association_type_code=p_association_type
AND AR.object_id=p_object_id;
SELECT AR.rt_oper_resource_id,
AR.aso_resource_id,
(AR.duration * AR.quantity ) duration, --Modified by Srini for Costing ER
AR.quantity,
AR.activity_id,
AR.cost_basis_id,
AR.scheduled_type_id,
AR.autocharge_type_id,
AR.standard_rate_flag,
AR.schedule_seq
FROM AHL_RT_OPER_RESOURCES AR
WHERE AR.association_type_code='OPERATION'
AND AR.object_id=c_operation_id;
SELECT aso_resource_id
FROM AHL_ALTERNATE_RESOURCES
WHERE rt_oper_resource_id=c_rt_oper_resource_id
ORDER BY priority;
SELECT BR.resource_id,
BR.resource_code,
BR.resource_type,
BR.description,
BR.unit_of_measure
FROM BOM_DEPARTMENT_RESOURCES BDR,
BOM_RESOURCES BR,
AHL_RESOURCE_MAPPINGS MAP
WHERE BDR.department_id=c_dept_id
AND BDR.resource_id=BR.resource_id
AND BR.organization_id=c_org_id
AND BR.resource_id=MAP.bom_resource_id
AND MAP.aso_resource_id=c_aso_resource_id;
SELECT
nvl(vtsk.quantity, 1)
FROM
ahl_visit_tasks_b vtsk,
ahl_workorders awo
WHERE
vtsk.visit_task_id = awo.visit_task_id AND
awo.workorder_id = p_workorder_id;
SELECT AR.rt_oper_resource_id,
AR.aso_resource_id,
(AR.duration * AR.quantity ) duration,
AR.quantity,
AR.activity_id,
AR.cost_basis_id,
AR.scheduled_type_id,
AR.autocharge_type_id,
AR.standard_rate_flag,
AR.schedule_seq
FROM AHL_RT_OPER_RESOURCES AR
WHERE AR.association_type_code=c_association_type
AND AR.object_id=c_object_id;
SELECT aso_resource_id
FROM AHL_ALTERNATE_RESOURCES
WHERE rt_oper_resource_id=c_rt_oper_resource_id
ORDER BY priority;
SELECT BR.resource_id,
BR.resource_code,
BR.resource_type,
BR.description,
BR.unit_of_measure
FROM BOM_DEPARTMENT_RESOURCES BDR,
BOM_RESOURCES BR,
AHL_RESOURCE_MAPPINGS MAP
WHERE BDR.department_id=c_dept_id
AND BDR.resource_id=BR.resource_id
AND BR.organization_id=c_org_id
AND BR.resource_id=MAP.bom_resource_id
AND MAP.aso_resource_id=c_aso_resource_id;
SELECT
nvl(vtsk.quantity, 1)
FROM
ahl_visit_tasks_b vtsk,
ahl_workorders awo
WHERE
vtsk.visit_task_id = awo.visit_task_id AND
awo.workorder_id = p_workorder_id;
SELECT MAT.rt_oper_material_id,
MAT.inventory_item_id,
MAT.item_group_id,
MAT.quantity,
MAT.uom_code,
-- Bug # 6377990 - start
MAT.in_service
-- Bug # 6377990 - end
FROM AHL_RT_OPER_MATERIALS MAT
WHERE MAT.association_type_code='OPERATION'
AND MAT.object_id=c_operation_id;
SELECT inventory_item_id
FROM AHL_ITEM_ASSOCIATIONS_B
WHERE item_group_id=c_item_group_id
ORDER BY priority;
SELECT 'X'
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id=c_inventory_item_id
AND organization_id=c_org_id;
SELECT MAT.rt_oper_material_id,
MAT.inventory_item_id,
MAT.item_group_id,
MAT.quantity,
MAT.uom_code
FROM AHL_RT_OPER_MATERIALS MAT
WHERE MAT.association_type_code=c_association_type
AND MAT.object_id=c_object_id;
SELECT inventory_item_id
FROM AHL_ITEM_ASSOCIATIONS_B
WHERE item_group_id=c_item_group_id
ORDER BY priority;
SELECT 'X'
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id=c_inventory_item_id
AND organization_id=c_org_id;
SELECT qa_inspection_type
--FROM AHL_ROUTES_V --Changed from AHL_ROUTES_B for Application Usage Complaince.
FROM AHL_ROUTES_APP_V --Changed from AHL_ROUTES_V for perf bug# 4949394.
WHERE route_id=c_route_id;
SELECT ACCOUNTING_CLASS_CODE
FROM AHL_ROUTES_APP_V
WHERE route_id=c_route_id;
select 'x'
from WIP_ACCOUNTING_CLASSES
where class_code = c_wip_acc_class_code
and organization_id = c_organization_id
and class_type = 6;
SELECT project_number
--FROM MTL_PROJECT_V
FROM PJM_PROJECTS_ORG_OU_SECURE_V
WHERE project_id = c_project_id;
SELECT VISIT_NAME
FROM AHL_VISITS_TL
WHERE VISIT_ID = c_visit_id;
SELECT
CSIA.summary, -- sr summary
CSIT.name -- sr type attribute
FROM
AHL_VISIT_TASKS_B VTSK,
AHL_UNIT_EFFECTIVITIES_B UE,
CS_INCIDENTS_ALL CSIA,
CS_INCIDENT_TYPES_VL CSIT
WHERE
VTSK.visit_task_id = p_visit_task_id AND
UE.Unit_effectivity_id = VTSK.unit_effectivity_id AND
UE.manually_planned_flag = 'Y' AND
UE.cs_incident_id IS NOT NULL AND
NOT EXISTS (SELECT
'X'
FROM
AHL_UE_RELATIONSHIPS UER
WHERE
UER.related_ue_id = UE.Unit_effectivity_id OR
UER.ue_id = UE.Unit_effectivity_id) AND
CSIA.incident_id = UE.cs_incident_id AND
CSIT.incident_type_id = CSIA.incident_type_id;
SELECT AHL_WORKORDERS_S.NEXTVAL
INTO p_x_prd_workorder_rec.WORKORDER_ID
FROM DUAL;
SELECT work_order_prefix,
default_eam_class
INTO p_x_prd_workorder_rec.JOB_NUMBER,
l_acc_class_code
FROM WIP_EAM_PARAMETERS
WHERE ORGANIZATION_ID=p_x_prd_workorder_rec.ORGANIZATION_ID;
SELECT p_x_prd_workorder_rec.JOB_NUMBER||TO_CHAR(AHL_WORKORDER_JOB_S.NEXTVAL)
INTO p_x_prd_workorder_rec.JOB_NUMBER
FROM DUAL;
p_x_prd_workorder_rec.LAST_UPDATE_DATE :=SYSDATE;
p_x_prd_workorder_rec.LAST_UPDATED_BY :=FND_GLOBAL.user_id;
p_x_prd_workorder_rec.LAST_UPDATE_LOGIN :=FND_GLOBAL.user_id;
SELECT RO.operation_id,
RO.step,
OP.concatenated_segments,
OP.operation_type_code,
OP.description
FROM AHL_OPERATIONS_VL OP,
AHL_ROUTE_OPERATIONS RO
WHERE OP.operation_id=RO.operation_id
-- AND OP.revision_status_code='COMPLETE'
AND RO.route_id=c_route_id
AND OP.revision_number IN
( SELECT MAX(OP1.revision_number)
FROM AHL_OPERATIONS_B_KFV OP1
WHERE OP1.concatenated_segments=OP.concatenated_segments
AND OP1.revision_status_code='COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
)
ORDER BY RO.step;
CURSOR c_can_update_quantity(p_task_id NUMBER)
IS
SELECT
'X'
FROM
ahl_visit_tasks_b vtsk
WHERE
vtsk.quantity IS NULL AND
vtsk.status_code <> 'DELETED' AND
vtsk.visit_task_id = p_task_id
UNION
SELECT
'X'
FROM
ahl_visit_tasks_b vtsk,
ahl_unit_effectivities_b aue
WHERE
nvl(aue.manually_planned_flag, 'N') = 'N' AND
vtsk.unit_effectivity_id = aue.unit_effectivity_id AND
vtsk.status_code <> 'DELETED' AND
vtsk.visit_task_id = p_task_id;
SELECT
csi.quantity
FROM
csi_item_instances csi,
ahl_visit_tasks_b vtsk
WHERE
vtsk.instance_id = csi.instance_id AND
vtsk.status_code <> 'DELETED' AND
vtsk.visit_task_id = p_task_id;
l_can_update_quantity VARCHAR2(1);
AHL_DEBUG_PUB.debug( l_api_name || ' - Before Insert into AHL_WORKORDERS' );
OPEN c_can_update_quantity(p_x_prd_workorder_rec.visit_task_id);
FETCH c_can_update_quantity INTO l_can_update_quantity;
CLOSE c_can_update_quantity;
IF l_can_update_quantity IS NOT NULL
THEN
OPEN c_get_instance_quantity(p_x_prd_workorder_rec.visit_task_id);
UPDATE
ahl_visit_tasks_b
SET
quantity = l_instance_quantity
WHERE
visit_task_id = p_x_prd_workorder_rec.visit_task_id;
INSERT INTO AHL_WORKORDERS
(
WORKORDER_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
WORKORDER_NAME,
WIP_ENTITY_ID,
VISIT_ID,
VISIT_TASK_ID,
STATUS_CODE,
PLAN_ID,
COLLECTION_ID,
ROUTE_ID,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
CONFIRM_FAILURE_FLAG,
MASTER_WORKORDER_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) VALUES
(
p_x_prd_workorder_rec.WORKORDER_ID,
p_x_prd_workorder_rec.OBJECT_VERSION_NUMBER,
p_x_prd_workorder_rec.LAST_UPDATE_DATE,
p_x_prd_workorder_rec.LAST_UPDATED_BY,
p_x_prd_workorder_rec.CREATION_DATE,
p_x_prd_workorder_rec.CREATED_BY,
p_x_prd_workorder_rec.LAST_UPDATE_LOGIN,
p_x_prd_workorder_rec.JOB_NUMBER,
p_x_prd_workorder_rec.WIP_ENTITY_ID,
p_x_prd_workorder_rec.VISIT_ID,
p_x_prd_workorder_rec.VISIT_TASK_ID,
p_x_prd_workorder_rec.STATUS_CODE,
p_x_prd_workorder_rec.PLAN_ID,
p_x_prd_workorder_rec.COLLECTION_ID,
p_x_prd_workorder_rec.ROUTE_ID,
p_x_prd_workorder_rec.ACTUAL_START_DATE,
p_x_prd_workorder_rec.ACTUAL_END_DATE,
p_x_prd_workorder_rec.CONFIRM_FAILURE_FLAG,
p_x_prd_workorder_rec.MASTER_WORKORDER_FLAG,
p_x_prd_workorder_rec.ATTRIBUTE_CATEGORY,
p_x_prd_workorder_rec.ATTRIBUTE1,
p_x_prd_workorder_rec.ATTRIBUTE2,
p_x_prd_workorder_rec.ATTRIBUTE3,
p_x_prd_workorder_rec.ATTRIBUTE4,
p_x_prd_workorder_rec.ATTRIBUTE5,
p_x_prd_workorder_rec.ATTRIBUTE6,
p_x_prd_workorder_rec.ATTRIBUTE7,
p_x_prd_workorder_rec.ATTRIBUTE8,
p_x_prd_workorder_rec.ATTRIBUTE9,
p_x_prd_workorder_rec.ATTRIBUTE10,
p_x_prd_workorder_rec.ATTRIBUTE11,
p_x_prd_workorder_rec.ATTRIBUTE12,
p_x_prd_workorder_rec.ATTRIBUTE13,
p_x_prd_workorder_rec.ATTRIBUTE14,
p_x_prd_workorder_rec.ATTRIBUTE15
);
AHL_DEBUG_PUB.debug( l_api_name || ' - Before Insert into AHL_WORKORDER_TXNS' );
INSERT INTO AHL_WORKORDER_TXNS
(
WORKORDER_TXN_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
WORKORDER_ID,
TRANSACTION_TYPE_CODE,
STATUS_CODE,
SCHEDULED_START_DATE,
SCHEDULED_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
LOT_NUMBER,
COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) VALUES
(
AHL_WORKORDER_TXNS_S.NEXTVAL,
p_x_prd_workorder_rec.OBJECT_VERSION_NUMBER,
p_x_prd_workorder_rec.LAST_UPDATE_DATE,
p_x_prd_workorder_rec.LAST_UPDATED_BY,
p_x_prd_workorder_rec.CREATION_DATE,
p_x_prd_workorder_rec.CREATED_BY,
p_x_prd_workorder_rec.LAST_UPDATE_LOGIN,
p_x_prd_workorder_rec.WORKORDER_ID,
0, -- check this transaction type code
p_x_prd_workorder_rec.STATUS_CODE,
p_x_prd_workorder_rec.SCHEDULED_START_DATE,
p_x_prd_workorder_rec.SCHEDULED_END_DATE,
p_x_prd_workorder_rec.ACTUAL_START_DATE,
p_x_prd_workorder_rec.ACTUAL_END_DATE,
NULL,
p_x_prd_workorder_rec.COMPLETION_SUBINVENTORY,
p_x_prd_workorder_rec.COMPLETION_LOCATOR_ID,
p_x_prd_workorder_rec.SECURITY_GROUP_ID,
p_x_prd_workorder_rec.ATTRIBUTE_CATEGORY,
p_x_prd_workorder_rec.ATTRIBUTE1,
p_x_prd_workorder_rec.ATTRIBUTE2,
p_x_prd_workorder_rec.ATTRIBUTE3,
p_x_prd_workorder_rec.ATTRIBUTE4,
p_x_prd_workorder_rec.ATTRIBUTE5,
p_x_prd_workorder_rec.ATTRIBUTE6,
p_x_prd_workorder_rec.ATTRIBUTE7,
p_x_prd_workorder_rec.ATTRIBUTE8,
p_x_prd_workorder_rec.ATTRIBUTE9,
p_x_prd_workorder_rec.ATTRIBUTE10,
p_x_prd_workorder_rec.ATTRIBUTE11,
p_x_prd_workorder_rec.ATTRIBUTE12,
p_x_prd_workorder_rec.ATTRIBUTE13,
p_x_prd_workorder_rec.ATTRIBUTE14,
p_x_prd_workorder_rec.ATTRIBUTE15
);
AHL_DEBUG_PUB.debug( l_api_name || ' - Before Update AHL_WORKORDERS with wip_entity_id' );
UPDATE AHL_WORKORDERS
SET wip_entity_id = p_x_prd_workorder_rec.wip_entity_id
WHERE workorder_id = p_x_prd_workorder_rec.workorder_id;
PROCEDURE update_job
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_default IN VARCHAR2 := FND_API.G_FALSE,
p_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_wip_load_flag IN VARCHAR2 := 'Y',
p_x_prd_workorder_rec IN OUT NOCOPY prd_workorder_rec,
p_x_prd_workoper_tbl IN OUT NOCOPY prd_workoper_tbl
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_JOB'; -- adithya::Corrected the variable precision
l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_PRD_WORKORDER_PVT.UPDATE_JOB';
SELECT WO.workorder_id,
WO.object_version_number,
WO.wip_entity_id,
WO.visit_task_id,
WO.status_code,
WIPJ.scheduled_start_date,
WIPJ.scheduled_completion_date scheduled_end_date,
WO.actual_start_date,
WO.actual_end_date
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS WOR,
WIP_DISCRETE_JOBS wipj
WHERE
WIPJ.wip_entity_id = WO.wip_entity_id
AND WO.wip_entity_id = WOR.parent_object_id
AND WO.master_workorder_flag = 'Y'
AND WO.status_code <> G_JOB_STATUS_DELETED
AND WOR.parent_object_type_id = 1
AND WOR.relationship_type = 1
AND WOR.child_object_type_id = 1
AND WOR.child_object_id = c_child_wip_entity_id;
SELECT WDJ.scheduled_start_date scheduled_start_date,
WDJ.scheduled_completion_date scheduled_end_date,
WO.actual_start_date actual_start_date,
WO.actual_end_date actual_end_date,
WO.status_code status_code
FROM WIP_DISCRETE_JOBS WDJ,
AHL_WORKORDERS WO
WHERE WDJ.wip_entity_id = WO.wip_entity_id
AND WO.status_code <> G_JOB_STATUS_DELETED
AND WO.wip_entity_id in
(
SELECT child_object_id
FROM WIP_SCHED_RELATIONSHIPS
WHERE parent_object_type_id = 1
AND child_object_type_id = 1
START WITH parent_object_id = c_wip_entity_id
AND relationship_type = 1
CONNECT BY parent_object_id = PRIOR child_object_id
AND relationship_type = 1
);
SELECT AWOS.status_code,
AWOS.workorder_name,
FNDL.meaning
FROM AHL_WORKORDERS AWOS,
FND_LOOKUP_VALUES_VL FNDL
WHERE AWOS.WORKORDER_ID = c_workorder_id
AND FNDL.lookup_type = 'AHL_JOB_STATUS'
AND FNDL.lookup_code(+) = AWOS.status_code;
SELECT scheduled_material_id
FROM ahl_job_oper_materials_v
WHERE workorder_id = p_workorder_id
AND reserved_quantity > 0;
SELECT employee_id, operation_seq_num, resource_seq_num
FROM ahl_work_login_times
WHERE workorder_id = p_workorder_id
AND LOGOUT_DATE IS NULL;
SELECT 'x'
FROM CSI_ITEM_INSTANCES CII, AHL_WORKORDERS AWO
WHERE CII.WIP_JOB_ID = AWO.WIP_ENTITY_ID
AND AWO.workorder_id = p_workorder_id
AND ACTIVE_START_DATE <= SYSDATE
AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
AND LOCATION_TYPE_CODE = 'WIP'
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE));
SELECT
visit_task_id
FROM
AHL_WORKORDERS
WHERE
workorder_id = p_wo_id;
CURSOR c_can_update_quantity(p_task_id NUMBER)
IS
SELECT
'X'
FROM
ahl_visit_tasks_b vtsk
WHERE
vtsk.quantity IS NULL AND
vtsk.status_code <> 'DELETED' AND
vtsk.visit_task_id = p_task_id
UNION
SELECT
'X'
FROM
ahl_visit_tasks_b vtsk,
ahl_unit_effectivities_b aue
WHERE
nvl(aue.manually_planned_flag, 'N') = 'N' AND
vtsk.unit_effectivity_id = aue.unit_effectivity_id AND
vtsk.status_code <> 'DELETED' AND
vtsk.visit_task_id = p_task_id;
SELECT
csi.quantity
FROM
csi_item_instances csi,
ahl_visit_tasks_b vtsk
WHERE
vtsk.instance_id = csi.instance_id AND
vtsk.status_code <> 'DELETED' AND
vtsk.visit_task_id = p_task_id;
l_can_update_quantity VARCHAR2(1);
SELECT
status_code
FROM
ahl_workorders
WHERE
workorder_id = p_workorder_id;
SELECT OP.operation_id
FROM AHL_OPERATIONS_VL OP
WHERE OP.concatenated_segments=p_operation_code
AND OP.revision_number IN
( SELECT MAX(OP1.revision_number)
FROM AHL_OPERATIONS_B_KFV OP1
WHERE OP1.concatenated_segments=OP.concatenated_segments
AND TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
AND OP1.revision_status_code='COMPLETE'
);
SAVEPOINT update_job_PVT;
IF (JTF_USR_HKS.Ok_to_execute('AHL_PRD_WORKORDER_PVT', 'UPDATE_JOB', 'B', 'C' )) THEN
ahl_prd_workorder_CUHK.update_job_pre(
p_prd_workorder_rec => p_x_prd_workorder_rec,
p_prd_workoper_tbl => p_x_prd_workoper_tbl,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND LOOKUP_CODE = p_x_prd_workorder_rec.status_code;
OPEN c_can_update_quantity(p_x_prd_workorder_rec.visit_task_id);
FETCH c_can_update_quantity INTO l_can_update_quantity;
CLOSE c_can_update_quantity;
IF l_can_update_quantity IS NOT NULL
THEN
OPEN c_get_instance_quantity(p_x_prd_workorder_rec.visit_task_id);
UPDATE
ahl_visit_tasks_b
SET
quantity = l_instance_quantity
WHERE
visit_task_id = p_x_prd_workorder_rec.visit_task_id;
UPDATE AHL_WORKORDERS SET
OBJECT_VERSION_NUMBER =p_x_prd_workorder_rec.OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE =NVL(p_x_prd_workorder_rec.LAST_UPDATE_DATE,SYSDATE),
LAST_UPDATED_BY =NVL(p_x_prd_workorder_rec.LAST_UPDATED_BY,FND_GLOBAL.user_id),
LAST_UPDATE_LOGIN =NVL(p_x_prd_workorder_rec.LAST_UPDATE_LOGIN,FND_GLOBAL.user_id),
STATUS_CODE =p_x_prd_workorder_rec.STATUS_CODE,
ACTUAL_START_DATE =p_x_prd_workorder_rec.ACTUAL_START_DATE,
ACTUAL_END_DATE =p_x_prd_workorder_rec.ACTUAL_END_DATE,
CONFIRM_FAILURE_FLAG =p_x_prd_workorder_rec.CONFIRM_FAILURE_FLAG,
SECURITY_GROUP_ID =p_x_prd_workorder_rec.SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY =p_x_prd_workorder_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 =p_x_prd_workorder_rec.ATTRIBUTE1,
ATTRIBUTE2 =p_x_prd_workorder_rec.ATTRIBUTE2,
ATTRIBUTE3 =p_x_prd_workorder_rec.ATTRIBUTE3,
ATTRIBUTE4 =p_x_prd_workorder_rec.ATTRIBUTE4,
ATTRIBUTE5 =p_x_prd_workorder_rec.ATTRIBUTE5,
ATTRIBUTE6 =p_x_prd_workorder_rec.ATTRIBUTE6,
ATTRIBUTE7 =p_x_prd_workorder_rec.ATTRIBUTE7,
ATTRIBUTE8 =p_x_prd_workorder_rec.ATTRIBUTE8,
ATTRIBUTE9 =p_x_prd_workorder_rec.ATTRIBUTE9,
ATTRIBUTE10 =p_x_prd_workorder_rec.ATTRIBUTE10,
ATTRIBUTE11 =p_x_prd_workorder_rec.ATTRIBUTE11,
ATTRIBUTE12 =p_x_prd_workorder_rec.ATTRIBUTE12,
ATTRIBUTE13 =p_x_prd_workorder_rec.ATTRIBUTE13,
ATTRIBUTE14 =p_x_prd_workorder_rec.ATTRIBUTE14,
ATTRIBUTE15 =p_x_prd_workorder_rec.ATTRIBUTE15,
HOLD_REASON_CODE =p_x_prd_workorder_rec.HOLD_REASON_CODE
WHERE WORKORDER_ID=p_x_prd_workorder_rec.WORKORDER_ID
AND OBJECT_VERSION_NUMBER=p_x_prd_workorder_rec.OBJECT_VERSION_NUMBER;
AHL_DEBUG_PUB.debug( l_api_name || ' - Before Inserting into AHL_WORKORDER_TXNS' );
INSERT INTO AHL_WORKORDER_TXNS
(
WORKORDER_TXN_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
WORKORDER_ID,
TRANSACTION_TYPE_CODE,
STATUS_CODE,
SCHEDULED_START_DATE,
SCHEDULED_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
LOT_NUMBER,
COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID,
HOLD_REASON_CODE
) VALUES
(
AHL_WORKORDER_TXNS_S.NEXTVAL,
NVL(p_x_prd_workorder_rec.OBJECT_VERSION_NUMBER,1),
NVL(p_x_prd_workorder_rec.LAST_UPDATE_DATE,SYSDATE),
NVL(p_x_prd_workorder_rec.LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
NVL(p_x_prd_workorder_rec.CREATION_DATE,SYSDATE),
NVL(p_x_prd_workorder_rec.CREATED_BY,FND_GLOBAL.USER_ID),
NVL(p_x_prd_workorder_rec.LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID),
p_x_prd_workorder_rec.WORKORDER_ID,
0,
p_x_prd_workorder_rec.STATUS_CODE,
p_x_prd_workorder_rec.SCHEDULED_START_DATE,
p_x_prd_workorder_rec.SCHEDULED_END_DATE,
p_x_prd_workorder_rec.ACTUAL_START_DATE,
p_x_prd_workorder_rec.ACTUAL_END_DATE,
0,
p_x_prd_workorder_rec.COMPLETION_SUBINVENTORY,
p_x_prd_workorder_rec.COMPLETION_LOCATOR_ID,
p_x_prd_workorder_rec.HOLD_REASON_CODE
);
l_prd_workoper_tbl(i).LAST_UPDATE_DATE :=p_x_prd_workoper_tbl(i).LAST_UPDATE_DATE;
l_prd_workoper_tbl(i).LAST_UPDATED_BY :=p_x_prd_workoper_tbl(i).LAST_UPDATED_BY;
l_prd_workoper_tbl(i).LAST_UPDATE_LOGIN :=p_x_prd_workoper_tbl(i).LAST_UPDATE_LOGIN;
p_x_prd_workorder_rec.status_code = G_JOB_STATUS_DELETED ) AND
( parent_csr.status_code = G_JOB_STATUS_UNRELEASED OR
parent_csr.status_code = G_JOB_STATUS_DRAFT ) ) THEN
l_parent_workorder_rec.status_code := G_JOB_STATUS_RELEASED;
p_x_prd_workorder_rec.status_code = G_JOB_STATUS_DELETED ) THEN
l_parent_workorder_rec.status_code := G_JOB_STATUS_RELEASED;
* Since master workorder cannot be updated to cancelled status
* when child workorder are not already cancelled. The recursive logic
* in this API updates parent workorders first and then child workorders.
* Cancelling parent workorders will be taken care by Cancel_Visit_Jobs API.
*
ELSIF l_status_code <> G_JOB_STATUS_CLOSED THEN
l_parent_workorder_rec.status_code := l_status_code;
AHL_DEBUG_PUB.debug( l_api_name || ' - Before update_job for parent workorder' );
update_job
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_TRUE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
p_default => FND_API.G_TRUE ,
p_module_type => NULL ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_wip_load_flag => p_wip_load_flag ,
p_x_prd_workorder_rec => l_parent_workorder_rec ,
p_x_prd_workoper_tbl => l_parent_workoper_tbl
);
AHL_DEBUG_PUB.debug( l_api_name || ' - update_job for parent_workorder successful' );
AHL_DEBUG_PUB.debug( l_api_name || ' - Before AHL_EAM_JOB_PVT.update_job_operations' );
AHL_EAM_JOB_PVT.update_job_operations
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_TRUE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
p_default => FND_API.G_TRUE ,
p_module_type => NULL ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_workorder_rec => p_x_prd_workorder_rec ,
p_operation_tbl => l_prd_workoper_tbl ,
p_material_req_tbl => l_material_tbl ,
p_resource_req_tbl => l_resource_tbl
);
AHL_DEBUG_PUB.debug( l_api_name || ' - AHL_EAM_JOB_PVT.update_job_operations succesful' );
G_JOB_STATUS_CANCELLED, G_JOB_STATUS_DELETED)) THEN
IF ( G_DEBUG = 'Y' ) THEN
AHL_DEBUG_PUB.debug( l_api_name || ' Before - AHL_RSV_RESERVATIONS_PVT.Delete_Reservation');
AHL_RSV_RESERVATIONS_PVT.Delete_Reservation (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
p_module_type => NULL,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_scheduled_material_id => get_scheduled_mater_rec.scheduled_material_id);
AHL_DEBUG_PUB.debug('Delete_Reservation failed for schedule material ID: '
|| get_scheduled_mater_rec.scheduled_material_id);
'Before calling AHL_LTP_REQST_MATRL_PVT.Update_Material_Reqrs_status'
);
-- Call LTP API to update material requirement status to History.
AHL_LTP_REQST_MATRL_PVT.Update_Material_Reqrs_status(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_module_type => NULL,
p_visit_task_id => p_x_prd_workorder_rec.VISIT_TASK_ID,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'return status after call to Update_Material_Reqrs_status -> '|| x_return_status
);
IF (JTF_USR_HKS.Ok_to_execute('AHL_PRD_WORKORDER_PVT', 'UPDATE_JOB', 'A', 'C' )) THEN
ahl_prd_workorder_CUHK.update_job_post(
p_prd_workorder_rec => p_x_prd_workorder_rec,
p_prd_workoper_tbl => p_x_prd_workoper_tbl,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
ROLLBACK TO update_job_PVT;
ROLLBACK TO update_job_PVT;
ROLLBACK TO update_job_PVT;
END update_job;
l_operation_tbl.DELETE;
l_resource_tbl.DELETE;
l_material_tbl.DELETE;
AHL_DEBUG_PUB.debug( 'Invoking update_job API for Workorder ' || i );
update_job
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_default => FND_API.G_FALSE,
p_module_type => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_wip_load_flag => 'N',
p_x_prd_workorder_rec => p_x_prd_workorder_tbl(i),
p_x_prd_workoper_tbl => l_dummy_op_tbl
);
AHL_DEBUG_PUB.debug( 'update_job API Success' );
UPDATE AHL_WORKORDERS
SET wip_entity_id = l_eam_wo_tbl(i).wip_entity_id,
object_version_number = p_x_prd_workorder_tbl(i).object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE workorder_id = p_x_prd_workorder_tbl(i).workorder_id;
UPDATE AHL_WORKORDERS
SET status_code = l_eam_wo_tbl(i).status_type,
object_version_number = p_x_prd_workorder_tbl(i).object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE workorder_id = p_x_prd_workorder_tbl(i).workorder_id;
SELECT workorder_id,
object_version_number,
status_code,
wip_entity_id,
workorder_name
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND status_code <> G_JOB_STATUS_DELETED
AND visit_task_id IS NULL;
SELECT WO.workorder_id workorder_id,
WO.object_version_number object_version_number,
WO.wip_entity_id wip_entity_id,
WO.status_code status_code,
WO.master_workorder_flag,
WO.workorder_name
FROM AHL_WORKORDERS WO
WHERE wip_entity_id = c_wip_entity_id
AND STATUS_CODE <> G_JOB_STATUS_DELETED;
SELECT REL.child_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.parent_object_id = c_wip_entity_id
AND REL.relationship_type = 1
CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
AND REL.relationship_type = 1
ORDER BY level;
SELECT REL.parent_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.child_object_id = c_wip_entity_id
AND REL.relationship_type = 1
CONNECT BY REL.child_object_id = PRIOR REL.parent_object_id
AND REL.relationship_type = 1
ORDER BY level DESC;
SELECT WO.workorder_id workorder_id,
WO.object_version_number object_version_number,
WO.status_code status_code,
WO.wip_entity_id wip_entity_id,
WO.workorder_name workorder_name,
WO.master_workorder_flag master_workorder_flag
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VT
WHERE WO.status_code <> G_JOB_STATUS_DELETED
AND WO.visit_task_id = VT.visit_task_id
AND VT.task_type_code IN ( 'SUMMARY', 'UNASSOCIATED' )
AND VT.unit_effectivity_id = c_unit_effectivity_id;
SELECT workorder_id,
object_version_number,
status_code,
wip_entity_id,
workorder_name
FROM AHL_WORKORDERS
WHERE workorder_id =p_workorder_id;
SELECT workorder_name,
wip_entity_id
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND status_code NOT IN ( G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_DELETED );
SELECT WO.workorder_name workorder_name
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS REL
WHERE WO.wip_entity_id = REL.parent_object_id
AND WO.visit_id <> c_visit_id
AND WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_DELETED )
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
AND REL.child_object_id = c_wip_entity_id
AND REL.relationship_type = 2;
SELECT WO.workorder_name workorder_name,
WO.wip_entity_id wip_entity_id
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VT
WHERE WO.visit_task_id = VT.visit_task_id
AND WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_DELETED )
AND VT.task_type_code IN ( 'SUMMARY', 'UNASSOCIATED' )
AND VT.unit_effectivity_id = c_unit_effectivity_id;
SELECT WO.wip_entity_id wip_entity_id,
WO.workorder_name workorder_name
FROM AHL_WORKORDERS WO
WHERE WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_DELETED )
AND WO.wip_entity_id IN (SELECT REL.child_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.parent_object_id = c_wip_entity_id
AND REL.relationship_type = 1
CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
AND REL.relationship_type = 1);
SELECT WO.workorder_name workorder_name,
WO.wip_entity_id wip_entity_id
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS REL
WHERE WO.wip_entity_id = REL.parent_object_id
AND WO.status_code NOT IN ( G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_DELETED )
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
AND REL.child_object_id = c_wip_entity_id
AND REL.relationship_type = 2;
SELECT workorder_name,
wip_entity_id
FROM AHL_WORKORDERS
WHERE workorder_id =p_workorder_id
AND status_code NOT IN ( G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_DELETED );
-- but now all child dependencies are being auto-deleted
-- Note: At present this API is being used by
-- cancel_visit_jobs API alone
-- If this API is called from any other API, then the below validations
-- may need to be added accordingly
/*
FOR dep_csr IN get_wo_dependencies( l_wip_entity_id ) LOOP
FOR j IN l_child_wo_tbl.FIRST..l_child_wo_tbl.LAST LOOP
IF ( dep_csr.wip_entity_id = l_child_wo_tbl(j).wip_entity_id ) THEN
l_match_found := TRUE;
-- but now all child dependencies are being auto-deleted
-- Note: At present this API is being used by
-- cancel_visit_jobs API alone
-- If this API is called from any other API, then the below validations
-- may need to be added accordingly
/*
IF l_child_wo_tbl.COUNT > 0 THEN
--
FOR i IN l_child_wo_tbl.FIRST..l_child_wo_tbl.LAST LOOP
FOR dep_csr IN get_wo_dependencies( l_child_wo_tbl(i).wip_entity_id ) LOOP
FOR j IN l_child_wo_tbl.FIRST..l_child_wo_tbl.LAST LOOP
IF ( dep_csr.wip_entity_id = l_child_wo_tbl(j).wip_entity_id ) THEN
l_match_found := TRUE;
-- completion dependencies are deleted automatically.
-- Note: At present this API is being used by
-- cancel_visit_jobs API alone
-- If this API is called from any other API, then the below validations
-- may need to be added accordingly
/*
FOR dep_csr IN get_wo_dependencies( l_wip_entity_id ) LOOP
FND_MESSAGE.set_name('AHL','AHL_PRD_DEP_WO_NOT_CMPL');
SELECT WO.workorder_id workorder_id
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS REL
WHERE WO.status_code NOT IN ( G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_DELETED )
AND WO.wip_entity_id = REL.child_object_id
AND REL.parent_object_id = c_wip_entity_id
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
AND REL.relationship_type = 1;
SELECT workorder_id,
object_version_number,
status_code,
wip_entity_id,
workorder_name
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND status_code NOT IN (G_JOB_STATUS_DELETED,G_JOB_STATUS_CANCELLED)
AND visit_task_id IS NULL;
SELECT WO.workorder_id workorder_id,
WO.object_version_number wo_object_version_number,
WO.status_code status_code,
WO.wip_entity_id wip_entity_id,
WO.workorder_name workorder_name,
WO.master_workorder_flag master_workorder_flag
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VT
WHERE WO.status_code <> G_JOB_STATUS_DELETED
AND WO.visit_task_id = VT.visit_task_id
AND VT.task_type_code IN ( 'SUMMARY', 'UNASSOCIATED' )
AND VT.unit_effectivity_id = c_unit_effectivity_id;
SELECT WO.wip_entity_id wip_entity_id
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VT
WHERE WO.status_code <> G_JOB_STATUS_DELETED
AND WO.visit_task_id = VT.visit_task_id
AND VT.task_type_code NOT IN ('SUMMARY')
AND VT.unit_effectivity_id = c_unit_effectivity_id;
SELECT workorder_id,
object_version_number,
status_code,
wip_entity_id,
workorder_name
FROM AHL_WORKORDERS
WHERE workorder_id =p_workorder_id;
SELECT WO.workorder_id workorder_id,
WO.object_version_number object_version_number,
WO.status_code status_code,
WO.wip_entity_id wip_entity_id,
WO.master_workorder_flag,
WO.workorder_name
FROM AHL_WORKORDERS WO
WHERE WO.wip_entity_id = c_wip_entity_id;
SELECT REL.child_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.parent_object_id = c_wip_entity_id
AND REL.relationship_type = 1
CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
AND REL.relationship_type = 1
ORDER BY level DESC;
SELECT REL.parent_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.child_object_id = c_wip_entity_id
AND REL.relationship_type = 1
CONNECT BY REL.child_object_id = PRIOR REL.parent_object_id
AND REL.relationship_type = 1
ORDER BY level;
* not update the UE Status to 'CANCELLED'. Hence Balaji commented out these 4 cursors.
* Reference bug #s 4095002 and 4094884.
*/
/*
CURSOR get_visit_mrs( c_visit_id NUMBER ) IS
SELECT UE.unit_effectivity_id unit_effectivity_id,
UE.object_version_number object_version_number
FROM AHL_UNIT_EFFECTIVITIES_B UE,
AHL_VISIT_TASKS_B VT
WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
AND UE.manually_planned_flag = 'Y'
-- Check added by balaji by balaji for bug # 4095002
-- As per the update in the bug, for Manually planned UEs of type SR
-- Status should not be updated to CANCELLED on workorder or MR or Visit
-- Cancellation. Hence adding the check to filter out UEs based on SRs.
AND UE.object_type <> 'SR'
AND VT.task_type_code = 'SUMMARY'
AND VT.originating_task_id IS NULL
AND VT.unit_effectivity_id IS NOT NULL
AND VT.visit_id = c_visit_id;
SELECT UE.object_version_number object_version_number
FROM AHL_UNIT_EFFECTIVITIES_B UE
WHERE UE.unit_effectivity_id = c_unit_effectivity_id
AND UE.manually_planned_flag = 'Y'
-- Check added by balaji by balaji for bug # 4095002
-- As per the update in the bug, for Manually planned UEs of type SR
-- Status should not be updated to CANCELLED on workorder or MR or Visit
-- Cancellation. Hence adding the check to filter out UEs based on SRs.
AND UE.object_type <> 'SR';
SELECT UE.unit_effectivity_id unit_effectivity_id,
UE.object_version_number object_version_number
FROM AHL_UNIT_EFFECTIVITIES_B UE,
AHL_VISIT_TASKS_B VT,
AHL_WORKORDERS WO
WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
AND UE.manually_planned_flag = 'Y'
-- Check added by balaji by balaji for bug # 4095002
-- As per the update in the bug, for Manually planned UEs of type SR
-- Status should not be updated to CANCELLED on workorder or MR or Visit
-- Cancellation. Hence adding the check to filter out UEs based on SRs.
AND UE.object_type <> 'SR'
AND VT.visit_task_id = WO.visit_task_id
AND WO.workorder_id = c_workorder_id;
SELECT UE.unit_effectivity_id unit_effectivity_id,
UE.object_version_number object_version_number
FROM AHL_UNIT_EFFECTIVITIES_B UE,
AHL_UE_RELATIONSHIPS REL
WHERE UE.unit_effectivity_id = REL.ue_id
AND UE.manually_planned_flag = 'Y'
-- Check added by balaji by balaji for bug # 4095002
-- As per the update in the bug, for Manually planned UEs of type SR
-- Status should not be updated to CANCELLED on workorder or MR or Visit
-- Cancellation. Hence adding the check to filter out UEs based on SRs.
AND UE.object_type <> 'SR'
START WITH REL.related_ue_id = c_unit_effectivity_id
AND REL.relationship_code = 'PARENT'
CONNECT BY REL.related_ue_id = PRIOR REL.ue_id
AND REL.relationship_code = 'PARENT'
ORDER BY level;
SELECT wip_entity_id
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND master_workorder_flag <> 'Y';
SELECT count(*)
FROM WIP_SCHED_RELATIONSHIPS
WHERE child_object_id = c_wip_entity_id
AND child_object_type_id = 1
AND relationship_type = 2;
SELECT REL.sched_relationship_id,
REL.parent_object_id,
REL.child_object_id
FROM --AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS REL
WHERE --WO.wip_entity_id = REL.child_object_id
REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.parent_object_id = c_wip_entity_id
AND REL.relationship_type = 2
CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
AND REL.relationship_type = 2
ORDER BY level DESC;
SELECT REL.sched_relationship_id,
REL.parent_object_id,
REL.child_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.child_object_id = c_wip_entity_id
AND REL.relationship_type = 2
CONNECT BY REL.child_object_id = PRIOR REL.parent_object_id
AND REL.relationship_type = 2
ORDER BY level;
SELECT REL.sched_relationship_id,
REL.parent_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.child_object_id = c_wip_entity_id
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
AND REL.relationship_type = 2;
SELECT REL.sched_relationship_id,
REL.child_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.parent_object_id = c_wip_entity_id
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
AND REL.relationship_type = 2;
SELECT REL.sched_relationship_id,
REL.parent_object_id
FROM WIP_SCHED_RELATIONSHIPS REL
WHERE REL.child_object_id = c_wip_entity_id
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
AND REL.relationship_type = 2;
SELECT 'x'
FROM AHL_WORKORDERS AWO, WIP_DISCRETE_JOBS WDJ
WHERE awo.wip_entity_id = wdj.wip_entity_id
AND wdj.date_completed IS NOT NULL
--AND master_workorder_flag = 'N'
--AND status_code NOT IN ('7', '22', '12')
AND VISIT_TASK_ID IS NOT NULL
AND awo.wip_entity_id IN (SELECT rel.child_object_id
FROM wip_sched_relationships rel
START WITH REL.parent_object_id = c_wip_entity_id
CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
AND REL.parent_object_type_id = PRIOR REL.child_object_type_id
AND REL.relationship_type = 1);
-- need to delete all completion dependencies
-- if the l_input_type = 'VST' (whole visit is cancelled)
-- so all the completion dependencies need to be deleted
-- will use process_eam_workorders
-- 1. Find all the workorders in the visit that are not master wos
-- 2. Loop through all the workorders and find the top level wos
-- 3. get the entire hierarchy of workorders for all top level workorders
idx := 1;
l_status_code = G_JOB_STATUS_DELETED ) THEN
--Get status meaning
SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND LOOKUP_CODE = l_status_code;
l_sts_code <> G_JOB_STATUS_DELETED ) THEN
-- rroy
-- ACL Changes
l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => l_wo_id,
p_ue_id => NULL,
p_visit_id => NULL,
p_item_instance_id => NULL);
-- If the Status is Draft, then, Delete else, Cancel
IF ( l_sts_code = G_JOB_STATUS_DRAFT ) THEN
l_workorder_tbl(l_wo_count).status_code := G_JOB_STATUS_DELETED;
-- If the Status is Draft, then, Delete else, Cancel
IF ( l_sts_code = G_JOB_STATUS_DRAFT ) THEN
l_master_workorder_tbl(l_mwo_count).status_code := G_JOB_STATUS_DELETED;
l_master_workorder_tbl(l_mwo_count).status_code := G_JOB_STATUS_DELETED;
l_status_code = G_JOB_STATUS_DELETED ) THEN
--Get status meaning
SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND LOOKUP_CODE = l_status_code;
l_sts_code <> G_JOB_STATUS_DELETED ) THEN
-- rroy
-- ACL Changes
l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => l_wo_id,
p_ue_id => NULL,
p_visit_id => NULL,
p_item_instance_id => NULL);
-- If the Status is Draft, then, Delete else, Cancel
IF ( l_sts_code = G_JOB_STATUS_DRAFT ) THEN
l_workorder_tbl(l_wo_count).status_code := G_JOB_STATUS_DELETED;
l_master_workorder_tbl(l_mwo_count).status_code := G_JOB_STATUS_DELETED;
l_master_workorder_tbl(l_mwo_count).status_code := G_JOB_STATUS_DELETED;
l_sts_code <> G_JOB_STATUS_DELETED ) THEN
-- Parent WO can be cancelled only if all the children are cancelled
IF ( are_child_wos_cancelled( parent_csr.parent_object_id, l_workorder_tbl ) ) THEN
-- rroy
-- ACL Changes
l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => l_wo_id,
p_ue_id => NULL,
p_visit_id => NULL,
p_item_instance_id => NULL);
l_workorder_tbl(l_wo_count).status_code := G_JOB_STATUS_DELETED;
-- adding the relationships to be deleted
-- delete only those relationships which contain
-- the ue
-- 1. first, all the child dependencies of the ue mwo need to be deleted
idx := 1;
-- therefore we need to delete all its child dependencies
FOR com_dep_rec IN get_completion_dep_wo_all(ue_parent_rec.parent_object_id) LOOP
-- if the relationship id does not exist already, then
-- add it to the relationships table
l_rel_found := FALSE;
-- delete its immediate parent dependency
FOR imm_parents_rec IN get_immediate_ue_parent(ue_parent_rec.parent_object_id) LOOP
l_workorder_rel_tbl(idx).wo_relationship_id := imm_parents_rec.sched_relationship_id;
l_status_code = G_JOB_STATUS_DELETED ) THEN
--Get status meaning
SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND LOOKUP_CODE = l_status_code;
l_workorder_tbl(l_wo_count).status_code := G_JOB_STATUS_DELETED;
l_sts_code <> G_JOB_STATUS_DELETED ) THEN
-- Parent WO can be cancelled only if all the children are cancelled
IF ( are_child_wos_cancelled( parent_csr.parent_object_id, l_workorder_tbl ) ) THEN
-- rroy
-- ACL Changes
l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => l_wo_id,
p_ue_id => NULL,
p_visit_id => NULL,
p_item_instance_id => NULL);
l_workorder_tbl(l_wo_count).status_code := G_JOB_STATUS_DELETED;
-- need to delete all completion dependencies
-- if the l_input_type = 'WO'
-- so all the completion dependencies for the
-- particular workorder need to be cancelled
idx := 1;
-- delete the workorder completion dependencies
IF l_eam_wo_relations_tbl.COUNT > 0 THEN
AHL_EAM_JOB_PVT.process_eam_workorders
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_default => FND_API.G_FALSE,
p_module_type => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_x_eam_wo_tbl => l_eam_wo_tbl,
p_eam_wo_relations_tbl => l_eam_wo_relations_tbl,
p_eam_op_tbl => l_eam_op_tbl,
p_eam_res_req_tbl => l_eam_res_req_tbl,
p_eam_mat_req_tbl => l_eam_mat_req_tbl
);
* not update the UE Status to 'CANCELLED'. Hence Balaji commented following portion of code
* which updates the UE status. Reference bug #s 4095002 and 4094884.
*/
/*
IF ( NVL( p_module_type, 'X' ) <> 'DF' ) THEN
-- Process Visit
IF ( l_input_type = 'VST' ) THEN
-- Get all the Unplanned Top UEs in the Visit
FOR mr_csr IN get_visit_mrs( p_visit_id ) LOOP
l_status_code := AHL_COMPLETIONS_PVT.get_mr_status( mr_csr.unit_effectivity_id );
AHL_UMP_UNITMAINT_PVT.capture_mr_updates
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_default => FND_API.G_TRUE,
p_module_type => NULL,
p_unit_effectivity_tbl => l_unit_effectivity_tbl,
p_x_unit_threshold_tbl => l_unit_threshold_tbl,
p_x_unit_accomplish_tbl => l_unit_accomplish_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT 1
FROM AHL_VISITS_B
WHERE VISIT_ID=c_visit_id;
SELECT AWO.WORKORDER_ID,
AWO.OBJECT_VERSION_NUMBER,
WIP.WIP_ENTITY_ID,
AWO.STATUS_CODE,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE
FROM AHL_WORKORDERS AWO,
WIP_DISCRETE_JOBS WIP
WHERE AWO.VISIT_ID=c_visit_id
AND AWO.VISIT_TASK_ID IS NULL
AND AWO.MASTER_WORKORDER_FLAG = 'Y'
AND AWO.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
AND AWO.STATUS_CODE NOT IN ('22', '7');
SELECT scheduled_start_date,
scheduled_completion_date
FROM wip_discrete_jobs
WHERE wip_entity_id = c_wip_entity_id;
-- so we need to update the master workorder end date
-- with a call to update jobs
l_prd_workorder_tbl(1).DML_OPERATION := 'U';
PROCEDURE INSERT_TURNOVER_NOTES
(
p_api_version IN NUMBER := 1.0 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_default IN VARCHAR2 := FND_API.G_FALSE,
p_module_type IN VARCHAR2 := Null,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trunover_notes_tbl IN OUT NOCOPY AHL_PRD_WORKORDER_PVT.turnover_notes_tbl_type
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_TURNOVER_NOTES';
SELECT FU.user_id, PF.person_id FROM fnd_user FU,PER_PEOPLE_F PF, HR_ORGANIZATION_UNITS HOU, PER_PERSON_TYPES PEPT, BOM_RESOURCE_EMPLOYEES BRE
WHERE NVL(PF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y'
AND PEPT.PERSON_TYPE_ID = PF.PERSON_TYPE_ID
AND PEPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PF.PERSON_ID = BRE.PERSON_ID
AND (TRUNC(SYSDATE) BETWEEN PF.EFFECTIVE_START_DATE AND PF.EFFECTIVE_END_DATE)
AND HOU.BUSINESS_GROUP_ID = PF.BUSINESS_GROUP_ID
AND HOU.ORGANIZATION_ID = NVL(p_org_id,HOU.ORGANIZATION_ID)
--AND NVL(FU.employee_id,-1) = PF.person_id
AND FU.employee_id = PF.person_id -- removed NVL to avoid FTS on fnd_user.
AND UPPER(PF.FULL_NAME) like UPPER(p_emp_name);
SELECT DISTINCT bre.person_id, fu.user_id
FROM mtl_employees_current_view pf, bom_resource_employees bre, fnd_user fu
WHERE pf.employee_id=bre.person_id
and pf.organization_id = bre.organization_id
and sysdate between BRE.EFFECTIVE_START_DATE and BRE.EFFECTIVE_END_DATE
and FU.employee_id = pf.employee_id
and pf.organization_id= p_org_id
and UPPER(pf.full_name) like UPPER(p_emp_name);
'ahl.plsql.AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES.begin',
'At the start of PLSQL procedure'
);
SAVEPOINT INSERT_TURNOVER_NOTES;
'ahl.plsql.AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES',
'p_init_message_list : ' || p_init_msg_list
);
'ahl.plsql.AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES',
'p_commit : ' || p_commit
);
'ahl.plsql.AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES',
'Invalid source object code for JTF notes' || p_trunover_notes_tbl(i).source_object_code
);
'ahl.plsql.AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES',
'Invalid enterded date' || to_char(p_trunover_notes_tbl(i).entered_date)
);
'ahl.plsql.AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES',
'Invalid enterded date' || to_char(p_trunover_notes_tbl(i).entered_date)
);
'ahl.plsql.AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES.end',
'At the end of PLSQL procedure'
);
Rollback to INSERT_TURNOVER_NOTES;
Rollback to INSERT_TURNOVER_NOTES;
Rollback to INSERT_TURNOVER_NOTES;
END INSERT_TURNOVER_NOTES;