[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_JOB_STATUS_DELETED VARCHAR2(2) := '22'; --Deleted
SELECT OP.workorder_operation_id,
OP.object_version_number,
OP.workorder_id,
WO.wip_entity_id,
OP.operation_sequence_num,
OP.organization_id,
OP.description,
OP.plan_id,
OP.collection_id,
OP.actual_start_date,
OP.actual_end_date,
OP.status_code,
OP.status
FROM AHL_WORKORDERS WO, AHL_WORKORDER_OPERATIONS_V OP
WHERE WO.workorder_id = OP.workorder_id
AND OP.workorder_operation_id = p_workorder_operation_id;
/*SELECT OP.workorder_operation_id,
OP.object_version_number,
OP.workorder_id,
WO.wip_entity_id,
OP.operation_sequence_num,
OP.organization_id,
OP.description,
OP.plan_id,
OP.collection_id,
OP.actual_start_date,
OP.actual_end_date,
OP.status_code,
OP.status
INTO x_operation_rec.workorder_operation_id,
x_operation_rec.object_version_number,
x_operation_rec.workorder_id,
x_operation_rec.wip_entity_id,
x_operation_rec.operation_sequence_num,
x_operation_rec.organization_id,
x_operation_rec.description,
x_operation_rec.plan_id,
x_operation_rec.collection_id,
x_operation_rec.actual_start_date,
x_operation_rec.actual_end_date,
x_operation_rec.status_code,
x_operation_rec.status
FROM AHL_WORKORDERS WO, AHL_WORKORDER_OPERATIONS_V OP
WHERE WO.workorder_id = OP.workorder_id
AND OP.workorder_operation_id = p_workorder_operation_id;*/
SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = c_lookup_type
AND LOOKUP_CODE = c_status_code;
SELECT WO.workorder_id,
WO.object_version_number,
WO.wip_entity_id,
WDJ.organization_id,
WO.plan_id,
WO.collection_id,
WO.actual_start_date,
WO.actual_end_date,
WO.STATUS_CODE,
MLU.MEANING,
WO.route_id,
WDJ.COMPLETION_SUBINVENTORY,
WDJ.COMPLETION_LOCATOR_ID,
WO.visit_id,
WO.visit_task_id
FROM AHL_WORKORDERS WO, FND_LOOKUP_VALUES_VL MLU,WIP_DISCRETE_JOBS WDJ,
(SELECT ORGANIZATION_ID FROM INV_ORGANIZATION_INFO_V WHERE
NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
WHERE WDJ.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND MLU.LOOKUP_TYPE(+)='AHL_JOB_STATUS' AND WO.STATUS_CODE=MLU.LOOKUP_CODE(+)
AND WDJ.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND WO.workorder_id = c_workorder_id;
SELECT
UE.unit_effectivity_id,
UE.object_version_number,
NVL(VTS.instance_id, VST.item_instance_id),
CSI.lot_number,
CSI.serial_number,
CSI.quantity
FROM AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS, CSI_ITEM_INSTANCES CSI,
AHL_UNIT_EFFECTIVITIES_B UE
WHERE VTS.unit_effectivity_id = UE.unit_effectivity_id
AND NVL(VTS.instance_id, VST.item_instance_id) = CSI.instance_id
AND VST.visit_id = VTS.visit_id
AND VTS.visit_task_id = c_visit_task_id;
SELECT nvl (VST.ITEM_INSTANCE_ID, VTSINST.instance_id ),
CSI.lot_number,
CSI.serial_number,
CSI.quantity
FROM AHL_VISITS_B VST,CSI_ITEM_INSTANCES CSI,
(select instance_id from ahl_visit_tasks_b where visit_id = c_visit_id and instance_id IS NOT NULL AND rownum = 1) VTSINST
WHERE nvl (VST.ITEM_INSTANCE_ID, VTSINST.instance_id )= CSI.INSTANCE_ID
AND VST.visit_id = c_visit_id;
SELECT
MR.auto_signoff_flag
FROM
AHL_MR_HEADERS_APP_V MR,
AHL_VISIT_TASKS_B VT,
AHL_WORKORDERS WO
WHERE MR.MR_HEADER_ID = VT.MR_ID AND
WO.VISIT_TASK_ID = VT.visit_task_id AND
WO.workorder_id = c_workorder_id;*/
SELECT
MR.auto_signoff_flag
FROM
AHL_MR_HEADERS_APP_V MR,
AHL_VISIT_TASKS_B VT
--AHL_WORKORDERS WO
WHERE MR.MR_HEADER_ID = VT.MR_ID AND
VT.visit_task_id = c_visit_task_id;
/*SELECT workorder_id,
job_number,
object_version_number,
wip_entity_id,
organization_id,
plan_id,
collection_id,
actual_start_date,
actual_end_date,
job_status_code,
job_status_meaning,
route_id,
unit_effectivity_id,
ue_object_version_number,
--auto_signoff_flag,
--'Y',
item_instance_id,
completion_subinventory,
completion_locator_id,
lot_number,
serial_number,
quantity
INTO x_workorder_rec.workorder_id,
x_workorder_rec.workorder_name,
x_workorder_rec.object_version_number,
x_workorder_rec.wip_entity_id,
x_workorder_rec.organization_id,
x_workorder_rec.plan_id,
x_workorder_rec.collection_id,
x_workorder_rec.actual_start_date,
x_workorder_rec.actual_end_date,
x_workorder_rec.status_code,
x_workorder_rec.status,
x_workorder_rec.route_id,
x_workorder_rec.unit_effectivity_id,
x_workorder_rec.ue_object_version_number,
--x_workorder_rec.automatic_signoff_flag,
x_workorder_rec.item_instance_id,
x_workorder_rec.completion_subinventory,
x_workorder_rec.completion_locator_id,
x_workorder_rec.lot_number,
x_workorder_rec.serial_number,
x_workorder_rec.txn_quantity
FROM AHL_ALL_WORKORDERS_V
WHERE workorder_id = p_workorder_id;*/
SELECT period_start_date
FROM ORG_ACCT_PERIODS
WHERE open_flag = 'Y'
AND organization_id = c_organization_id
ORDER BY period_start_date;
SELECT schedule_close_date
FROM ORG_ACCT_PERIODS
WHERE open_flag = 'Y'
AND organization_id = c_organization_id
ORDER BY schedule_close_date DESC;
SELECT result_column_name,
enabled_flag,
displayed_flag
INTO l_result_column_name,
l_enabled_flag,
l_displayed_flag
FROM QA_PLAN_CHARS
WHERE plan_id = p_plan_id
AND char_id = p_char_id;
l_result_sql_stmt := 'SELECT ' || l_result_column_name || ' FROM QA_RESULTS_V WHERE collection_id = :c_collection_id AND occurrence = ( SELECT MAX( occurrence ) FROM QA_RESULTS WHERE collection_id = :c_collection_id )';
SELECT MAX ( TXN.creation_date )
FROM AHL_WORKORDER_MTL_TXNS TXN, AHL_WORKORDER_OPERATIONS OP
WHERE TXN.workorder_operation_id = OP.workorder_operation_id
AND OP.workorder_id = c_workorder_id;
SELECT transaction_type,
transaction_date
FROM EAM_JOB_COMPLETION_TXNS
WHERE wip_entity_id = p_wip_entity_id
ORDER BY transaction_date DESC;
SELECT organization_id
INTO l_organization_id
FROM QA_PLANS
WHERE plan_id = G_CTR_READING_PLAN_ID;
SELECT AHL_WORKORDER_TXNS_S.NEXTVAL
INTO l_workorder_txn_id
FROM DUAL;
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 (
l_workorder_txn_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
p_workorder_rec.workorder_id,
p_transaction_type_code,
p_workorder_rec.status_code,
p_workorder_rec.actual_start_date,
p_workorder_rec.actual_end_date,
p_workorder_rec.actual_start_date,
p_workorder_rec.actual_end_date,
p_workorder_rec.lot_number,
p_workorder_rec.completion_subinventory,
p_workorder_rec.completion_locator_id
);
FUNCTION update_ahl_workorder
(
p_workorder_rec IN workorder_rec_type,
p_status_code IN VARCHAR2
) RETURN VARCHAR2
IS
l_transaction_type_code NUMBER := 1;
UPDATE AHL_WORKORDERS
SET status_code = p_status_code,
object_version_number = 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_workorder_rec.workorder_id
AND object_version_number = p_workorder_rec.object_version_number;
END update_ahl_workorder;
SELECT MAX ( creation_date )
FROM AHL_WORKORDER_MTL_TXNS
WHERE workorder_operation_id = c_workorder_operation_id;
SELECT transaction_type,
transaction_date
FROM EAM_OP_COMPLETION_TXNS
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_operation_sequence_num
ORDER BY transaction_date DESC;
/*SELECT MEANING INTO l_op_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_OPERATION_STATUS'
AND lookup_code = p_operation_rec.status_code;
/*SELECT MEANING INTO l_job_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND lookup_code = p_workorder_rec.status_code;
SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL
INTO l_wo_operation_txn_id
FROM DUAL;
INSERT INTO AHL_WO_OPERATIONS_TXNS
(
WO_OPERATION_TXN_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE_CODE,
LOAD_TYPE_CODE,
WORKORDER_OPERATION_ID,
OP_ACTUAL_START_DATE,
OP_ACTUAL_END_DATE
) VALUES (
l_wo_operation_txn_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
p_operation_rec.workorder_operation_id,
l_transaction_type_code,
l_load_type_code,
p_operation_rec.actual_start_date,
p_operation_rec.actual_end_date
);
UPDATE AHL_WORKORDER_OPERATIONS
SET status_code = G_OP_STATUS_COMPLETE,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE workorder_operation_id = p_operation_rec.workorder_operation_id
AND object_version_number = p_operation_rec.object_version_number;
SELECT workorder_operation_id,
actual_start_date,
actual_end_date,
status_code,
status
FROM AHL_WORKORDER_OPERATIONS_V
WHERE workorder_id = c_workorder_id;
SELECT WO.workorder_name,
WO.status_code
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS WOR
WHERE WO.wip_entity_id = WOR.parent_object_id
AND WO.master_workorder_flag = 'N'
AND WO.status_code <> G_JOB_STATUS_DELETED
AND WOR.parent_object_type_id = 1
AND WOR.relationship_type = 2
AND WOR.child_object_type_id = 1
AND WOR.child_object_id = c_child_wip_entity_id;
/*SELECT MEANING INTO l_job_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND lookup_code = p_workorder_rec.status_code;
/*SELECT MEANING INTO l_job_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND lookup_code = p_workorder_rec.status_code;
SELECT csi_item_instance_id,
mr_header_id,
cs_incident_id,
mr_title,
status_code,
status,
qa_inspection_type,
actual_end_date,
plan_id,
collection_id
FROM AHL_MR_INSTANCES_V
WHERE unit_effectivity_id = c_unit_effectivity_id
AND object_version_number = c_ue_object_version_no;*/
SELECT mr_header_id,
unit_effectivity_id,
status_code
FROM AHL_UNIT_EFFECTIVITIES_B
WHERE unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE unit_effectivity_id = related_ue_id
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT'
);*/
SELECT mr_header_id,
unit_effectivity_id,
status_code
FROM AHL_UNIT_EFFECTIVITIES_B UE, (
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT'
)CH
WHERE UE.unit_effectivity_id = CH.related_ue_id;
SELECT DISTINCT
CTR.counter_id counter_id,
--CTR.counter_group_id counter_group_id,
CTR.DEFAULTED_GROUP_ID COUNTER_GROUP_ID,
--CTR.counter_value_id counter_value_id,
--NVL(CTR.net_reading, 0) net_reading,
--CTR.type type
CTR.COUNTER_TYPE type
--FROM CSI_CP_COUNTERS_V CTR
--WHERE CTR.customer_product_id = c_item_instance_id
FROM csi_counter_associations CCA, csi_counters_vl CTR
WHERE CCA.counter_id = CTR.counter_id
AND CCA.source_object_id = c_item_instance_id
AND CCA.source_object_code = 'CP'
ORDER BY CTR.counter_id;
SELECT NVL(CV.net_reading, 0) net_reading, cv.counter_value_id
FROM csi_counter_values_v cv
WHERE cv.counter_id = c_counter_id
ORDER by value_timestamp DESC;
SELECT
awo.actual_end_date
FROM
ahl_workorders awo
WHERE
awo.wip_entity_id = p_wip_entity_id;
SELECT
nvl(CCR.NET_READING,0) net_reading,
ccr.counter_value_id
FROM
CSI_COUNTERS_VL CC,
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = CC.COUNTER_ID
AND CC.COUNTER_ID = c_counter_id
AND CCR.VALUE_TIMESTAMP <= NVL(p_actual_date,CCR.VALUE_TIMESTAMP)
ORDER BY
CCR.VALUE_TIMESTAMP DESC;
SELECT * FROM (
SELECT
nvl(CCR.NET_READING,0) net_reading,
ccr.counter_value_id
FROM
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = c_counter_id
AND nvl(CCR.disabled_flag,'N') = 'N'
AND CCR.VALUE_TIMESTAMP <= NVL(p_actual_date,CCR.VALUE_TIMESTAMP)
ORDER BY
CCR.VALUE_TIMESTAMP DESC
)
WHERE ROWNUM < 2;
SELECT DISTINCT
CTR.counter_id counter_id,
--CTR.counter_group_id counter_group_id,
CTR.DEFAULTED_GROUP_ID COUNTER_GROUP_ID,
--NVL(CTR.net_reading, 0) net_reading,
CTR.COUNTER_TYPE type,
MRI.reset_value reset_value
--FROM CSI_CP_COUNTERS_V CTR, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE
FROM csi_counter_associations CCA, csi_counters_vl CTR, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE
WHERE CCA.counter_id = CTR.counter_id
AND CCA.source_object_id = c_item_instance_id
AND CCA.source_object_code = 'CP'
--AND CTR.counter_name = MRI.counter_name
AND CTR.counter_template_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_effectivity_id = c_mr_eff_id
AND MRE.mr_header_id = c_mr_header_id
ORDER BY CTR.counter_id, MRI.reset_value DESC;
SELECT
nvl(CCR.NET_READING,0) net_reading
FROM
CSI_COUNTERS_VL CC,
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = CC.COUNTER_ID
AND CC.COUNTER_ID = c_counter_id
AND CCR.VALUE_TIMESTAMP <= p_actual_date
ORDER by
CCR.value_timestamp DESC;
SELECT * FROM (
SELECT
nvl(CCR.NET_READING,0) net_reading
FROM
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = c_counter_id
AND nvl(CCR.disabled_flag,'N') = 'N'
AND CCR.VALUE_TIMESTAMP <= p_actual_date
ORDER by
CCR.value_timestamp DESC
)
WHERE ROWNUM < 2;
SELECT
nvl(CCR.NET_READING,0) net_reading
FROM
CSI_COUNTERS_VL CC,
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = CC.COUNTER_ID
AND CC.COUNTER_ID = c_counter_id
ORDER BY
CCR.VALUE_TIMESTAMP DESC;
SELECT
nvl(CCR.NET_READING,0) net_reading
FROM
CSI_COUNTERS_B CC,
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_VALUE_ID = CC.CTR_VAL_MAX_SEQ_NO
AND nvl(CCR.disabled_flag,'N') = 'N'
AND CC.COUNTER_ID = c_counter_id;
FUNCTION update_ump
(
p_unit_effectivity_id IN NUMBER,
p_ue_object_version IN NUMBER,
p_actual_end_date IN DATE,
p_counter_tbl IN counter_tbl_type,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) RETURN VARCHAR2
IS
l_return_status VARCHAR2(2000);
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
);
END update_ump;
PROCEDURE Delete_Serial_Reservations (p_workorder_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR get_scheduled_mater_csr (p_workorder_id IN NUMBER) IS
SELECT scheduled_material_id
FROM ahl_job_oper_materials_v
WHERE workorder_id = p_workorder_id
AND reserved_quantity > 0;
fnd_log.string(l_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WORKORDER_PVT.delete_serial_reservations.begin',
'At the start of procedure for workorder_id:' || p_workorder_id);
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 => x_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 );
fnd_log.string(l_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WORKORDER_PVT.delete_serial_reservations',
'AHL_RSV_RESERVATIONS_PVT.Delete_Reservation failed for schedule material ID: '
|| get_scheduled_mater_rec.scheduled_material_id);
END Delete_Serial_Reservations;
SELECT
WIOP.organization_id
FROM
WIP_OPERATIONS WIOP
WHERE
WIOP.wip_entity_id = p_wip_entity_id
AND WIOP.operation_seq_num = p_operation_seq_num;
SELECT
MIN(WIPT.TRANSACTION_DATE),
MAX(WIPT.TRANSACTION_DATE + (WIPT.TRANSACTION_QUANTITY/24))
FROM
WIP_TRANSACTIONS WIPT
WHERE
WIPT.wip_entity_id = p_wip_entity_id
AND WIPT.operation_seq_num = p_operation_seq_num
AND WIPT.organization_id = p_organization_id;
SELECT MIN(WIPT.TRANSACTION_DATE),
MAX(WIPT.TRANSACTION_DATE + (WIPT.TRANSACTION_QUANTITY/24))
FROM
WIP_COST_TXN_INTERFACE WIPT
WHERE
WIPT.wip_entity_id = p_wip_entity_id
AND WIPT.operation_seq_num = p_operation_seq_num
AND WIPT.organization_id = p_organization_id;
SELECT workorder_id,
Operation_sequence_num,
Actual_start_date,
Actual_end_date
FROM AHL_WORKORDER_OPERATIONS
WHERE workorder_operation_id = x_wo_op_id;
SELECT employee_id,
resource_seq_num
FROM ahl_work_login_times
WHERE workorder_id = p_workorder_id
AND OPERATION_SEQ_NUM = p_operation_seq_num
AND login_level IN ('O','R') -- logout only those employees who are logged in at oper, oper-resrc level.
AND LOGOUT_DATE IS NULL;
UPDATE AHL_WORKORDER_OPERATIONS
SET ACTUAL_START_DATE = l_operation_tbl(1).actual_start_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_OPERATION_ID = p_workorder_operation_id;
UPDATE AHL_WORKORDER_OPERATIONS
SET ACTUAL_END_DATE = l_operation_tbl(1).actual_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_OPERATION_ID = p_workorder_operation_id;
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
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS WOR
WHERE 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 DISTINCT WO.status_code status_code
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS WOR
WHERE WO.wip_entity_id = WOR.child_object_id
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.parent_object_id = c_master_wip_entity_id;
SELECT 1
FROM AHL_UE_DEFERRAL_DETAILS_V
WHERE unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE unit_effectivity_id = related_ue_id
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT'
);*/
SELECT 1
FROM AHL_UNIT_EFFECTIVITIES_B UE,(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT'
)CH
WHERE UE.unit_effectivity_id = CH.related_ue_id;
SELECT Actual_start_date,
Actual_end_date,
Workorder_name
FROM AHL_WORKORDERS
WHERE workorder_id = x_wo_id;
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;
AHL_PRD_WORKORDER_PVT.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 => 'Y' ,
p_x_prd_workorder_rec => l_up_workorder_rec ,
p_x_prd_workoper_tbl => l_up_workoper_tbl
);
UPDATE AHL_WORKORDERS
SET ACTUAL_START_DATE = l_def_actual_start_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_ID = p_workorder_id;
UPDATE AHL_WORKORDERS
SET ACTUAL_END_DATE = l_def_actual_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_ID = p_workorder_id;
update_ahl_workorder
(
p_workorder_rec => l_workorder_rec,
p_status_code => G_JOB_STATUS_COMPLETE
);
Delete_Serial_Reservations (p_workorder_id => p_workorder_id,
x_return_status => l_return_status);
update_ahl_workorder
(
p_workorder_rec => l_workorder_rec,
p_status_code => G_JOB_STATUS_COMPLETE
);
SELECT UE.unit_effectivity_id unit_effectivity_id,
UE.object_version_number ue_object_version_number
FROM AHL_MR_HEADERS_B MR,
AHL_UNIT_EFFECTIVITIES_B UE,
AHL_UE_RELATIONSHIPS REL
WHERE MR.mr_header_id = UE.mr_header_id
AND MR.auto_signoff_flag = 'Y'
AND UE.unit_effectivity_id = REL.ue_id
AND REL.related_ue_id = c_unit_effectivity_id
AND REL.relationship_code = 'PARENT';
update_ump
(
p_unit_effectivity_id => p_x_mr_rec.unit_effectivity_id,
p_ue_object_version => p_x_mr_rec.ue_object_version_no,
p_actual_end_date => p_x_mr_rec.actual_end_date,
p_counter_tbl => l_counter_tbl,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT UE.ump_status_code
FROM AHL_UE_DEFERRAL_DETAILS_V UE
WHERE UE.unit_effectivity_id = c_unit_effectivity_id;
SELECT DISTINCT DECODE( WO.status_code,
G_JOB_STATUS_CLOSED, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_PARTS_HOLD, G_JOB_STATUS_ON_HOLD,
G_JOB_STATUS_DEFERRAL_PENDING,G_JOB_STATUS_ON_HOLD,
WO.status_code ) status_code
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VT
WHERE WO.visit_task_id = VT.visit_task_id
AND WO.status_code <> G_JOB_STATUS_DELETED
AND WO.master_workorder_flag = 'N'
AND VT.unit_effectivity_id = c_unit_effectivity_id;
SELECT DISTINCT DECODE( CWO.status_code,
G_JOB_STATUS_CLOSED, decode(WIPJ.date_completed, null, G_JOB_STATUS_CANCELLED, G_JOB_STATUS_COMPLETE),
G_JOB_STATUS_COMPLETE_NC, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_PARTS_HOLD, G_JOB_STATUS_ON_HOLD,
G_JOB_STATUS_DEFERRAL_PENDING,G_JOB_STATUS_ON_HOLD,
CWO.status_code ) status_code
FROM AHL_WORKORDERS CWO, ahl_visit_tasks_b vst, wip_discrete_jobs WIPJ
where CWO.visit_task_id = vst.visit_task_id
AND vst.unit_effectivity_id = c_unit_effectivity_id
AND CWO.master_workorder_flag = 'N'
AND CWO.wip_entity_id in (SELECT REL.child_object_id
from WIP_SCHED_RELATIONSHIPS REL
START WITH REL.parent_object_id IN
(
SELECT PWO.wip_entity_id
FROM AHL_WORKORDERS PWO,
AHL_VISIT_TASKS_B VT,
AHL_VISITS_B VS
WHERE PWO.master_workorder_flag = 'Y'
AND PWO.visit_task_id = VT.visit_task_id
AND VS.VISIT_ID = VT.VISIT_ID
AND VS.STATUS_CODE NOT IN ('CLOSED','CANCELLED')
AND VT.unit_effectivity_id = c_unit_effectivity_id
)
AND REL.parent_object_type_id = 1
AND REL.relationship_type = 1
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
)
AND WIPJ.wip_entity_id = CWO.wip_entity_id;*/
SELECT DISTINCT DECODE( CWO.status_code,
G_JOB_STATUS_CLOSED, decode(WIPJ.date_completed, null, G_JOB_STATUS_CANCELLED, G_JOB_STATUS_COMPLETE),
G_JOB_STATUS_COMPLETE_NC, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_PARTS_HOLD, G_JOB_STATUS_ON_HOLD,
G_JOB_STATUS_DEFERRAL_PENDING,G_JOB_STATUS_ON_HOLD,
CWO.status_code ) status_code
FROM AHL_WORKORDERS CWO, ahl_visit_tasks_b vst, wip_discrete_jobs WIPJ
where CWO.visit_task_id = vst.visit_task_id
AND vst.unit_effectivity_id = c_unit_effectivity_id
AND vst.task_type_code = 'UNASSOCIATED'
AND WIPJ.wip_entity_id = CWO.wip_entity_id; */
SELECT DISTINCT DECODE( CWO.status_code,
G_JOB_STATUS_CLOSED, decode(WIPJ.date_completed, null, G_JOB_STATUS_CANCELLED, G_JOB_STATUS_COMPLETE),
G_JOB_STATUS_COMPLETE_NC, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_PARTS_HOLD, G_JOB_STATUS_ON_HOLD,
G_JOB_STATUS_DEFERRAL_PENDING,G_JOB_STATUS_ON_HOLD,
CWO.status_code ) status_code
FROM AHL_WORKORDERS CWO, ahl_visit_tasks_b vst, wip_discrete_jobs WIPJ
where CWO.visit_task_id = vst.visit_task_id
--AND vst.unit_effectivity_id = c_unit_effectivity_id
AND CWO.master_workorder_flag = 'N'
AND CWO.wip_entity_id in (SELECT REL.child_object_id
from WIP_SCHED_RELATIONSHIPS REL
START WITH REL.parent_object_id IN
(
SELECT PWO.wip_entity_id
FROM AHL_WORKORDERS PWO,
AHL_VISIT_TASKS_B VT,
AHL_VISITS_B VS
WHERE PWO.master_workorder_flag = 'Y'
AND PWO.visit_task_id = VT.visit_task_id
AND VS.VISIT_ID = VT.VISIT_ID
AND VS.STATUS_CODE NOT IN ('CLOSED','CANCELLED')
AND VT.unit_effectivity_id = c_unit_effectivity_id
)
AND REL.parent_object_type_id = 1
AND REL.relationship_type = 1
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
)
AND WIPJ.wip_entity_id = CWO.wip_entity_id;*/
SELECT
CWO.status_code
FROM AHL_WORKORDERS CWO, ahl_visit_tasks_b vst
where CWO.visit_task_id = vst.visit_task_id
AND CWO.master_workorder_flag = 'N'
AND CWO.status_code IN (p_status_code)
AND vst.unit_effectivity_id IN (select related_ue_id
from ahl_ue_relationships
start with ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
connect by prior related_ue_id = ue_id
AND relationship_code = 'PARENT'
union all
select c_unit_effectivity_id
from dual)
AND rownum < 2;
SELECT
CWO.status_code
FROM AHL_WORKORDERS CWO, ahl_visit_tasks_b vst
where CWO.visit_task_id = vst.visit_task_id
AND CWO.master_workorder_flag = 'N'
AND CWO.status_code IN (G_JOB_STATUS_PARTS_HOLD, G_JOB_STATUS_ON_HOLD,
G_JOB_STATUS_DEFERRAL_PENDING)
AND vst.unit_effectivity_id IN (select related_ue_id
from ahl_ue_relationships
start with ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
connect by prior related_ue_id = ue_id
AND relationship_code = 'PARENT'
union all
select c_unit_effectivity_id
from dual)
AND rownum < 2;
SELECT
CWO.status_code
FROM AHL_WORKORDERS CWO, ahl_visit_tasks_b vst, wip_discrete_jobs wipj
where CWO.visit_task_id = vst.visit_task_id
AND WIPJ.wip_entity_id = CWO.wip_entity_id
AND CWO.master_workorder_flag = 'N'
AND CWO.status_code IN (G_JOB_STATUS_CLOSED, G_JOB_STATUS_COMPLETE_NC, G_JOB_STATUS_COMPLETE)
AND WIPJ.date_completed IS NOT NULL
AND vst.unit_effectivity_id IN (select related_ue_id
from ahl_ue_relationships
start with ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
connect by prior related_ue_id = ue_id
AND relationship_code = 'PARENT'
union all
select c_unit_effectivity_id
from dual)
AND rownum < 2;
SELECT
CWO.status_code
FROM AHL_WORKORDERS CWO, ahl_visit_tasks_b vst, wip_discrete_jobs wipj
where CWO.visit_task_id = vst.visit_task_id
AND WIPJ.wip_entity_id = CWO.wip_entity_id
--AND vst.unit_effectivity_id = c_unit_effectivity_id
AND CWO.master_workorder_flag = 'N'
AND CWO.status_code IN (G_JOB_STATUS_CLOSED, G_JOB_STATUS_CANCELLED)
AND WIPJ.date_completed IS NULL
AND vst.unit_effectivity_id IN (select related_ue_id
from ahl_ue_relationships
start with ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
connect by prior related_ue_id = ue_id
AND relationship_code = 'PARENT'
union all
select c_unit_effectivity_id
from dual)
AND rownum < 2;
SELECT workorder_id,
workorder_name
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND MASTER_WORKORDER_FLAG = 'N';
SELECT
UE.title
FROM
ahl_workorders WO,
ahl_visit_tasks_b VTSK,
ahl_unit_effectivities_v UE
WHERE
WO.workorder_id = p_workorder_id
AND VTSK.visit_task_id = WO.visit_task_id
AND UE.unit_effectivity_id = VTSK.unit_effectivity_id;
FUNCTION update_mwo_actual_dates
(
p_wip_entity_id IN NUMBER,
p_default_flag IN VARCHAR2,
p_actual_start_date IN DATE,
p_actual_end_date IN DATE
) RETURN VARCHAR2
IS
-- To get the Child Workorder Details for a Master WO
CURSOR get_child_wos( c_wip_entity_id NUMBER ) IS
SELECT CWO.workorder_id workorder_id,
CWO.object_version_number object_version_number,
CWO.workorder_name workorder_name,
CWO.wip_entity_id wip_entity_id,
REL.parent_object_id parent_object_id,
CWO.actual_start_date actual_start_date,
CWO.actual_end_date actual_end_date,
CWO.status_code status_code,
CWO.master_workorder_flag master_workorder_flag
FROM AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL
WHERE CWO.wip_entity_id = REL.child_object_id
AND CWO.status_code <> G_JOB_STATUS_DELETED
AND 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;
l_api_name VARCHAR2(30) := 'update_mwo_actual_dates';
UPDATE AHL_WORKORDERS
SET object_version_number = object_version_number + 1,
actual_start_date = l_workorder_tbl(l_ctr).actual_start_date,
actual_end_date = l_workorder_tbl(l_ctr).actual_end_date
WHERE workorder_id = wo_csr.workorder_id
AND object_version_number = wo_csr.object_version_number;
UPDATE AHL_WORKORDERS
SET object_version_number = object_version_number + 1,
actual_start_date = l_min,
actual_end_date = l_max
WHERE wip_entity_id = p_wip_entity_id;
END update_mwo_actual_dates;
SELECT WO.wip_entity_id parent_we_id,
WO.workorder_name parent_wo_name,
DECODE( WO.status_code,
G_JOB_STATUS_COMPLETE, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_COMPLETE_NC, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CLOSED, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CANCELLED, G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_DELETED, G_JOB_STATUS_COMPLETE,
WO.status_code ) parent_status_code,
WOR.child_object_id child_we_id
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS WOR
WHERE WO.wip_entity_id = WOR.parent_object_id
AND WOR.top_level_object_id = c_wip_entity_id
AND WOR.relationship_type = 2
AND WOR.parent_object_type_id = 1
AND WOR.child_object_type_id = 1;
END LOOP; -- Match Update Parent Status Loop
SELECT Actual_start_date,
Actual_end_date,
Workorder_name
FROM AHL_WORKORDERS
WHERE workorder_id = x_wo_id;
SELECT UE.unit_effectivity_id unit_effectivity_id,
UE.title ue_title,
UE.object_version_number ue_object_version_number,
UE.ump_status_code ue_status_code,
UE.qa_inspection_type_code ue_qa_inspection_type_code,
UE.qa_plan_id ue_qa_plan_id,
UE.qa_collection_id ue_qa_collection_id,
WO.workorder_id workorder_id,
WIP.organization_id,
WO.object_version_number wo_object_version_number,
WO.workorder_name workorder_name,
WO.wip_entity_id wip_entity_id,
VWO.wip_entity_id visit_wip_entity_id,
VT.instance_id item_instance_id,
WIP.scheduled_start_date scheduled_start_date,
WIP.scheduled_completion_date scheduled_end_date,
WO.actual_start_date actual_start_date,
WO.actual_end_date actual_end_date,
WO.status_code wo_status_code,
WO.plan_id wo_plan_id,
WO.collection_id wo_collection_id,
WO.master_workorder_flag
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS WO,
AHL_WORKORDERS VWO,
AHL_VISIT_TASKS_B VT,
AHL_UE_DEFERRAL_DETAILS_V UE
WHERE WIP.wip_entity_id = WO.wip_entity_id
AND WO.visit_task_id = VT.visit_task_id
AND VWO.visit_task_id IS NULL
AND VWO.visit_id = VT.visit_id
AND VT.task_type_code IN ( 'SUMMARY' , 'UNASSOCIATED' )
AND VT.unit_effectivity_id = UE.unit_effectivity_id
AND UE.unit_effectivity_id = c_unit_effectivity_id;*/
SELECT UE.unit_effectivity_id unit_effectivity_id,
DECODE( UE.Mr_header_id, null,
(select cit.name || '-' || cs.incident_number from cs_incidents_all_vl cs, cs_incident_types_vl cit WHERE cs.incident_type_id = cit.incident_type_id AND cs.incident_id = UE.Cs_Incident_id),
(select title from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) ue_title,
UE.object_version_number ue_object_version_number,
UE.status_code ue_status_code,
--UE.qa_inspection_type_code ue_qa_inspection_type_code,
DECODE( UE.Mr_header_id, null,null,(select QA_INSPECTION_TYPE from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) ue_qa_inspection_type_code,
-1 ue_qa_plan_id,
UE.qa_collection_id ue_qa_collection_id,
WO.workorder_id workorder_id,
WIP.organization_id,
WO.object_version_number wo_object_version_number,
WO.workorder_name workorder_name,
WO.wip_entity_id wip_entity_id,
VWO.wip_entity_id visit_wip_entity_id,
VT.instance_id item_instance_id,
WIP.scheduled_start_date scheduled_start_date,
WIP.scheduled_completion_date scheduled_end_date,
WO.actual_start_date actual_start_date,
WO.actual_end_date actual_end_date,
WO.status_code wo_status_code,
WO.plan_id wo_plan_id,
WO.collection_id wo_collection_id,
WO.master_workorder_flag,
WIP.ORGANIZATION_ID org_id
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS WO,
AHL_WORKORDERS VWO,
AHL_VISIT_TASKS_B VT,
AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE WIP.wip_entity_id = WO.wip_entity_id
AND WO.visit_task_id = VT.visit_task_id
AND VWO.visit_task_id IS NULL
AND VWO.visit_id = VT.visit_id
AND VT.task_type_code IN ( 'SUMMARY' , 'UNASSOCIATED' )
AND VT.unit_effectivity_id = UE.unit_effectivity_id
AND UE.unit_effectivity_id = c_unit_effectivity_id;
SELECT qa.plan_id from qa_results qa
where qa.collection_id = p_collection_id and rownum < 2;
SELECT QP.plan_id FROM QA_PLANS_VAL_V QP, QA_PLAN_TRANSACTIONS QPT, QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE QP.plan_id = QPT.plan_id AND QPT.plan_transaction_id = QPCT.plan_transaction_id
AND QP.organization_id = p_org_id
AND QPT.transaction_number in (9999,2001)
AND QPCT.collection_trigger_id = 87
AND QPCT.low_value = p_qa_inspection_type
group by qp.plan_id, qpt.transaction_number having transaction_number = MAX(transaction_number);
SELECT unit_effectivity_id,
object_version_number,
title,
ump_status_code,
qa_inspection_type_code,
qa_plan_id,
qa_collection_id
FROM AHL_UE_DEFERRAL_DETAILS_V
WHERE unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE unit_effectivity_id = related_ue_id
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT'
);*/
SELECT unit_effectivity_id,
object_version_number,
DECODE( UE.Mr_header_id, null,
(select cit.name || '-' || cs.incident_number from cs_incidents_all_vl cs, cs_incident_types_vl cit WHERE cs.incident_type_id = cit.incident_type_id AND cs.incident_id = UE.Cs_Incident_id),
(select title from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) title,
status_code ump_status_code,
DECODE( UE.Mr_header_id, null,null,(select QA_INSPECTION_TYPE from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) qa_inspection_type_code,
-1 qa_plan_id,
qa_collection_id
FROM AHL_UNIT_EFFECTIVITIES_B UE,(SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT') CH
WHERE UE.unit_effectivity_id = CH.related_ue_id ORDER BY unit_effectivity_id ASC;
SELECT CWO.workorder_id workorder_id,
CWO.object_version_number object_version_number,
CWO.workorder_name workorder_name,
CWO.wip_entity_id wip_entity_id,
WIP.scheduled_start_date scheduled_start_date,
WIP.scheduled_completion_date scheduled_end_date,
CWO.actual_start_date actual_start_date,
CWO.actual_end_date actual_end_date,
CWO.status_code status_code,
CWO.master_workorder_flag master_workorder_flag,
CWO.plan_id plan_id,
CWO.collection_id collection_id
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL
WHERE WIP.wip_entity_id = CWO.wip_entity_id
AND CWO.wip_entity_id = REL.child_object_id
AND CWO.status_code <> G_JOB_STATUS_DELETED
AND 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 WOP.workorder_operation_id workorder_operation_id,
WOP.object_version_number object_version_number,
CWO.workorder_name workorder_name,
WIP.wip_entity_id wip_entity_id,
WIP.operation_seq_num operation_seq_num,
WIP.first_unit_start_date scheduled_start_date,
WIP.last_unit_completion_date scheduled_end_date,
WOP.actual_start_date actual_start_date,
WOP.actual_end_date actual_end_date,
WOP.status_code status_code,
WOP.plan_id plan_id,
WOP.collection_id collection_id
FROM AHL_WORKORDER_OPERATIONS WOP,
WIP_OPERATIONS WIP,
AHL_WORKORDERS CWO
WHERE WOP.operation_sequence_num = WIP.operation_seq_num
AND WOP.workorder_id = CWO.workorder_id
AND WIP.wip_entity_id = CWO.wip_entity_id
--AND CWO.status_code <> G_JOB_STATUS_DELETED
--Balaji added the check for BAE bug.
AND CWO.status_code NOT IN
(
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_DELETED
)
AND WIP.WIP_ENTITY_ID IN (
SELECT CWO.wip_entity_id
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL
WHERE WIP.wip_entity_id = CWO.wip_entity_id
AND CWO.wip_entity_id = REL.child_object_id
AND CWO.status_code <> G_JOB_STATUS_DELETED
AND 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 WOP.workorder_operation_id workorder_operation_id,
WOP.object_version_number object_version_number,
CWO.workorder_name workorder_name,
WIP.wip_entity_id wip_entity_id,
WIP.operation_seq_num operation_seq_num,
WIP.first_unit_start_date scheduled_start_date,
WIP.last_unit_completion_date scheduled_end_date,
WOP.actual_start_date actual_start_date,
WOP.actual_end_date actual_end_date,
WOP.status_code status_code,
WOP.plan_id plan_id,
WOP.collection_id collection_id
FROM AHL_WORKORDER_OPERATIONS WOP,
WIP_OPERATIONS WIP,
AHL_WORKORDERS CWO
WHERE WOP.operation_sequence_num = WIP.operation_seq_num
AND WOP.workorder_id = CWO.workorder_id
AND WIP.wip_entity_id = CWO.wip_entity_id
AND WIP.WIP_ENTITY_ID = c_wip_entity_id
AND CWO.MASTER_WORKORDER_FLAG = 'N'
--Balaji added the status check for BAE Bug
AND CWO.status_code NOT IN
(
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_DELETED
);
SELECT WOR.wip_entity_id wip_entity_id,
CWO.workorder_name,
CWO.workorder_id,
WOP.workorder_operation_id,
WOR.operation_seq_num operation_seq_num,
WOR.resource_seq_num resource_seq_num,
WOR.organization_id organization_id,
WOR.department_id department_id,
BOM.resource_code resource_name,
WOR.resource_id resource_id,
BOM.resource_type,
WOR.uom_code uom_code,
WOR.usage_rate_or_amount usage_rate_or_amount
FROM BOM_RESOURCES BOM,
WIP_OPERATION_RESOURCES WOR,
AHL_WORKORDER_OPERATIONS WOP,
AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL
WHERE BOM.resource_type IN ( 1 , 2 )
AND BOM.resource_id = WOR.resource_id
AND WOR.operation_seq_num = WOP.operation_sequence_num
AND WOR.wip_entity_id = CWO.wip_entity_id
AND WOP.status_code <> G_OP_STATUS_COMPLETE
AND WOP.workorder_id = CWO.workorder_id
AND CWO.status_code NOT IN
(
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_DELETED
)
AND CWO.wip_entity_id = REL.child_object_id
AND 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 WOR.wip_entity_id wip_entity_id,
CWO.workorder_name,
CWO.workorder_id,
WOP.workorder_operation_id,
WOR.operation_seq_num operation_seq_num,
WOR.resource_seq_num resource_seq_num,
WOR.organization_id organization_id,
WOR.department_id department_id,
BOM.resource_code resource_name,
WOR.resource_id resource_id,
BOM.resource_type,
WOR.uom_code uom_code,
WOR.usage_rate_or_amount usage_rate_or_amount
FROM BOM_RESOURCES BOM,
WIP_OPERATION_RESOURCES WOR,
AHL_WORKORDER_OPERATIONS WOP,
AHL_WORKORDERS CWO
WHERE BOM.resource_type IN ( 1 , 2 )
AND BOM.resource_id = WOR.resource_id
AND WOR.operation_seq_num = WOP.operation_sequence_num
AND WOR.wip_entity_id = CWO.wip_entity_id
AND WOP.status_code <> G_OP_STATUS_COMPLETE
AND WOP.workorder_id = CWO.workorder_id
AND CWO.status_code NOT IN
(
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_DELETED
)
AND CWO.wip_entity_id = c_wip_entity_id
AND CWO.MASTER_WORKORDER_FLAG = 'N';
SELECT NVL( SUM( transaction_quantity ), 0 )
FROM WIP_TRANSACTIONS
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_operation_seq_num
AND resource_seq_num = c_resource_seq_num;
SELECT NVL( SUM( transaction_quantity ), 0 )
FROM WIP_COST_TXN_INTERFACE
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_operation_seq_num
AND resource_seq_num = c_resource_seq_num
AND process_status = 1;
/*SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_PRD_MR_STATUS'
AND LOOKUP_CODE = l_mr_rec.ue_status_code;
/*SELECT MEANING INTO l_job_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND lookup_code = wo_csr.status_code;
/* SELECT person_id
INTO l_employee_id
FROM PER_PEOPLE_F
WHERE employee_number = p_signoff_mr_rec.employee_number
AND rownum = 1; */
SELECT employee_id
INTO l_employee_id
FROM mtl_employees_current_view
WHERE organization_id = l_mr_rec.organization_id
AND employee_num = p_signoff_mr_rec.employee_number
AND rownum = 1;
AHL_WIP_JOB_PVT.insert_resource_txn
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ahl_res_txn_tbl => l_res_txn_tbl
);
-- Update Actual Date only if it is empty
/* Bug # 4955278 - start */
IF ( l_unassoc_ue_op_rec.actual_end_date IS NULL ) THEN
IF ( p_signoff_mr_rec.default_actual_dates_flag = 'Y' ) THEN
-- Update with Scheduled End Date or SYSDATE
l_operation_tbl(l_ctr).actual_end_date := LEAST( SYSDATE , l_def_actual_end_date );
-- Update with User Entered Value
l_operation_tbl(l_ctr).actual_end_date := p_signoff_mr_rec.actual_end_date;
-- Update with DB Value
l_operation_tbl(l_ctr).actual_end_date := l_unassoc_ue_op_rec.actual_end_date;
-- Update Actual Date only if it is empty
IF ( l_unassoc_ue_op_rec.actual_start_date IS NULL ) THEN
IF ( p_signoff_mr_rec.default_actual_dates_flag = 'Y' ) THEN
-- Update with Scheduled Date
IF ( l_def_actual_start_date < SYSDATE ) THEN
l_operation_tbl(l_ctr).actual_start_date := l_def_actual_start_date;
-- Update with User Entered Value
l_operation_tbl(l_ctr).actual_start_date := p_signoff_mr_rec.actual_start_date;
-- Update with DB Value
l_operation_tbl(l_ctr).actual_start_date := l_unassoc_ue_op_rec.actual_start_date;
UPDATE AHL_WORKORDER_OPERATIONS
SET object_version_number = object_version_number + 1,
actual_start_date = l_operation_tbl(i).actual_start_date,
actual_end_date = l_operation_tbl(i).actual_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE workorder_operation_id = l_operation_tbl(i).workorder_operation_id
AND object_version_number = l_operation_tbl(i).object_version_number;
UPDATE AHL_WORKORDERS
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ACTUAL_START_DATE = l_def_actual_start_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_ID = l_workorder_tbl(i).workorder_id
AND OBJECT_VERSION_NUMBER = l_workorder_tbl(i).object_version_number;
UPDATE AHL_WORKORDERS
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ACTUAL_END_DATE = l_def_actual_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_ID = l_workorder_tbl(i).workorder_id
AND OBJECT_VERSION_NUMBER = l_workorder_tbl(i).object_version_number;
UPDATE AHL_WORKORDERS
SET object_version_number = object_version_number + 1,
actual_start_date = l_workorder_tbl(i).actual_start_date,
actual_end_date = l_workorder_tbl(i).actual_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE workorder_id = l_workorder_tbl(i).workorder_id
AND object_version_number = l_workorder_tbl(i).object_version_number;
update_mwo_actual_dates
(
p_wip_entity_id => l_mr_rec.visit_wip_entity_id,
p_default_flag => p_signoff_mr_rec.default_actual_dates_flag,
p_actual_start_date => l_actual_start_date,
p_actual_end_date => l_actual_end_date
);
/*SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_PRD_MR_STATUS'
AND LOOKUP_CODE = l_ue_status_code;
SELECT Actual_start_date,
Actual_end_date,
Workorder_name
FROM AHL_WORKORDERS
WHERE workorder_id = x_wo_id;
SELECT VST.visit_id visit_id,
VST.visit_number visit_number,
VST.object_version_number object_version_number,
VST.status_code status_code,
WO.workorder_id workorder_id,
WO.object_version_number wo_object_version_number,
WO.workorder_name workorder_name,
WIP.organization_id,
WO.wip_entity_id wip_entity_id,
VST.item_instance_id item_instance_id,
WIP.scheduled_start_date scheduled_start_date,
WIP.scheduled_completion_date scheduled_end_date,
WO.actual_start_date actual_start_date,
WO.actual_end_date actual_end_date,
WO.status_code wo_status_code,
WO.plan_id wo_plan_id,
WO.collection_id wo_collection_id
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS WO,
AHL_VISITS_B VST
WHERE WIP.wip_entity_id = WO.wip_entity_id
AND WO.visit_task_id IS NULL
AND WO.master_workorder_flag = 'Y'
AND WO.visit_id = VST.visit_id
AND VST.visit_id = c_visit_id;
SELECT UE.unit_effectivity_id unit_effectivity_id,
UE.title title,
UE.object_version_number object_version_number,
UE.ump_status_code ump_status_code,
UE.qa_inspection_type_code qa_inspection_type_code,
UE.qa_plan_id qa_plan_id,
UE.qa_collection_id qa_collection_id
FROM AHL_UE_DEFERRAL_DETAILS_V UE,
AHL_VISIT_TASKS_B VT
WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
AND ((VT.originating_task_id IS NULL
AND VT.task_type_code = 'SUMMARY')
OR (TASK_TYPE_CODE = 'UNASSOCIATED' ))
AND VT.visit_id = c_visit_id;
SELECT UE.unit_effectivity_id unit_effectivity_id,
UE.title title,
UE.object_version_number object_version_number,
UE.ump_status_code ump_status_code,
UE.qa_inspection_type_code qa_inspection_type_code,
UE.qa_plan_id qa_plan_id,
UE.qa_collection_id qa_collection_id
FROM AHL_UE_DEFERRAL_DETAILS_V UE,
AHL_VISIT_TASKS_B VT,
ahl_workorders awo
WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
AND ( (VT.task_type_code = 'SUMMARY')
OR (TASK_TYPE_CODE = 'UNASSOCIATED' ) )
AND vt.visit_task_id = awo.visit_task_id
and awo.wip_entity_id in ( SELECT
wsch.child_object_id
FROM
wip_sched_relationships wsch,
ahl_workorders awo1
WHERE
wsch.parent_object_id = awo1.wip_entity_id
and awo1.visit_task_id is null
and awo1.master_workorder_flag = 'Y'
and awo1.visit_id = c_visit_id
);*/
SELECT UE.unit_effectivity_id unit_effectivity_id,
DECODE( UE.Mr_header_id, null,
(select cit.name || '-' || cs.incident_number from cs_incidents_all_vl cs, cs_incident_types_vl cit WHERE cs.incident_type_id = cit.incident_type_id AND cs.incident_id = UE.Cs_Incident_id),
(select title from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) title,
UE.object_version_number object_version_number,
UE.status_code ump_status_code,
DECODE( UE.Mr_header_id, null,null,(select QA_INSPECTION_TYPE from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) qa_inspection_type_code,
-1 qa_plan_id,
UE.qa_collection_id qa_collection_id
FROM AHL_UNIT_EFFECTIVITIES_B UE,
AHL_VISIT_TASKS_B VT,
ahl_workorders awo
WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
AND ( (VT.task_type_code = 'SUMMARY')
OR (TASK_TYPE_CODE = 'UNASSOCIATED' ) )
AND vt.visit_task_id = awo.visit_task_id
and awo.wip_entity_id in ( SELECT
wsch.child_object_id
FROM
wip_sched_relationships wsch,
ahl_workorders awo1
WHERE
wsch.parent_object_id = awo1.wip_entity_id
and awo1.visit_task_id is null
and awo1.master_workorder_flag = 'Y'
and awo1.visit_id = c_visit_id
);
SELECT qa.plan_id from qa_results qa
where qa.collection_id = p_collection_id and rownum < 2;
SELECT QP.plan_id FROM QA_PLANS_VAL_V QP, QA_PLAN_TRANSACTIONS QPT, QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE QP.plan_id = QPT.plan_id AND QPT.plan_transaction_id = QPCT.plan_transaction_id
AND QP.organization_id = p_org_id
AND QPT.transaction_number in (9999,2001)
AND QPCT.collection_trigger_id = 87
AND QPCT.low_value = p_qa_inspection_type
group by qp.plan_id, qpt.transaction_number having transaction_number = MAX(transaction_number);
SELECT unit_effectivity_id,
object_version_number,
title,
ump_status_code,
qa_inspection_type_code,
qa_plan_id,
qa_collection_id
FROM AHL_UE_DEFERRAL_DETAILS_V
WHERE unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE unit_effectivity_id = related_ue_id
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT'
);*/
SELECT unit_effectivity_id,
object_version_number,
DECODE( UE.Mr_header_id, null,
(select cit.name || '-' || cs.incident_number from cs_incidents_all_vl cs, cs_incident_types_vl cit WHERE cs.incident_type_id = cit.incident_type_id AND cs.incident_id = UE.Cs_Incident_id),
(select title from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) title,
status_code ump_status_code,
DECODE( UE.Mr_header_id, null,null,(select QA_INSPECTION_TYPE from AHL_MR_HEADERS_B MR where MR.mr_header_id = UE.mr_header_id )) qa_inspection_type_code,
-1 qa_plan_id,
qa_collection_id
FROM AHL_UNIT_EFFECTIVITIES_B UE,(SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT') CH
WHERE UE.unit_effectivity_id = CH.related_ue_id ORDER BY unit_effectivity_id ASC;
SELECT WO.workorder_id workorder_id,
WO.object_version_number object_version_number,
WO.workorder_name workorder_name,
WO.wip_entity_id wip_entity_id,
WIP.scheduled_start_date scheduled_start_date,
WIP.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,
WO.master_workorder_flag master_workorder_flag,
WO.plan_id plan_id,
WO.collection_id collection_id
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS WO
WHERE WIP.wip_entity_id = WO.wip_entity_id
AND WO.status_code <> G_JOB_STATUS_DELETED
AND WO.visit_task_id IS NOT NULL
AND WO.visit_id = c_visit_id
ORDER BY WO.master_workorder_flag;
SELECT WOP.workorder_operation_id workorder_operation_id,
WOP.object_version_number object_version_number,
WO.workorder_name workorder_name,
WIP.wip_entity_id wip_entity_id,
WIP.operation_seq_num operation_seq_num,
WIP.first_unit_start_date scheduled_start_date,
WIP.last_unit_completion_date scheduled_end_date,
WOP.actual_start_date actual_start_date,
WOP.actual_end_date actual_end_date,
WOP.status_code status_code,
WOP.plan_id plan_id,
WOP.collection_id collection_id
FROM AHL_WORKORDER_OPERATIONS WOP,
WIP_OPERATIONS WIP,
AHL_WORKORDERS WO
WHERE WOP.operation_sequence_num = WIP.operation_seq_num
AND WOP.workorder_id = WO.workorder_id
AND WIP.wip_entity_id = WO.wip_entity_id
AND WO.status_code NOT IN
(
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_DELETED
)
AND WO.visit_task_id IS NOT NULL
AND WO.visit_id = c_visit_id;
SELECT WOR.wip_entity_id wip_entity_id,
WO.workorder_name,
WO.workorder_id,
WOP.workorder_operation_id,
WOR.operation_seq_num operation_seq_num,
WOR.resource_seq_num resource_seq_num,
WOR.organization_id organization_id,
WOR.department_id department_id,
BOM.resource_code resource_name,
WOR.resource_id resource_id,
BOM.resource_type,
WOR.uom_code uom_code,
WOR.usage_rate_or_amount usage_rate_or_amount
FROM BOM_RESOURCES BOM,
WIP_OPERATION_RESOURCES WOR,
AHL_WORKORDER_OPERATIONS WOP,
AHL_WORKORDERS WO
WHERE BOM.resource_type IN ( 1 , 2 )
AND BOM.resource_id = WOR.resource_id
AND WOR.operation_seq_num = WOP.operation_sequence_num
AND WOR.wip_entity_id = WO.wip_entity_id
AND WOP.status_code <> G_OP_STATUS_COMPLETE
AND WOP.workorder_id = WO.workorder_id
AND WO.status_code NOT IN
(
G_JOB_STATUS_COMPLETE_NC,
G_JOB_STATUS_COMPLETE,
G_JOB_STATUS_CLOSED,
G_JOB_STATUS_CANCELLED,
G_JOB_STATUS_DELETED
)
AND WO.visit_task_id IS NOT NULL
AND WO.visit_id = c_visit_id;
SELECT NVL( SUM( transaction_quantity ), 0 )
FROM WIP_TRANSACTIONS
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_operation_seq_num
AND resource_seq_num = c_resource_seq_num;
SELECT NVL( SUM( transaction_quantity ), 0 )
FROM WIP_COST_TXN_INTERFACE
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_operation_seq_num
AND resource_seq_num = c_resource_seq_num
AND process_status = 1;
/*SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND lookup_code = wo_csr.status_code;
SELECT person_id
INTO l_employee_id
FROM PER_ALL_PEOPLE_F
WHERE employee_number = p_close_visit_rec.employee_number
AND rownum = 1;
SELECT employee_id
INTO l_employee_id
FROM mtl_employees_current_view
WHERE organization_id = l_visit_rec.organization_id
AND employee_num = p_close_visit_rec.employee_number
AND rownum = 1;
AHL_WIP_JOB_PVT.insert_resource_txn
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ahl_res_txn_tbl => l_res_txn_tbl
);
UPDATE AHL_WORKORDER_OPERATIONS
SET object_version_number = object_version_number + 1,
actual_start_date = l_operation_tbl(i).actual_start_date,
actual_end_date = l_operation_tbl(i).actual_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE workorder_operation_id = l_operation_tbl(i).workorder_operation_id
AND object_version_number = l_operation_tbl(i).object_version_number;
UPDATE AHL_WORKORDERS
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ACTUAL_START_DATE = l_def_actual_start_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_ID = l_workorder_tbl(i).workorder_id
AND OBJECT_VERSION_NUMBER = l_workorder_tbl(i).object_version_number;
UPDATE AHL_WORKORDERS
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ACTUAL_END_DATE = l_def_actual_end_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE WORKORDER_ID = l_workorder_tbl(i).workorder_id
AND OBJECT_VERSION_NUMBER = l_workorder_tbl(i).object_version_number;
UPDATE AHL_WORKORDERS
SET object_version_number = object_version_number + 1,
actual_start_date = l_workorder_tbl(i).actual_start_date,
actual_end_date = l_workorder_tbl(i).actual_end_date
WHERE workorder_id = l_workorder_tbl(i).workorder_id
AND object_version_number = l_workorder_tbl(i).object_version_number;
update_mwo_actual_dates
(
p_wip_entity_id => l_visit_rec.wip_entity_id,
p_default_flag => p_close_visit_rec.default_actual_dates_flag,
p_actual_start_date => l_actual_start_date,
p_actual_end_date => l_actual_end_date
);
/*SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_PRD_MR_STATUS'
AND LOOKUP_CODE = l_ue_status_code;
SELECT
workorder_operation_id
FROM
AHL_WORKORDER_OPERATIONS
WHERE
workorder_id = p_workorder_id AND
operation_sequence_num = p_op_seq_no;
SELECT --UE.unit_effectivity_id unit_effectivity_id,
--UE.title ue_title,
--UE.ump_status_code ue_status_code,
UE.status_code ue_status_code,
--UE.qa_inspection_type_code ue_qa_inspection_type_code,
--UE.qa_plan_id ue_qa_plan_id,
--UE.qa_collection_id ue_qa_collection_id,
WO.workorder_id workorder_id,
WO.wip_entity_id wip_entity_id
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VT,
--AHL_UE_DEFERRAL_DETAILS_V UE
AHL_UNIT_EFFECTIVITIES_B UE
WHERE WO.visit_task_id = VT.visit_task_id
AND VT.task_type_code IN ( 'SUMMARY' , 'UNASSOCIATED' )
AND VT.unit_effectivity_id = UE.unit_effectivity_id
AND UE.unit_effectivity_id = c_unit_effectivity_id;
SELECT unit_effectivity_id,
title,
ump_status_code,
qa_inspection_type_code,
qa_plan_id,
qa_collection_id
FROM AHL_UE_DEFERRAL_DETAILS_V
WHERE unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE unit_effectivity_id = related_ue_id
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT'
);*/
SELECT CWO.workorder_id workorder_id
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL
WHERE WIP.wip_entity_id = CWO.wip_entity_id
AND CWO.wip_entity_id = REL.child_object_id
AND CWO.status_code NOT IN (G_JOB_STATUS_DELETED, G_JOB_STATUS_COMPLETE, G_JOB_STATUS_COMPLETE_NC, G_JOB_STATUS_CANCELLED, G_JOB_STATUS_CLOSED)
AND 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 WOP.workorder_id workorder_id,
WIP.operation_seq_num operation_seq_num
FROM AHL_WORKORDER_OPERATIONS WOP,
WIP_OPERATIONS WIP,
AHL_WORKORDERS CWO
WHERE WOP.operation_sequence_num = WIP.operation_seq_num
AND WOP.workorder_id = CWO.workorder_id
AND WIP.wip_entity_id = CWO.wip_entity_id
AND WIP.WIP_ENTITY_ID IN (
SELECT CWO.wip_entity_id
FROM WIP_DISCRETE_JOBS WIP,
AHL_WORKORDERS CWO,
WIP_SCHED_RELATIONSHIPS REL
WHERE WIP.wip_entity_id = CWO.wip_entity_id
AND CWO.wip_entity_id = REL.child_object_id
AND CWO.status_code <> G_JOB_STATUS_DELETED
AND 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 WORKORDER_OPERATION_ID,
OBJECT_VERSION_NUMBER,
SCHEDULED_START_DATE,
SCHEDULED_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE
FROM AHL_WORKORDER_OPERATIONS_V
WHERE WORKORDER_ID = x_workorder_id
AND OPERATION_SEQUENCE_NUM = x_operation_seq_num;
SELECT WIP_ENTITY_ID
FROM AHL_WORKORDERS
WHERE WORKORDER_ID = x_workorder_id;
SELECT MIN(WIPT.TRANSACTION_DATE),
MAX(WIPT.TRANSACTION_DATE + (WIPT.TRANSACTION_QUANTITY/24))
-- Using the transaction quantity above since we know the
-- transaction uom is in hours
FROM WIP_TRANSACTIONS WIPT,
BOM_RESOURCES BOMR
WHERE WIPT.RESOURCE_ID = BOMR.RESOURCE_ID
AND WIPT.WIP_ENTITY_ID = x_wip_entity_id
AND WIPT.OPERATION_SEQ_NUM = x_operation_seq_num
AND BOMR.RESOURCE_TYPE = 2; -- Person
SELECT MIN(WIPT.TRANSACTION_DATE),
MAX(WIPT.TRANSACTION_DATE + (WIPT.TRANSACTION_QUANTITY/24))
FROM WIP_COST_TXN_INTERFACE WIPT
WHERE WIPT.WIP_ENTITY_ID = x_wip_entity_id
AND WIPT.OPERATION_SEQ_NUM = x_operation_seq_num
AND WIPT.RESOURCE_TYPE = 2 -- Person
AND WIPT.PROCESS_STATUS = 1; -- Pending
SELECT ACTUAL_START_DATE,
ACTUAL_END_DATE,
WORKORDER_OPERATION_ID
FROM AHL_WORKORDER_OPERATIONS
WHERE WORKORDER_ID = x_workorder_id
AND OPERATION_SEQUENCE_NUM = x_operation_seq_num;
SELECT MIN(ACTUAL_START_DATE),
MAX(ACTUAL_END_DATE)
FROM AHL_WORKORDER_OPERATIONS
WHERE WORKORDER_ID = x_workorder_id;
SELECT MASTER_WORKORDER_FLAG
FROM AHL_WORKORDERS
WHERE WORKORDER_ID = x_workorder_id;
SELECT ( CASE
WHEN UE.STATUS_CODE IN ('ACCOMPLISHED', 'DEFERRED', 'TERMINATED','CANCELLED')
THEN UE.STATUS_CODE
WHEN UE.orig_deferral_ue_id IS NOT NULL
THEN ORIG_DEF.approval_status_code
WHEN DEF.APPROVAL_STATUS_CODE IS NOT NULL
THEN DEF.APPROVAL_STATUS_CODE
ELSE UE.STATUS_CODE
END)UMP_STATUS_CODE
FROM AHL_UNIT_DEFERRALS_B ORIG_DEF,AHL_UNIT_DEFERRALS_B
DEF,AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE UE.orig_deferral_ue_id = orig_def.unit_effectivity_id(+)
AND orig_def.unit_deferral_type(+) = 'DEFERRAL'
AND UE.unit_effectivity_id = def.unit_effectivity_id(+)
AND def.unit_deferral_type(+) = 'DEFERRAL'
AND UE.unit_effectivity_id = c_unit_effectivity_id;
SELECT workorder_id, object_version_number, wip_entity_id
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id
AND master_workorder_flag = 'Y'
--AND status_code NOT IN ('7', '22', '4', '12', '5')
AND VISIT_TASK_ID IS NULL;
SELECT WO.workorder_id,
WO.object_version_number, VTS.task_type_code,
WO.wip_entity_id
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VTS
WHERE WO.visit_task_id = VTS.visit_task_id
AND VTS.unit_effectivity_id = c_ue_id
--AND WO.status_code NOT IN ('7', '22', '4', '12', '5')
AND VTS.task_type_code IN ('SUMMARY', 'UNASSOCIATED');
SELECT object_version_number, wip_entity_id
FROM AHL_WORKORDERS
WHERE workorder_id = c_workorder_id;
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);
SELECT
AWO.workorder_id,
AWO.workorder_name,
AWO.visit_task_id,
AWO.master_workorder_flag,
AWO.object_version_number,
'Y' valid_for_close,
WIPJ.scheduled_start_date,
WIPJ.scheduled_completion_date,
AWO.actual_start_date,
AWO.actual_end_date,
WIPJ.completion_subinventory,
WIPJ.completion_locator_id,
AWO.security_group_id,
AWO.attribute_category,
AWO.attribute1,
AWO.attribute2,
AWO.attribute3,
AWO.attribute4,
AWO.attribute5,
AWO.attribute6,
AWO.attribute7,
AWO.attribute8,
AWO.attribute9,
AWO.attribute10,
AWO.attribute11,
AWO.attribute12,
AWO.attribute13,
AWO.attribute14,
AWO.attribute15
FROM
AHL_WORKORDERS AWO,
WIP_DISCRETE_JOBS WIPJ,
WIP_ENTITIES WIPE
WHERE
AWO.status_code in (4,5,7)
AND AWO.wip_entity_id = WIPJ.wip_entity_id
AND WIPE.entity_type = 7
AND WIPE.wip_entity_id = WIPJ.wip_entity_id
AND WIPJ.status_type = 12;
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));
fnd_file.put_line(fnd_file.log, 'Total Work Orders selected for processing -> '||l_workorder_id_tbl.COUNT);
FND_MSG_PUB.Delete_Msg;
-- 3. Update the status of all materials of the Work Order to History before closing them.
-- Call LTP API to update material requirement status to History.
IF l_master_workorder_flag_tbl(l_index) = 'N'
THEN
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 => l_visit_task_id_tbl(l_index),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
-- Update Eligigble Work Orders
BEGIN
FORALL l_count IN l_workorder_id_tbl.FIRST .. l_workorder_id_tbl.LAST
SAVE EXCEPTIONS
UPDATE
AHL_WORKORDERS
SET
status_code = 12,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE
workorder_id = l_workorder_id_tbl(l_count)
AND object_version_number = l_object_version_number_tbl(l_count)
AND l_valid_for_close_tbl(l_count) = 'Y';
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,
l_object_version_number_tbl(l_txn_count) + 1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_workorder_id_tbl(l_txn_count),
0,
12, -- this is close workorder. On successful update the Workorder will be in status 12.
l_wo_sch_str_tbl(l_txn_count),
l_wo_sch_end_tbl(l_txn_count),
l_wo_act_str_tbl(l_txn_count),
l_wo_act_end_tbl(l_txn_count),
NULL,
l_wo_comp_subinv_tbl(l_txn_count),
l_wo_comp_loc_id_tbl(l_txn_count),
l_wo_sc_grp_id_tbl(l_txn_count),
l_wo_att_category_tbl(l_txn_count),
l_wo_att_1_tbl(l_txn_count),
l_wo_att_2_tbl(l_txn_count),
l_wo_att_3_tbl(l_txn_count),
l_wo_att_4_tbl(l_txn_count),
l_wo_att_5_tbl(l_txn_count),
l_wo_att_6_tbl(l_txn_count),
l_wo_att_7_tbl(l_txn_count),
l_wo_att_8_tbl(l_txn_count),
l_wo_att_9_tbl(l_txn_count),
l_wo_att_10_tbl(l_txn_count),
l_wo_att_11_tbl(l_txn_count),
l_wo_att_12_tbl(l_txn_count),
l_wo_att_13_tbl(l_txn_count),
l_wo_att_14_tbl(l_txn_count),
l_wo_att_15_tbl(l_txn_count)
);
DELETE
ahl_workorder_txns WOTXNO
WHERE
wotxno.workorder_txn_id = (
select
MAX(wotxn.workorder_txn_id)
from
AHL_WORKORDER_TXNS WOTXN
WHERE
wotxn.workorder_id = l_workorder_id_tbl(l_txn_del_count)
AND l_valid_for_close_tbl(l_txn_del_count) = 'N'
);
IS SELECT
UE.unit_effectivity_id,
UE.object_version_number,
UE.csi_item_instance_id,
UE.mr_header_id,
UE.cs_incident_id,
--FL.Lookup_code,
--FL.Meaning,
UE.qa_collection_id
from ahl_unit_effectivities_b UE--, FND_LOOKUP_VALUES_VL FL
where --FL.Lookup_type = 'AHL_PRD_MR_STATUS'
--AND FL.Lookup_code = AHL_COMPLETIONS_PVT.get_mr_status( p_unit_effectivity_id ) AND
UE.unit_effectivity_id = p_unit_effectivity_id
AND UE.object_version_number = p_ue_object_version_no;
Select FL.Meaning FROM FND_LOOKUP_VALUES_VL FL
Where FL.Lookup_type = 'AHL_PRD_MR_STATUS'
AND FL.Lookup_code = p_status_code;
IS select title, QA_INSPECTION_TYPE from AHL_MR_HEADERS_B MR where MR.mr_header_id = p_mr_header_id;
IS select cit.name || '-' || cs.incident_number
from cs_incidents_all_vl cs, cs_incident_types_vl cit
WHERE cs.incident_type_id = cit.incident_type_id
AND cs.incident_id = p_cs_incident_id;
SELECT VST.ORGANIZATION_ID,VT.visit_task_id
FROM AHL_VISIT_TASKS_B VT, AHL_VISITS_B VST
WHERE VT.TASK_TYPE_CODE IN ( 'SUMMARY' , 'UNASSOCIATED' )
AND VST.VISIT_ID = VT.VISIT_ID
AND VT.UNIT_EFFECTIVITY_ID = p_unit_effectivity_id;
SELECT wo.actual_end_date FROM ahl_workorders WO
WHERE WO.visit_task_id = p_visit_task_id
AND master_workorder_flag = 'Y';
SELECT qa.plan_id from qa_results qa
where qa.collection_id = p_collection_id and rownum < 2;
SELECT QP.plan_id FROM QA_PLANS_VAL_V QP, QA_PLAN_TRANSACTIONS QPT, QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE QP.plan_id = QPT.plan_id AND QPT.plan_transaction_id = QPCT.plan_transaction_id
AND QP.organization_id = p_org_id
AND QPT.transaction_number in (9999,2001)
AND QPCT.collection_trigger_id = 87
AND QPCT.low_value = p_qa_inspection_type
group by qp.plan_id, qpt.transaction_number having transaction_number = MAX(transaction_number);
SELECT 'x' from ahl_workorder_operations
WHERE status_code = p_status_code
AND workorder_id = p_workorder_id
AND rownum < 2;