select * from (
SELECT proj.PROJ_ID project_name ,a.PROJECT_ID,'Hull' hull_Or_Topside ,'NA' object_Code,a.SYSTEM_NO,a.SUB_SYSTEM_NO,a.cable_No,'CABLE' item_type,to_char(a.LENGTH) quantity,'METER' quantity_unit,a.CP_ORDER_NO pullin_cable_report
,a.MR_SICODE conection_report,a.MR_SICODE assembly_report,'NA' hydrostec_test,'NA' calibration_report,a.MR_SICODE continuity_test,a.MR_SICODE megger_test ,a.MR_SICODE HIPOT_test,
nvl(CASE WHEN CONNECTION_END_ENDTIME IS NOT NULL THEN '05 - Assembled Item'
WHEN CONNECTION_END_ENDTIME IS NULL THEN '00 - Not received'
WHEN a.cp_Finish_Date IS NOT NULL THEN '02 - Waiting Inspection'
ELSE '01 - Assembly not started' end,'00 - Not received') status_eit,
'NA' megablock ,'NA' Elevation_or_compartment,a.certification certification,'NA' nr10,'NA' ex,'NA' blank_test_report,a.MR_SICODE loop_Test_Report
FROM CE_CABLE a
LEFT JOIN proj ON a.PROJECT_ID = proj.PROJ_NO
where a.PROJECT_ID in (select to_number(regexp_replace(i.name, '[^0-9]', '')) from BASE_DICTIONARY d join base_dictionary_info i on d.id=i.dicid where d.code='P80H614projectweld' AND i.STATE = 1) and a.is_delete = 0 AND a.is_top = 1
<if test="model.projectId != null">
AND a.PROJECT_ID = #{model.projectId}
</if>
UNION all
SELECT proj.PROJ_ID project_name ,a.PROJECT_ID,'Hull' hull_Or_Topside
,CASE WHEN a.REMARKS = 'Instrument' THEN a.object_Code ELSE a.big_Reg end object_Code
, a.system_no SYSTEM_NO
, a.SUB_SYSTEM_NO SUB_SYSTEM_NO
,a.TAG_NO cable_No
,CASE WHEN a.REMARKS = 'Instrument' THEN 'INSTRUMENT' ELSE 'EQUIPMENT' end item_type,
'1' quantity,
'SET' quantity_unit,
'NA' pullin_cable_report,
a.MR_SICODE conection_report,
a.MR_SICODE assembly_report,
'NA' hydrostec_test,
CASE WHEN a.REMARKS = 'Instrument' THEN a.Calibration_Report ELSE 'NA' END calibration_report,
a.MR_SICODE continuity_test,a.MR_SICODE megger_test ,a.MR_SICODE HIPOT_test,
nvl(CASE WHEN a.instal_endtime is NULL THEN '00 - Not received' ELSE '05 - Assembled Item' end,'00 - Not received') status_eit,
CASE WHEN a.REMARKS = 'Instrument' THEN a.object_Code end megablock,
CASE WHEN a.REMARKS = 'Instrument' THEN a.room_No end Elevation_or_compartment
,a.certification certification
,a.nr10 nr10
,ex_Yn en
,CASE WHEN a.REMARKS = 'Instrument' THEN a.tct_Discipline end blank_test_report,a.MR_SICODE loop_Test_Report
FROM T_EQUIPMENT a
LEFT JOIN proj ON a.PROJECT_ID = proj.PROJ_NO
where a.IS_DELETE = 0 AND (a.REMARKS = 'Instrument' OR TCT_DISCIPLINE = 'EIT Equipment' ) and a.PROJECT_ID in (select to_number(regexp_replace(i.name, '[^0-9]', '')) from BASE_DICTIONARY d join base_dictionary_info i on d.id=i.dicid where d.code='P80H614projectweld' AND i.STATE = 1)
<if test="model.projectId != null">
AND a.PROJECT_ID = #{model.projectId}
</if>
UNION all
SELECT
a.PROJ_NO project_name ,a.PROJ_ID PROJECT_ID,a.hull_Or_Topside ,a.AREA as object_Code,
a.PI_SYCODE SYSTEM_NO,a.PI_SSCODE SUB_SYSTEM_NO,
a.LINE_NUMBER cable_No,
a.ITEM_TYPE,
to_char ( a.QUANTITY ) QUANTITY,
a.QUANTITY_UNIT,
'NA' pullin_cable_report,
'NA' conection_report,
'NA' assembly_report,
a.PI_TICODE hydrostec_test,
'NA' calibration_report,
'NA' continuity_test,
'NA' megger_test ,
'NA' HIPOT_test,
nvl(d.status_Eit,'00 - Not received') status_Eit,
'NA' megablock ,
'NA' Elevation_or_compartment,
'NA' certification,
'NA' nr10,
'NA' ex,
'NA' blank_test_report,
'NA' loop_Test_Report
FROM
((
SELECT
ct.LINE_NUMBER,
ct.PROJ_ID,
ct.PROJ_NO,
ct.AREA,
'Hull' AS hull_Or_Topside,
ct.SHOP_DRAWING,
ct.ITEM_TYPE,
ct.QUANTITY,
ct.QUANTITY_UNIT,
pm.PI_TICODE,
pm.PI_SYCODE ,
pm.PI_SSCODE,
pm.PROJECT_NUMBER,
ct.DELETE_FLAG
FROM
CP_TUBING ct
LEFT JOIN PDCS_MCPIPEINFO pm ON
ct.LINE_NUMBER = pm.PI_LINENO
AND ct.PROJ_NO = pm.PROJECT_NUMBER
AND pm.IS_DELETE = 0)a
JOIN
(
SELECT
ct.LINE_NUMBER,
pm.PI_TICODE,
COALESCE(
(
SELECT mr.MR_COMPDATE
FROM (
SELECT pm2.MR_COMPDATE
FROM PDCS_MCRESULT pm2
WHERE pm2.MR_TICODE = pm.PI_TICODE
AND pm2.MR_SICODE = 'MP01'
AND pm2.IS_DELETE = 0
ORDER BY pm2.ID
) mr
WHERE ROWNUM = 1
),'') assembly_Date
FROM
CP_TUBING ct
LEFT JOIN PDCS_MCPIPEINFO pm ON
ct.LINE_NUMBER = pm.PI_LINENO
AND ct.PROJ_NO = pm.PROJECT_NUMBER
AND pm.IS_DELETE = 0)b
ON
a.LINE_NUMBER = b.LINE_NUMBER
LEFT JOIN (
SELECT
ct.LINE_NUMBER,
pm.PI_TICODE,
COALESCE(
(
SELECT mr.MR_COMPDATE
FROM (
SELECT pm2.MR_COMPDATE
FROM PDCS_MCRESULT pm2
WHERE pm2.MR_TICODE = pm.PI_TICODE
AND pm2.MR_SICODE = 'MP02'
AND pm2.IS_DELETE = 0
ORDER BY pm2.ID
) mr
WHERE ROWNUM = 1
),'') hydrostatic_Test_Date
FROM
CP_TUBING ct
LEFT JOIN PDCS_MCPIPEINFO pm ON
ct.LINE_NUMBER = pm.PI_LINENO
AND ct.PROJ_NO = pm.PROJECT_NUMBER
AND pm.IS_DELETE = 0)c
ON
a.LINE_NUMBER = c.LINE_NUMBER
LEFT JOIN
(SELECT
ct.LINE_NUMBER,
pm.PI_TICODE,
CASE
WHEN EXISTS (
SELECT 1
FROM PDCS_MCRESULT pm2
WHERE pm2.MR_TICODE = pm.PI_TICODE
AND pm2.MR_SICODE = 'MP05'
AND pm2.MR_COMPDATE IS NOT NULL
AND pm2.IS_DELETE = 0
) THEN '05 - Assembled Item'
WHEN EXISTS (
SELECT 1
FROM PDCS_MCRESULT pm2
WHERE pm2.MR_TICODE = pm.PI_TICODE
AND pm2.MR_SICODE IN ('MP01', 'MP02', 'MP03', 'MP04')
AND pm2.MR_COMPDATE IS NOT NULL
AND pm2.IS_DELETE = 0
) THEN '02 - Waiting Inspection'
WHEN NOT EXISTS (
SELECT 1
FROM PDCS_MCRESULT pm2
WHERE pm2.MR_TICODE = pm.PI_TICODE
AND pm2.MR_COMPDATE IS NOT NULL
AND pm2.IS_DELETE = 0
) THEN '01 - Assembly not started'
ELSE '01 - Assembly not started'
END AS status_Eit
FROM
CP_TUBING ct
LEFT JOIN PDCS_MCPIPEINFO pm ON
ct.LINE_NUMBER = pm.PI_LINENO
AND ct.PROJ_NO = pm.PROJECT_NUMBER
AND pm.IS_DELETE = 0)d
ON a.LINE_NUMBER = d.LINE_NUMBER)
WHERE a.DELETE_FLAG = 0
AND a.PROJ_ID in (select to_number(regexp_replace(i.name, '[^0-9]', '')) from BASE_DICTIONARY d join base_dictionary_info i on d.id=i.dicid where d.code='P80H614projectweld' AND i.STATE = 1)
<if test="model.projectId != null">
AND a.PROJ_ID = #{model.projectId}
</if>
UNION ALL
SELECT
t.proj_id project_name,
t.project_id PROJECT_ID,
'Topdide' hull_Or_Topside,
t.job_object object_Code,
'NA' SYSTEM_NO,
'NA' SUB_SYSTEM_NO,
t.component_no cable_No,
'EIT' item_type,
to_char ( t.QUANTITY ) QUANTITY,
'SET' quantity_unit,
'NA' pullin_cable_report,
'NA' conection_report,
'NA' assembly_report,
'NA' hydrostec_test,
'NA' calibration_report,
'NA' continuity_test,
'NA' megger_test,
'NA' HIPOT_test,
t.status_type status_eit,
'NA' megablock,
'NA' Elevation_or_compartment,
'NA' certification,
'NA' nr10,
'NA' ex,
'NA' blank_test_report,
'NA' loop_Test_Report
FROM (
SELECT
proj.proj_id,
t.project_id,
t.object_code AS job_object,
t.part_number AS component_no,
t.print_no AS draw_no,
t.quantity,
CASE
WHEN t.pre_pro_state = 0 THEN '00 - Not received'
WHEN t.instal_pro_state = 0 OR t.instal_pro_state = 1 THEN '01 - Assembly not started'
WHEN (t.is_pb = 1 AND t.pb_insp_finish_date IS NULL) THEN '02 - Waiting Inspection'
WHEN t.instal_pro_state = 2 THEN '05 - Assembled Item'
END AS status_type,
ROW_NUMBER() OVER (PARTITION BY t.project_id, t.print_no, t.part_number ORDER BY t.rev DESC) AS rev_max
FROM project_item_electric t
LEFT JOIN proj ON proj.proj_no = t.project_id
WHERE t.state = 1
<if test="model.projectId != null">
AND t.project_id = #{model.projectId}
</if>
) t WHERE t.rev_max = 1
)把每个表名前面都加上ECIMS_AIO.
最新发布