[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
timezone_code
FROM
fnd_timezones_vl
WHERE
upgrade_tz_id = fnd_profile.VALUE('SERVER_TIMEZONE_ID');
SELECT substr(tz_offset(c_tz_code),0,6)
FROM
dual;
SELECT
wo.job_number,
wo.job_description,
wo.job_status_meaning,
wo.visit_number,
wo.organization_name,
wo.department_name,
decode(wo.scheduled_start_date, null, null, TO_CHAR(wo.scheduled_start_date,''YYYY-MM-DD'')||''T''||TO_CHAR(wo.scheduled_start_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) scheduled_start_date,
decode(wo.scheduled_end_date, null, null, TO_CHAR(wo.scheduled_end_date, ''YYYY-MM-DD'')||''T''||TO_CHAR(wo.scheduled_end_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) scheduled_end_date,
decode(wo.actual_start_date, null, null, TO_CHAR(wo.actual_start_date,''YYYY-MM-DD'')||''T''||TO_CHAR(wo.actual_start_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset )actual_start_date,
decode(wo.actual_end_date, null, null, TO_CHAR(wo.actual_end_date, ''YYYY-MM-DD'')||''T''||TO_CHAR(wo.actual_end_date, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) actual_end_date,
wo.unit_name,
wo.wo_part_number,
wo.serial_number,
wo.mr_title,
ro.ROUTE_NO route_title
FROM
AHL_SEARCH_WORKORDERS_V wo,
ahl_routes_app_v ro, ';
SELECT
decode(ENTERED_DATE, null, null, TO_CHAR(ENTERED_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(ENTERED_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) ENTERED_DATE,
ENTERED_BY_NAME ,
notes
FROM
JTF_NOTES_VL
WHERE
source_object_code = ''AHL_WO_TURNOVER_NOTES'' and
source_object_id = ';
SELECT
OPERATION_SEQUENCE_NUM,
OPERATION_CODE,
DESCRIPTION,
STATUS,';
SELECT
CONCATENATED_SEGMENTS,
OPERATION_SEQUENCE,
DESCRIPTION,
REQUESTED_QUANTITY required_quantity,
decode(REQUESTED_DATE, null, null, TO_CHAR(REQUESTED_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(REQUESTED_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) required_date,
SCHEDULE_QUANTITY,
decode(SCHEDULE_DATE, null, null, TO_CHAR(SCHEDULE_DATE, ''YYYY-MM-DD'')||''T''||TO_CHAR(SCHEDULE_DATE, ''hh24:mi:ss'')||AHL_PRD_PRINT_PVT.get_tz_offset ) SCHEDULE_DATE,
ISSUED_QTY,
UOM
FROM
AHL_JOB_OPER_MATERIALS_V
WHERE
WORKORDER_ID = ';
SELECT
DOC.DOCUMENT_NO,
DOC.DOCUMENT_TITLE,
DOC.ASO_OBJECT_TYPE_DESC,
DOC.REVISION_NO,
DOC.CHAPTER,
DOC.SECTION,
DOC.SUBJECT,
DOC.PAGE,
DOC.FIGURE,
DOC.NOTE
FROM
AHL_WORKORDERS WO,
AHL_DOCUMENT_ASSOS_V DOC
WHERE
WO.ROUTE_ID = DOC.ASO_OBJECT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''ROUTE''
AND WO.WORKORDER_ID = ';
SELECT
DOC.DOCUMENT_NO,
DOC.DOCUMENT_TITLE,
DOC.ASO_OBJECT_TYPE_DESC,
DOC.REVISION_NO,
DOC.CHAPTER,
DOC.SECTION,
DOC.SUBJECT,
DOC.PAGE,
DOC.FIGURE,
DOC.NOTE
FROM
AHL_WORKORDER_OPERATIONS WOP,
AHL_DOCUMENT_ASSOS_V DOC
WHERE
WOP.OPERATION_ID = DOC.ASO_OBJECT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''OPERATION''
AND WOP.WORKORDER_ID = ';
SELECT
DOC.DOCUMENT_NO,
DOC.DOCUMENT_TITLE,
DOC.ASO_OBJECT_TYPE_DESC,
DOC.REVISION_NO,
DOC.CHAPTER,
DOC.SECTION,
DOC.SUBJECT,
DOC.PAGE,
DOC.FIGURE,
DOC.NOTE
FROM
AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VST,
AHL_DOCUMENT_ASSOS_V DOC
WHERE
WO.VISIT_TASK_ID = VST.VISIT_TASK_ID
AND VST.MR_ID = DOC.ASO_OBJECT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''MR''
AND WO.WORKORDER_ID = ';
SELECT
DOC.DOCUMENT_NO,
DOC.DOCUMENT_TITLE,
DOC.ASO_OBJECT_TYPE_DESC,
DOC.REVISION_NO,
DOC.CHAPTER,
DOC.SECTION,
DOC.SUBJECT,
DOC.PAGE,
DOC.FIGURE,
DOC.NOTE
FROM
AHL_WORKORDERS WO,
CSI_II_RELATIONSHIPS CSI,
AHL_VISIT_TASKS_B VTS,
AHL_DOCUMENT_ASSOS_V DOC
WHERE
WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND VTS.INSTANCE_ID = CSI.SUBJECT_ID
AND CSI.RELATIONSHIP_TYPE_CODE = ''COMPONENT-OF''
AND (SYSDATE BETWEEN NVL(CSI.ACTIVE_START_DATE, SYSDATE) AND NVL(CSI.ACTIVE_END_DATE, SYSDATE))
AND CSI.POSITION_REFERENCE = TO_CHAR(DOC.ASO_OBJECT_ID)
AND DOC.ASO_OBJECT_TYPE_CODE = ''MC''
AND WO.WORKORDER_ID = ';
SELECT
DOC.DOCUMENT_NO,
DOC.DOCUMENT_TITLE,
DOC.ASO_OBJECT_TYPE_DESC,
DOC.REVISION_NO,
DOC.CHAPTER,
DOC.SECTION,
DOC.SUBJECT,
DOC.PAGE,
DOC.FIGURE,
DOC.NOTE
FROM
AHL_WORKORDERS WO,
AHL_PC_ASSOCIATIONS PCA,
AHL_VISIT_TASKS_B VTS,
AHL_DOCUMENT_ASSOS_V DOC
WHERE
WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(VTS.INSTANCE_ID) = PCA.UNIT_ITEM_ID
AND PCA.PC_NODE_ID = DOC.ASO_OBJECT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''PC''
AND WO.WORKORDER_ID = ';
SELECT DISTINCT
wass.employee_id,
pf.full_name employee_name
FROM
ahl_Work_Assignments wass,
ahl_Operation_Resources opr,
ahl_Workorder_Operations wop,
per_people_f pf,
per_Person_Types Pt
WHERE
wass.Operation_Resource_Id = opr.operation_resource_id and
opr.Workorder_Operation_Id = wop.workorder_operation_id and
wop.workorder_id = p_wo_id and
wass.employee_id = nvl(p_employee_id, wass.employee_id) and
wass.employee_id = pf.PERSON_ID and
pt.Person_Type_Id = Pf.Person_Type_Id And
pt.System_Person_Type ='EMP' And
( Trunc(Sysdate) Between Pf.Effective_Start_Date And
Pf.Effective_End_Date);
SELECT
workorder_operation_id,
operation_sequence_num
FROM
ahl_workorder_operations
WHERE
workorder_id = p_workorder_id;
SELECT
PLAN_ID,
COLLECTION_ID
FROM
AHL_WORKORDERS
WHERE
workorder_id = p_workorder_id;
SELECT
OPERATION_SEQUENCE_NUM, --balaji added for Bug 6777371
PLAN_ID,
COLLECTION_ID
FROM
AHL_WORKORDER_OPERATIONS
WHERE
workorder_id = p_wo_id;
SELECT
wop.workorder_id,
TO_CHAR(MIN(AHL_PRD_UTIL_PKG.Get_Op_Assigned_Start_Date('||
l_emp_tbl(l_count).employee_id ||
',wop.workorder_id
,wop.operation_sequence_num
,''LINE''
)),
''YYYY-MM-DD hh:mm:ss+HH:MM'') Assigned_Start_Date,
TO_CHAR(MAX(AHL_PRD_UTIL_PKG.Get_Op_Assigned_Start_Date('||
l_emp_tbl(l_count).employee_id ||
',wop.workorder_id
,wop.operation_sequence_num
,''LINE'')),
''YYYY-MM-DD hh:mm:ss+HH:MM'') Assigned_End_Date
FROM
AHL_WORKORDER_OPERATIONS WOP
GROUP BY workorder_id
) WODE ';
SELECT
char_id,
organization_id,
prompt_sequence ,
prompt,
enabled_flag,
default_value,
default_value_id,
result_column_name,
values_exist_flag,
displayed_flag,
plan_name,
plan_description,
char_name,
datatype,
hardcoded_column,
developer_name
FROM
QA_PLAN_CHARS_V QA
WHERE
plan_id = p_plan_id;
l_query_string := 'SELECT RESULTS.COLLECTION_ID, RESULTS.OCCURRENCE ';
l_dummy_query := ' UNION ALL SELECT null collection_id, null occurrence';
l_query_string := ' select
wo.workorder_id
from
ahl_workorders wo,
wip_discrete_jobs wipd,
ahl_visits_b vst,
ahl_visit_tasks_b vtsk
where
wo.visit_id = vst.visit_id and
wo.wip_entity_id = wipd.wip_entity_id and
wo.visit_task_id = vtsk.visit_task_id and
vst.visit_id = vtsk.visit_id and
wo.status_code in (3, 4, 5, 6, 7, 12, 19) and
wo.master_workorder_flag <> ''Y'' and
vst.status_code in (''RELEASED'',''PARTIALLY RELEASED'') ';
Select OBJECT_VERSION_NUMBER,OBJECT_TYPE_CODE,OBJECT_ID from AHL_ENIGMA_JCG_DETAILS
Where JCG_REQUEST_ID = c_jcg_id
FOR UPDATE of STATUS_CODE;
Select OBJECT_VERSION_NUMBER,OBJECT_TYPE_CODE,OBJECT_ID from AHL_ENIGMA_JCG_DETAILS
where jcg_request_id = c_jcg_id
FOR UPDATE of STATUS_CODE;
Select JCG_REQUEST_ID from AHL_ENIGMA_JCG_DETAILS
Where OBJECT_TYPE_CODE = c_object_type
AND OBJECT_ID = c_object_id
AND JCG_REQUEST_ID < c_jcg_id ;
Select JCG_REQUEST_ID from AHL_ENIGMA_JCG_DETAILS
Where OBJECT_TYPE_CODE = c_object_type
AND OBJECT_ID = c_object_id
AND STATUS_CODE IN ('E','P')
AND JCG_REQUEST_ID < c_jcg_id ;
'Before inserting a new row in AHL_ENIGMA_JCG_DETAILS.'
);
INSERT INTO AHL_ENIGMA_JCG_DETAILS ( JCG_REQUEST_ID,
OBJECT_TYPE_CODE,
OBJECT_ID,
STATUS_CODE,
FILE_LOCATION,
SPLIT_FLAG,
XML_DATA_FILENAME,
OBJECT_VERSION_NUMBER,
ERROR_MESSAGE,
CONCURRENT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE ,
CREATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID
)
VALUES (AHL_ENIGMA_JCG_DETAILS_S.NEXTVAL,
p_object_type,
p_object_id,
p_status_code,
p_file_location,
p_split_flag,
p_xml_data_filename,
1,
p_error_message,
p_concurrent_request_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
NULL
)
RETURNING JCG_REQUEST_ID INTO p_x_jcg_id;
DELETE FROM AHL_ENIGMA_JCG_DETAILS WHERE JCG_REQUEST_ID = jcg_rec.JCG_REQUEST_ID ;
DELETE FROM AHL_ENIGMA_JCG_DETAILS WHERE JCG_REQUEST_ID = jcg_rec.JCG_REQUEST_ID ;
UPDATE AHL_ENIGMA_JCG_DETAILS SET FILE_LOCATION = p_file_location,
STATUS_CODE = p_status_code,
OBJECT_VERSION_NUMBER = l_ovn+1,
ERROR_MESSAGE = p_error_message,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
security_group_id = null,
CONCURRENT_REQUEST_ID = p_concurrent_request_id
WHERE JCG_REQUEST_ID = p_x_jcg_id ;
DELETE FROM AHL_ENIGMA_JCG_DETAILS WHERE JCG_REQUEST_ID = jcg_rec.JCG_REQUEST_ID ;
DELETE FROM AHL_ENIGMA_JCG_DETAILS WHERE JCG_REQUEST_ID = jcg_rec.JCG_REQUEST_ID ;
Select 'X' from AHL_ENIGMA_JCG_DETAILS
Where OBJECT_TYPE_CODE = c_object_type
AND OBJECT_ID = c_object_id
AND STATUS_CODE = 'S';
Select FILE_LOCATION from AHL_ENIGMA_JCG_DETAILS
where JCG_REQUEST_ID = (Select max(JCG_REQUEST_ID) from (
Select JCG_REQUEST_ID from AHL_ENIGMA_JCG_DETAILS
Where OBJECT_TYPE_CODE = c_object_type
AND OBJECT_ID = c_object_id
AND STATUS_CODE = 'S'
));
Select STATUS_CODE from AHL_ENIGMA_JCG_DETAILS
where JCG_REQUEST_ID = (Select max(JCG_REQUEST_ID) from (
Select JCG_REQUEST_ID from AHL_ENIGMA_JCG_DETAILS
Where OBJECT_TYPE_CODE = c_object_type
AND OBJECT_ID = c_object_id
));
Select ERROR_MESSAGE from AHL_ENIGMA_JCG_DETAILS
where JCG_REQUEST_ID = (Select max(JCG_REQUEST_ID) from (
Select JCG_REQUEST_ID from AHL_ENIGMA_JCG_DETAILS
Where OBJECT_TYPE_CODE = c_object_type
AND OBJECT_ID = c_object_id
AND STATUS_CODE = 'E'
));
SELECT HROU.NAME ORG_NAME,
DECODE(WIP.DATE_RELEASED, NULL, NULL,
TO_CHAR(WIP.DATE_RELEASED,'YYYY-MM-DD')
||'T'
||TO_CHAR(WIP.DATE_RELEASED, 'hh24:mi:ss') ) DATE_RELEASED
FROM AHL_WORKORDERS WO ,
WIP_DISCRETE_JOBS WIP ,
AHL_VISITS_B VST ,
HR_ALL_ORGANIZATION_UNITS HROU
WHERE WO.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
AND VST.ORGANIZATION_ID = HROU.ORGANIZATION_ID
AND WO.VISIT_ID = VST.VISIT_ID
AND WO.WORKORDER_ID = c_wo_id;
SELECT UC.NAME UC_NAME ,
FND1.MEANING MODEL_MEANING
FROM AHL_UNIT_CONFIG_HEADERS UC ,
AHL_MC_HEADERS_B MC ,
FND_LOOKUP_VALUES_VL FND1
WHERE UC.MASTER_CONFIG_ID = MC.MC_HEADER_ID
AND FND1.LOOKUP_CODE(+) = MC.MODEL_CODE
AND FND1.LOOKUP_TYPE(+) = 'AHL_ENIGMA_MODEL_CODE'
AND UC.UNIT_CONFIG_HEADER_ID = c_uc_id;
SELECT
WO.object_version_number,
WO.job_number,
WO.job_description,
WO.job_status_meaning,
WO.department_name,
decode(WO.scheduled_start_date, null, null, TO_CHAR(WO.scheduled_start_date,'YYYY-MM-DD')||'T'||TO_CHAR(WO.scheduled_start_date, 'hh24:mi:ss') ) scheduled_start_date,
decode(WO.scheduled_end_date, null, null, TO_CHAR(WO.scheduled_end_date, 'YYYY-MM-DD')||'T'||TO_CHAR(WO.scheduled_end_date, 'hh24:mi:ss') ) scheduled_end_date,
WO.unit_name,
WO.wo_part_number,
WO.item_instance_number,
WO.serial_number ,
WO.lot_number,
WO.visit_number,
RO.model_meaning,
--WIP.date_released,
decode(WIP.DATE_RELEASED, null, null, TO_CHAR(WIP.DATE_RELEASED,'YYYY-MM-DD')||'T'||TO_CHAR(WIP.DATE_RELEASED, 'hh24:mi:ss') ) DATE_RELEASED,
WIP.net_quantity,
UE.ata_code,
WO.incident_number,
HZ.party_name,
--UE.originating_wo_id
(select job_number from ahl_workorders_v WO , AHL_UNIT_EFFECTIVITIES_B UE where
WO.workorder_id = UE.ORIGINATING_WO_ID
and WO.unit_effectivity_id = UE.unit_effectivity_id) OriginatingWorkorder,
WO.item_instance_id,
decode(WO.plan_id,null,'F','T') isQualityEnabled
FROM AHL_WORKORDERS_V WO, WIP_DISCRETE_JOBS WIP, AHL_ROUTES_V RO, AHL_UNIT_EFFECTIVITIES_B UE, HZ_PARTIES HZ, CSI_ITEM_INSTANCES CSI
WHERE WO.wip_entity_id = WIP.wip_entity_id
AND WO.route_id = RO.route_id (+)
AND UE.unit_effectivity_id(+) = WO.unit_effectivity_id
AND CSI.instance_id = WO.ITEM_INSTANCE_ID(+)
AND CSI.owner_party_id(+) = HZ.party_id
AND workorder_id = c_wo_id;
SELECT WO.OBJECT_VERSION_NUMBER,
WO.JOB_NUMBER ,
WO.JOB_DESCRIPTION ,
WO.JOB_STATUS_MEANING ,
WO.DEPARTMENT_NAME ,
DECODE(WO.SCHEDULED_START_DATE, NULL, NULL, TO_CHAR(WO.SCHEDULED_START_DATE,'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.SCHEDULED_START_DATE, 'hh24:mi:ss') ) SCHEDULED_START_DATE,
DECODE(WO.SCHEDULED_END_DATE, NULL, NULL, TO_CHAR(WO.SCHEDULED_END_DATE, 'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.SCHEDULED_END_DATE, 'hh24:mi:ss') ) SCHEDULED_END_DATE,
WO.UNIT_NAME ,
WO.WO_PART_NUMBER ,
WO.ITEM_INSTANCE_NUMBER ,
WO.SERIAL_NUMBER ,
WO.LOT_NUMBER ,
WO.VISIT_NUMBER ,
--RO.MODEL_MEANING ,
MODELTYPE.MEANING MODEL_MEANING ,
DECODE(WIP.DATE_RELEASED, NULL, NULL, TO_CHAR(WIP.DATE_RELEASED,'YYYY-MM-DD')
||'T'
||TO_CHAR(WIP.DATE_RELEASED, 'hh24:mi:ss') ) DATE_RELEASED,
WIP.NET_QUANTITY ,
--UE.ATA_CODE ,
DECODE(RO.ENIGMA_ROUTE_ID, NULL, NULL, SUBSTR(route_no, instr(route_no, ':',1,1)+1)) ATA_CODE,
-- fix for bug# 10016417
--(SELECT CSIN.INCIDENT_NUMBER
--FROM CS_INCIDENTS_ALL_B CSIN
--WHERE CSIN.INCIDENT_ID = UE.cs_incident_id
--)
(SELECT CSIN.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_B CSIN, AHL_VISIT_TASKS_B TSK
WHERE CSIN.INCIDENT_ID = TSK.SERVICE_REQUEST_ID
AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
)
INCIDENT_NUMBER,
(SELECT HZ.PARTY_NAME
FROM HZ_PARTIES HZ
WHERE HZ.PARTY_ID = CSI.OWNER_PARTY_ID
)
PARTY_NAME,
(SELECT WORKORDER_NAME
FROM AHL_WORKORDERS WO --,
--AHL_UNIT_EFFECTIVITIES_B UE1
WHERE WO.WORKORDER_ID = UE.ORIGINATING_WO_ID
--AND WO.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
)
ORIGINATINGWORKORDER,
WO.ITEM_INSTANCE_ID ,
DECODE(W.PLAN_ID,NULL,'F','T') ISQUALITYENABLED,
WO.ROUTE_ID
FROM AHL_WORKORDER_TASKS_V WO ,
WIP_DISCRETE_JOBS WIP ,
AHL_ROUTES_B RO ,
AHL_UNIT_EFFECTIVITIES_B UE ,
--HZ_PARTIES HZ ,
CSI_ITEM_INSTANCES CSI ,
--CS_INCIDENTS_ALL_B CSIN ,
--AHL_VISIT_TASKS_VL VTS ,
AHL_WORKORDERS W ,
(SELECT LOOKUP_CODE ,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ENIGMA_MODEL_CODE'
AND LANGUAGE = USERENV('LANG')
)
MODELTYPE
WHERE WO.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
AND WO.ROUTE_ID = RO.ROUTE_ID (+)
AND UE.UNIT_EFFECTIVITY_ID(+) = WO.UNIT_EFFECTIVITY_ID
AND CSI.INSTANCE_ID = WO.ITEM_INSTANCE_ID
--AND CSI.OWNER_PARTY_ID(+) = HZ.PARTY_ID
AND MODELTYPE.LOOKUP_CODE(+) = RO.MODEL_CODE
--AND VTS.SERVICE_REQUEST_ID = CSIN.INCIDENT_ID(+)
--AND WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND W.workorder_id = WO.workorder_id
AND WO.WORKORDER_ID = c_wo_id;
SELECT MSI.CONCATENATED_SEGMENTS,
WRO.OPERATION_SEQ_NUM OPERATION_SEQUENCE ,
MSI.DESCRIPTION ,
IG.NAME ,
WRO.REQUIRED_QUANTITY REQUESTED_QUANTITY ,
DECODE(WRO.date_required, NULL, NULL, TO_CHAR(WRO.date_required, 'YYYY-MM-DD')
||'T'
||TO_CHAR(WRO.date_required, 'hh24:mi:ss') ) required_date,
WRO.QUANTITY_ISSUED ISSUED_QTY ,
MSI.PRIMARY_UNIT_OF_MEASURE UOM ,
ASM.SCHEDULED_MATERIAL_ID,
(SELECT f.meaning from fnd_lookup_values_vl f
WHERE f.lookup_type = 'ITEM_TYPE' and f.lookup_code = msi.item_type) item_type
FROM --AHL_JOB_OPER_MATERIALS_V JM,
WIP_REQUIREMENT_OPERATIONS WRO, AHL_SCHEDULE_MATERIALS ASM,
AHL_ITEM_GROUPS_B IG, AHL_WORKORDERS WO, MTL_SYSTEM_ITEMS_KFV MSI
WHERE WO.wip_entity_id = WRO.wip_entity_id
AND WO.visit_task_id = ASM.visit_task_id
AND ASM.operation_sequence = WRO.operation_seq_num
AND ASM.inventory_item_id = WRO.inventory_item_id
AND WRO.organization_id = MSI.organization_id
AND WRO.inventory_item_id = MSI.inventory_item_id
AND IG.ITEM_GROUP_ID(+) = ASM.ITEM_GROUP_ID
AND WO.WORKORDER_ID = c_wo_id
ORDER BY 2;
SELECT WOR.OPERATION_SEQ_NUM operation_sequence,
WOR.RESOURCE_SEQ_NUM resource_sequence ,
BOM.RESOURCE_CODE resource_code,
BOM.DESCRIPTION resource_name,
MFG.MEANING resource_type_name,
WOR.ASSIGNED_UNITS quantity,
DECODE(nvl(WOR.ASSIGNED_UNITS,0), 0, 0, WOR.USAGE_RATE_OR_AMOUNT/WOR.ASSIGNED_UNITS) duration ,
(SELECT A.UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE A
WHERE A.UOM_CODE=BOM.UNIT_OF_MEASURE) uom_name,
WOR.RESOURCE_ID,
(select awo.operation_id from AHL_WORKORDER_OPERATIONS AWO
where AWO.workorder_id = wo.workorder_id
and awo.operation_sequence_num = WOR.OPERATION_SEQ_NUM) operation_id
FROM WIP_OPERATION_RESOURCES WOR,
BOM_RESOURCES BOM, MFG_LOOKUPS MFG, AHL_WORKORDERS WO
WHERE WO.workorder_id = c_wo_id
AND WOR.resource_id = BOM.resource_id
AND WO.wip_entity_id = WOR.wip_entity_id
AND MFG.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
AND MFG.LOOKUP_CODE(+) = BOM.RESOURCE_TYPE
ORDER BY 1,2;
SELECT workorder_id
--from ahl_workorders_v
FROM ahl_workorders
WHERE visit_id = c_visit_id
AND master_workorder_flag = 'N'
AND STATUS_CODE NOT IN ( '7','12','14','15','17','22' )
order by workorder_name;
SELECT wo.WORKORDER_ID
--FROM AHL_WORKORDERS_V
FROM AHL_WORKORDERS WO,
ahl_visit_tasks_b tsk
WHERE wo.visit_task_id = tsk.visit_task_id
AND wo.master_workorder_flag = 'N'
AND WO.STATUS_CODE NOT IN ('7','12','14','15','17','22')
AND tsk.UNIT_EFFECTIVITY_ID = c_ue_id
order by WO.workorder_name;
SELECT WO.operation_sequence_num,
WO.operation_code ,
WO.description ,
WO.status ,
WO.department_name ,
--WO.scheduled_start_date ,
--WO.scheduled_end_date ,
DECODE(WO.scheduled_start_date, NULL, NULL, TO_CHAR(WO.scheduled_start_date,'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.scheduled_start_date, 'hh24:mi:ss') ) scheduled_start_date,
DECODE(WO.scheduled_end_date, NULL, NULL, TO_CHAR(WO.scheduled_end_date, 'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.scheduled_end_date, 'hh24:mi:ss') ) scheduled_end_date,
DECODE(WO.plan_id,NULL,'F','T') isQualityEnabled ,
WO.workorder_operation_id
FROM AHL_WORKORDER_OPERATIONS_V WO --AHL_OPERATIONS_B OP
WHERE -- WO.operation_id = OP.operation_id(+) and
WO.workorder_id = c_wo_id
ORDER BY 1 ;
select
object_version_number,
route_no,
title,
route_type,
revision_number,
enigma_doc_id,
enigma_route_id,
zone,
sub_zone,
process,
job_card_lyt_meaning,
model_code
from AHL_ROUTES_V
where route_id = c_route_id;
SELECT R.object_version_number ,
R.route_no ,
T.title ,
RTTYPE.MEANING route_type ,
R.revision_number ,
R.enigma_doc_id ,
R.enigma_route_id ,
ZONE.MEANING zone ,
SUBZONE.MEANING sub_zone ,
PROCESS.MEANING process ,
JOBCARDLYT.MEANING job_card_lyt_meaning ,
R.model_code ,
R.last_update_date
FROM AHL_ROUTES_B R ,
AHL_ROUTES_TL T ,
(SELECT LOOKUP_CODE ,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_RM_JOB_CARD_LYT'
AND LANGUAGE = USERENV('LANG')
)
JOBCARDLYT ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_SUB_ZONE'
AND LANGUAGE = USERENV('LANG')
)
SUBZONE ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_PROCESS_CODE'
AND LANGUAGE = USERENV('LANG')
)
PROCESS ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ZONE'
AND LANGUAGE = USERENV('LANG')
)
ZONE ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ROUTE_TYPE'
AND LANGUAGE = USERENV('LANG')
)
RTTYPE
WHERE R.ROUTE_ID = T.ROUTE_ID
AND T.LANGUAGE = USERENV('LANG')
AND JOBCARDLYT.LOOKUP_CODE (+) = R.JOBCARDLYT_CODE
AND ZONE.LOOKUP_CODE (+) = R.ZONE_CODE
AND SUBZONE.LOOKUP_CODE (+) = R.SUB_ZONE_CODE
AND PROCESS.LOOKUP_CODE (+) = R.PROCESS_CODE
AND RTTYPE.LOOKUP_CODE (+) = R.ROUTE_TYPE_CODE
AND R.route_id = c_route_id;
SELECT DISTINCT WO.OPERATION_SEQUENCE_NUM,
WO.OPERATION_CODE ,
WO.DESCRIPTION ,
WO.OPERATION_TYPE ,
OP.ENIGMA_DOC_ID ,
OP.OPERATION_ID ,
OP.REVISION_NUMBER ,
OP.LAST_UPDATE_DATE
FROM AHL_WORKORDER_OPERATIONS_V WO,
AHL_ROUTE_OPERATIONS RP ,
AHL_OPERATIONS_B OP
WHERE RP.OPERATION_ID(+) = WO.OPERATION_ID
AND WO.OPERATION_ID = OP.OPERATION_ID(+)
AND RP.route_id = c_route_id
AND WO.workorder_id = c_wo_id
ORDER BY 1;
/* SELECT ue.title ,
ue.description ,
ue.version_number Version ,
ue.program_type ProgramType ,
mr.program_subtype ProgramSubtype ,
ue.counter_name DueCounterName ,
ue.due_counter_value DueCounterValue ,
mi.unit_of_measure UOM ,
mi.interval_value IntervalValue ,
mi.start_value StartValue ,
mi.start_date StartDate ,
ue.due_date DueDate ,
ue.csi_item_instance_id ,
mr.mr_header_id ,
mr.last_update_date
FROM ahl_mr_headers_v mr ,
ahl_unit_effectivities_v ue,
ahl_mr_intervals_v mi
WHERE ue.mr_header_id = mr.mr_header_id
AND ue.mr_interval_id = mi.mr_interval_id(+)
AND ue.unit_effectivity_id = c_ue_id;
SELECT MRB.title ,
MRT.description ,
MRB.version_number Version ,
F1.MEANING ProgramType ,
SUBTYPE.MEANING ProgramSubtype ,
C.NAME DueCounterName ,
B.due_counter_value DueCounterValue ,
I.unit_of_measure UOM ,
I.interval_value IntervalValue ,
I.start_value StartValue ,
I.start_date StartDate ,
B.due_date DueDate ,
B.csi_item_instance_id ,
MRB.mr_header_id ,
MRB.last_update_date
FROM ahl_mr_headers_b MRB ,
ahl_unit_effectivities_b B,
ahl_mr_intervals_v I ,
fnd_lookup_values F1 ,
csi_counter_template_vl C ,
ahl_mr_headers_tl MRT ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_SUBTYPE'
AND LANGUAGE = USERENV('LANG')
) SUBTYPE
WHERE B.mr_header_id = MRB.mr_header_id
AND B.mr_interval_id = I.mr_interval_id(+)
AND B.object_type = 'MR'
AND F1.lookup_code = MRB.program_type_code
AND F1.lookup_type = 'AHL_FMP_MR_PROGRAM_TYPE'
AND F1.language = USERENV('LANG')
AND I.counter_id = C.counter_id(+)
AND B.mr_interval_id = I.mr_interval_id(+)
AND MRB.mr_header_id = MRT.mr_header_id
AND MRT.language = USERENV('LANG')
AND SUBTYPE.lookup_code(+) = MRB.program_subtype_code
AND B.unit_effectivity_id = c_ue_id;
SELECT DISTINCT plan_name,
plan_description
FROM QA_PLAN_CHARS_V
WHERE plan_id = c_qa_plan_id;
SELECT plan_id,
collection_id
FROM AHL_WORKORDER_OPERATIONS
WHERE workorder_operation_id = c_wo_op_id;
SELECT plan_id,
collection_id
FROM AHL_WORKORDERS
WHERE workorder_id = c_wo_id;
SELECT occurrence
FROM QA_RESULTS
WHERE collection_id = c_collection_id;
SELECT visit_number
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_wo_id;
SELECT char_id CharId ,
prompt_sequence PromptSequence,
prompt Prompt ,
default_value DefaultValue ,
enabled_flag ,
displayed_flag
FROM QA_PLAN_CHARS
WHERE plan_id = c_plan_id
ORDER BY 2;
SELECT segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
segment6 ,
segment7 ,
segment8 ,
segment9 ,
segment10 ,
segment11 ,
segment12 ,
segment13 ,
segment14 ,
segment15 ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM ahl_routes_b
WHERE route_id = c_route_id;
SELECT panel_type ,
panel_type_desc
FROM AHL_RT_OPER_ACCESS_PANELS_V
WHERE ASSOCIATION_TYPE_CODE = 'ROUTE'
AND object_id = c_route_id;
SELECT 'Y'
FROM AHL_RT_OPER_RESOURCES AR
WHERE AR.association_type_code='ROUTE'
AND AR.object_id=p_route_id
AND ROWNUM < 2;
SELECT CS.INCIDENT_ID,
UE.UNIT_EFFECTIVITY_ID,
(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) title,
UE.FLIGHT_NUMBER,
CS_TYPE.NAME,
(SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_LOG_SERIES_CODE'
AND LOOKUP_CODE =UE.LOG_SERIES_CODE
AND LANGUAGE = USERENV('LANG')
) UE_LOG_SERIES,
UE.LOG_SERIES_NUMBER LOG_NUMBER,
MTL.CONCATENATED_SEGMENTS UE_ITEM_NUMBER,
CSI.SERIAL_NUMBER UE_SERIAL_NUMBER ,
(SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_UNIT_EFFECTIVITY_STATUS'
AND LOOKUP_CODE = UE.STATUS_CODE
AND LANGUAGE = USERENV('LANG')
) UE_STATUS,
--CS.CREATION_DATE ,
DECODE(CS.CREATION_DATE, NULL, NULL, TO_CHAR(CS.CREATION_DATE,'YYYY-MM-DD')
||'T'
||TO_CHAR(CS.CREATION_DATE, 'hh24:mi:ss') ) CREATION_DATE ,
WO.WORKORDER_NAME ,
PARTY.PARTY_NAME SR_CUSTOMER_NAME,
DECODE(SR_CONT.CONTACT_TYPE,'PERSON',PARTY.PARTY_NAME,
(SELECT CS_EMPL.FULL_NAME
FROM PER_ALL_PEOPLE_F CS_EMPL
WHERE TRUNC(SYSDATE) BETWEEN CS_EMPL.EFFECTIVE_START_DATE AND CS_EMPL.EFFECTIVE_END_DATE
AND CS_EMPL.PERSON_ID = SR_CONT.PARTY_ID
)) SR_CONTACT_NAME,
CS.PROBLEM_CODE ,
(SELECT CS_TL.SUMMARY
FROM CS_INCIDENTS_ALL_TL CS_TL
WHERE LANGUAGE = USERENV('LANG')
AND CS.INCIDENT_ID = CS_TL.INCIDENT_ID
) SR_PROBLEM_SUMMARY ,
(SELECT CS_SEV.NAME
FROM CS_INCIDENT_SEVERITIES_TL CS_SEV
WHERE LANGUAGE = USERENV('LANG')
AND CS.INCIDENT_SEVERITY_ID = CS_SEV.INCIDENT_SEVERITY_ID
) SR_INCIDENT_SEVERITY,
CS.EXPECTED_RESOLUTION_DATE,
CS.RESOLUTION_CODE,
UE_ATA_LKUP.MEANING UE_ATA_MEANING ,
UE_ATA_LKUP.DESCRIPTION UE_ATA_DESC,
(SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_MEL_CDL_TYPE'
AND LOOKUP_CODE =UE.MEL_CDL_TYPE_CODE
AND LANGUAGE = USERENV('LANG')
) UE_MEL_CDL_TYPE,
AHL_UMP_NONROUTINES_PVT.GET_MEL_CDL_STATUS(UE.UNIT_EFFECTIVITY_ID, 'F') UE_MEL_CDL_STATUS,
ATASEQ.UE_ATA_PC_NODE_NAME,
ATASEQ.UE_ATA_REPAIR_CATEGORY,
UE.DUE_DATE,
ATASEQ.UE_ATA_REPAIR_TIME,
(SELECT JNTT.NOTES_DETAIL
FROM JTF_NOTES_B JNTB,
JTF_NOTES_TL JNTT
WHERE JNTB.JTF_NOTE_ID = JNTT.JTF_NOTE_ID
AND JNTT.LANGUAGE = USERENV('LANG')
AND JNTB.SOURCE_OBJECT_CODE = 'AHL_MEL_CDL'
AND JNTB.SOURCE_OBJECT_ID = ATASEQ.MEL_CDL_ATA_SEQUENCE_ID
) UE_ATA_REMARKS,
CS.INCIDENT_OCCURRED_DATE,
ATASEQ.UE_ATA_HEADER_REVISION,
(Select wip.date_released
from wip_discrete_jobs wip,
ahl_workorders wo
where wo.wip_entity_id = wip.wip_entity_id
and wo.master_workorder_flag = 'Y'
and wo.visit_task_id = visit_task.visit_task_id
) RELEASE_DATE
FROM
AHL_UNIT_EFFECTIVITIES_B UE,
MTL_SYSTEM_ITEMS_KFV MTL ,
CSI_ITEM_INSTANCES CSI ,
CS_INCIDENTS_ALL_B CS,
AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VISIT_TASK,
HZ_PARTIES PARTY ,
CS_INCIDENT_TYPES_TL CS_TYPE,
(SELECT INCIDENT_ID ,
PARTY_ID ,
CONTACT_TYPE
FROM CS_HZ_SR_CONTACT_POINTS
WHERE PRIMARY_FLAG = 'Y'
) SR_CONT ,
(SELECT LOOKUP_CODE,
MEANING ,
DESCRIPTION
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ATA_CODE'
AND LANGUAGE = USERENV('LANG')
) UE_ATA_LKUP,
(SELECT UDF.UNIT_EFFECTIVITY_ID ,
ATA.MEL_CDL_ATA_SEQUENCE_ID ,
SRURG.NAME UE_ATA_REPAIR_CATEGORY ,
REPCAT.REPAIR_TIME UE_ATA_REPAIR_TIME ,
PCN.NAME UE_ATA_PC_NODE_NAME ,
HDR.REVISION UE_ATA_HEADER_REVISION
FROM AHL_UNIT_DEFERRALS_B UDF,
AHL_MEL_CDL_ATA_SEQUENCES ATA ,
AHL_MEL_CDL_HEADERS HDR ,
AHL_PC_NODES_B PCN ,
AHL_REPAIR_CATEGORIES REPCAT ,
CS_INCIDENT_URGENCIES_TL SRURG
WHERE UDF.UNIT_DEFERRAL_TYPE IN ('MEL', 'CDL')
AND UDF.ATA_SEQUENCE_ID = ATA.MEL_CDL_ATA_SEQUENCE_ID
AND ATA.MEL_CDL_HEADER_ID = HDR.MEL_CDL_HEADER_ID
AND HDR.PC_NODE_ID = PCN.PC_NODE_ID
AND ATA.REPAIR_CATEGORY_ID = REPCAT.REPAIR_CATEGORY_ID
AND REPCAT.SR_URGENCY_ID = SRURG.INCIDENT_URGENCY_ID
AND SRURG.LANGUAGE = USERENV('LANG')
) ATASEQ
WHERE
UE.OBJECT_TYPE = 'SR'
AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND CSI.LAST_VLD_ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND UE.UNIT_EFFECTIVITY_ID = VISIT_TASK.UNIT_EFFECTIVITY_ID(+)
AND VISIT_TASK.VISIT_TASK_ID = WO.VISIT_TASK_ID(+)
AND VISIT_TASK.TASK_TYPE_CODE IN ('SUMMARY', 'UNASSOCIATED')
AND CS.CUSTOMER_ID = PARTY.PARTY_ID
AND SR_CONT.INCIDENT_ID(+) = CS.INCIDENT_ID
AND UE.ATA_CODE = UE_ATA_LKUP.LOOKUP_CODE(+)
AND ATASEQ.UNIT_EFFECTIVITY_ID(+) = UE.UNIT_EFFECTIVITY_ID
AND CS.INCIDENT_TYPE_ID = CS_TYPE.INCIDENT_TYPE_ID
AND CS_TYPE.LANGUAGE = USERENV('LANG')
AND UE.UNIT_EFFECTIVITY_ID = c_ue_id;
SELECT c_ue_id FROM DUAL
union
SELECT unit_effectivity_id
FROM AHL_UNIT_EFFECTIVITIES_B UE,
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = c_ue_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
VST1.UNIT_EFFECTIVITY_ID
FROM AHL_VISIT_TASKS_B VST,
AHL_UNIT_EFFECTIVITIES_B UE,
AHL_VISIT_TASKS_B VST1
WHERE VST.SERVICE_REQUEST_ID = VST1.SERVICE_REQUEST_ID
AND VST.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
AND UE.UNIT_EFFECTIVITY_ID = c_ue_id;
SELECT UE.unit_effectivity_id ,
MRB.TITLE
FROM AHL_UNIT_EFFECTIVITIES_B UE,
AHL_MR_HEADERS_B MRB,
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = c_ue_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
AND UE.MR_HEADER_ID = MRB.MR_HEADER_ID;
SELECT DISTINCT
VST.UNIT_EFFECTIVITY_ID ,
MRB.TITLE
FROM AHL_MR_HEADERS_B MRB,
AHL_VISIT_TASKS_B VST
WHERE VST.SERVICE_REQUEST_ID = c_incident_id
AND VST.MR_ID = MRB.MR_HEADER_ID;
SELECT DOC.DOCUMENT_NO "DocumentNumber" ,
DOC.DOCUMENT_TITLE "DocumentTitle" ,
DOC.DOC_TYPE_CODE "DocumentType" ,
DOCV.DOC_SUB_TYPE_CODE "DocumentSubType" ,
DOC.ASO_OBJECT_TYPE_DESC "AsoObjectTypeDesc" ,
NULL "OperationSequenceNumber",
DOC.REVISION_NO "RevisionNumber" ,
DOC.CHAPTER "Chapter" ,
DOC.SECTION "Section" ,
DOC.SUBJECT "Subject" ,
DOC.PAGE "Page" ,
DOC.FIGURE "Figure" ,
DOC.NOTE "Note"
FROM --AHL_WORKORDERS WO ,
AHL_DOCUMENT_ASSOS_V DOC,
AHL_DOCUMENTS_V DOCV
WHERE --WO.ROUTE_ID = DOC.ASO_OBJECT_ID
DOC.DOCUMENT_ID = DOCV.DOCUMENT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''ROUTE''
AND DOC.ASO_OBJECT_ID = :1';
SELECT DOC.DOCUMENT_NO "DocumentNumber" ,
DOC.DOCUMENT_TITLE "DocumentTitle" ,
DOC.DOC_TYPE_CODE "DocumentType" ,
DOCV.DOC_SUB_TYPE_CODE "DocumentSubType" ,
DOC.ASO_OBJECT_TYPE_DESC "AsoObjectTypeDesc" ,
WOP.OPERATION_SEQUENCE_NUM "OperationSequenceNumber",
DOC.REVISION_NO "RevisionNumber" ,
DOC.CHAPTER "Chapter" ,
DOC.SECTION "Section" ,
DOC.SUBJECT "Subject" ,
DOC.PAGE "Page" ,
DOC.FIGURE "Figure" ,
DOC.NOTE "Note"
FROM AHL_WORKORDER_OPERATIONS WOP,
AHL_DOCUMENT_ASSOS_V DOC ,
AHL_DOCUMENTS_V DOCV
WHERE WOP.OPERATION_ID = DOC.ASO_OBJECT_ID
AND DOC.DOCUMENT_ID = DOCV.DOCUMENT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''OPERATION''
AND WOP.WORKORDER_ID = :1
AND WOP.OPERATION_ID = :2';
SELECT DOC.DOCUMENT_NO "DocumentNumber" ,
DOC.DOCUMENT_TITLE "DocumentTitle" ,
DOC.DOC_TYPE_CODE "DocumentType" ,
DOCV.DOC_SUB_TYPE_CODE "DocumentSubType" ,
DOC.ASO_OBJECT_TYPE_DESC "AsoObjectTypeDesc" ,
NULL "OperationSequenceNumber",
DOC.REVISION_NO "RevisionNumber" ,
DOC.CHAPTER "Chapter" ,
DOC.SECTION "Section" ,
DOC.SUBJECT "Subject" ,
DOC.PAGE "Page" ,
DOC.FIGURE "Figure" ,
DOC.NOTE "Note"
FROM AHL_DOCUMENT_ASSOS_V DOC ,
--AHL_UNIT_EFFECTIVITIES_B UE,
AHL_DOCUMENTS_V DOCV
WHERE --UE.MR_HEADER_ID = DOC.ASO_OBJECT_ID
DOC.ASO_OBJECT_ID = :1
AND DOC.DOCUMENT_ID = DOCV.DOCUMENT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''MR''';
SELECT MA.ATTRIBUTE_CATEGORY "AttributeCategory",
MA.ATTRIBUTE1 "Attribute1" ,
MA.ATTRIBUTE2 "Attribute2" ,
MA.ATTRIBUTE3 "Attribute3" ,
MA.ATTRIBUTE4 "Attribute4" ,
MA.ATTRIBUTE5 "Attribute5" ,
MA.ATTRIBUTE6 "Attribute6" ,
MA.ATTRIBUTE7 "Attribute7" ,
MA.ATTRIBUTE8 "Attribute8" ,
MA.ATTRIBUTE9 "Attribute9" ,
MA.ATTRIBUTE10 "Attribute10" ,
MA.ATTRIBUTE11 "Attribute11" ,
MA.ATTRIBUTE12 "Attribute12" ,
MA.ATTRIBUTE13 "Attribute13" ,
MA.ATTRIBUTE14 "Attribute14" ,
MA.ATTRIBUTE15 "Attribute15"
FROM AHL_SCHEDULE_MATERIALS MA--, AHL_JOB_OPER_MATERIALS_V MV
WHERE MA.SCHEDULED_MATERIAL_ID = :1';
SELECT RE.ATTRIBUTE_CATEGORY "AttributeCategory",
RE.ATTRIBUTE1 "Attribute1" ,
RE.ATTRIBUTE2 "Attribute2" ,
RE.ATTRIBUTE3 "Attribute3" ,
RE.ATTRIBUTE4 "Attribute4" ,
RE.ATTRIBUTE5 "Attribute5" ,
RE.ATTRIBUTE6 "Attribute6" ,
RE.ATTRIBUTE7 "Attribute7" ,
RE.ATTRIBUTE8 "Attribute8" ,
RE.ATTRIBUTE9 "Attribute9" ,
RE.ATTRIBUTE10 "Attribute10" ,
RE.ATTRIBUTE11 "Attribute11" ,
RE.ATTRIBUTE12 "Attribute12" ,
RE.ATTRIBUTE13 "Attribute13" ,
RE.ATTRIBUTE14 "Attribute14" ,
RE.ATTRIBUTE15 "Attribute15"
FROM AHL_RT_OPER_RESOURCES RE, ahl_resource_mappings ar, bom_resources br
WHERE br.organization_id = ar.BOM_ORG_ID
AND br.resource_id = ar.BOM_RESOURCE_ID
AND br.resource_id = :1
AND ar.aso_resource_id = re.aso_resource_id
AND re.ASSOCIATION_TYPE_CODE = :2
AND re.object_id = :3';
SELECT OP.ATTRIBUTE_CATEGORY "AttributeCategory",
OP.ATTRIBUTE1 "Attribute1" ,
OP.ATTRIBUTE2 "Attribute2" ,
OP.ATTRIBUTE3 "Attribute3" ,
OP.ATTRIBUTE4 "Attribute4" ,
OP.ATTRIBUTE5 "Attribute5" ,
OP.ATTRIBUTE6 "Attribute6" ,
OP.ATTRIBUTE7 "Attribute7" ,
OP.ATTRIBUTE8 "Attribute8" ,
OP.ATTRIBUTE9 "Attribute9" ,
OP.ATTRIBUTE10 "Attribute10" ,
OP.ATTRIBUTE11 "Attribute11" ,
OP.ATTRIBUTE12 "Attribute12" ,
OP.ATTRIBUTE13 "Attribute13" ,
OP.ATTRIBUTE14 "Attribute14" ,
OP.ATTRIBUTE15 "Attribute15"
FROM AHL_OPERATIONS_B OP
WHERE OP.OPERATION_ID = :1';
SELECT char_id "CharId" ,
prompt_sequence "PromptSequence",
prompt "Prompt" ,
default_value "DefaultValue"
FROM QA_PLAN_CHARS
WHERE plan_id = :1';
SELECT QC.char_id "CharId",
CI.attribute_value "AttributeValue"
FROM CSI_IEA_VALUES CI,
QA_PLAN_CHARS QC ,
QA_RESULTS_V QR
WHERE QC.plan_id = QR.plan_id
AND CI.instance_id(+) = QR.csi_instance_id
AND QR.collection_id = :1';
SELECT cc.counter_template_name "CounterName",
(SELECT ccr.net_reading
FROM csi_counter_readings ccr
WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
AND NVL(ccr.disabled_flag,''N'') = ''N''
)
"CurrentReading"
--FROM csi_counter_associations cca, csi_counters_vl cc, ahl_workorders_v wo
FROM csi_counter_associations cca,
csi_counters_vl cc ,
ahl_workorders WO ,
ahl_visit_tasks_b tsk
WHERE cca.counter_id = cc.counter_id
AND wo.visit_task_id = tsk.visit_task_id
AND source_object_code = ''CP''
--AND source_object_id = wo.item_instance_id
AND source_object_id = tsk.instance_id
AND wo.workorder_id = :1';
SELECT cc.counter_template_name "CounterName",
(SELECT ccr.net_reading
FROM csi_counter_readings ccr
WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
AND NVL(ccr.disabled_flag,''N'') = ''N''
)
"CurrentReading"
FROM csi_counter_associations cca,
csi_counters_vl cc
WHERE cca.counter_id = cc.counter_id
AND source_object_code = ''CP''
AND source_object_id = :1';
SELECT OFFI.concatenated_segments RemovedItem ,
OFFC.serial_number RemovedSerial ,
PC.removed_quantity RemovedQuantity ,
OFFC.lot_number RemovedLot ,
OFFC.inventory_revision RemovedRevision ,
ONI.concatenated_segments InstalledItem ,
ONC.serial_number InstalledSerial ,
PC.installed_quantity InstalledQuantity,
ONC.lot_number InstalledLot ,
ONC.inventory_revision InstalledRevision,
DIS.position_reference position
FROM ahl_part_changes_v PC ,
csi_item_instances OFFC ,
mtl_system_items_kfv OFFI,
csi_item_instances ONC ,
mtl_system_items_kfv ONI ,
ahl_prd_dispositions_v DIS
WHERE PC.part_change_id = DIS.part_change_id
AND PC.removed_instance_id = OFFC.instance_id (+)
AND PC.installed_instance_id = ONC.instance_id (+)
AND OFFC.inventory_item_id = OFFI.inventory_item_id (+)
AND OFFC.inv_master_organization_id = OFFI.organization_id (+)
AND ONC.inventory_item_id = ONI.inventory_item_id (+)
AND ONC.inv_master_organization_id = ONI.organization_id (+)
AND DIS.status <> ''Terminated''
AND DIS.workorder_id = :1';
SELECT PP.OPERATION_SEQUENCE "OperationSequenceNumber",
PP.RESOURCE_SEQUENCE "ResourceSequenceNumber" ,
PP.RESOURCE_CODE "Item" ,
PP.RESOURCE_TYPE_NAME "ItemType" ,
PP.RESOURCE_NAME "Description" ,
PP.QUANTITY "Quantity" ,
PP.DURATION "Duration" ,
PP.UOM_NAME "UOM"
FROM AHL_PP_REQUIREMENT_V PP,
AHL_WORKORDERS WO
WHERE PP.JOB_ID = WO.WORKORDER_ID
AND PP.resource_type_code = 1
AND WO.WORKORDER_ID = :1';
SELECT AVB.VISIT_ID "VisitId",
AVB.VISIT_NUMBER "VisitNumber",
--TO_CHAR(AVB.START_DATE_TIME, ''YYYY-MM-DD hh24:mi:ss'') "PlannedStartDate",
DECODE(AVB.START_DATE_TIME, NULL, NULL, TO_CHAR(AVB.START_DATE_TIME,''YYYY-MM-DD'')
||''T''
||TO_CHAR(AVB.START_DATE_TIME, ''hh24:mi:ss'') ) "PlannedStartDate",
--TO_CHAR(AVB.CLOSE_DATE_TIME, ''YYYY-MM-DD hh24:mi:ss'') "PlannedEndDate",
DECODE(AVB.CLOSE_DATE_TIME, NULL, NULL, TO_CHAR(AVB.CLOSE_DATE_TIME,''YYYY-MM-DD'')
||''T''
||TO_CHAR(AVB.CLOSE_DATE_TIME, ''hh24:mi:ss'') ) "PlannedEndDate",
(SELECT --TO_CHAR(WDJ.DATE_RELEASED, ''YYYY-MM-DD hh24:mi:ss'')
DECODE(WDJ.DATE_RELEASED, NULL, NULL, TO_CHAR(WDJ.DATE_RELEASED,''YYYY-MM-DD'')
||''T''
||TO_CHAR(WDJ.DATE_RELEASED, ''hh24:mi:ss'') )
FROM AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.VISIT_TASK_ID IS NULL
AND AVB.VISIT_ID = WO.VISIT_ID
) "ReleaseDate",
AHL_UTILITY_PVT.GET_UNIT_NAME(AVB.ITEM_INSTANCE_ID) "UnitName",
MTSB.CONCATENATED_SEGMENTS "Item",
CSIS.SERIAL_NUMBER "SerialNumber",
FLVT.MEANING "VisitType",
HROU.NAME "Organization",
BDPT.DESCRIPTION "Department",
-- (Select INCIDENT_NUMBER from CS_INCIDENTS_ALL_B where INCIDENT_ID = AVB.SERVICE_REQUEST_ID) "NonRoutineTitle",
MTLI.subinventory_code "SubInventory",
DECODE(MSI.segment19, NULL, MTLI.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MTLI.concatenated_segments)
|| fnd_flex_ext.get_delimiter(''INV'', ''MTLL'', 101)
|| INV_ProjectLocator_PUB.get_project_number(MSI.segment19)
|| fnd_flex_ext.get_delimiter(''INV'', ''MTLL'', 101)
|| INV_ProjectLocator_PUB.get_task_number(MSI.segment20)) "Locator",
(Select INCIDENT_NUMBER from CS_INCIDENTS_ALL_B where INCIDENT_ID = AVB.SERVICE_REQUEST_ID) "NonRoutineTitle"
FROM AHL_VISITS_B AVB,
MTL_SYSTEM_ITEMS_B_KFV MTSB,
CSI_ITEM_INSTANCES CSIS,
FND_LOOKUP_VALUES_VL FLVT,
HR_ALL_ORGANIZATION_UNITS HROU,
BOM_DEPARTMENTS BDPT,
mtl_item_locations_kfv MTLI,
mtl_item_locations MSI
WHERE VISIT_ID = :1
AND AVB.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND AVB.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND AVB.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+)
AND FLVT.LOOKUP_TYPE(+) = ''AHL_PLANNING_VISIT_TYPE''
AND FLVT.LOOKUP_CODE(+) = AVB.VISIT_TYPE_CODE
AND AVB.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND AVB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND MTLI.inventory_location_id(+) = AVB.inv_locator_id
AND MSI.inventory_location_id(+) = AVB.inv_locator_id';
'SELECT unit_effectivity_id "UnitEffectivityId",
MRB.TITLE "Title"
FROM AHL_UNIT_EFFECTIVITIES_B UE,
AHL_MR_HEADERS_B MRB,
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = :1
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
AND UE.MR_HEADER_ID = MRB.MR_HEADER_ID';
SELECT DISTINCT
VST.UNIT_EFFECTIVITY_ID "UnitEffectivityId",
MRB.TITLE "Title"
FROM AHL_MR_HEADERS_B MRB,
AHL_VISIT_TASKS_B VST
WHERE VST.SERVICE_REQUEST_ID = :1
AND VST.MR_ID = MRB.MR_HEADER_ID
';
SELECT visit_id
INTO l_visit_id
FROM ahl_workorders
WHERE workorder_id = l_wo_id_tbl(1);
SELECT vst.unit_effectivity_id
BULK COLLECT INTO l_ue_id_tbl
FROM ahl_visit_tasks_b vst, ahl_unit_effectivities_b ue, ahl_workorders wo
WHERE vst.visit_id = l_visit_id
and vst.task_type_code = 'SUMMARY'
and wo.visit_task_id = vst.visit_task_id
and wo.status_code NOT IN ('7','12','14','15','17','22')
and wo.master_workorder_flag = 'Y'
and vst.unit_effectivity_id = ue.unit_effectivity_id
order by ue.object_type;
SELECT visit_id
INTO l_visit_id
FROM ahl_visit_tasks_b
WHERE unit_effectivity_id = l_ue_id_tbl(1)
AND NVL(status_code,'DELETED') <> 'DELETED'
AND task_type_code = 'SUMMARY';
SELECT VST.unit_effectivity_id
INTO l_ue_id_tbl(1)
FROM ahl_workorders WO,
ahl_visit_tasks_b VST
WHERE WO.visit_task_id = VST.visit_task_id
AND WO.workorder_id = l_wo_id_tbl(1);
select counter_value,
(select ccr.net_reading
from csi_counter_readings ccr
where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
and nvl(ccr.disabled_flag,'N') = 'N') net_reading
into l_counter_value, l_net_reading
from ahl_unit_accomplishmnts ua, csi_counter_associations cca, csi_counters_vl cc
where ua.unit_effectivity_id = l_unit_eff_id
and ua.counter_id = cc.counter_id
and cc.counter_template_name = get_mr_details_rec.DueCounterName
and cca.counter_id = cc.counter_id
and source_object_code = 'CP'
and source_object_id = get_mr_details_rec.csi_item_instance_id;
dummy := DBMS_XMLGEN.CONVERT(get_mr_details_rec.LAST_UPDATE_DATE);
SELECT workorder_id
INTO l_wo_id
FROM ahl_workorders
WHERE visit_task_id IS NULL
AND visit_id = p_object_id;
SELECT UC.UNIT_CONFIG_HEADER_ID, UC.NAME, VST.item_instance_id
INTO l_uc_id, l_unit_name, l_count_instance_id
FROM AHL_VISITS_B VST, AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.CSI_ITEM_INSTANCE_ID(+) = VST.ITEM_INSTANCE_ID
AND VST.VISIT_ID = p_object_id;
SELECT VT.instance_id
INTO l_count_instance_id
FROM ahl_workorders WO,
ahl_visit_tasks_b VT
WHERE WO.visit_task_id = VT.visit_task_id
AND WO.workorder_id = l_wo_id;
SELECT WO.workorder_id, VT.INSTANCE_ID
INTO l_wo_id, l_count_instance_id
FROM ahl_workorders WO,
ahl_visit_tasks_b vt
WHERE wo.visit_task_id = vt.visit_task_id
AND vt.task_type_code = 'SUMMARY'
AND vt.UNIT_EFFECTIVITY_ID = p_object_id ;
SELECT wo.workorder_id
BULK COLLECT INTO l_wo_id_tbl
FROM ahl_workorders wo,
ahl_visit_tasks_b vt
WHERE wo.visit_task_id = vt.visit_task_id
AND vt.task_type_code = 'UNASSOCIATED'
AND wo.status_code NOT IN ('7','12','14','15','17','22')
AND vt.visit_id = l_visit_id;
SELECT vt.task_type_code
INTO l_task_type
FROM ahl_workorders wo,
ahl_visit_tasks_b vt
WHERE wo.visit_task_id = vt.visit_task_id
AND wo.workorder_id = l_wo_id_tbl(1);
SELECT JCG_REQUEST_ID,OBJECT_TYPE_CODE,OBJECT_ID,SPLIT_FLAG
FROM ahl_enigma_jcg_details
WHERE JCG_REQUEST_ID = p_jcg_id;
l_err_update_status VARCHAR2(30);
l_parameter_list.DELETE;
l_err_update_status := HANDLE_JCG_ERRORS(l_enigma_request_id , to_char(l_conc_request_id) || ':' || l_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Error Update Status :' || l_err_update_status);
l_err_update_status := HANDLE_JCG_ERRORS(l_enigma_request_id , to_char(l_conc_request_id) || ':' || l_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Error Update Status :' || l_err_update_status);
l_err_update_status := HANDLE_JCG_ERRORS(l_enigma_request_id , to_char(l_conc_request_id) || ':' || l_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Error Update Status :' || l_err_update_status);
SELECT JCG_REQUEST_ID,OBJECT_TYPE_CODE,OBJECT_ID,SPLIT_FLAG,CONCURRENT_REQUEST_ID
FROM ahl_enigma_jcg_details
WHERE JCG_REQUEST_ID = p_jcg_id;
select 'x' from ahl_enigma_jcg_details
where object_type_code = p_object_type_code
and object_id = p_object_id
and jcg_request_id > p_jcg_id
AND concurrent_request_id > p_concurrent_request_id;
SELECT JCG_REQUEST_ID,OBJECT_TYPE_CODE,OBJECT_ID,SPLIT_FLAG,CONCURRENT_REQUEST_ID
FROM ahl_enigma_jcg_details
WHERE JCG_REQUEST_ID = p_jcg_id;
';
l_xsl_part2 := '
';
l_xsl_part3 := '
-
';
<xsl:attribute name="value">
<xsl:value-of select="wp:Route/wp:RouteTitle"/>
</xsl:attribute>
<xsl:attribute name="value">
<xsl:value-of select="wp:OpDescription"/>
</xsl:attribute>
';
l_xsl_part5 := '
';
l_xsl_part6 := '
-
';
<xsl:attribute name="value">
<xsl:value-of select="wp:Route/wp:RouteTitle"/>
</xsl:attribute>
<xsl:attribute name="value">
<xsl:value-of select="wp:OpDescription"/>
</xsl:attribute>
';
l_xsl_part8 := '
-
<xsl:attribute name="value">
<xsl:value-of select="wp:Route/wp:RouteTitle"/>
</xsl:attribute>
<xsl:attribute name="value">
<xsl:value-of select="wp:OpDescription"/>
</xsl:attribute>
';
SELECT
char_id,
organization_id,
prompt_sequence ,
prompt,
enabled_flag,
default_value,
default_value_id,
result_column_name,
values_exist_flag,
displayed_flag,
plan_name,
plan_description,
char_name,
datatype,
hardcoded_column,
developer_name
FROM
QA_PLAN_CHARS_V QA
WHERE
plan_id = p_plan_id
AND enabled_flag = 1
AND displayed_flag = 1
order by prompt_sequence;
l_query_string := 'SELECT RESULTS.OCCURRENCE "Occurence"';
l_dummy_query := ' UNION ALL SELECT null "Occurence"';
SELECT WO.OBJECT_VERSION_NUMBER,
WO.JOB_NUMBER ,
WO.JOB_DESCRIPTION ,
WO.JOB_STATUS_MEANING ,
WO.DEPARTMENT_NAME ,
DECODE(WO.SCHEDULED_START_DATE, NULL, NULL, TO_CHAR(WO.SCHEDULED_START_DATE,'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.SCHEDULED_START_DATE, 'hh24:mi:ss') ) SCHEDULED_START_DATE,
DECODE(WO.SCHEDULED_END_DATE, NULL, NULL, TO_CHAR(WO.SCHEDULED_END_DATE, 'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.SCHEDULED_END_DATE, 'hh24:mi:ss') ) SCHEDULED_END_DATE,
WO.UNIT_NAME ,
WO.WO_PART_NUMBER ,
WO.ITEM_INSTANCE_NUMBER ,
WO.SERIAL_NUMBER ,
WO.LOT_NUMBER ,
WO.VISIT_NUMBER ,
--RO.MODEL_MEANING ,
MODELTYPE.MEANING MODEL_MEANING ,
DECODE(WIP.DATE_RELEASED, NULL, NULL, TO_CHAR(WIP.DATE_RELEASED,'YYYY-MM-DD')
||'T'
||TO_CHAR(WIP.DATE_RELEASED, 'hh24:mi:ss') ) DATE_RELEASED,
--WIP.NET_QUANTITY ,
(Select quantity from ahl_visit_tasks_b where visit_task_id = w.visit_task_id) NET_QUANTITY,
--UE.ATA_CODE ,
DECODE(RO.ENIGMA_ROUTE_ID, NULL, NULL, SUBSTR(route_no, instr(route_no, ':',1,1)+1)) ATA_CODE,
-- fix for bug# 10016417
--(SELECT CSIN.INCIDENT_NUMBER
--FROM CS_INCIDENTS_ALL_B CSIN
--WHERE CSIN.INCIDENT_ID = UE.cs_incident_id
--)
/*(SELECT CSIN.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_B CSIN, AHL_VISIT_TASKS_B TSK
WHERE CSIN.INCIDENT_ID = TSK.SERVICE_REQUEST_ID
AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
)
INCIDENT_NUMBER,*/
INCIDENTDET.INCIDENT_NUMBER,
INCIDENTDET.NAME INCIDENT_TYPE,
INCIDENTDET.SUMMARY INCIDENT_SUMMARY,
(SELECT HZ.PARTY_NAME
FROM HZ_PARTIES HZ
WHERE HZ.PARTY_ID = CSI.OWNER_PARTY_ID
)
PARTY_NAME,
(SELECT WORKORDER_NAME
FROM AHL_WORKORDERS WO --,
--AHL_UNIT_EFFECTIVITIES_B UE1
WHERE WO.WORKORDER_ID = UE.ORIGINATING_WO_ID
--AND WO.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
)
ORIGINATINGWORKORDER,
WO.ITEM_INSTANCE_ID ,
DECODE(W.PLAN_ID,NULL,'F','T') ISQUALITYENABLED,
WO.ROUTE_ID
FROM AHL_WORKORDER_TASKS_V WO ,
WIP_DISCRETE_JOBS WIP ,
AHL_ROUTES_B RO ,
AHL_UNIT_EFFECTIVITIES_B UE ,
--HZ_PARTIES HZ ,
CSI_ITEM_INSTANCES CSI ,
--CS_INCIDENTS_ALL_B CSIN ,
--AHL_VISIT_TASKS_VL VTS ,
AHL_WORKORDERS W ,
(SELECT LOOKUP_CODE ,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ENIGMA_MODEL_CODE'
AND LANGUAGE = USERENV('LANG')
)
MODELTYPE,
(SELECT CS.INCIDENT_NUMBER,
CS_TYPE.NAME,
CS_TL.SUMMARY,
TSK.VISIT_TASK_ID
FROM CS_INCIDENTS_ALL_B CS,
AHL_VISIT_TASKS_B TSK,
CS_INCIDENT_TYPES_TL CS_TYPE,
CS_INCIDENTS_ALL_TL CS_TL
WHERE CS.INCIDENT_ID = TSK.SERVICE_REQUEST_ID
--AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND CS.INCIDENT_TYPE_ID = CS_TYPE.INCIDENT_TYPE_ID
AND CS.INCIDENT_ID = CS_TL.INCIDENT_ID
AND CS_TYPE.LANGUAGE = USERENV('LANG')
AND CS_TL.LANGUAGE = USERENV('LANG')
) INCIDENTDET
WHERE WO.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
AND WO.ROUTE_ID = RO.ROUTE_ID (+)
AND UE.UNIT_EFFECTIVITY_ID(+) = WO.UNIT_EFFECTIVITY_ID
AND CSI.INSTANCE_ID = WO.ITEM_INSTANCE_ID
--AND CSI.OWNER_PARTY_ID(+) = HZ.PARTY_ID
AND MODELTYPE.LOOKUP_CODE(+) = RO.MODEL_CODE
--AND VTS.SERVICE_REQUEST_ID = CSIN.INCIDENT_ID(+)
--AND WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
AND W.workorder_id = WO.workorder_id
AND INCIDENTDET.VISIT_TASK_ID(+) = WO.VISIT_TASK_ID
AND WO.WORKORDER_ID = c_wo_id;
SELECT MSI.CONCATENATED_SEGMENTS,
WRO.OPERATION_SEQ_NUM OPERATION_SEQUENCE ,
MSI.DESCRIPTION ,
IG.NAME ,
WRO.REQUIRED_QUANTITY REQUESTED_QUANTITY ,
DECODE(WRO.date_required, NULL, NULL, TO_CHAR(WRO.date_required, 'YYYY-MM-DD')
||'T'
||TO_CHAR(WRO.date_required, 'hh24:mi:ss') ) required_date,
WRO.QUANTITY_ISSUED ISSUED_QTY ,
MSI.PRIMARY_UNIT_OF_MEASURE UOM ,
ASM.SCHEDULED_MATERIAL_ID,
(SELECT f.meaning from fnd_lookup_values_vl f
WHERE f.lookup_type = 'ITEM_TYPE' and f.lookup_code = msi.item_type) item_type
FROM --AHL_JOB_OPER_MATERIALS_V JM,
WIP_REQUIREMENT_OPERATIONS WRO, AHL_SCHEDULE_MATERIALS ASM,
AHL_ITEM_GROUPS_B IG, AHL_WORKORDERS WO, MTL_SYSTEM_ITEMS_KFV MSI,AHL_WORKORDER_OPERATIONS AWOP
WHERE WO.wip_entity_id = WRO.wip_entity_id
AND WO.visit_task_id = ASM.visit_task_id
AND ASM.operation_sequence = WRO.operation_seq_num
AND ASM.inventory_item_id = WRO.inventory_item_id
AND WRO.organization_id = MSI.organization_id
AND WRO.inventory_item_id = MSI.inventory_item_id
AND IG.ITEM_GROUP_ID(+) = ASM.ITEM_GROUP_ID
AND ASM.status in ('ACTIVE', 'IN-SERVICE', 'HISTORY')
AND AWOP.workorder_operation_id = ASM.workorder_operation_id
AND AWOP.WORKORDER_ID = WO.WORKORDER_ID
AND WO.WORKORDER_ID = c_wo_id
ORDER BY 2;
SELECT WOR.OPERATION_SEQ_NUM operation_sequence,
WOR.RESOURCE_SEQ_NUM resource_sequence ,
BOM.RESOURCE_CODE resource_code,
BOM.DESCRIPTION resource_name,
MFG.MEANING resource_type_name,
WOR.ASSIGNED_UNITS quantity,
DECODE(nvl(WOR.ASSIGNED_UNITS,0), 0, 0, WOR.USAGE_RATE_OR_AMOUNT/WOR.ASSIGNED_UNITS) duration ,
(SELECT A.UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE A
WHERE A.UOM_CODE=BOM.UNIT_OF_MEASURE) uom_name,
WOR.RESOURCE_ID,
(select awo.operation_id from AHL_WORKORDER_OPERATIONS AWO
where AWO.workorder_id = wo.workorder_id
and awo.operation_sequence_num = WOR.OPERATION_SEQ_NUM) operation_id
FROM WIP_OPERATION_RESOURCES WOR,
BOM_RESOURCES BOM, MFG_LOOKUPS MFG, AHL_WORKORDERS WO
WHERE WO.workorder_id = c_wo_id
AND WOR.resource_id = BOM.resource_id
AND WO.wip_entity_id = WOR.wip_entity_id
AND MFG.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
AND MFG.LOOKUP_CODE(+) = BOM.RESOURCE_TYPE
ORDER BY 1,2;
SELECT WO.operation_sequence_num,
WO.operation_code ,
WO.description ,
WO.status ,
WO.department_name ,
--WO.scheduled_start_date ,
--WO.scheduled_end_date ,
DECODE(WO.scheduled_start_date, NULL, NULL, TO_CHAR(WO.scheduled_start_date,'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.scheduled_start_date, 'hh24:mi:ss') ) scheduled_start_date,
DECODE(WO.scheduled_end_date, NULL, NULL, TO_CHAR(WO.scheduled_end_date, 'YYYY-MM-DD')
||'T'
||TO_CHAR(WO.scheduled_end_date, 'hh24:mi:ss') ) scheduled_end_date,
DECODE(WO.plan_id,NULL,'F','T') isQualityEnabled ,
WO.workorder_operation_id
FROM AHL_WORKORDER_OPERATIONS_V WO --AHL_OPERATIONS_B OP
WHERE -- WO.operation_id = OP.operation_id(+) and
WO.workorder_id = c_wo_id
ORDER BY 1 ;
SELECT R.object_version_number ,
R.route_no ,
T.title ,
RTTYPE.MEANING route_type ,
R.revision_number ,
R.enigma_doc_id ,
R.enigma_route_id ,
ZONE.MEANING zone ,
SUBZONE.MEANING sub_zone ,
PROCESS.MEANING process ,
JOBCARDLYT.MEANING job_card_lyt_meaning ,
R.model_code ,
R.last_update_date
FROM AHL_ROUTES_B R ,
AHL_ROUTES_TL T ,
(SELECT LOOKUP_CODE ,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_RM_JOB_CARD_LYT'
AND LANGUAGE = USERENV('LANG')
)
JOBCARDLYT ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_SUB_ZONE'
AND LANGUAGE = USERENV('LANG')
)
SUBZONE ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_PROCESS_CODE'
AND LANGUAGE = USERENV('LANG')
)
PROCESS ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ZONE'
AND LANGUAGE = USERENV('LANG')
)
ZONE ,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ROUTE_TYPE'
AND LANGUAGE = USERENV('LANG')
)
RTTYPE
WHERE R.ROUTE_ID = T.ROUTE_ID
AND T.LANGUAGE = USERENV('LANG')
AND JOBCARDLYT.LOOKUP_CODE (+) = R.JOBCARDLYT_CODE
AND ZONE.LOOKUP_CODE (+) = R.ZONE_CODE
AND SUBZONE.LOOKUP_CODE (+) = R.SUB_ZONE_CODE
AND PROCESS.LOOKUP_CODE (+) = R.PROCESS_CODE
AND RTTYPE.LOOKUP_CODE (+) = R.ROUTE_TYPE_CODE
AND R.route_id = c_route_id;
SELECT DISTINCT
WO.OPERATION_SEQUENCE_NUM,
WO.OPERATION_CODE ,
WO.DESCRIPTION ,
WO.OPERATION_TYPE ,
OP.ENIGMA_DOC_ID ,
OP.OPERATION_ID ,
OP.REVISION_NUMBER ,
OP.LAST_UPDATE_DATE
FROM AHL_WORKORDER_OPERATIONS_V WO,
AHL_ROUTE_OPERATIONS RP ,
AHL_OPERATIONS_B OP
WHERE RP.OPERATION_ID(+) = WO.OPERATION_ID
AND WO.OPERATION_ID = OP.OPERATION_ID(+)
AND RP.route_id = c_route_id
AND WO.workorder_id = c_wo_id
ORDER BY 1;
SELECT DISTINCT plan_name,
plan_description
FROM QA_PLAN_CHARS_V
WHERE plan_id = c_qa_plan_id;
SELECT plan_id,
collection_id
FROM AHL_WORKORDER_OPERATIONS
WHERE workorder_operation_id = c_wo_op_id;
SELECT plan_id,
collection_id
FROM AHL_WORKORDERS
WHERE workorder_id = c_wo_id;
SELECT occurrence
FROM QA_RESULTS
WHERE collection_id = c_collection_id;
SELECT visit_number
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_wo_id;
SELECT char_id CharId ,
prompt_sequence PromptSequence,
prompt Prompt ,
default_value DefaultValue ,
enabled_flag ,
displayed_flag
FROM QA_PLAN_CHARS
WHERE plan_id = c_plan_id
ORDER BY 2;
SELECT segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
segment6 ,
segment7 ,
segment8 ,
segment9 ,
segment10 ,
segment11 ,
segment12 ,
segment13 ,
segment14 ,
segment15 ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM ahl_routes_b
WHERE route_id = c_route_id;
SELECT panel_type ,
panel_type_desc
FROM AHL_RT_OPER_ACCESS_PANELS_V
WHERE ASSOCIATION_TYPE_CODE = 'ROUTE'
AND object_id = c_route_id;
SELECT 'Y'
FROM AHL_RT_OPER_RESOURCES AR
WHERE AR.association_type_code='ROUTE'
AND AR.object_id=p_route_id
AND ROWNUM < 2;
SELECT DOC.DOCUMENT_NO "DocumentNumber" ,
DOC.DOCUMENT_TITLE "DocumentTitle" ,
DOC.DOC_TYPE_CODE "DocumentType" ,
DOCV.DOC_SUB_TYPE_CODE "DocumentSubType" ,
DOC.ASO_OBJECT_TYPE_DESC "AsoObjectTypeDesc" ,
NULL "OperationSequenceNumber",
DOC.REVISION_NO "RevisionNumber" ,
DOC.CHAPTER "Chapter" ,
DOC.SECTION "Section" ,
DOC.SUBJECT "Subject" ,
DOC.PAGE "Page" ,
DOC.FIGURE "Figure" ,
DOC.NOTE "Note"
FROM --AHL_WORKORDERS WO ,
AHL_DOCUMENT_ASSOS_V DOC,
AHL_DOCUMENTS_V DOCV
WHERE --WO.ROUTE_ID = DOC.ASO_OBJECT_ID
DOC.DOCUMENT_ID = DOCV.DOCUMENT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''ROUTE''
AND DOC.ASO_OBJECT_ID = :1';
SELECT DOC.DOCUMENT_NO "DocumentNumber" ,
DOC.DOCUMENT_TITLE "DocumentTitle" ,
DOC.DOC_TYPE_CODE "DocumentType" ,
DOCV.DOC_SUB_TYPE_CODE "DocumentSubType" ,
DOC.ASO_OBJECT_TYPE_DESC "AsoObjectTypeDesc" ,
WOP.OPERATION_SEQUENCE_NUM "OperationSequenceNumber",
DOC.REVISION_NO "RevisionNumber" ,
DOC.CHAPTER "Chapter" ,
DOC.SECTION "Section" ,
DOC.SUBJECT "Subject" ,
DOC.PAGE "Page" ,
DOC.FIGURE "Figure" ,
DOC.NOTE "Note"
FROM AHL_WORKORDER_OPERATIONS WOP,
AHL_DOCUMENT_ASSOS_V DOC ,
AHL_DOCUMENTS_V DOCV
WHERE WOP.OPERATION_ID = DOC.ASO_OBJECT_ID
AND DOC.DOCUMENT_ID = DOCV.DOCUMENT_ID
AND DOC.ASO_OBJECT_TYPE_CODE = ''OPERATION''
AND WOP.WORKORDER_ID = :1
AND WOP.OPERATION_ID = :2';
SELECT MA.ATTRIBUTE_CATEGORY "AttributeCategory",
MA.ATTRIBUTE1 "Attribute1" ,
MA.ATTRIBUTE2 "Attribute2" ,
MA.ATTRIBUTE3 "Attribute3" ,
MA.ATTRIBUTE4 "Attribute4" ,
MA.ATTRIBUTE5 "Attribute5" ,
MA.ATTRIBUTE6 "Attribute6" ,
MA.ATTRIBUTE7 "Attribute7" ,
MA.ATTRIBUTE8 "Attribute8" ,
MA.ATTRIBUTE9 "Attribute9" ,
MA.ATTRIBUTE10 "Attribute10" ,
MA.ATTRIBUTE11 "Attribute11" ,
MA.ATTRIBUTE12 "Attribute12" ,
MA.ATTRIBUTE13 "Attribute13" ,
MA.ATTRIBUTE14 "Attribute14" ,
MA.ATTRIBUTE15 "Attribute15"
FROM AHL_SCHEDULE_MATERIALS MA--, AHL_JOB_OPER_MATERIALS_V MV
WHERE MA.SCHEDULED_MATERIAL_ID = :1';
SELECT RE.ATTRIBUTE_CATEGORY "AttributeCategory",
RE.ATTRIBUTE1 "Attribute1" ,
RE.ATTRIBUTE2 "Attribute2" ,
RE.ATTRIBUTE3 "Attribute3" ,
RE.ATTRIBUTE4 "Attribute4" ,
RE.ATTRIBUTE5 "Attribute5" ,
RE.ATTRIBUTE6 "Attribute6" ,
RE.ATTRIBUTE7 "Attribute7" ,
RE.ATTRIBUTE8 "Attribute8" ,
RE.ATTRIBUTE9 "Attribute9" ,
RE.ATTRIBUTE10 "Attribute10" ,
RE.ATTRIBUTE11 "Attribute11" ,
RE.ATTRIBUTE12 "Attribute12" ,
RE.ATTRIBUTE13 "Attribute13" ,
RE.ATTRIBUTE14 "Attribute14" ,
RE.ATTRIBUTE15 "Attribute15"
FROM AHL_RT_OPER_RESOURCES RE, ahl_resource_mappings ar, bom_resources br
WHERE br.organization_id = ar.BOM_ORG_ID
AND br.resource_id = ar.BOM_RESOURCE_ID
AND br.resource_id = :1
AND ar.aso_resource_id = re.aso_resource_id
AND re.ASSOCIATION_TYPE_CODE = :2
AND re.object_id = :3';
SELECT OP.ATTRIBUTE_CATEGORY "AttributeCategory",
OP.ATTRIBUTE1 "Attribute1" ,
OP.ATTRIBUTE2 "Attribute2" ,
OP.ATTRIBUTE3 "Attribute3" ,
OP.ATTRIBUTE4 "Attribute4" ,
OP.ATTRIBUTE5 "Attribute5" ,
OP.ATTRIBUTE6 "Attribute6" ,
OP.ATTRIBUTE7 "Attribute7" ,
OP.ATTRIBUTE8 "Attribute8" ,
OP.ATTRIBUTE9 "Attribute9" ,
OP.ATTRIBUTE10 "Attribute10" ,
OP.ATTRIBUTE11 "Attribute11" ,
OP.ATTRIBUTE12 "Attribute12" ,
OP.ATTRIBUTE13 "Attribute13" ,
OP.ATTRIBUTE14 "Attribute14" ,
OP.ATTRIBUTE15 "Attribute15"
FROM AHL_OPERATIONS_B OP
WHERE OP.OPERATION_ID = :1';
SELECT char_id "CharId" ,
prompt_sequence "PromptSequence",
prompt "Prompt" ,
default_value "DefaultValue"
FROM QA_PLAN_CHARS
WHERE plan_id = :1';
SELECT QC.char_id "CharId",
CI.attribute_value "AttributeValue"
FROM CSI_IEA_VALUES CI,
QA_PLAN_CHARS QC ,
QA_RESULTS_V QR
WHERE QC.plan_id = QR.plan_id
AND CI.instance_id(+) = QR.csi_instance_id
AND QR.collection_id = :1';
SELECT cc.counter_template_name "CounterName",
(SELECT ccr.net_reading
FROM csi_counter_readings ccr
WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
AND NVL(ccr.disabled_flag,''N'') = ''N''
)
"CurrentReading"
--FROM csi_counter_associations cca, csi_counters_vl cc, ahl_workorders_v wo
FROM csi_counter_associations cca,
csi_counters_vl cc ,
ahl_workorders WO ,
ahl_visit_tasks_b tsk
WHERE cca.counter_id = cc.counter_id
AND wo.visit_task_id = tsk.visit_task_id
AND source_object_code = ''CP''
--AND source_object_id = wo.item_instance_id
AND source_object_id = tsk.instance_id
AND wo.workorder_id = :1';
SELECT cc.counter_template_name "CounterName",
(SELECT ccr.net_reading
FROM csi_counter_readings ccr
WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
AND NVL(ccr.disabled_flag,''N'') = ''N''
)
"CurrentReading"
FROM csi_counter_associations cca,
csi_counters_vl cc
WHERE cca.counter_id = cc.counter_id
AND source_object_code = ''CP''
AND source_object_id = :1';
SELECT OFFI.concatenated_segments RemovedItem ,
OFFC.serial_number RemovedSerial ,
PC.removed_quantity RemovedQuantity ,
OFFC.lot_number RemovedLot ,
OFFC.inventory_revision RemovedRevision ,
ONI.concatenated_segments InstalledItem ,
ONC.serial_number InstalledSerial ,
PC.installed_quantity InstalledQuantity,
ONC.lot_number InstalledLot ,
ONC.inventory_revision InstalledRevision,
DIS.position_reference position
FROM ahl_part_changes_v PC ,
csi_item_instances OFFC ,
mtl_system_items_kfv OFFI,
csi_item_instances ONC ,
mtl_system_items_kfv ONI ,
ahl_prd_dispositions_v DIS
WHERE PC.part_change_id = DIS.part_change_id
AND PC.removed_instance_id = OFFC.instance_id (+)
AND PC.installed_instance_id = ONC.instance_id (+)
AND OFFC.inventory_item_id = OFFI.inventory_item_id (+)
AND OFFC.inv_master_organization_id = OFFI.organization_id (+)
AND ONC.inventory_item_id = ONI.inventory_item_id (+)
AND ONC.inv_master_organization_id = ONI.organization_id (+)
AND DIS.status <> ''Terminated''
AND DIS.workorder_id = :1';
SELECT PP.OPERATION_SEQUENCE "OperationSequenceNumber",
PP.RESOURCE_SEQUENCE "ResourceSequenceNumber" ,
PP.RESOURCE_CODE "Item" ,
PP.RESOURCE_TYPE_NAME "ItemType" ,
PP.RESOURCE_NAME "Description" ,
PP.QUANTITY "Quantity" ,
PP.DURATION "Duration" ,
PP.UOM_NAME "UOM"
FROM AHL_PP_REQUIREMENT_V PP,
AHL_WORKORDERS WO
WHERE PP.JOB_ID = WO.WORKORDER_ID
AND PP.resource_type_code = 1
AND WO.WORKORDER_ID = :1';
SELECT FLV.meaning
INTO l_position_meaning
FROM ahl_mc_relationships MC,
FND_LOOKUP_VALUES_VL FLV
WHERE FLV.LOOKUP_CODE = MC.POSITION_REF_CODE
AND FLV.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
AND FLV.ENABLED_FLAG = 'Y'
AND FLV.END_DATE_ACTIVE >= sysdate
AND MC.relationship_id = l_relationship_id;
dummy := DBMS_XMLGEN.CONVERT(route_details_rec.LAST_UPDATE_DATE);
select concatenated_segments into l_concat_segment
from ahl_routes_b_kfv
where route_id = l_route_id;
dummy := DBMS_XMLGEN.CONVERT(wo_op_details_rec.LAST_UPDATE_DATE);