[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 maintenance_object_id
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = c_wip_entity_id;
SELECT inventory_item_id, serial_number
FROM csi_item_instances csi
WHERE csi.instance_id = p_instance_id;
AHL_DEBUG_PUB.debug('Update Workorder: Defaulting Inv Item ID for Visit MWO:' || l_prd_workorder_rec.inventory_item_id);
AHL_DEBUG_PUB.debug('Update Workorder: Defaulting Serial Num for Visit MWO:' || l_prd_workorder_rec.serial_number);
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
AND TRUNC(NVL(DISABLE_DATE,SYSDATE+1)) > TRUNC(SYSDATE);
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
AND NVL(start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(end_date_active, SYSDATE + 1) > SYSDATE
AND NVL(enabled_flag, 'Y') = 'Y';
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;
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)
AND nvl(ENABLED_FLAG,'Y') = 'Y';
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
AND TRUNC(NVL(DISABLE_DATE,SYSDATE+1)) > TRUNC(SYSDATE);
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 'x'
from WIP_ACCOUNTING_CLASSES
where class_code = c_wip_acc_class_code
and organization_id = c_organization_id
and class_type = 6
AND TRUNC(NVL(DISABLE_DATE,SYSDATE+1)) > TRUNC(SYSDATE);
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()
-- added following filter to fix bug# 8662561 (FP for 8630840)
AND inventory_organization_id = p_prd_workorder_rec.organization_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 ONE_BOMRES_ORG
from ahl_resources
where RESOURCE_ID = c_aso_resource_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 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.resource_id = BR.resource_id
AND BR.resource_id = MAP.bom_resource_id
AND MAP.aso_resource_id= c_aso_resource_id
AND BDR.department_id = c_dept_id
AND (MAP.department_id = c_dept_id or MAP.department_id is NULL)
AND MAP.bom_org_id = c_org_id
ORDER BY MAP.department_id nulls last;
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 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.resource_id = BR.resource_id
AND BR.resource_id = MAP.bom_resource_id
AND MAP.aso_resource_id= c_aso_resource_id
AND BDR.department_id = c_dept_id
AND (MAP.department_id = c_dept_id or MAP.department_id is NULL)
AND MAP.bom_org_id = c_org_id
ORDER BY MAP.department_id nulls last;
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;
x_update_res_tbl OUT NOCOPY AHL_PP_RESRC_REQUIRE_PVT.resrc_require_tbl_type,
x_delete_res_tbl OUT NOCOPY AHL_PP_RESRC_REQUIRE_PVT.resrc_require_tbl_type
)
IS
--
/* Cursor for getting the BOM Resource for the CMRO Resource, Visit's Organization and Department.
* In case there is more than 1 BOM resource i.e.
* 1) BOM resource with department same as that of visit task and
* 2) BOM resource with department as null,
* then preference is given to the 1st BOM resource.
*/
CURSOR get_bom_resource (c_aso_resource_id NUMBER,
c_aso_resource_dur NUMBER,
c_org_id NUMBER,
c_dept_id NUMBER)
IS
SELECT BR.resource_id,
BR.resource_code,
BR.resource_type,
BR.unit_of_measure,
RESTYPE.meaning resource_type_name,
c_aso_resource_dur duration
FROM BOM_DEPARTMENT_RESOURCES BDR,
BOM_RESOURCES BR,
AHL_RESOURCE_MAPPINGS MAP,
MFG_LOOKUPS RESTYPE
WHERE BDR.resource_id = BR.resource_id
AND BR.resource_id = MAP.bom_resource_id
AND MAP.aso_resource_id = c_aso_resource_id
AND BDR.department_id = c_dept_id
AND (MAP.department_id = c_dept_id OR MAP.department_id IS NULL)
AND MAP.bom_org_id = c_org_id
AND RESTYPE.lookup_type = 'BOM_RESOURCE_TYPE'
AND RESTYPE.lookup_code(+) = BR.resource_type
AND RESTYPE.enabled_flag = 'Y'
AND TRUNC(nvl(RESTYPE.start_date_active, SYSDATE - 1)) <= TRUNC(SYSDATE)
AND TRUNC(nvl(RESTYPE.end_date_active , SYSDATE + 1)) > TRUNC(SYSDATE)
ORDER BY MAP.department_id nulls last;
SELECT AWO.workorder_operation_id,
AWO.operation_sequence_num,
WIPO.first_unit_start_date scheduled_start_date,
WIPO.last_unit_completion_date scheduled_end_date
FROM AHL_WORKORDERS AHLW,
WIP_OPERATIONS WIPO,
AHL_WORKORDER_OPERATIONS AWO
WHERE AHLW.workorder_id = c_workorder_id
AND AHLW.wip_entity_id = WIPO.wip_entity_id
AND AHLW.workorder_id = AWO.workorder_id
AND WIPO.operation_seq_num = AWO.operation_sequence_num
-- clause added in order to use index AHL_WORKORDER_OPERATIONS_N1 (workorder_id, status_code)
AND AWO.status_code = G_OP_STATUS_UNCOMPLETE;
SELECT operation_resource_id,
object_version_number,
duration
FROM AHL_OPERATION_RESOURCES
WHERE workorder_operation_id = c_wo_oper_id
AND resource_id = c_bom_resource_id;
SELECT max(resource_sequence_num)
FROM AHL_OPERATION_RESOURCES
WHERE workorder_operation_id = c_wo_oper_id;
* resource in Stage Workorder. If found, then populate update resource table with durations added,
* otherwise populate create resource table.
*/
l_bom_res_id_indx := l_bom_resource_tbl.FIRST;
x_update_res_tbl(l_upd_res_ctr).operation_resource_id := l_oper_bom_res_rec.operation_resource_id;
x_update_res_tbl(l_upd_res_ctr).object_version_number := l_oper_bom_res_rec.object_version_number;
x_update_res_tbl(l_upd_res_ctr).req_start_date := l_wo_oper_rec.scheduled_start_date;
x_update_res_tbl(l_upd_res_ctr).req_end_date := l_wo_oper_rec.scheduled_end_date;
x_update_res_tbl(l_upd_res_ctr).resource_type_name := l_bom_resource_tbl(l_bom_res_id_indx).resource_type_name;
x_update_res_tbl(l_upd_res_ctr).resource_name := l_bom_resource_tbl(l_bom_res_id_indx).resource_code;
x_update_res_tbl(l_upd_res_ctr).quantity := 1;
x_update_res_tbl(l_upd_res_ctr).duration := l_bom_resource_tbl(l_bom_res_id_indx).duration;
x_update_res_tbl(l_upd_res_ctr).operation_flag := 'U';
ELSIF ( p_workorder_rec.dml_operation = 'U' ) THEN -- update means case of MR deletion
/* Similar to above case, iterate BOM Resource Requirements, and find the corresponding BOM resource in Stage Workorder.
* If not found, then ignore it as it's the result of discrepancies that arise due to some unhandled cases,
* otherwise subtract durations. If duration < 1, then populate delete resource table, else update resource table.
*/
l_bom_res_id_indx := l_bom_resource_tbl.FIRST;
x_delete_res_tbl(l_del_res_ctr).operation_resource_id := l_oper_bom_res_rec.operation_resource_id;
x_delete_res_tbl(l_del_res_ctr).object_version_number := l_oper_bom_res_rec.object_version_number;
x_delete_res_tbl(l_del_res_ctr).workorder_operation_id := l_wo_oper_rec.workorder_operation_id;
x_delete_res_tbl(l_del_res_ctr).workorder_id := p_workorder_rec.workorder_id;
x_delete_res_tbl(l_del_res_ctr).operation_flag := 'D';
x_update_res_tbl(l_upd_res_ctr).operation_resource_id := l_oper_bom_res_rec.operation_resource_id;
x_update_res_tbl(l_upd_res_ctr).object_version_number := l_oper_bom_res_rec.object_version_number;
x_update_res_tbl(l_upd_res_ctr).req_start_date := l_wo_oper_rec.scheduled_start_date;
x_update_res_tbl(l_upd_res_ctr).req_end_date := l_wo_oper_rec.scheduled_end_date;
x_update_res_tbl(l_upd_res_ctr).resource_type_name := l_bom_resource_tbl(l_bom_res_id_indx).resource_type_name;
x_update_res_tbl(l_upd_res_ctr).resource_name := l_bom_resource_tbl(l_bom_res_id_indx).resource_code;
x_update_res_tbl(l_upd_res_ctr).quantity := 1;
x_update_res_tbl(l_upd_res_ctr).duration := l_bom_resource_tbl(l_bom_res_id_indx).duration;
x_update_res_tbl(l_upd_res_ctr).operation_flag := 'U';
AHL_DEBUG_PUB.debug( l_full_name || ' - Total Resource requirements to be Updated: ' || x_update_res_tbl.COUNT );
AHL_DEBUG_PUB.debug( l_full_name || ' - Total Resource requirements to be Deleted: ' || x_delete_res_tbl.COUNT );
SELECT workorder_operation_id
FROM AHL_WORKORDER_OPERATIONS
WHERE workorder_id = c_workorder_id
-- clause added in order to use index AHL_WORKORDER_OPERATIONS_N1 (workorder_id, status_code)
AND status_code = G_OP_STATUS_UNCOMPLETE;
SELECT operation_resource_id,
object_version_number
FROM AHL_OPERATION_RESOURCES
WHERE workorder_operation_id = c_wo_operation_id;
SELECT 1
FROM AHL_WORKORDERS
WHERE workorder_id = c_workorder_id
AND status_code = G_JOB_STATUS_DRAFT;
l_resource_tbl.DELETE;
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
--sukhwsin::Added position key for position based mat req.
MAT.position_key
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 DISTINCT WO.uc_header_id, UCH.master_config_id, WO.visit_id
FROM AHL_WORKORDER_TASKS_V WO, AHL_UNIT_CONFIG_HEADERS UCH
WHERE WO.workorder_id = c_workorder_id AND
UCH.unit_config_header_id = WO.uc_header_id;
l_pos_mtl_req_tbl.delete();
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
AND TRUNC(NVL(DISABLE_DATE,SYSDATE+1)) > TRUNC(SYSDATE);
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;
(Select ARO.route_operation_id,
AWO.operation_sequence_num
From AHL_WORKORDER_OPERATIONS AWO,
AHL_ROUTE_OPERATIONS ARO
Where AWO.workorder_id = c_workorder_id
AND AWO.operation_id = ARO.operation_id
AND ARO.route_id = c_route_id
)
SELECT FROMOP.operation_sequence_num from_operation_seq,
TOOP.operation_sequence_num to_operation_seq,
AROD.dependency_code precedence_constraint,
AROD.rt_op_dependency_id route_oper_reln_id
FROM AHL_RT_OPER_DEPENDENCIES AROD,
VALID_RT_OPER_IDS FROMOP,
VALID_RT_OPER_IDS TOOP
WHERE AROD.from_rt_op_id = FROMOP.route_operation_id
AND AROD.to_rt_op_id = TOOP.route_operation_id;
INSERT INTO AHL_WO_OPERATIONS_RELN
(
workorder_id,
wip_entity_id,
from_operation_seq,
to_operation_seq,
precedence_constraint,
route_oper_reln_id,
min_separation,
min_separation_time_unit,
max_separation,
max_separation_time_unit,
security_group_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES
(
p_workorder_rec.workorder_id,
p_workorder_rec.wip_entity_id,
oper_dependency_rec.from_operation_seq,
oper_dependency_rec.to_operation_seq,
oper_dependency_rec.precedence_constraint,
oper_dependency_rec.route_oper_reln_id,
null, -- since separation between the workorder operations is not taken into consideration
null,
null,
null,
p_workorder_rec.security_group_id,
p_workorder_rec.object_version_number,
p_workorder_rec.last_update_date,
p_workorder_rec.last_updated_by,
p_workorder_rec.creation_date,
p_workorder_rec.created_by,
p_workorder_rec.last_update_login
);
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;
SELECT 'Y'
FROM AHL_VISIT_TASKS_B
WHERE visit_task_id = c_visit_task_id
AND task_type_code = 'STAGE';
l_can_update_quantity VARCHAR2(1);
SELECT COUNT(MAT.scheduled_material_id)
FROM AHL_SCHEDULE_MATERIALS MAT
WHERE MAT.VISIT_TASK_ID = c_visit_task_id
AND NVL(MAT.STATUS,'NOTDEL') <> 'DELETED'
AND
(
(
MAT.ITEM_GROUP_ID IS NOT NULL
AND MAT.INVENTORY_ITEM_ID IS NOT NULL
)
OR
(
MAT.MC_HEADER_ID IS NOT NULL
AND MAT.POSITION_KEY IS NOT NULL
)
);
SELECT RTNG.INST_ROUTING_CODE INSTANCE_RTNG
FROM
AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_VISIT_INST_ROUTINGS RTNG
WHERE
WO.WORKORDER_ID = p_workorder_id
AND WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID
AND TSK.INSTANCE_ID IS NOT NULL
AND TSK.VISIT_ID = RTNG.VISIT_ID
AND TSK.INSTANCE_ID = RTNG.INSTANCE_ID;
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,
--sukhwsin:: Added column for VCP integration requirement
ITEM_ALTERNATES_EXIST,
-- JKJain, NR Analysis and Forecasting
UC_HEADER_ID,
NHA_UC_HEADER_ID,
FLEET_HEADER_ID,
MAINTENANCE_TYPE_CODE,
AOG_FLAG
) 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,
--sukhwsin:: Added column for VCP integration requirement
l_alt_item_exist,
-- JKJain, NR Analysis and Forecasting
l_uc_header_id,
l_nha_uc_header_id,
l_fleet_header_id,
L_MAINTENANCE_TYPE_CODE,
p_x_prd_workorder_rec.AOG_FLAG
);
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,
-- JKJain, NR Analysis and Forecasting
UC_HEADER_ID,
NHA_UC_HEADER_ID
) 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,
-- JKJain, NR Analysis and Forecasting
l_uc_header_id,
l_nha_uc_header_id
);
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;
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
WDJ.scheduled_start_date,
WDJ.scheduled_completion_date
FROM
WIP_DISCRETE_JOBS WDJ
WHERE
WDJ.wip_entity_id = p_wip_entity_id;
SELECT
WO.master_workorder_flag
FROM
AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WDJ
WHERE WDJ.wip_entity_id = WO.wip_entity_id
AND WDJ.wip_entity_id = p_wip_entity_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
wo.status_code, wdj.firm_planned_flag
FROM
ahl_workorders wo, wip_discrete_jobs wdj
WHERE
wo.wip_entity_id = wdj.wip_entity_id
and wo.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'
);
SELECT
WDJ.firm_planned_flag
FROM
WIP_DISCRETE_JOBS WDJ,
AHL_WORKORDERS AWO
WHERE
AWO.wip_entity_id = WDJ.wip_entity_id AND
AWO.workorder_id = c_workorder_id;
SELECT 'x'
FROM WIP_COST_TXN_INTERFACE wict
WHERE wict.wip_entity_id = p_wip_entity_id
AND process_status IN (1,2,3);
SELECT warranty_entitlement_id ,object_version_number
from ahl_warranty_entitlements
where visit_task_id = p_visit_task_id;
SELECT uc_header_id ,nha_uc_header_id,fleet_header_id,maintenance_type_code
from AHL_WORKORDERS
where workorder_id = p_wo_id;
SELECT AW.workorder_id,
AVT.VISIT_TASK_ID,
AW.MASTER_WORKORDER_FLAG,
WDJ.WIP_ENTITY_ID,
WDJ.ORGANIZATION_ID,
WDJ.PRIMARY_ITEM_ID,
AW.WORKORDER_NAME,
WDJ.DESCRIPTION,
AVT.INSTANCE_ID,
WDJ.CLASS_CODE,
AW.STATUS_CODE,
AV.PROJECT_ID,
AVT.PROJECT_TASK_ID,
AVT.DEPARTMENT_ID,
WDJ.FIRM_PLANNED_FLAG,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE
FROM AHL_MR_HEADERS_B AMH,
AHL_WORKORDERS AW,
AHL_VISITS_B AV,
AHL_VISIT_TASKS_B AVT,
wip_discrete_jobs wdj
WHERE AW.VISIT_TASK_ID = AVT.VISIT_TASK_ID
AND AV.VISIT_ID = AVT.VISIT_ID
AND AVT.MR_ID = AMH.MR_HEADER_ID (+)
AND AW.MASTER_WORKORDER_FLAG = 'N'
AND WDJ.WIP_ENTITY_ID = AW.WIP_ENTITY_ID
AND AW.STATUS_CODE NOT IN (4, 5, 7, 12, 15, 17, 22)
AND AMH.MR_HEADER_ID = P_MR_HEADER_ID
AND AW.VISIT_ID = P_VISIT_ID;
SELECT ASM.AOG_FLAG
FROM AHL_SCHEDULE_MATERIALS ASM,
AHL_VISIT_TASKS_B AVT,
AHL_WORKORDERS AW
WHERE ASM.VISIT_TASK_ID = AVT.VISIT_TASK_ID
AND AW.VISIT_TASK_ID = AVT.VISIT_TASK_ID
AND ASM.AOG_FLAG = 'Y'
AND AW.WORKORDER_ID = P_WORKORDER_ID;
SELECT mtl.reservation_id RESERVATION_ID
FROM mtl_reservations mtl ,
ahl_workorders awo
WHERE mtl.supply_source_header_id = awo.wip_entity_id
AND awo.workorder_id = P_WORKORDER_ID;
SAVEPOINT update_job_PVT;
AHL_DEBUG_PUB.debug( l_api_name || ' - At Start of Update Job API' );
IF (JTF_USR_HKS.Ok_to_execute('AHL_PRD_WORKORDER_PVT', 'UPDATE_JOB', 'B', 'C' )) THEN
AHL_DEBUG_PUB.debug( FND_MSG_PUB.count_msg || ' skp2 FND_MSG_PUB.count_msg= '||FND_MSG_PUB.count_msg );
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;
AHL_DEBUG_PUB.DEBUG( L_API_NAME || ' - Workorder AOG status to be updated to - ' || P_X_PRD_WORKORDER_REC.AOG_FLAG);
AHL_DEBUG_PUB.DEBUG( L_API_NAME || ' Workorder AOG status can not be updated to NO');
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,
-- JKJain, NR Analysis and Forecasting
UC_HEADER_ID =l_uc_header_id,
NHA_UC_HEADER_ID =l_nha_uc_header_id,
FLEET_HEADER_ID =l_fleet_header_id,
MAINTENANCE_TYPE_CODE =L_MAINTENANCE_TYPE_CODE,
-- sansatpa added for marshalling AOG updation
AOG_FLAG = p_x_prd_workorder_rec.AOG_FLAG
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,
-- JKJain, NR Analysis and Forecasting
UC_HEADER_ID,
NHA_UC_HEADER_ID,
AOG_FLAG
) 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,
-- JKJain, NR Analysis and Forecasting
l_uc_header_id,
l_nha_uc_header_id,
p_x_prd_workorder_rec.AOG_FLAG
);
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_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 || ' - Before updating MR Priority, priority selected - ' || P_X_PRD_WORKORDER_REC.JOB_PRIORITY || ', MR header id - ' || P_X_PRD_WORKORDER_REC.MR_HEADER_ID);
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 => L_X_PRD_WORKORDER_REC ,
P_OPERATION_TBL => L_OPERATION_TBL ,
P_MATERIAL_REQ_TBL => L_MATERIAL_REQ_TBL ,
P_RESOURCE_REQ_TBL => L_RESOURCE_REQ_TBL
);
END IF; -- Check if Set MR Priority check box is selected ends
Update_Master_Wo_Dates(p_x_prd_workorder_rec.workorder_id);
AHL_DEBUG_PUB.debug( l_api_name || ' - After AHL_EAM_JOB_PVT.update_job_operations or move ' );
AHL_PRD_WORKORDER_PVT.Update_Master_Wo_Dates(l_resource_tbl(l_res_count).workorder_id);
AHL_PRD_WORKORDER_PVT.Update_Master_Wo_Dates(p_x_prd_workorder_rec.workorder_id);
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);
IF (p_x_prd_workorder_rec.status_code IN (G_JOB_STATUS_CANCELLED, G_JOB_STATUS_DELETED)) THEN
FOR GET_SRC_RES_ID_REC IN GET_SRC_RES_ID_CSR(p_x_prd_workorder_rec.workorder_id)
LOOP
AHL_MM_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_reservation_id => GET_SRC_RES_ID_REC.RESERVATION_ID);
AHL_DEBUG_PUB.debug('Delete_Reservation failed for Reservation ID: '
|| GET_SRC_RES_ID_REC.RESERVATION_ID);
'Before calling AHL_LTP_REQST_MATRL_PVT.Update_Material_Reqrs_status'
);
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;
SELECT mr_id, unit_effectivity_id
FROM AHL_VISIT_TASKS_B
WHERE visit_task_id = c_visit_task_id;
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' );
* existing MR workorders which are in Deleted status to a local table for updating
* NR profile resources corresponding to them in Stage workorders.
*/
IF ( p_x_prd_workorder_tbl(i).master_workorder_flag = 'Y' AND
p_x_prd_workorder_tbl(i).mr_header_id IS NOT NULL AND
( ( p_x_prd_workorder_tbl(i).dml_operation = 'C' AND
p_x_prd_workorder_tbl(i).status_code = G_JOB_STATUS_DRAFT ) OR
( p_x_prd_workorder_tbl(i).dml_operation = 'U' AND
p_x_prd_workorder_tbl(i).status_code = G_JOB_STATUS_DELETED )
)
) THEN
total_mr_workorders := total_mr_workorders + 1;
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 STAGE.stage_id
FROM AHL_VISIT_STAGE_TYP_ASOC ASOC,
AHL_VWP_STAGES_B STAGE,
AHL_VISIT_TASKS_B AVTB
WHERE ASOC.stage_type_code = c_stage_type_code
AND ASOC.stage_id = STAGE.stage_id
AND STAGE.visit_id = c_visit_id
-- to check that Stage task is not already P2P
AND AVTB.visit_id = c_visit_id
AND AVTB.stage_id = STAGE.stage_id
AND AVTB.task_type_code = 'STAGE'
AND AVTB.status_code = 'PLANNING';
SELECT AHLW.workorder_id,
AHLW.wip_entity_id,
AVB.organization_id,
AVB.department_id,
BOMD.description department_name
FROM AHL_VISIT_TASKS_B AVTB,
AHL_WORKORDERS AHLW,
AHL_VISITS_B AVB,
BOM_DEPARTMENTS BOMD
WHERE ( AVTB.stage_id = c_stage_id OR
(c_stage_id = 0 AND AVTB.visit_id = c_visit_id AND AVTB.stage_id IS NULL) )
AND AVTB.task_type_code = 'STAGE'
AND AVTB.visit_task_id = AHLW.visit_task_id
AND AVTB.visit_id = AVB.visit_id
AND AVB.department_id = BOMD.department_id;
l_update_res_tbl AHL_PP_RESRC_REQUIRE_PVT.resrc_require_tbl_type;
l_delete_res_tbl AHL_PP_RESRC_REQUIRE_PVT.resrc_require_tbl_type;
* created, updated or deleted based on the passed MR workorders.
*/
IF ( p_module_type = 'VWP' ) THEN
IF ( G_DEBUG = 'Y' ) THEN
AHL_DEBUG_PUB.debug( l_full_name || ' - Invoking Remove_wo_resrc_reqs API. Total Stage workorders: ' || p_workorder_tbl.COUNT );
l_prof_res_req_tbl.DELETE;
l_create_res_tbl.DELETE;
l_update_res_tbl.DELETE;
l_delete_res_tbl.DELETE;
x_update_res_tbl => l_update_res_tbl,
x_delete_res_tbl => l_delete_res_tbl
);
IF ( l_update_res_tbl.COUNT > 0 ) THEN
IF ( G_DEBUG = 'Y' ) THEN
AHL_DEBUG_PUB.debug( l_full_name || ' - Invoking Process_resrc_require API for Updating Resource Requirements for Stage Workorder id: ' || l_stg_workorder_rec.workorder_id );
p_x_resrc_require_tbl => l_update_res_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END IF; -- if l_update_res_tbl.COUNT > 0
IF ( l_delete_res_tbl.COUNT > 0 ) THEN
IF ( G_DEBUG = 'Y' ) THEN
AHL_DEBUG_PUB.debug( l_full_name || ' - Invoking Process_resrc_require API for Removing Resource Requirements for Stage Workorder id: ' || l_stg_workorder_rec.workorder_id );
p_x_resrc_require_tbl => l_delete_res_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END IF; -- if l_delete_res_tbl.COUNT > 0
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 );
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 awo.workorder_id,
awo.object_version_number,
awo.status_code,
awo.wip_entity_id,
awo.workorder_name,
vst.visit_number
FROM AHL_WORKORDERS AWO, AHL_VISITS_B VST
WHERE awo.visit_id = c_visit_id
AND awo.visit_id = vst.visit_id
AND awo.status_code NOT IN (G_JOB_STATUS_DELETED,G_JOB_STATUS_CANCELLED)
AND awo.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);
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);
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_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);
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;
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;
* 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;
SELECT DISTINCT CWO.workorder_id workorder_id,
CWO.object_version_number object_version_number,
CWO.wip_entity_id wip_entity_id,
CWO.status_code status_code,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE,
level
FROM AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL,
WIP_DISCRETE_JOBS WIP
WHERE CWO.wip_entity_id = REL.parent_object_id
AND CWO.wip_entity_id = WIP.WIP_ENTITY_ID
AND WIP.firm_planned_flag = '1'
AND CWO.status_code NOT IN ('22','7','4','5','12')
AND CWO.master_workorder_flag = 'Y'
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.child_object_id IN
(select wos.wip_entity_id from ahl_workorders wos, WIP_DISCRETE_JOBS WIPS
where wos.wip_entity_id = wips.wip_entity_id
and wips.date_completed IS NOT NULL
and exists (Select 'X' from WIP_DISCRETE_JOBS WIPSP,WIP_SCHED_RELATIONSHIPS RELP
where RELP.child_object_id = wips.wip_entity_id
and RELP.parent_object_id = WIPSP.wip_entity_id
and DECODE(c_offset_direction, 1,WIPSP.scheduled_start_date+c_offset,WIPS.scheduled_completion_date + c_offset)
> DECODE(c_offset_direction, 1,WIPS.scheduled_start_date ,WIPSP.scheduled_completion_date))
and wos.visit_id = c_visit_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,workorder_id asc;*/
SELECT cwo.workorder_id workorder_id, cwo.visit_id,
cwo.object_version_number object_version_number,
cwo.wip_entity_id wip_entity_id, cwo.status_code status_code,
wip.scheduled_start_date,
wip.scheduled_completion_date scheduled_end_date, LEVEL
FROM
(select cwo.workorder_id, cwo.visit_id,
cwo.object_version_number,
cwo.wip_entity_id , cwo.status_code status_code
from ahl_workorders cwo
WHERE cwo.visit_id = c_visit_id
and cwo.status_code NOT IN ('22', '7', '4', '5', '12')
AND cwo.master_workorder_flag = 'Y') cwo,
wip_sched_relationships rel,
wip_discrete_jobs wip
WHERE cwo.wip_entity_id = rel.parent_object_id
AND cwo.wip_entity_id = wip.wip_entity_id
AND wip.firm_planned_flag = '1'
AND rel.parent_object_type_id = 1
AND rel.child_object_type_id = 1
START WITH rel.child_object_id IN (
SELECT distinct wos.wip_entity_id
FROM wip_discrete_jobs wips, wip_discrete_jobs wipsp,
wip_sched_relationships relp, ahl_workorders wos
WHERE wos.visit_id = c_visit_id
AND wos.wip_entity_id = wips.wip_entity_id
AND wips.date_completed IS NOT NULL
AND relp.child_object_id = wips.wip_entity_id
AND relp.relationship_type = 1
AND relp.child_object_type_id = 1
AND relp.parent_object_id = wipsp.wip_entity_id
AND DECODE (c_offset_direction,1, wipsp.scheduled_start_date + c_offset,wips.scheduled_completion_date + c_offset)>
DECODE (c_offset_direction,1, wips.scheduled_start_date, wipsp.scheduled_completion_date))
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, workorder_id ASC;
SELECT DISTINCT CWO.workorder_id,
CWO.workorder_name,
CWO.wip_entity_id wip_entity_id,
CWO.status_code status_code,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE
FROM AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL,
WIP_DISCRETE_JOBS WIP
WHERE CWO.wip_entity_id = REL.parent_object_id
AND CWO.wip_entity_id = WIP.WIP_ENTITY_ID
AND WIP.firm_planned_flag = '1'
AND CWO.status_code NOT IN ('22','7','4','5','12')
AND CWO.master_workorder_flag = 'Y'
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.child_object_id IN
(select wos.wip_entity_id from ahl_workorders wos, WIP_DISCRETE_JOBS WIPS
where wos.wip_entity_id = wips.wip_entity_id
and wips.date_completed IS NOT NULL
and wos.visit_id = c_visit_id)
AND REL.relationship_type = 1
CONNECT BY REL.child_object_id = PRIOR REL.parent_object_id
AND REL.relationship_type = 1
ORDER BY workorder_id asc;
SELECT min(scheduled_start_date) max_start_date,
max(scheduled_completion_date) min_end_date
FROM wip_discrete_jobs wips, ahl_workorders wos
WHERE wips.wip_entity_id = wos.wip_entity_id
and wips.date_completed IS NOT NULL
and wos.visit_id = c_visit_id
group by wos.visit_id;
l_prd_workorder_tbl.DELETE;
'CMRO Updated but prior to EAM move - Scheduled start and completion dates : '
|| '{mwo_rec.workorder_name : ' || mwo_rec.workorder_name || '}'
|| '{mwo_rec.wip_entity_id : ' || mwo_rec.wip_entity_id || '}'
|| '{ mwo_rec.status_code : ' || mwo_rec.status_code || '}'
|| '{ mwo_rec.SCHEDULED_START_DATE : ' || to_char(mwo_rec.SCHEDULED_START_DATE,'DD-MON-YYYY HH24:MI:SS') || '}'
|| '{mwo_rec.SCHEDULED_COMPLETION_DATE : ' || to_char(mwo_rec.SCHEDULED_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') || '}'
);
l_prd_workorder_tbl.DELETE;
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;
SELECT
WO.WIP_ENTITY_ID,
WO.WORKORDER_NAME JOB_NUMBER,
WDJ.DESCRIPTION JOB_DESCRIPTION,
WO.OBJECT_VERSION_NUMBER,
VST.ORGANIZATION_ID ORGANIZATION_ID,
WDJ.FIRM_PLANNED_FLAG FIRM_PLANNED_FLAG,
WDJ.CLASS_CODE CLASS_CODE,
WDJ.OWNING_DEPARTMENT DEPARTMENT_ID,
WO.STATUS_CODE JOB_STATUS_CODE,
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,
CSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID) ITEM_INSTANCE_ID,
WDJ.COMPLETION_SUBINVENTORY COMPLETION_SUBINVENTORY,
WDJ.COMPLETION_LOCATOR_ID COMPLETION_LOCATOR_ID,
WO.MASTER_WORKORDER_FLAG MASTER_WORKORDER_FLAG,
WO.VISIT_TASK_ID VISIT_TASK_ID,
-- FP of PIE changes
--VST.PROJECT_ID PROJECT_ID,
--VTS.PROJECT_TASK_ID PROJECT_TASK_ID,
DECODE(WO.MASTER_WORKORDER_FLAG,'Y',to_number(NULL),VST.PROJECT_ID) PROJECT_ID,
DECODE(WO.MASTER_WORKORDER_FLAG,'Y',to_number(NULL),VTS.PROJECT_TASK_ID) PROJECT_TASK_ID,
WDJ.PRIORITY PRIORITY
FROM
AHL_WORKORDERS WO,
AHL_VISITS_B VST,
AHL_VISIT_TASKS_B VTS,
WIP_DISCRETE_JOBS WDJ,
CSI_ITEM_INSTANCES CSI
WHERE
WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND VST.VISIT_ID = WO.VISIT_ID
AND NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID) = CSI.INSTANCE_ID
AND WO.VISIT_TASK_ID IS NOT NULL
AND WO.STATUS_CODE <> '22'
AND WO.workorder_id = c_workorder_id;
PROCEDURE Update_Master_Wo_Dates(
p_workorder_id IN NUMBER
)
IS
CURSOR get_curr_wo_details(c_workorder_id NUMBER)
IS
SELECT
AWO.wip_entity_id
FROM
ahl_workorders AWO
WHERE
awo.workorder_id = c_workorder_id;
SELECT WO.workorder_id,
WO.object_version_number,
WO.wip_entity_id,
WO.visit_task_id,
WO.STATUS_CODE,
WDJ.SCHEDULED_START_DATE SCHEDULED_START_DATE,
WDJ.scheduled_completion_date scheduled_end_date
FROM AHL_WORKORDERS WO,WIP_DISCRETE_JOBS WDJ,
WIP_SCHED_RELATIONSHIPS WOR
WHERE WO.WIP_ENTITY_ID = WOR.PARENT_OBJECT_ID
AND WDJ.wip_entity_id = WO.wip_entity_id
AND WO.master_workorder_flag = 'Y'
AND WO.visit_task_id IS NOT NULL
AND WO.status_code <> '22'
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.wip_entity_id,
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 <> '22'
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
AND parent_object_id = c_wip_entity_id
AND relationship_type = 1
);
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
) VALUES
(
AHL_WORKORDER_TXNS_S.NEXTVAL,
NVL(l_x_prd_workorder_rec.OBJECT_VERSION_NUMBER,1),
NVL(l_x_prd_workorder_rec.LAST_UPDATE_DATE,SYSDATE),
NVL(l_x_prd_workorder_rec.LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
NVL(l_x_prd_workorder_rec.CREATION_DATE,SYSDATE),
NVL(l_x_prd_workorder_rec.CREATED_BY,FND_GLOBAL.USER_ID),
NVL(l_x_prd_workorder_rec.LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID),
l_x_prd_workorder_rec.WORKORDER_ID,
0,
l_x_prd_workorder_rec.STATUS_CODE,
l_x_prd_workorder_rec.SCHEDULED_START_DATE,
l_x_prd_workorder_rec.SCHEDULED_END_DATE,
l_x_prd_workorder_rec.ACTUAL_START_DATE,
l_x_prd_workorder_rec.ACTUAL_END_DATE,
0,
l_x_prd_workorder_rec.COMPLETION_SUBINVENTORY,
l_x_prd_workorder_rec.COMPLETION_LOCATOR_ID
);
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 => l_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
);
END Update_Master_Wo_Dates;
select 'x'
from dual
where exists (select 'x' from ahl_workorders
where visit_id = c_visit_id
and status_code NOT IN ('1','3','7', '17')
);
select 'x'
from dual
where exists (select 'x'
from ahl_workorders awo, ahl_workorder_operations awop
where awo.workorder_id = awop.workorder_id
and awo.visit_id = c_visit_id
and awop.status_code = '1'
);
select 'x'
from dual
where exists (select 'x'
from ahl_workorders awo, ahl_workorder_operations awop, ahl_part_changes apc
where awo.workorder_id = awop.workorder_id
and awop.workorder_operation_id = apc.workorder_operation_id
and awo.visit_id = c_visit_id
);
select 'x'
from dual
where exists (select 'x'
from ahl_workorders awo, csi_item_instances cii
where awo.wip_entity_id = cii.wip_job_id
and cii.location_type_code = 'WIP'
and cii.ACTIVE_START_DATE <= SYSDATE
and ((cii.ACTIVE_END_DATE IS NULL) OR (cii.ACTIVE_END_DATE >= SYSDATE))
and awo.visit_id = c_visit_id
);
select 'x'
from dual
where exists (select 'x'
from ahl_workorders awo, wip_transactions wipt
where awo.wip_entity_id = wipt.wip_entity_id
and awo.visit_id = c_visit_id
UNION ALL
select 'x'
from ahl_workorders awos, wip_COST_TXN_INTERFACE wict
where awos.wip_entity_id = wict.wip_entity_id
and awos.visit_id = c_visit_id
and process_status IN (1,3)
);
SELECT 'x'
FROM dual
WHERE EXISTS
( SELECT 'X'
FROM csi_transactions cst ,
csi_txn_types cstrntyp,
ahl_workorders awo
WHERE cstrntyp.source_transaction_type = 'ITEM_SERIAL_CHANGE'
AND cst.transaction_type_id = cstrntyp.transaction_type_id
AND cst.source_line_ref = 'AHL_PRD_WO'
AND cst.source_line_ref_id = awo.workorder_id
AND awo.visit_id = c_visit_id
);
SELECT INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER, sum(quantity) quantity
FROM ahl_workorder_mtl_txns amt, ahl_workorder_operations awo, ahl_workorders aw
WHERE aw.visit_id = c_visit_id
and aw.workorder_id = awo.workorder_id
and awo.workorder_operation_id = amt.workorder_operation_id
and TRANSACTION_TYPE_ID = 35
group by INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER
MINUS
SELECT INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER, sum(quantity) quantity
FROM ahl_workorder_mtl_txns amt, ahl_workorder_operations awo, ahl_workorders aw
WHERE aw.visit_id = c_visit_id
and aw.workorder_id = awo.workorder_id
and awo.workorder_operation_id = amt.workorder_operation_id
and TRANSACTION_TYPE_ID = 43
group by INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER;
SELECT concatenated_segments INTO l_inv_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = mtl_rec.inventory_item_id
and rownum < 2;
SELECT WORKORDER_ID,
WIP_ENTITY_ID,
OBJECT_VERSION_NUMBER,
JOB_STATUS_CODE,
ORGANIZATION_ID,
CLASS_CODE,
DATE_CLOSED,
DATE_COMPLETED
FROM AHL_WORKORDERS_V
WHERE WORKORDER_ID = c_workorder_id
FOR UPDATE OF JOB_STATUS_CODE;
SELECT WO.wip_entity_id wip_entity_id,
WO.object_version_number ovn,
WO.STATUS_CODE,
WDJ.ORGANIZATION_ID organization_id,
WDJ.CLASS_CODE class_code,
WDJ.DATE_CLOSED,
--sukhwsin::VEE Changes- included workorder_id
WO.workorder_id
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS WOR,
WIP_DISCRETE_JOBS WDJ
WHERE WO.wip_entity_id = WOR.parent_object_id
AND WDJ.wip_entity_id = WO.wip_entity_id
AND WO.master_workorder_flag = 'Y'
AND WO.status_code = G_JOB_STATUS_CLOSED
AND WOR.parent_object_type_id = 1
AND WOR.relationship_type = 1
AND WOR.child_object_type_id = 1
START WITH WOR.child_object_id = c_wip_entity_id
AND WOR.relationship_type = 1
CONNECT BY WOR.child_object_id = PRIOR WOR.parent_object_id
FOR UPDATE OF WO.STATUS_CODE,WDJ.DATE_CLOSED ;
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 => l_prd_workoper_tbl,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
UPDATE wip_discrete_jobs
SET date_closed = NULL,
status_type = G_JOB_STATUS_COMPLETE
WHERE organization_id = mwo_rec.organization_id
AND WIP_ENTITY_ID = mwo_rec.wip_entity_id;
UPDATE ahl_workorders
SET status_code = G_JOB_STATUS_COMPLETE,
object_version_number = mwo_rec.ovn+1
WHERE wip_entity_id = mwo_rec.wip_entity_id;
UPDATE wip_discrete_jobs
SET date_closed = NULL,
status_type = G_JOB_STATUS_COMPLETE
WHERE organization_id = l_organization_id
AND WIP_ENTITY_ID = l_wip_entity_id;
UPDATE ahl_workorders
SET status_code = G_JOB_STATUS_COMPLETE,
object_version_number = l_obj_ver_no+1
WHERE wip_entity_id = l_wip_entity_id;
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 => l_prd_workoper_tbl,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
SELECT WIP.WIP_ENTITY_ID,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE
FROM AHL_WORKORDERS AWO,
WIP_DISCRETE_JOBS WIP
WHERE AWO.VISIT_ID = p_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 DISTINCT CWO.workorder_id workorder_id,
CWO.object_version_number object_version_number,
CWO.wip_entity_id wip_entity_id,
CWO.status_code status_code,
CWO.visit_task_id,
WIP.SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE,
level
FROM AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL,
WIP_DISCRETE_JOBS WIP
WHERE CWO.wip_entity_id = REL.parent_object_id
AND CWO.wip_entity_id = WIP.WIP_ENTITY_ID
AND CWO.master_workorder_flag = 'Y'
AND REL.parent_object_type_id = 1
AND REL.child_object_type_id = 1
START WITH REL.child_object_id IN (select wos.wip_entity_id
from ahl_workorders wos
where wos.workorder_id = p_workorder_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,workorder_id asc;
SELECT
awo.workorder_id,
awo.object_version_number,
-- STHILAK :: FP:Bug 9186962
-- Fetch visit task id also
awo.visit_task_id
FROM
ahl_workorders awo,
ahl_visits_b vst
WHERE
awo.visit_id = vst.visit_id
AND vst.status_code not in ('CANCELLED', 'DELETED')
AND vst.visit_id = p_visit_id
order by visit_task_id desc; -- VWP expects visit master work order to be the first wo in table.
SELECT
vst.visit_id
FROM
ahl_visits_b vst
WHERE
vst.status_code not in ('CANCELLED', 'DELETED', 'DRAFT')
AND vst.department_id = nvl(p_department_id, vst.department_id)
AND vst.organization_id = nvl(p_organization_id, vst.organization_id);
fnd_file.put_line(fnd_file.log, 'before calling AHL_VWP_PROJ_PROD_PVT.Update_Project_Task_Times');
AHL_VWP_PROJ_PROD_PVT.Update_Project_Task_Times(
p_prd_workorder_tbl => l_prd_workorder_tbl,
p_commit => Fnd_Api.G_TRUE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_file.put_line(fnd_file.log, 'before calling AHL_VWP_PROJ_PROD_PVT.Update_Project_Task_Times');
AHL_VWP_PROJ_PROD_PVT.Update_Project_Task_Times(
p_prd_workorder_tbl => l_prd_workorder_tbl,
p_commit => Fnd_Api.G_TRUE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT WARRANTY_CONTRACT_ID
FROM AHL_WARRANTY_CONTRACTS_B AWCB,
AHL_VISITS_B VST,
AHL_VISIT_TASKS_B VTS
WHERE VTS.VISIT_TASK_ID = c_visit_task_id
AND VTS.VISIT_ID = VST.VISIT_ID
AND NVL(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID) = AWCB.ITEM_INSTANCE_ID
AND CONTRACT_STATUS_CODE = 'ACTIVE'
AND CONTRACT_NUMBER = c_contract_number;
SELECT ENTITLEMENT_STATUS_CODE,
WARRANTY_CONTRACT_ID
FROM AHL_WARRANTY_ENTITLEMENTS
where VISIT_TASK_ID = c_visit_task_id;
SELECT service_request_id
FROM AHL_VISIT_TASKS_B
WHERE TASK_TYPE_CODE = 'PLANNED'
AND MR_ROUTE_ID IS NULL;
p_warranty_entl_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
IF p_warranty_entl_rec.operation_flag = AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE THEN
OPEN get_old_entl_details(p_warranty_entl_rec.visit_task_id);
SELECT distinct pf.full_name emp_name, pf.employee_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= c_organization_id
and fu.user_id = fnd_global.user_id;
select meaning
from FND_LOOKUP_VALUES_VL
where
lookup_type = c_lookup_type
and lookup_code = c_lookup_code
and enabled_flag = 'Y'
and nvl(end_date_active,sysdate + 1) > sysdate;
select wo.status_code, vst.organization_id
from ahl_workorders wo, ahl_visits_b vst, ahl_visit_tasks_b vts
where
wo.visit_task_id = vts.visit_task_id
and vts.visit_id = vst.visit_id
and wo.workorder_id = c_workorder_id;
select vst.organization_id, oper.operation_sequence_num, oper.status_code, wo.workorder_id
from ahl_workorders wo, ahl_visits_b vst, ahl_visit_tasks_b vts,ahl_workorder_operations oper
where
wo.visit_task_id = vts.visit_task_id
and vts.visit_id = vst.visit_id
and oper.workorder_id = wo.workorder_id
and oper.workorder_operation_id = c_workorder_op_id;
l_insert_flag VARCHAR2(1);
l_insert_flag := 'N';
l_insert_flag := 'Y';
l_insert_flag := 'Y';
IF(l_insert_flag = 'Y') THEN
l_notes := fnd_message.get;
INSERT_TURNOVER_NOTES
(
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_default => p_default,
p_module_type => p_module_type,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trunover_notes_tbl => l_turnover_notes_tbl
); */
SELECT ue.fleet_header_id,
vtsk.unit_effectivity_id,
DECODE(ue.defer_from_ue_id, NULL,
decode(vtsk.service_request_id, NULL, decode(vtsk.task_type_code,'PLANNED', 'ROUTINE', 'ROUTINE_UNKNOWN'), 'NONROUTINE'),
decode(vtsk.service_request_id, NULL, 'DEFER_ROUTINE','DEFER_NONROUTINE')) maintenance_type_code
FROM ahl_visit_tasks_b vtsk, ahl_unit_effectivities_b ue
WHERE vtsk.unit_effectivity_id = ue.unit_effectivity_id(+)
AND vtsk.visit_task_id = c_visit_task_id;
Select UNIT_CONFIG_HEADER_ID from
AHL_UNIT_CONFIG_HEADERS UC, AHL_VISITS_B V
where V.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID
AND V.ITEM_INSTANCE_ID = c_visit_id;
SELECT WIP.DATE_RELEASED
FROM AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WIP
WHERE WO.workorder_id = c_wo_id
AND WO.wip_entity_id = WIP.wip_entity_id;
SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE
LOOKUP_TYPE = 'AHL_INSTANCE_ROUTING'
AND LOOKUP_CODE = c_inst_routing_code
AND NVL(ENABLED_FLAG,'N') = 'Y'
AND NVL(END_DATE_ACTIVE,SYSDATE+1) > SYSDATE;
PROCEDURE UPDATE_WO_AOG_STATUS
(
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_workorder_id_tbl IN WORKORDER_ID_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_wo_aog_status';
SELECT WO.WORKORDER_ID,
WO.WIP_ENTITY_ID,
WO.WORKORDER_NAME JOB_NUMBER,
WDJ.DESCRIPTION JOB_DESCRIPTION,
WO.OBJECT_VERSION_NUMBER,
WO.STATUS_CODE JOB_STATUS_CODE,
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,
WDJ.COMPLETION_SUBINVENTORY COMPLETION_SUBINVENTORY,
WDJ.COMPLETION_LOCATOR_ID COMPLETION_LOCATOR_ID,
WDJ.PRIORITY PRIORITY
FROM AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.STATUS_CODE NOT IN (4, 5, 7, 12, 15, 17, 22)
AND WO.WORKORDER_ID = P_WORKORDER_ID;
L_API_NAME, 'At the start of PLSQL procedure UPDATE_WO_AOG_STATUS');
L_API_NAME, ' No of Workorders to be updated for AOG Status - ' || p_workorder_id_tbl.COUNT);
SAVEPOINT UPDATE_WO_AOG_STATUS;
l_sql_string := 'SELECT AW.WORKORDER_ID, AW.WORKORDER_NAME, AW.STATUS_CODE, FLV.MEANING WO_STATUS FROM AHL_WORKORDERS AW, FND_LOOKUP_VALUES_VL FLV WHERE AW.STATUS_CODE = FLV.LOOKUP_CODE AND FLV.LOOKUP_TYPE = ''AHL_JOB_STATUS'' AND WORKORDER_ID IN (0';
FND_LOG.STRING( G_LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, 'All workorder have proper status, lets update');
UPDATE AHL_WORKORDERS
SET AOG_FLAG = 'Y',
OBJECT_VERSION_NUMBER = P_WORKORDER_ID_TBL(I).OBJ_VER_NUM + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE WORKORDER_ID = P_WORKORDER_ID_TBL(I).WORKORDER_ID
and OBJECT_VERSION_NUMBER = P_WORKORDER_ID_TBL(I).OBJ_VER_NUM;
FND_LOG.STRING( G_LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, 'Workorder updation over. Lets insert transaction details to AHL_WORKORDER_TXNS table');
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,
AOG_FLAG
)
VALUES
(
AHL_WORKORDER_TXNS_S.NEXTVAL,
NVL(L_PRD_WORKORDER_REC.OBJECT_VERSION_NUMBER,1),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
L_PRD_WORKORDER_REC.WORKORDER_ID,
0,
L_PRD_WORKORDER_REC.JOB_STATUS_CODE,
L_PRD_WORKORDER_REC.SCHEDULED_START_DATE,
L_PRD_WORKORDER_REC.SCHEDULED_END_DATE,
L_PRD_WORKORDER_REC.ACTUAL_START_DATE,
L_PRD_WORKORDER_REC.ACTUAL_END_DATE,
0,
L_PRD_WORKORDER_REC.COMPLETION_SUBINVENTORY,
L_PRD_WORKORDER_REC.COMPLETION_LOCATOR_ID,
'Y'
);
ROLLBACK TO UPDATE_WO_AOG_STATUS;
ROLLBACK TO UPDATE_WO_AOG_STATUS;
ROLLBACK TO UPDATE_WO_AOG_STATUS;
ROLLBACK TO UPDATE_WO_AOG_STATUS;
p_procedure_name => 'UPDATE_WO_AOG_STATUS',
p_error_text => SUBSTR(SQLERRM,1,240));
L_API_NAME, 'At the end of PLSQL procedure UPDATE_WO_AOG_STATUS');
END UPDATE_WO_AOG_STATUS;